OUTLINE

DURATION:  2 DAYS (16 HOURS)

DIFFICULTY LEVEL: INTERMEDIATE

1. Formula references

Absolute & relative references

References when copying formulas in an array 

2. Grouping and Outlining

Manual Grouping, Auto Outlining

SUBTOTAL, AGGREGATE Functions 

3. Formula Auditing

Tracking Dependents and Precedents

Showing formulas

Error checking and tracing errors 

4. Lookup functions

VLOOKUP Function

Match types-looking up an exact match and looking up a range 

5. IF, IFERROR, SUM & COUNT functions

SUMIF, SUMIFS, COUNTIF, COUNTIFS Functions

IF

IFERROR 

6. Hyperlinks

Types of hyperlinks and uses

Creating, formatting & editing Hyperlinks 

7. Protecting the Worksheet & the Workbook

Protecting the Worksheet and specific ranges

Protecting the Workbook 

8. Excel Lists

What is Autofill?

Customizing an Autofill list 

9. Sorting & Filtering

Working with lists

Applying Sorting and Filtering to a list

Simple sorting

Sorting by multiple columns

Using AutoFilter

Advanced filtering

Using custom filter

Using Advanced Filter

Removing duplicates from data 

10.  An introduction to Table

Function of the Table

Creating the Table

Advance Filtering 

11.  Linking, Combining & Consolidating data

Linking worksheets and workbooks

Combining data from worksheets and workbooks 

12.  Excel charts

Preparing data for graphs and charts

Creating graphs and charts from the Charts Ribbon

Chart styles

Custom chart templates

Copying your chart to PowerPoint