| Topic | Files |
|---|---|
| Introduction to Course Syllabus Review What Excel can do:
Prerequisite knowledge: |
In-class assignment: ---Baseline Project: saving for something special Baseline Project Solution (if you are stuck) |
| ---Headings and footers | |
| ---copying worksheets | |
| ---customized formats | |
| ---quicksum | file: File-8-quicksum-2010 |
| ---subtotals | |
| ---special autofills | |
| ---see formulas (w and w/o goto) | |
| ---arrays (CSE formulas) | file: File-37-CSE |
| ---advanced filters | file: 23-Autofilter-2010 |
| ---conditional formatting | file:
16-conditional-formatting-2010 video: formula-driven conditional formatting file: SalesSummaryFirstQuarter2008 |
| ---VLOOKUP/HLOOKUP | file:
looking-up-data-2010 file: Cust-Name-and-rep-number-no-repname-2010-st file: rep-number-and-name-2010 file: rep-number-no-repname-no-CustName-2010-st |
| ---Text() and Value() | |
| Fixing bad data | file: 34-Bad-Data-2010 |
| Match Function | file: LookingUpData-2010 sheet: IndexOriginal |
| Index/Match | we will revisit index/match several more times |
| Logic, continued cond. format, cont'd |
free tutorial for conditional formatting |
| Custom Sorts | file: custom sorts |
| Dates: formatting, arithmetic | file: 4-Dates-2010 |
| joining text | file: 28-CalculatingText-2010 sheet: JoinOriginal |
| Transpose | file: transpose-2010 |
| counting records combine Sum and If --array formulas revisited --using SUMIFS (update) |
file: 24-CountIf-2010 Update: using SUMIFS adn COUNTIFS for compound conditions (instead of CSE formula) |
| Text functions revisited | file: 26-Textfunctions-2010 |
Data Cleaning
|
|
|
file: compare-2-lists file: instructions for comparing 2 lists creating and using named ranges (useful for compare-2-lists) |
|
|
| What is VBA? | |
| Excel Object Model | |
| Object-oriented programming | |
| VBE | file: first program file: first program finished |
| Recording macros | file: recording macros file: recording macros finished |