When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing your data and allowing you to manipulate it in different ways.
Optional: Download our practice workbook.
Watch the video below to learn more about PivotTables.
Consider the example below. Let's say we wanted to answer the question What is the amount sold by each salesperson? Answering it could be time consuming and difficult; each salesperson appears on multiple rows, and we would need to total all of their different orders individually. We could use the Subtotal command to help find the total for each salesperson, but we would still have a lot of data to work with.
Fortunately, a PivotTable can instantly calculate and summarize the data in a way that will make it much easier to read. When we're done, the PivotTable will look something like this:
Once you've created a PivotTable, you can use it to answer different questions by rearranging—or pivoting—the data. For example, let's say we wanted to answer What is the total amount sold in each month? We could modify our PivotTable to look like this:
Just like with normal spreadsheets, you can sort the data in a PivotTable using the Sort & Filter command on the Home tab. You can also apply any type of number formatting you want. For example, you may want to change the number format to Currency. However, be aware that some types of formatting may disappear when you modify the PivotTable.
If you change any of the data in your source worksheet, the PivotTable will not update automatically. To manually update it, select the PivotTable and then go to AnalyzeRefresh.
One of the best things about PivotTables is that they can quickly pivot—or reorganize—your data, allowing you to examine your worksheet in several ways. Pivoting data can help you answer different questions and even experiment with your data to discover new trends and patterns.
So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns, you'll need to add a field to the Columns area.
Changing a row or column can give you a completely different perspective on your data. All you have to do is remove the field in question, then replace it with another.
Once you're comfortable with PivotTables, check out our Doing More with PivotTables lesson for even more ways to customize and manipulate them.