In this tutorial you will learn what a PivotTable is, find a number of examples showing how to create and use Pivot Tables in all version of Excel 365 through Excel 2007. Show
If you are working with large data sets in Excel, Pivot Table comes in really handy as a quick way to make an interactive summary from many records. Among other things, it can automatically sort and filter different subsets of data, count totals, calculate average as well as create cross tabulations. Another benefit of using Pivot Tables is that you can set up and change the structure of your summary table simply by dragging and dropping the source table's columns. This rotation or pivoting gave the feature its name. What is a Pivot Table in Excel?An Excel Pivot Table is a tool to explore and summarize large amounts of data, analyze related totals and present summary reports designed to:
For example, you may have hundreds of entries in your worksheet with sales figures of local resellers: One possible way to sum this long list of numbers by one or several conditions is to use formulas as demonstrated in SUMIF and SUMIFS tutorials. However, if you want to compare several facts about each figure, using a Pivot Table is a far more efficient way. In just a few mouse clicks, you can get a resilient and easily customizable summary table that totals the numbers by any field you want. The screenshots above demonstrate just a few of many possible layouts. And the steps below show how you can quickly create your own Pivot Table in all versions of Excel. How to make a Pivot Table in ExcelMany people think that creating a Pivot Table is burdensome and time-consuming. But this is not true! Microsoft has been refining the technology for many years, and in the modern versions of Excel, the summary reports are user-friendly are incredibly fast. In fact, you can build your own summary table in just a couple of minutes. And here's how: 1. Organize your source dataBefore creating a summary report, organize your data into rows and columns, and then convert your data range in to an Excel Table. To do this, select all of the data, go to the Insert tab and click Table. Using an Excel Table for the source data gives you a very nice benefit - your data range becomes "dynamic". In this context, a dynamic range means that your table will automatically expand and shrink as you add or remove entries, so won't have to worry that your Pivot Table is missing the latest data.
Useful tips:
2. Create a Pivot TableSelect any cell in the source data table, and then go to the Insert tab > Tables group > PivotTable. This will open the Create PivotTable window. Make sure the correct table or range of cells is highlighted in the Table/Range field. Then choose the target location for your Excel Pivot Table:
Clicking OK creates a blank Pivot Table in the target location, which will look similar to this:
Useful tips:
3. Arrange the layout of your Pivot Table reportThe area where you work with the fields of your summary report is called PivotTable Field List. It is located in the right-hand part of the worksheet and divided into the header and body sections:
The changes that you make in the PivotTable Field List are immediately reflected to your table. How to add a field to Pivot TableTo add a field to the Layout section, select the check box next to the field name in the Field section. By default, Microsoft Excel adds the fields to the Layout section in the following way:
How to remove a field from a Pivot TableTo delete a certain field, you can either:
How to arrange Pivot Table fieldsYou can arrange the fields in the Layout section in three ways:
4. Choose the function for the Values field (optional)By default, Microsoft Excel uses the Sum function for numeric value fields that you place in the Values area of the Field List. When you place non-numeric data (text, date, or Boolean) or blank values in the Values area, the Count function is applied. But of course, you can choose a different summary function if you want to. In Excel 2013 and higher, right-click the value field you want to change, click Summarize Values By, and choose the summary function you want. In Excel 2010 and lower, the Summarize Values By option is also available on the ribbon - on the Options tab, in the Calculations group. Below you can see an example of the Pivot Table with the Average function: The functions' names are mostly self-explanatory:
To get more specific functions, click Summarize Values By > More Options… You can find the full list of available summary functions and their detailed descriptions here. 5. Show different calculations in value fields (optional)Excel Pivot Tables provide one more useful feature that enables you to present values in different ways, for example show totals as percentage or rank values from smallest to largest and vice versa. The full list of calculation options is available here. This feature is called Show Values As and it's accessible by right-clicking the field in the table in Excel 2013 and higher. In Excel 2010 and lower, you can also find this option on the Options tab, in the Calculations group.
Tip. The Show Values As feature may prove especially useful if you add the same field more than once and show, for example, total sales and sales as a percent of total at the same time. See an example of such a table. This is how you create Pivot Tables in Excel. And now it's time for you to experiment with the fields a bit to choose the layout best suited for your data set. Working with PivotTable Field ListThe Pivot Table pane, which is formally called PivotTable Field List, is the main tool that you use to arrange your summary table exactly the way you want. To make your work with the fields more comfortable, you may want to customize the pane to your liking. Changing the Field List viewIf you want to change how the sections are displayed in the Field List, click the Tools button, and choose your preferred layout. You can also resize the pane horizontally by dragging the bar (splitter) that separates the pane from the worksheet. Closing and opening the PivotTable paneClosing the PivotTableField List is as easy as clicking the Close button (X) in the top right corner of the pane.Making it to show up again is not so obvious :) To display the Field List again, right-click anywhere in the table, and then select Show Field List from the context menu. You can also click the Field List button on the Ribbon, which resides on the Analyze / Options tab, in the Show group. Using Recommended PivotTablesAs you have just seen, creating a Pivot Table in Excel is easy. However, the modern versions of Excel take even a step further and make it possible to automatically make a report most suited for your source data. All you have to do is 4 mouse clicks:
As you see in the screenshot above, Excel was able to suggest just a couple of basic layouts for my source data, which are far inferior to the Pivot Tables we created manually a moment ago. Of course, this is only my opinion and I am biased, you know : ) Overall, using the Recommended PivotTable is a quick way to get started, especially when you have a lot of data and are not sure where to start. How to use Pivot Table in ExcelNow that you know the basics, you can navigate to the Analyze and Design tabs of the PivotTable Tools in Excel 2013 and higher, (Options and Design tabs in Excel 2010 and 2007) to explore the groups and options provided there. These tabs become available as soon as you click anywhere within your table. You can also access options and features that are available for a specific element by right-clicking on it. How to design and improve Pivot TableOnce you have created a Pivot Table based on your source data, you may want to refine it further to make powerful data analysis. To improve the table's design, head over to the Design tab where you will find plenty of pre-defined styles. To create your own style, click the More button in the PivotTable Styles gallery, and then click "New PivotTable Style...". To customize the layout of a certain field, click on that field, then click the Field Settings button on the Analyze tab in Excel 2013 and higher (Options tab in Excel 2010 and 2007). Alternatively, you can right click the field and choose Field Settings from the context menu. The screenshot below demonstrate a new design and layout for our Pivot Table in Excel 2013. How to get rid of "Row Labels" and "Column Labels" headingsWhen you are creating a Pivot Table, Excel applies the Compact layout by default. This layout displays "Row Labels" and "Column Labels" as the table headings. Agree, these aren't very meaningful headings, especially for novices. An easy way to get rid of these ridiculous headings is to switch from the Compact layout to Outline or Tabular. To do this, go to the Design ribbon tab, click the Report Layout dropdown, and choose Show in Outline Form or Show in Tabular Form. This will display the actual field names, as you see in the table on the right, which makes much more sense. Another solution is to go to the Analyze (Options) tab, click the Options button, switch to the Display tab and uncheck the "Display Field Captions and Filter Dropdowns" box. However, this will remove all field captions as well as filter dropdowns in your table. How to refresh a Pivot Table in ExcelAlthough a Pivot Table report is connected to your source data, you might be surprised to know that Excel does not refresh it automatically. You can get any data updates by performing a refresh operation manually, or have it refresh automatically when you open the workbook. Refresh the Pivot Table data manually
To refresh all Pivot Tables in your workbook, click the Refresh button arrow, and then click Refresh All.
Note. If the format of your Pivot Table gets changed after refreshing, make sure the "Autofit column width on update" and "Preserve cell formatting on update" options are selected. To check this, click the Analyze (Options) tab > PivotTable group > Options button. In the PivotTable Options dialog box, switch to the Layout & Format tab and you will find these check boxes there. After starting a refresh, you can review the status or cancel it if you've changed your mind. Just click on the Refresh button arrow, and then click either Refresh Status or Cancel Refresh. Refreshing a Pivot Table automatically when opening the workbook
How to move a Pivot Table to a new locationIf you want to move your table to a new workbook, worksheet are some other area in the current sheet, head over to the Analyze tab (Options tab in Excel 2010 and earlier) and click the Move PivotTable button in the Actions group. Select a new destination and click OK. How to delete an Excel Pivot TableIf you no longer need a certain summary report, you can delete it in a number of ways.
Note. If any PivotTable chart is associated with your table, deleting the Pivot Table will turn it into a standard chart can no longer be pivoted or updated. Pivot Table examplesThe screenshots below demonstrate a few possible Pivot Table layouts for the same source data that might help you to get started on the right path. Feel free to download them get a hands-on experience. Pivot Table example 1: Two-dimensional table
Pivot Table example 2: Three-dimensional table
This Pivot Table lets you filter the report by month. Pivot Table example 3: One field is displayed twice - as total and % of total
This summary report shows total sales and sales as a percent of total at the same time. Hopefully, this Pivot Table tutorial has been a good starting point for you. If you want to learn advanced features and capabilities of Excel Pivot Tables, check out the links below. And thank you for reading! Available downloads:Pivot Table examples |