Excel Level 3

Level 3 Excel

Duration: 7.5 hours Cost: $249

1. Streamlining Workflow

• Find / Replace
• Go To
  • Navigate quickly to specific location, but it can do so much more.
• Insert Cut / Copied cells
• Merge cells
• Conditional formatting – Review
• Conditional formatting – Advanced
  • Format cell(s) based the content of other cells
• Data Validation

• Data validation will permit only the type of input you specify

2. Excel Security

• Lock / Unlock cells
• Protect Worksheets
• Protect Workbook structure
• Create password to open/modify Excel workbook

3. Excel Templates

• Excel templates
  • Create custom Excel templates, or replace Excel’s default template with your own

4. Performing Calculations

• Relative / Absolute / Mixed Cell References
• Functions – Advanced
  • =IF()
  • =TEXT()
  • =TRIM()
  • =CONCATENATE()
  • =LEFT(), RIGHT(), MID()
  • =UPPER(), LOWER(), PROPER()
  • =VLOOKUP(), HLOOKUP()
  • =INDEX()/MATCH()
  • =SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS()
  • =DAY(), MONTH(), YEAR()
• Nested Functions
  • Nested IF
  • IF/AND, IF/OR
• Show formulas instead of the resulting values, for troubleshooting
• Circular reference
• Errors in formulas – what do they mean?
  • #REF!
  • #DIV/0!
  • #NAME?
  • #VALUE!
  • #NUM!
  • #N/A
  • #NULL!
  • Use IFERROR() for better error handling
• Linking data from other worksheets
  • Edit link
• Goal Seek – What-If?
  • Save hours with this powerful tool
• Use Sparklines to analyze your data

5. Sort & Filter

• Excel as a Database (fields & records) – How to structure an effective database
• Freeze rows & columns
• Split Window to view multiple portions of the workbook simultaneously
• Sort
• Excel is not limited to sorting alphabetically / numerically
• AutoFilter

6. Tables, PivotTables, Charts

• Format as a table
  • Learn the advantages of using Excel’s Table feature
• Pivot Tables – Powerful tool to summarize and analyze large amounts of data
• Pivot Charts
• Slicers
• Charts

7. Mail Merge with MS Word

• Use MS Word to create address labels, personalize a form letter, and more, using Excel as its data source.

8. Basic Macros

• Record a macro
• Run a macro from a keyboard shortcut
• Run a macro from a button or graphic control
• Edit a macro