Step-by-step tutorial for making and editing a pivot table in Excel
This article was co-authored by wikiHow staff writer, Kyle Smith. Kyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
wikiHow marks an article as reader-approved once it receives enough positive feedback. This article has 20 testimonials from our readers, earning it our reader-approved status.
This article has been viewed 2,132,130 times.
Learn more...
Trying to make a new pivot table in Microsoft Excel? The process is quick and easy using Excel's built-in tools. Pivot tables are a great way to create an interactive table for data analysis and reporting. Excel allows you to drag and drop the variables you need in your table to immediately rearrange it. This wikiHow guide will show you how to create pivot tables in Microsoft Excel.
Things You Should Know
- Go to the Insert tab and click "PivotTable" to create a new pivot table.
- Use the PivotTable Fields pane to arrange your variables by row, column, and value.
- Click the drop-down arrow next to fields in the pivot table to sort and filter.
Steps
Building the Pivot Table
-
1Open the Excel file where you want to create the pivot table. A pivot table allows you to create tabular reports of data in a spreadsheet. You can also perform calculations without having to input formulas.
- You can also create a pivot table in Excel using an outside data source, such as an Access database.
-
2Highlight the cells you want to make into a pivot table. Note that the original spreadsheet data will be preserved. Skip this step if you're going to make the pivot table using an external source of data.
- Make sure your data is formatted correctly. To create a pivot table, you'll need a dataset that is organized in columns. It should have a single header row.
- Optionally, formatting your original data as a table using Insert > Table will help make sure the formatting is correct.
Advertisement -
3Go to the Insert tab and click PivotTable. This will open a new window for creating the pivot table.
- If you are using Excel 2003 or earlier, click the Data menu and select PivotTable and PivotChart Report.
- If you're using an external source of data, click the drop-down arrow under PivotTable and select From External Data Source. Then click Choose Connection in the new window.
-
4Select the location for your pivot table and click OK. This will place the new pivot table in the selected location. By default, Excel will place the table on a new worksheet, allowing you to switch back and forth by clicking the tabs at the bottom of the window. You can also choose to place the pivot table on the same sheet as the data, which allows you to pick the cell where you want it to be placed.
- You can later delete the pivot table without losing its data if needed.
Configuring the Pivot Table
-
1Click the checkbox next to fields you want in the PivotTables Fields pane. This adds the field to your pivot table. Note that fields are what Excel calls the variables in your dataset, based on the headers in the header row.[1]
- Clicking the checkboxes automatically adds the field to a section of the pivot table. Non-numeric fields are placed in the rows section, dates are placed in the columns section, and numeric fields are placed in the values section.
- You can rearrange the fields by dragging them to a different section.
-
2Add or move a row field. Drag a field from the field list on the right onto the "Row" section of the pivot table pane to add the field to your table.
- For example, your company sells two products: tables and chairs. You have a spreadsheet with the number (Sales) of each product (Product Type) sold in your five stores (Store).
- Drag the Store field from the field list into the Row Fields section of the pivot table. Your list of stores will appear, each as its own row.
-
3Add a value field. Drag a field from the field list on the right onto the "Values" section of the pivot table pane to add the field to your table. The values will be calculated and organized based on the rows and columns you select.
- Continuing our example: Click and drag the Sales field into the Value Fields section of the pivot table. You will see your table display the sales information for each of your stores.
-
4Add or move a column field. Drag a field from the field list on the right onto the "Column" section of the pivot table pane to add the column field to your table. This is used to separate your data by different categories or dates.
- Continuing our example, you could move Product Type to the columns section to see the product sales data for each store.
-
5Add multiple fields to a section. Pivot tables allow you to add multiple fields to each section, displaying more information on the table or further subdividing the data.
- Using the above example, say you make several types of tables and several types of chairs. Your data notes whether the item is a table or chair (Product Type), but also the exact model of the table or chair sold (Model).
- Drag the Model field onto the Column Fields section. The columns will now display the breakdown of sales per model and overall type. You can change the order that these labels are displayed by clicking the arrow button next to the field in the boxes in the lower-right corner of the window. Select Move Up or Move Down to change the order.
-
6Change the way data is displayed. You can change the way values are displayed by following these steps:
- Click the arrow icon next to a value in the Values box.
- Select Value Field Settings to change the way the values are calculated.
- For example, you could display the average value instead of a sum.
- You can add the same field to the Value box multiple times to take advantage of this. In the above example, the sales total for each store is displayed. By adding the Sales field again, you can change the value settings to show the second Sales as a percentage of total sales.
-
7Learn some of the ways that values can be manipulated. When changing the ways values are calculated, you have several options to choose from depending on your needs.
- Sum - This is the default for value fields. Excel will total all of the values in the selected field.
- Count - This will count the number of cells that contain data in the selected field.
- Average - This will take the average of all of the values in the selected field.
-
8Click the drop-down arrow next to a field to add a filter and sort data. Note that this is next to the field header in the pivot table, not in the pivot table editor pane. Adding a filter allows you to display only certain data given the criteria you select. Sorting the data changes the order that it appears in the pivot table.
Using the Pivot Table
-
1Update your pivot table. Your pivot table will automatically update as you modify the data in the spreadsheet. This can be great for monitoring your spreadsheets and tracking changes.
- You can also manually update the pivot table by clicking Refresh in the PivotTable Analyze tab.
-
2Change your pivot table around. Pivot tables are easy to edit using the editing pane. Try dragging different fields to different locations to come up with a pivot table that meets your exact needs.
- This is where the pivot table gets its name. Moving the data to different locations is known as "pivoting" as you are changing the direction that the data is displayed.
-
3Create a pivot chart. You can use a pivot chart to show dynamic visual reports. Your Pivot Chart can be created directly from your completed pivot table.
- Click PivotChart in the "Tools" section of the PivotTable Analyze tab to make a pivot chart.
Expert Q&A
-
QuestionI have created pivot tables in Excel 2020. I have selected in Design Totals to show Column and row totals but row totals will not show. Any advice please?
Kyle SmithKyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
wikiHow Technology WriterYou can try using the PivotTable Options menu to resolve this issue. Go to the PivotTable Analyze tab > click the drop-down arrow under the PivotTable button > click Options > go to the Totals & Filters tab > check the box next to "Show grand totals for rows." -
QuestionHow do I change a name in a field/column in an existing pivot table without changing the field before/after the field requiring the update?Kyle SmithKyle Smith is a wikiHow Technology Writer, learning and sharing information about the latest technology. He has presented his research at multiple engineering conferences and is the writer and editor of hundreds of online electronics repair guides. Kyle received a BS in Industrial Engineering from Cal Poly, San Luis Obispo.
wikiHow Technology WriterYou can change a field name by right-clicking a value of the field in the pivot table > select Field Settings > and enter a new name in the "Custom Name" section. -
QuestionWhy doesn't my pivot table show the changes I made to the base file?
Community AnswerThere could be couple of reasons: the base file could be missing from original location, or you did not save the changes properly in the base file. Also, remember to save your pivot table before you can expect to see any changes reflected.
wikiHow Video: How to Create Pivot Tables in Excel
Warnings
- If you are using data in an existing spreadsheet, make sure that the range that you select has a unique column name at the top of each column of data.⧼thumbs_response⧽
References
About This Article
A pivot table is an interactive table that lets you group and summarize data in a concise, tabular format. To create a pivot table, click the Insert tab, and then click the PivotTable icon on the toolbar. You can enter your data range manually, or quickly select it by dragging the mouse cursor across all cells in the range, including the labeled column headers. Or, if the data is in an external database, select Use an external data source, and then choose that database and range. Your new pivot table will be placed on the active worksheet by default, but you can change the sheet name and range under ""Existing Worksheet"" to put it elsewhere, or select New Worksheet to place it on its own brand new sheet. Click OK to place your pivot table on the selected sheet. You'll use the Pivot Table Fields bar on the right to lay out your table in columns and rows. Drag fields to the Columns and Rows areas, and then drag fields that represent values to the Values area. Adding fields to the Filters area lets you filter your table by the type of data in that field. You can add multiple data fields to any of these sections, and move things around until they look the way you'd like. Once you've created your table, you can click the PivotTable Analyze tab to view and manage more settings, or the Design tab to customize its color and style.
