Skip to main content
select insert and pivot table

How do you create a pivot table in Excel? 7 easy steps!

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

excel table that will be pivoted

How to create the table

Step 1 : Select any cell in the table that u want to pivot. Then click on Insert -> Pivot table.

select insert and 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.

select range and new worksheet

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.

excel pivot table fields

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.

excel pivot table fields filled in

Using Filters

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.

 

excel pivot table fields with filter

Step 5: Click on the drop-down box and select “Brussels“.

excel pivot table fields with filter result

Step 6: Now we will only see data from cities within “Brussels“.

excel pivot after filtering on region "brussel"

 

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.

 

Leave a Reply

%d bloggers like this: