What is a PivotTable?
A PivotTable is feature in Excel that allows you to summarize, organize, and analyze your data. In many cases, it is difficult to analyze your data when you are looking at the raw data in columnar format, especially if you are working with large amounts of data. A PivotTable quickly and easily aggregates the data for you in a format that is easy to read and even easier to draw conclusions.
Raw Data in Columnar Format:
Same Data in a PivotTable:
Preparing Your Data for the PivotTable
The first thing to do with your data is to ensure that every column has a unique name. Duplicate column names are not allowed. Second, make sure that there are no blank rows or columns. Third, remove any totals or subtotals since the PivotTable will automatically create them. It is then HIGHLY recommended to convert your data to a table. If you convert your data to a table, then you will never have to worry about updating the source of your PivotTable when the data grows or shrinks. To convert your data to a table:
- Click any cell within the data
- Click on Insert -->Table
- Ensure that the checkmark is on for “My table has headers"
- Click Ok
You should now notice a new ribbon called “Table Design”. You can click on this ribbon and change the properties of the table including Name, Style, whether the Total Row is on, etc. Also, notice that Filter Buttons are automatically turned on. Whenever you need to add data, you can just add it to the bottom of the table and the table grows.
Creating a PivotTable
Once you are ready to summarize the data as a PivotTable, you can click within the data and select Insert --> PivotTable…and click OK.
Building a PivotTable
To build a PivotTable, you need to drag the fields that you want to aggregate into one of the four areas: Filters, Columns, Rows, or Values.
In this example, we dragged the PRODUCTID field into the Rows, and the GROSSAMOUNT, NETAMOUNT, and TAXAMOUNT fields into the Values. Which then produced the PivotTable shown below.
If you did not like the results, you can keep dragging fields around until you get the desired results. You can also remove the checkmark on the field to remove the field from the PivotTable.
The Areas of a PivotTable
There are four areas of a PivotTable: Filters, Columns,
Rows, and Values. Each area is very different. Whatever fields you put in the
Filters area will appear in row 1 of the worksheet and allows you to filter the PivotTable data.
In this example, we dragged the EMPLOYEEID field into the Filters area. Whatever fields you add into the Columns area will appear across the top of the PivotTable. (In this example, we did not add anything into the Columns area, so it automatically added the fields from the Values area.) Whatever fields you add into the Rows area will appear down the left of the PivotTable (for example, PRODUCTID). Whatever fields you add into the Values area will appear in the center of the PivotTable (for example: GROSSAMOUNT, NETAMOUNT, TAXAMOUNT).
Refreshing the Data
Pivot tables will need to be refreshed whenever
the data is changed or updated. You can easily refresh it by right+clicking
anywhere in the PivotTable and selecting
Refresh You can also update
ALL pivot tables by using the Refresh All button found underneath the Refresh
button on the PivotTable Analyze ribbon.
Conclusion
You've now taken the first steps towards working smarter,
not harder, with Excel PivotTables. If you're ready to learn more, check out
our
PowerPivot course. We can customize the training for your team and deliver it online
or in-person. We also offer a wide variety
Microsoft training, including Excel Power Query, Power BI, Microsoft Fabric, and more, for all skill levels.
Written by Holly French, MOS Master
Holly trains all levels of the Microsoft & Adobe
products including Microsoft 365, SharePoint Online, Teams, Power BI, Power
Automate, Planner, and more. Holly is
also a MOS (Microsoft Office Specialist) Master.
