COURSE CONTENTS
1.Introduction to Excel
a.Overview of Excel
b.Excel for Beginners
2.Excel Formulas
a.Relative & Absolute Cell References
b.Using IF Formula, Nesting IF Formulas
c.COUNTIF, SUMIF Formulas
d.Excel Text Formulas
e.VLOOKUP formula, INDEX, MATCH Formulas
f.Advanced VLOOKUP Tricks Advanced
g.How to Get Unique Values from a List using Formulas Advanced
h.Date Formulas
i.OFFSET Formula Advanced
j.SUMPRODUCT Formula Advanced
k.SUMIFS Formula, COUNTIFS Formula
l.SMALL, LARGE Formulas Advanced
m.ROWS, COLUMNS Formulas
n.Formula Editing Tips and Tricks
o.Formula Debugging/Auditing
3.Excel Formatting
a.Formatting Paste Special
b.Conditional Formatting
4.Charting
a.How to Select Correct Chart
b.Introduction to Excel Charts
c.Chart Formatting – Tips
d.Bar & Column Chart Formatting
e.Advanced Charting in Excel
f.Dynamic Charts
g.Interactive Charts In Excel Advanced
5.Class Project # 1
6.Working with Tables & Pivot Tables
a.Excel Tables
b.Excel Pivot Tables
c.Pivot Table Tricks
7.Data Filters, Sorting & Data Validation
a.Data Filters & Sorting
b.Data Validation
8.Importing External Data, Exporting Excel Workbooks
a.Importing External Data
b.Export Excel to Other Formats
c.Excel Access Integration
9.Advanced Formulas
a.Named Ranges
b.Using Formula Audit Tools
c.3D References (example: consolidate sales data)
d.Array Formulas - What are they? (Example: MAXIF, MINIF)
e.Circular References - What are they?
f.Various Calculation Modes and How to use them.
g.Formula Errors and how to handle them
10.Macros & VBA
a.Introduction to Macros
11.Class Project # 2
12.Additional Topics
a.Working with Complex Worksheets
13.Excel Dashsboard
a.What is a Dashboard?
b.Dashboard Creation Process
c.Making Excel Dashboards
d.Dashboard Design – Tips & Tricks
|