Intermediate Excel Course

Enhancing the values e.g. percentage of another column

This is a preview lesson

Purchase this course, or sign in if you’re already enrolled, to take this lesson.

Although most people can do a simple SUM or COUNT in a Pivot Table, for some reason they haven’t realised that you can also do a whole lot more. In fact most of the calculations you do outside the pivot table area (% of the total, cumulative values, growth from one period to the next) can all be done inside the Pivot Table.

Show Value – Percent of Grand Total, Rows or Columns

To follow along in your Excel Download File go to 11.3

The key lessons from this video are:

  • Look at the Show Value As tab for many new options in enhancing the values.
  • You can calculate what percent a number is the grand total, row or column.

Percent of the previous item (e.g. month) or a set item (e.g. benchmark)

To follow along in your Excel Download File go to 11.4

The key lessons from this video are:

  • For the percent of option, you need to choose the Base Field to use and the Base Item
  • Base field is which field it must use and it must be one of the fields in the Rows or Columns of the pivot table
  • Base Item is what is used in the percentage i.e. percent of what, the denominator
    • You can choose the previous or next options, espically when using dates
    • You can also choose one of the items to act as the benchmark.

Percent of the parent row (row, column and total)

To follow along in your Excel Download File go to 11.5

The key lessons from this video are:

  • This option allows you to see a hierachy of the percentage of fields. So each breakdown on a percentage basis will be shown
  • If your option doesn’t make sense try the other one. So if you choose the row option, try column. It is not always easy to know what Excel means with row and column so just try both and see which one gives you the answer.

Difference From

To follow along in your Excel Download File go to 11.6

The key lessons from this video are:

  • This option is very useful to see month on month growth in a data set
  • With the Difference From you need to choose hte Base Field and Base Item
  • Base Field is the primary field you want to use and is one of the fields in the rows or columns
  • Base Item is what is used as the subtractor
    • you can use a relative indicator i.e. previous or next
    • you can specify a single item e.g. a benchmark which must be subtracted from all items

Running Totals (e.g. Cumulative year to date)

To follow along in your Excel Download File go to 11.7

The key lessons from this video are:

  • This option allows you to create a running total in a field.
  • Best use is for calculating a cumulative year to date field.
  • You need to specify the Base Field (not the base item in this case)
    • the Base Field is one of the fields in the Row or Column part of your pivot

Rank largest to smallest and smallest to largest

To follow along in your Excel Download File go to 11.8

The key lessons from this video are:

  • This option allows you to rank items.
  • You need to specify the Base Field (not the base item in this case)
    • the Base Field is one of the fields in the Row or Column part of your pivot

Index

To follow along in your Excel Download File go to 11.9

The key lessons from this video are:

  • This option allows you to assess the importance of a number to a matrix
  • Based on the following formula
    • ((value in cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))
0 of 136 lessons complete (0%)
0
    0
    Your Cart
    Your cart is emptyReturn to Shop