How to Create a Pivot Table

Pivot tables allow you to summarize details from a table or chart in Microsoft Excel without having to copy all of the information into a new spreadsheet. Businesses use the pivot table function to quickly create summary tables, without having to spend hours creating a new spreadsheet from scratch.

Take the Excel Deep Dive: Pivot Tables Workshop with Office Ninja on Udemy!

Steps to Create a Pivot Table

Pivot tables are as easy to create as they are to read, and they take virtually no time to put together.  By following this small 5 step plan, you will be able to create and customize a pivot table with ease.

Step 1 – Once you locate the table that you want to summarize, highlight the areas that you want to transfer over.

Step 2 – Open the Pivot Table and Pivot Chart Wizard from the menu. To find it, go to Data, Pivot Table, and Pivot Chart Report. It will automatically open from this point. 

Step 3 – The first item you’ll see is a question that reads “Where is the data that you want to analyze?” Navigate to the option in the wizard that reads ‘Microsoft Office Excel List or Database’ and tick the bubble. The question directly under this option reads, “What kind of report do you want to create?” Select the option that reads ‘Pivot Table’. Once you finish, click ‘Next’.

Step 4 – Step 2 of 3 of the Pivot Table and Pivot Chart Wizard should be open at this point. You will see a question that reads, “Where is the data that you want to use?” If you selected the cells in step 1, then the range reference should appear in the ‘Range’ bar. If you didn’t make the selection it’s fine, you can look back at the spreadsheet and manually type the range in the ‘Range’ bar. When you’re finished, click ‘Next’. If you saved the range, you can ‘Open’ the range value from this option as well.

Step 5 – Step 3 of 3 of the Pivot Table and Pivot Chart Wizard should be open. The question here will read, “Where do you want to put the Pivot Table report?” Microsoft Excel has automatically selected ‘New Worksheet’, however, you can choose ‘Existing Worksheet’ if you wish. When you’ve made your selection, select ‘Finish’.

Customizing Your Pivot Table

After the wizard closes, a blank Pivot Table will be open, but it won’t contain any information. The table will be sectioned into customization regions. To customize each section, simply drag an item from the ‘Pivot Table Field List’ into the selected field in the spreadsheet. This information is available to the right of the chart in the ‘Pivot Table Field List’. In addition, you’ll be able to select more customization options from the floating ‘Pivot Table Toolbar’.

Once you’ve finished, simply save your work and there you have it, your first saved Pivot Table.  If you want more information about how to create Pivot charts check out our Excel Deep Dive: Pivot Tables Workshop with Office Ninja course. If you’re new to Excel, sign up for our Microsoft Excel 2013 Training Tutorial  or our Excel 2010 Intermediate course. For more advanced training, have a look at our Advanced Excel Training – Online Excel Course.