Download ArticleStep-by-step tutorial for making and editing a pivot table in Excel
Download ArticleX
This article was co-authored by How.com.vn staff writer, Kyle Smith. Kyle Smith is a How.com.vn 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.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 2,151,798 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 How.com.vn 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
Open 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.
Highlight 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
Go 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.
Select 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.
Advertisement
Click 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.
Add 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.
Add 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.
Add 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.
Add 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.
Change 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.
Learn 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.
Click 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.
Advertisement
Update 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.
Change 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.
Create 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.
Advertisement
Expert Q&A
Search
- 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 How.com.vn 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.
How.com.vn 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 How.com.vn 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.
How.com.vn 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.
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
SubmitAdvertisement
How.com.vn Video: How to Create Pivot Tables in Excel
Watch
Tips
- In addition to pivot tables, another powerful tool in Excel is the Solver function.Thanks
- If you use the Import Data command from the Data menu, you have more options on how to import data ranging from Office Database connections, Excel files, Access databases, Text files, ODBC DSNs, webpages, OLAP and XML/XSL. You can then use your data as you would an Excel list.Thanks
- If you are using an AutoFilter (Under "Data", "Filter"), disable this when creating the pivot table. It is okay to re-enable it after you have created the pivot table.Thanks
Submit a Tip
All tip submissions are carefully reviewed before being published
Thanks for submitting a tip for review!
Advertisement
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.Thanks
Advertisement
References
About This Article
How.com.vn Technology Writer
This article was co-authored by How.com.vn staff writer, Kyle Smith. Kyle Smith is a How.com.vn 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. This article has been viewed 2,151,798 times.
How helpful is this?
Co-authors: 48
Updated: December 22, 2023
Views: 2,151,798
Categories: Featured Articles | Microsoft Excel
Article SummaryX
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.
Did this summary help you?
Thanks to all authors for creating a page that has been read 2,151,798 times.
Reader Success Stories
"What helped me most with my questions were the sequential steps and explanations on what to do and how to do it. Some sites provide too much information at one time without providing examples on how to get it done."..." more
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
Advertisement
