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:

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:

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:

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: