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
,Building a PivotTable Report – Part Six ................................................................................................... 37
Displaying multiple row labels in columns, or tabular form. .............................................................. 37
Other Cool Things to do with a Pivot Table – Part Seven ....................................................................... 39
Report Filters....................................................................................................................................... 39
Report Slicers ...................................................................................................................................... 40
Expanding Filter Results to Individual Tabs ........................................................................................ 41
Formatting as a Table - Part Eight ........................................................................................................... 41
2
,Formulas
VLOOKUP
The VLOOKUP function searches vertically (top to bottom) the leftmost column of a table until a value that
matches or exceeds the one you are looking up is found.
The elements being looked up must be unique and must be arranged or sorted in ascending order; that is,
alphabetical order for text entries, and lowest-to-highest order for numeric entries.
The syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).
An example of the formula is: VLOOKUP(E2,D2:M3,2,TRUE) The English translation is using the value found in the
cell E2, look in the range of D2 to M3 row by row. If you find a value that matches or exceeds the value in E2,
using that row, go over 2 columns to the right, grab the value there and bring it back.
There are two range_lookup argument options; TRUE or FALSE
TRUE
Is the default answer, so you may leave it out of the formula
Looks for an approximate match
If it finds an exact match it will use it.
If it doesn’t find an exact match, it will use the last item before it got greater
Alphabetical: Looking for Cat. If elements are Apple, Bird, Carpet, Dog; then Carpet
would be returned because Dog exceeds Cat alphabetically.
Numeric: Looking for 5.25. If elements are 3.0, 4.0, 5.0, 6.0, 7.0, then 5.0 would be used.
The last number before 5.25 was exceeded.
FALSE
Looks for an exact match.
If it finds an exact match it will use it.
If it doesn’t find an exact match, it will return #N/A
Alphabetical: Looking for Cat. If elements are Apple, Bird, Carpet, Dog; then #N/A would
be returned.
Numeric: Looking for 5.25. If elements are 3.0, 4.0, 5.0, 6.0, 7.0, then #N/A would be
returned because there is no exact match.
3
, COUNTIFS
Recall quickly the COUNT and IF commands.
COUNT
The COUNT function counts the number of cells that contain numbers and counts numbers within the
list of arguments.
The syntax is COUNT( value1, value2, …)
Continuing on with our SUM formula from above, let’s not only add up the values of the range A1:A4,
but let’s count how many numbers are included within the range, i.e. how many cells within the range
has a value in it.
The formula is =COUNT(A1:A4). The English translation is count how many cells within the range has a
value in it and display the result.
Notice that the range is exactly the same as our
SUM, A1:A4, which includes four rows. The value
returned in cell A7 is three, because only three of
the four rows have values in them.
4
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
,Building a PivotTable Report – Part Six ................................................................................................... 37
Displaying multiple row labels in columns, or tabular form. .............................................................. 37
Other Cool Things to do with a Pivot Table – Part Seven ....................................................................... 39
Report Filters....................................................................................................................................... 39
Report Slicers ...................................................................................................................................... 40
Expanding Filter Results to Individual Tabs ........................................................................................ 41
Formatting as a Table - Part Eight ........................................................................................................... 41
2
,Formulas
VLOOKUP
The VLOOKUP function searches vertically (top to bottom) the leftmost column of a table until a value that
matches or exceeds the one you are looking up is found.
The elements being looked up must be unique and must be arranged or sorted in ascending order; that is,
alphabetical order for text entries, and lowest-to-highest order for numeric entries.
The syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).
An example of the formula is: VLOOKUP(E2,D2:M3,2,TRUE) The English translation is using the value found in the
cell E2, look in the range of D2 to M3 row by row. If you find a value that matches or exceeds the value in E2,
using that row, go over 2 columns to the right, grab the value there and bring it back.
There are two range_lookup argument options; TRUE or FALSE
TRUE
Is the default answer, so you may leave it out of the formula
Looks for an approximate match
If it finds an exact match it will use it.
If it doesn’t find an exact match, it will use the last item before it got greater
Alphabetical: Looking for Cat. If elements are Apple, Bird, Carpet, Dog; then Carpet
would be returned because Dog exceeds Cat alphabetically.
Numeric: Looking for 5.25. If elements are 3.0, 4.0, 5.0, 6.0, 7.0, then 5.0 would be used.
The last number before 5.25 was exceeded.
FALSE
Looks for an exact match.
If it finds an exact match it will use it.
If it doesn’t find an exact match, it will return #N/A
Alphabetical: Looking for Cat. If elements are Apple, Bird, Carpet, Dog; then #N/A would
be returned.
Numeric: Looking for 5.25. If elements are 3.0, 4.0, 5.0, 6.0, 7.0, then #N/A would be
returned because there is no exact match.
3
, COUNTIFS
Recall quickly the COUNT and IF commands.
COUNT
The COUNT function counts the number of cells that contain numbers and counts numbers within the
list of arguments.
The syntax is COUNT( value1, value2, …)
Continuing on with our SUM formula from above, let’s not only add up the values of the range A1:A4,
but let’s count how many numbers are included within the range, i.e. how many cells within the range
has a value in it.
The formula is =COUNT(A1:A4). The English translation is count how many cells within the range has a
value in it and display the result.
Notice that the range is exactly the same as our
SUM, A1:A4, which includes four rows. The value
returned in cell A7 is three, because only three of
the four rows have values in them.
4
