Pabok
In Excel, Pivot Tables enable you to aggregate and arrange data from big data sets so as to get summary information. You will have to update a pivot table after you’ve created it. This tutorial will explain the way to edit a pivot table with the source data, columns, rows, and layouts in Excel. If you create any changes to your Pivot Table’s data, you will need to refresh it to look at the changes.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
My Latest Video
5 Different Ways to Edit a Pivot Table
Assume you have a dataset including some ordered items, their unit pricing, quantities, and expenses. Furthermore, as seen in the picture below, you have previously developed a pivot table to analyze and build a relationship with many factors. Now, we will edit the table by changing source data, adding rows/columns, and rearranging the appearance.
1. Change Data Source to Edit a Pivot Table
In the image below, you can see our data source table. From there, we’ll construct a pivot table and edit it to include new data.
Your pivot table will look like the image below once you’ve created it using the aforementioned dataset. For instance, suppose you wish to update the pivot table. For example, suppose you want to change the number 6 to 12. Follow the instructions below to learn it!
Step 1:
- First of all, change the value 6 to 12 in the data source table.
Step 2:
- Just click a cell in your pivot table. Your pivot table toolbar will be activated.
- Then, click on PivotTable Analyze from the toolbar.
- Select Change Data Source.
Step 3:
- After that, select the table in range B4:G12.
- Press Enter.
Step 4:
- Finally, click Refresh to make an update in the pivot table.
As a result, you can visualize the change in cell D5 in the pivot table.
Read More: How to Edit Chart Data in Excel (5 Suitable Examples)
2. Add a Column/Row to Edit a Pivot Table
2.1 Add a Column
For an extra parameter, you may need to add a column to your pivot table. You may achieve this by approaching it in the same way as the prior method. Let’s imagine we want to add Date as a new parameter to differentiate when they are purchased.
Step 1:
- From the pivot table toolbar, select PivotTable Analyze.
- Click on Change Data Source.
Step 2:
- To include the Date column, reselect the table in range A4:G12.
- Then, press Enter to add the new table.
Step 3:
- Refresh again to update the table, you will see a new field named date will be added in the PivotTable Fields.
- Consequently, for adding the Date column, changes in the pivot table will be shown like the image below.
2.2 Add a Row
In a Pivot Table, you may add rows in the same way you would add columns. For example, for row 13, you wish to add a new row in a Pivot Table. To complete it, simply follow the procedures discussed in Method 2!
Consequently, you will obtain the new row in a Pivot Table as marked in the below screenshot.
Read More: How to Add New Row Automatically in an Excel Table
3. Select the Displaying Fields to Edit a Pivot Table
You may also alter the way your Pivot Table is displayed. You may simply mark the fields you want to show and unmark the ones you don’t want to show in PivotTable Fields. Notice that, all fields are displayed in the picture below. However, in order to create a more noticeable difference, we now wish to display some specified fields.
Step 1:
- From the PivotTable Fields, just unmark the Date and Discount.
Therefore, you will visualize that Date and Discount options have been omitted here.
Read More: How to Use Calculated Field in Excel Pivot Table (8 Ways)
Similar Readings
- How to Insert A Pivot Table in Excel (A Step-by-Step Guideline)
- Refresh Pivot Table in Excel (4 Effective Ways)
- How to Group Columns in Excel Pivot Table (2 Methods)
- Pivot Table Custom Grouping: With 3 Criteria
- How to Edit a Cell in Excel without Double Clicking (3 Easy Ways)
4. Rearrange Fields to Edit a Pivot Table
For a better organization, you can rearrange the fields among columns, rows, and values. From the Pivot Table, quantity is shown in columns as in the PivotTable Fields it is placed in Values. For a reason, you want to rearrange the quantity as a row.
Steps:
- Drag Quantity from the values and placed it in Rows.
- After dragging to Rows, PivotTable Fields will show as the below image.
- Therefore, in the Pivot Table, you can see that the Quantity field is rearranged in rows.
Read More: How to Get a Count in Excel Pivot Table Calculated Field
5. Customize the Appearance to Edit A Pivot Table
In addition to the previous methods, Microsoft Excel offers to design our layout according to comfort and objective. There are three Report Layout options available.
We will show them one by one in this section.
Steps:
- Go to Pivot Table
- Select
- Click the Report Layout
- Choose any of the three options available.
1. In A Compact Form
Permits to showcase items from several row segment fields in a column.
2. Show in Outline Form
Permits you to use the classic pivot table style to show the pivot table. Each field is shown in one column, with space for field headings. Subtotals can also be displayed at the top of groups.
3. Show in Tabular Form
The pivot table can be displayed in a typical table format. Each field is shown in one column, with space for field headings.
Read More: How to Edit Header in Excel (6 Easy Methods)
Conclusion
To sum up, I hope this article has offered clear instructions on how to edit a pivot table in Excel utilizing various tools. All of these methods should be learned and applied to your dataset. Examine the practice book and put your newly acquired skills to the test. We’re encouraged to keep developing classes like this because of your significant support.
If you have any questions, please do not bother to contact us. Please share your ideas in the comments section below.
Your inquiries will always be acknowledged by the ExcelDemy Team.
Related Articles
- How to Edit a Cell in Excel (4 Easy Methods)
- Create Table in Excel Using Shortcut (8 Methods)
- Insert Table in Excel (2 Easy and Quick Methods)
- How to Make a Table in Excel (With Customization)
- Types of Tables in Excel: A Complete Overview
- Cannot Edit Excel File in Protected View (3 Reasons with Solutions)
