How to create a Pivot Table in Excel
In this tutorial, we will show you step by step how to create a Pivot table from scratch, but first, what are Pivot Tables to begin with?
For example, imagine having hundreds or even thousands of records in an Excel spreadsheet, but you need to get the answers of your questions fast without having to do any manual work. You may have questions as to which state had the highest sales? Which rep? and so forth.
This is when the Excel Pivot tables come to the rescue! In a few clicks, you will be able to summarize and make sense of your data, without having to do any manual counting or cross-references.
Excel Pivot tables allow you to create very dynamic tables where you decide what goes in each column or row and get quick answers.
Download the FREE Excel File here!
In the following example, we will work with a list of insurance policies by an agent. In there, we have the policy number each agent sold, when was the policy opened, the information of the agent, and then the First name and last name of the new customer with the policy.
Let’s do a simple Excel Pivot table from scratch, always think of the answer you want from the Pivot table before you click in Excel. If it helps to write it down or draw it for visual reference, go for it.
In this case, we are very curious to know which state and month gave us the most sold insurance policies, perhaps with this information our team could strategize on our marketing more wisely, so let’s begin!
- Clean up the raw data in your Excel spreadsheet:
- First label your columns
- Remove any empty rows
- Check for duplicate rows if applicable
2. Convert the raw data into a table by selecting anywhere inside the data and select CTRL+T. Excel will let you create a Pivot table if you skip this step, but we highly recommend it:
- The table’s advanced filters will let you find any information quickly and accurately.
- If you add new information to the table and refresh the Pivot table, the new information will be included.
- You can export the table easily to a SharePoint list.
The next dialog appears to confirm the cell locations, check “My table has headers” and click “OK.”
4.) Again, click anywhere inside your newly created table and click in the “Insert” tab >> “Pivot Table”
5.) Leave the default Table1 selection, then add a New Worksheet which you can rename later, click “OK”
6.) In the next step, the Pivot table editor appears.
7.) Notice that the columns are now called “Fields” which is a traditional term when working with data and database applications.
8.) The Excel Pivot Table above answered our first question: which Branch had the most sales? Notice that Texas or TX has the higher sales.
9.) Now what if we want to know who was the top agent in Texas? In this case, just check the “Agent” checkbox and look at the new Pivot Table:
10.) Check again the table, we renamed the columns by double-clicking on them and entering “Branch” instead of “Row Labels.”
11.) “Sum of Policy Amount” sounded a little too long, we renamed it “Sales by Branch”
12.) You can also format the Pivot table’s fonts, colors and formatting. We also changed the format of the sales column by changing them to Accounting number format in the “Home” tab.
- To finalize this section, let’s pretend that your supervisor wants to see the Agents displayed in Columns instead of views AND see the percentage of sales per agent, based on the Grand total sales, how can you fix this quickest and most efficient way possible?
- Drag the “Agent” field under Columns
- In the Rows section, notice that the “Agent” row disappeared because Excel knows it’s already under columns.
14.) Remember that this is the first part of the request. How can you show the percentages of sales per agent without having to create another column on the original table or start all over?
15.) You can copy/paste Pivot Tables by clicking anywhere inside the Pivot Table, press CTRL + A to select the entire table, then press CTRL + C to copy.
16.) Press any cell below, we selected cell A10 and press “Enter” so now you have the copy.
17.) To finish, click on any of the numbers inside the Pivot table (including Grand totals), right click and select “Show Values as” >> “ % of Grand Total” and notice the other options available depending on your project.
18.) The new Pivot table is ready! We can easily see that Barry Potter was the top agent from the Louisiana branch, generating 30.11% of sales followed by Rebecca from Texas, with 29.47% of sales for the entire agency.
how to create a pivot table in excel 2016
pivot table example
pivot table in excel shortcut
excel pivot table tutorial pdf
how to create a pivot table in excel youtube
what is a pivot table and how does it work
types of pivot tables
pivot tables for dummies excel 365