We were recently invited to provide Excel training for a client—three one-hour sessions with an open-invitation to all employees. Like most organizations, they have people using Excel in a wide variety of situations (e.g. survey analysis, basic reporting, managing lists) all with different skill levels and needs. It seemed a perfect opportunity for us given: 1) our belief that Excel is the most important business intelligence tool in any organization, and 2) our interest in embracing constraints (three hours to teach all that is important in Excel?!)
Which left me with the question: What is the core Excel knowledge required to be an effective data analyst?
We spent a few days trying to boil down the myriad of tips and tricks, functions and formulas to a short list of the most essential items. Before we finished building our teaching worksheets, I wanted to post our current "lesson plan" outline to see if our readers had suggestions on what they find most valuable. Tell us what features you find most important. Point us to online resources or tutorials that will help with parts of our lessons. I’ll incorporate your suggestions and in a couple of weeks we will post our Excel training spreadsheets for download.
Lesson 1: Moving and manipulating data
a. Using keyboarding to work more efficiently, e.g.
- Accessing menu commands with ALT letters
- CTRL (SHIFT) arrows to move through (select) data ranges
- CTRL click to format an object
- CTRL enter to fill an entire highlighted region
- ALT up/down arrow to access drop-down lists
- F2 to toggle in and out of formula and spreadsheet
- SHIFT mouse drag, CTRL mouse drag
b. Functions for data cleaning and manipulation, e.g.
- VLOOKUP(), MATCH()
- Text formulas: LEFT(), RIGHT(), MID(), string concatentation,
- Sum, Ave, Count, etc. on lower left toolbar
- Error checking, e.g. ISERROR()
- Measuring your data: COUNT(), COUNTA(), COUNTIF(), SUBTOTAL()
- Working with dates
- Delimited files
- Find/Replace, using wildcards
- Sorting
c. Formatting data, e.g.
- Cells contain either functions, strings, or numbers
- Strings: Comprised of characters; digits not recognized as numbers
- Numbers: Can be formatted differently to represent dates, phone numbers, time, etc.
- Functions: How functions work and finding the right one (tip: Alt + Enter to break up formulas)
- CTRL 1 takes you to the format menu
- Custom number formats
- Paste special
- Conditional formatting
Lesson 2: Working with Raw Data
a. Flat data tables
- Why this is important and how data tables can be constraining
- Column headers on the first row
- Block of data, no gaps in rows or columns
- Freeze column and row labels
b. Data filters
- ALT-D-F-F, ensure you have the full data list
- Advanced data filtering
- Custom sorting
- Pulling out unique records
c. Names
- Named fields
- Named ranges
- Dynamic ranges using OFFSET() and COUNTA()
d. PivotTables
- How to make one; what is happening behind the scenes
- Using dynamic ranges with PivotTables
- Table options
- Field options
e. Trimming file size
- Pivot table caches
- How formulas take up space
- CTRL end, CTRL home
Lesson 3: Presenting Data
a. Charts
- Tufte principles vs. Excel defaults (and the )
- Chart formatting (lines, fill, points, scale, etc.)
- Two-axis and multi-format charts
- Selecting an individual point/series to format or add a data label
- Dynamic chart titles
- Pasting charts into PowerPoint (do’s and don’ts)
b. Other data presentation tricks
- In-cell charting
- Camera tool
c. Creating dynamic reporting with D-T-P (Data-Transform-Present)
- Separate data, transform, and present tabs to ensure flexibility (value to planning your workbook)
- Using a master pivot table to drive other pivot tables (via a VBA macro)
d. Preparing for sharing
- Improve legibility (gridlines, fonts, colors)
- Protecting cells, sheets, workbooks
- Hiding sheets
- Formatting input/output cells