Teach Yourself Excel Lesson 25 - Create A Pivot Table

Nick's picture


Create A Pivot Table

Download starting Sheet

And now the lesson you've all been waiting for - How to create a pivot table !

A pivot table is an ingenious invention that allows you to slice and dice your data in as many ways as you can think of.

We're going to create 3 pivot tables that are useful for tracking your expenses:

  • A Monthly Summary
    • We can then chart this, and you can visually see how your expenditure changes month by month
  • A Summary By Expense Type
    • We'll then create a pie chart that will make you realise where all the money is going
  • A breakdown of Necessary vs Unnecessary Expenditure
    • This will give you an idea of how much you can cut out

Steps to create A Monthly Summary:

  • Select the Data Worksheet
  • Press CTRL + G
  • Type: A:H   then press OK
  • On your ribbon, select the "Insert" menu
  • Click "Pivot Table" and choose "Pivot Table"
  • Don't change anything on the next screeen.. Press OK
  • You'll now see your pivot table on a new worksheet
  • On the right, you'll see "Year" in the "Pivot Table Field List"
  • Click and drag this down to where it says "Row Labels"
  • Click on it, and choose "Value Field Settings"
  • Under "Subtotals" choose "None"
  • Now drag "Month" just under where you put "Year"
  • Drag "Amount" to where it says "Values"
  • Click on it, and choose "Value Field Settings"
  • Change "Count" to "Sum"
  • Rename the worksheet "Monthly Summary"

It should look like this:

create-a-pivot-table

 

Steps to create a Summary By Expense Type:

  • Right click on the "Monthly Summary" Worksheet tab
  • Choose "Move Or Copy"
  • Click "Create a copy"
  • Press OK
  • In the "Pivot Table Field List", uncheck "Year" and "Month", and check "Expense Type"
  • Rename the worksheet "Summary By Expense Type"

It should look like this:

create-a-pivot-table

Steps to create a A breakdown of Necessary vs Unnecessary Expenditure:

  • Right click on the "Monthly Summary" Worksheet tab
  • Choose "Move Or Copy"
  • Click "Create a copy"
  • Press OK
  • In the "Pivot Table Field List", uncheck "Year" and "Month", and check "Necessary?"
  • Rename the worksheet "Necessary"

It should look like this:

create-a-pivot-table

 

Most Importantly, Note:

  • If you enter more expenses and you want to update the pivot tables, simply right click on the pivot table and choose "Refresh"

Your sheet should now look like this

Next Lesson: Teach Yourself Excel Lesson 26 - Create A Chart

Training Video on how to Create A Pivot Table in Excel:

AttachmentSize
create-a-pivot-table.xls27 KB

Making calculations outside a Pivot table

How can you calculate diferences between columns (years) in a column to the right of a pivot table; I tried naming yr2011 minus Yr2010 but when an additional column added (ie another month, the calculation is wrong.
This is when new data is added to the datasource. Column headers are
2009, 2010, 1,2,3,4,5,6,7,8,9 Total2011, diff2011-2010, %diff.
so when you add data for month 10, all the formula in last 2 column must be re-entered.

Add Calculated field

Select the PivotTable.
Press Alt+P
select Formulas
Choose Calculated Field.
.. then add your calculated field