How to Edit a Pivot Table in Excel (5 Methods)

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.


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.

Featured

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.

Change Data Source to Edit a Pivot Table

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!

Change Data Source to Edit a Pivot Table

Step 1:

  • First of all, change the value 6 to 12 in the data source table.

Change Data Source to Edit a Pivot 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.

Change Data Source to Edit a Pivot Table

Step 3:

  • After that, select the table in range B4:G12.
  • Press Enter.

Change Data Source to Edit a Pivot Table

Step 4:

  • Finally, click Refresh to make an update in the pivot table.

Change Data Source to Edit a Pivot Table

As a result, you can visualize the change in cell D5 in the pivot table.

Change Data Source to Edit a Pivot Table

Note. Make sure to refresh every time when you add a new data or make any change. Shortcut to refresh:  Alt + F5.

Read More: 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel


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.

Add a Column/Row to Edit a Pivot Table

Step 1:

  • From the pivot table toolbar, select PivotTable Analyze.
  • Click on Change Data Source.

Add a Column/Row to Edit a Pivot Table

Step 2:

  • To include the Date column, reselect the table in range A4:G12.
  • Then, press Enter to add the new table.

Add a Column/Row to Edit a Pivot Table

Step 3:

  • Refresh again to update the table, you will see a new field named date will be added in the PivotTable Fields.

Add a Column/Row to Edit a Pivot Table

  • Consequently, for adding the Date column, changes in the pivot table will be shown like the image below.

Add a Column/Row to Edit a Pivot Table

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!

Add a Column/Row to Edit a Pivot Table

Consequently, you will obtain the new row in a Pivot Table as marked in the below screenshot.

Add a Column/Row to Edit a Pivot Table

Read more: How to Insert or Delete Rows and Columns from 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.

Select the Displaying Fields to Edit a Pivot Table

Step 1:

  • From the PivotTable Fields, just unmark the Date and Discount.

Select the Displaying Fields to Edit a Pivot Table

Therefore, you will visualize that Date and Discount options have been omitted here.

Select the Displaying Fields to Edit a Pivot Table


Similar Readings


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.

Rearrange Fields

Steps:

  • Drag Quantity from the values and placed it in Rows.

Rearrange Fields

  • After dragging to Rows, PivotTable Fields will show as the below image.

Rearrange Fields

  • Therefore, in the Pivot Table, you can see that the Quantity field is rearranged in rows.

Rearrange Fields


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.

Customize the Appearance

1. In A Compact Form

Permits to showcase items from several row segment fields in a column.

Customize the Appearance

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.

Customize the Appearance

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.

Customize the Appearance


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.


Further Readings

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo