In this tutorial, we’ll show you everything that you need to know about how to edit a pivot table in Excel. Usually, after you have created a pivot table, there are some instances where you need to edit it by adding data, removing data, or changing the pivot table data source.
Learn the tricks to edit pivot table in no time with this article, in which we’ll give you an idea of how to do it step-by-step. After this, you may now edit your pivot table for better data analysis.
Pivot Table in Excel
A pivot table in Excel allows you to arrange data in various ways with just a few clicks. It automatically analyzes, summarizes, sorts, and filters a huge amount of data.
As well as, it calculates the total and average of the data and presents the results in a reasonable manner. Apart from that, users can easily compare data from different perspectives.
How to add or insert a pivot table in Excel
Here are the things that you need to follow when adding a pivot table in Excel. You have to organize your data first. Arrange it in tabular format with a header. It will be easier for you to select your data when adding a pivot table.
- Select any cells in your current worksheet.
- Go to “Insert tab” in the menu bar.
- Click “Tables,” and it will show the list of tables.
- Select “From Pivot Table/Range.” A dialog box will pop up.
- In “PivotTable from or range,” dialog box you can select the range of that you wanted to use and choose where you want to put your pivot table, either in a new worksheet or an existing worksheet.
- 7. When you choose “Existing Worksheet,” you have to click any cell where you want to display your pivot table in your current worksheet, and then it will display in the “Location.”
- 8. But if you choose “New Worksheet,” it will open a new worksheet.
- 9. Don’t forget to check the checkbox “Add this data to the Data Model.”
- 10. Then click “OK.”
This is the result:
Finally, you’re done inserting your pivot table, and now it’s time to add data and edit your pivot table.
Kindly refer to the video below:
How to edit pivot tables in Excel
Now, let’s explore what you should do when updating pivot tables in Excel.
After you added the pivot table to your worksheet, you may now start to edit it. You can add or delete fields, change the calculation type, and change the data format.
To add a field
- Check the fields under “Choose fields to add to report.“
- Or else, simply drag the data in “Drag fields between areas below:” from the Field List to the Rows or Columns area.
To remove or delete a field
- Click on the drop-down arrow next to the field name and select “Remove Field.”
Result: The sum of debt has been successfully removed from your pivot table.
Or you can do:
- Right-click the data, and a context menu will display.
- Select “Remove.” It will automatically remove the data.
To change the calculation or value type
You can also right-click the data and choose “Value Field Settings.”
Another way to change the value type.
- Right-click the data that you want to change its calculation type.
- After you right-click, a context menu will pop up.
- Select “Summarize Values By,” and it will display different value types.
- If you want more value types, just click “More Options.” A “Value Field Settings” dialog box will display.
- Then, click the “OK” button. It will automatically change the value of your selected row of data. Refer to the image below.
- Click on the drop-down arrow at the right side of the field name and select Value Field Settings.
- A “Value Field Settings” dialog box will pop up, and you can choose what value type you want.
- Click the “OK” button. Refer to the image below.
Note: Don’t forget to refresh if you make changes to your data so that tour pivot table will update.
If you make changes to your data, you will need to refresh your pivot table in order to update it. To do this, just click on the Analyze tab and then click on the Refresh button. This will automatically update the pivot table.
Change Pivot Table Data Source
- Click “Analyze,” which in our case is “PivotTable Analyze” because of our worksheet name.
- Select the “Change Data Source” icon or drop-down arrow to select “Change Data Source.”
- 3. A “Move PivotTable” and then in the “Table/Range:” You can choose a range of cells and add another piece of data, either in your current cell or in another worksheet.
- 4. It will show, like in the image below, that you select data by just checking it or by dragging it to the “Drag fields between areas below:”
This step-by-step tutorial on how to edit a pivot table in Excel will help users easily create, remove, and edit their pivot table in less than a minute. Users will definitely save time and increase their efficiency when working by using this guide.
Thank you very much for continuing to read until the end of this article. In case you have more questions, feel free to comment. You can also visit our website for additional information.