In this tutorial, you'll learn how to create a pivot table in Excel. You can easily. What were the total sales in the South region in 2016? What are the top five.
Note: Your data shouldn't have any empty rows or columns. It must have only a single-row heading.
Select Insert PivotTable. Under Choose the data that you want to analyze, select Select a table or range. In Table/Range, verify the cell range.
Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear. Building out your PivotTable. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane. Before you get started:. Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an like in our example above. Tables are a great PivotTable data source, because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List.
Otherwise, you need to either, or use a. Data types in columns should be the same. For example, you shouldn't mix dates and text in the same column. PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way. Create a PivotTable If you have limited experience with PivotTables, or are not sure how to get started, a Recommended PivotTable is a good choice.
When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the PivotTable. This helps give you a starting point for additional experimentation.
After a recommended PivotTable is created, you can explore different orientations and rearrange fields to achieve your specific results. You can also download our interactive tutorial.
Recommended PivotTable Manually create a PivotTable. Click a cell in the source data or table range.
Go to Insert Recommended PivotTable. Excel analyzes your data and presents you with several options, like in this example using the household expense data. Select the PivotTable that looks best to you and press OK. Excel will create a PivotTable on a new sheet, and display the PivotTable Fields List. Click a cell in the source data or table range. Go to Insert PivotTable. If you're using Excel for Mac 2011 and earlier, the PivotTable button is on the Data tab in the Analysis group.
Excel will display the Create PivotTable dialog with your range or table name selected. In this case, we're using a table called 'tblHouseholdExpenses'. In the Choose where you want the PivotTable report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, select the cell where you want the PivotTable placed. Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.
In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.
PivotTable Fields list Corresponding fields in a PivotTable. Summarize by By default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT.
This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Field Settings option. Next, change the calculation in the Summarize by section.
Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like 'Sum of FieldName', but you can change it. If you click the Number. Button, you can change the number format for the entire field. Tip: Since the changing the calculation in the Summarize by section will change the PivotTable field name, it's best not to rename your PivotTable fields until you're done setting up your PivotTable. One trick is to click Replace ( on the Edit menu) Find what ' Sum of', then Replace with leave blank to replace everything at once instead of manually retyping.
Show data as Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a% of Grand Total instead of the sum of the values.
Once you've opened the Field Settings dialog, you can make your selections from the Show data as tab. Display a value as both a calculation and percentage. Simply drag the item into the Values section twice, right-click the value and select Field Settings, then set the Summarize by and Show data as options for each one. In the PivotTable Fields area at the top, select the check box for any field you want to add to your PivotTable.
By default, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance. PivotTable Fields list Corresponding fields in a PivotTable.
Summarize Values By By default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Value Field Settings option.
Next, change the calculation in the Summarize Values By section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like 'Sum of FieldName', but you can change it. If you click the Number Format button, you can change the number format for the entire field. Tip: Since the changing the calculation in the Summarize Values By section will change the PivotTable field name, it's best not to rename your PivotTable fields until you're done setting up your PivotTable.
One trick is to use Find & Replace ( Ctrl+H) Find what ' Sum of', then Replace with leave blank to replace everything at once instead of manually retyping. Show Values As Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a% of Grand Total instead of the sum of the values. Once you've opened the Value Field Setting dialog, you can make your selections from the Show Values As tab. Display a value as both a calculation and percentage. Simply drag the item into the Values section twice, then set the Summarize Values By and Show Values As options for each one.
Insert a Pivot Table To insert a pivot table, execute the following steps. Click any single cell inside the data set. On the Insert tab, in the Tables group, click PivotTable. The following dialog box appears.
Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.
Drag fields The PivotTable Fields pane appears. To get the total amount exported of each product, drag the following fields to the different areas. Product field to the Rows area.
Amount field to the Values area. Country field to the Filters area.
Below you can find the pivot table. Bananas are our main export product.
That's how easy pivot tables can be! Sort To get Banana at the top of the list, sort the pivot table. Click any cell inside the Sum of Amount column. Right click and click on Sort, Sort Largest to Smallest.
Filter Because we added the Country field to the Filters area, we can filter this pivot table by Country. For example, which products do we export the most to France? Click the filter drop-down and select France. Apples are our main export product to France. Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of specific products.
Change Summary Calculation By default, Excel summarizes your data by either summing or counting the items. To change the type of calculation that you want to use, execute the following steps. Click any cell inside the Sum of Amount column.
Right click and click on Value Field Settings. Choose the type of calculation you want to use. For example, click Count. 16 out of the 28 orders to France were 'Apple' orders.
Two-dimensional Pivot Table If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot table. Next, to get the total amount exported to each country, of each product, drag the following fields to the different areas. Country field to the Rows area. Product field to the Columns area.
Amount field to the Values area. Category field to the Filters area.
Below you can find the two-dimensional pivot table. To easily compare these numbers, create a and apply a filter. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful pivot table features Excel has to offer.