Pivot Tables are a very fast, neat way of analysing sales data like this:
If you would like a formatted example of how it works, you can download our surprisingle inexpensive spreadsheet:
When you swap in your own data, don’t forget to refresh the Pivot Table:
Want to Add a Pivot Table to your own Spreadsheet Sales Analysis?
Pivot Tables have lots of options. They also have lots of ways of achieving the same thing. Here are some step-by-step instructions to get you started:
Get your data into a table with column headings and rows of data, as below.
Highlight the table of data then in the Ribbon click Insert > PivotTable
This will bring up the ‘Create PivotTable’ dialog box
The Select a table or range values will be already filled in. Select ‘Existing Worksheet’ and enter a cell reference below where your data stops, in this case, F25.
This inserts a Pivot Table holder into your Worksheet
Meanwhile, over on the right hand side
A new dialog box opens up showing the column names of your data.
Click and Drag the fields
In our example, drag date into the ‘Rows’ box, drag Salesman into the ‘Columns’ box.
Then drag Qty and Total Price to the Values box.
As you add the fields, the Pivot Table will start to populate.
Refresh the Pivot Table data
When you change the data in the table, you have to manually refresh the Pivot Table to update the data.
You can then format the cells and values in the Pivot Table using the usual buttons in the Home Tab on the Ribbon.
If you would like to see our completed example, it’s complementary!
Want some more tips and examples of how to use Excel to make your work easier?
Subscribe to our Newsletter for examples of Excel Spreadsheets, Business Admin Tips and Databases.
