Excel Advanced
Contents
Formulas ....................................................................................................................................................... 3
VLOOKUP................................................................................................................................................... 3
COUNTIFS .................................................................................................................................................. 4
COUNT ................................................................................................................................................... 4
IF ............................................................................................................................................................ 5
COUNTIF ................................................................................................................................................ 5
COUNTIFS .............................................................................................................................................. 6
Filters............................................................................................................................................................. 7
Ribbon Tour............................................................................................................................................... 7
Quick Filtering ........................................................................................................................................... 7
Filtering by Multiple Criteria ..................................................................................................................... 9
Saving the Filtered Data .......................................................................................................................... 11
Performing Calculations on Filtered Data ............................................................................................... 12
PivotTables .................................................................................................................................................. 13
Defined .................................................................................................................................................... 13
Basic PivotTable Data .............................................................................................................................. 14
Inserting a Pivot Table............................................................................................................................. 14
PivotTable Geography ............................................................................................................................. 15
Building a PivotTable Report – Part One................................................................................................. 16
Adding row labels, adding column data, changing formulas in columns, changing headers & number
formats ................................................................................................................................................ 16
Building a PivotTable Report – Part Two ................................................................................................ 24
Adding multiple row labels, collapsing and expanding, drill down to data, sorting, & refreshing ..... 24
Building a PivotTable Report – Part Three .............................................................................................. 26
Grouping by dates, grouping by ranges, show items with no detail, show values in empty cells,
grouping across columns .................................................................................................................... 26
Building a PivotTable Report – Part Four ................................................................................................ 33
User defined groups, adding/removing subtotals .............................................................................. 33
Building a PivotTable Report – Part Five ................................................................................................. 35
Using formulas on pivoted data .......................................................................................................... 35