Course Outline

Microsoft Excel 2003 Tips & Tricks

A specialized course covering some of Excel’s most useful features. This class is designed for the experienced Excel user. It covers topics ranging from customizing the Excel environment to using named ranges to fixing common problems with data imported from external sources to solving problems with array formulas. Participants will learn several practical techniques, including using custom number formats to solve a variety of everyday problems and using keyboard shortcuts in combination with the pointer to manipulate worksheet data quickly and efficiently. 6 hours

Introduction to custom number formats
  • Understanding how custom number formats work
  • Creating and deleting custom number formats
  • Rounding numbers and aligning decimals
Creating special-purpose toolbars
  • Putting your favorite commands right where you need them
Using named ranges
  • Import an Access table
  • Find and Fix Problem Text Data
  • Find and fix Number Data Problems
  • Importing a Text File into a Worksheet
Working with data problems
  • Identifying and fixing problems with the TRIM and CLEAN functions
  • Filling in the blanks to make table data useable
  • Using the Paste Special Multiply technique to fix numbers stored as text
 Lookups with data validation lists
  • Creating compound VLOOKUP formulas
  • Creating and using items lists to drive data analyses
Power lookups
  • Beyond VLOOKUP with INDEX and MATCH functions
  • Using array formulas for multiple-value lookups
Conditional statistical functions
  • Using COUNTIFS, AVERAGEIFS and SUMIFS to analyze large datasets
Super shortcuts: columns and rows
  • Four simple but amazingly powerful keyboard / pointer combinations for efficient Cut, Copy, Paste and Insert operations
Prerequisites:  Excel Level 2 or equivalent experience.
Revised 6/17/10
Return to Class Schedules             Computer Magic Training Home Page
Computer Magic Training • 4030 Moorpark Avenue • Suite 108 • San Jose, California 95117 • 408-261-2600