Pivot tables are used to summarize by something mathematical – sum, average, Count…
Your data must be layout in a linear format – top row must be field header names and the records of data in rows following immediately underneath. There must be no blank columns or rows, all headings must be labeled.
Correct Data Layout
Incorrect Data Layout
- Ensure your cursor is in the data set
- Go to the Menu for Insert
- Select Pivot Table
- It should have automatically detected the start and finish point of your data set.
- By Default the pivot table is created on a New Worksheet, Press Ok
- You should now see a Pivot Field list to your right.
- Tick the field of what you would like to group by: ie Car Make
- Because it is formatted for text it will be automatically inserted into the row field
- Tick the Monthly Repayment – because it is formatted for number it should go to the Value field. If it does not it will go to the row field. This will happen because the field is formatted for text not number.
- Drag it to the Value area if it does not display “SUM” then it will say Count
- If that happens right mouse click on a figure in the pivot table and select number format.