Excel Foundations File References

Topic Files
Introduction to Course
Syllabus Review
What Excel can do:
  • Demo:  project files
  • Demo:  form controls
  • Demo:  file access (VBA)
  • Demo:  form design (VBA)
Finding sources for Excel
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
---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
  • reviewing string functions
  • more VLOOKUP
  • discuss data collection assignment

  • Logical functions
  • advanced conditional formatting
file:  compare-2-lists
file: instructions for comparing 2 lists
creating and using named ranges (useful for compare-2-lists)
  • Pivot Tables Basics
  • Pivot Table Customizing (Formatting)
  • Pivot Table Grouping, Sorting
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