What is a pivot table?
A pivot table is used to summarize and organize a very large data set. This creates a second table with the result without changing the original data. They are used to sort, count, and total the data. When used the right way the will save you lot’s of time.
Warning: you can’t use the pivot table function when there are empty cell’s. You won’t be able to select all the data.
I will show you a simple example on how to do this. The spreadsheet below tracks the profit that is generated by Coffee shops. Feel free to download the xlsx-file to try the exercises yourself:
Download Pivot table spreadsheet
How to create the table
Step 1 : Select any cell in the table that u want to pivot. Then click on Insert -> Pivot table.
Step 2: The following pop up box will appear. Check if the table range is selected. This should already be filled automatically. Next, choose to show the pivot table in a new worksheet. Click on the ok button to proceed.
Step 3: Choose “PivotTable Fields” on the right side of your screen. This is done by dragging and dropping fields into the areas below. Start off by selecting the appropriate the values for Rows.
Tip: Select the check boxes and let the excel algorithm guess how you want to display your data.
As a result we will see a table with al regions and their total profit. It’s quite amazing how fast we can analyse data using this tool.
Step 4: We can easily create a drop-down list to filter data in our table. You can do this by dragging a field to the Filter area. There will appear a drop-down list on top of our current table.
Step 5: Click on the drop-down box and select “Brussels“.
Step 6: Now we will only see data from cities within “Brussels“.
7. Additional tips
- Experiment with the recommended Pivot Tables option. What are the results?
- You can refresh data from your pivot table by right clicking on any cell from the pivot table on the right side of your screen. Next, select “refresh” from te option menu.