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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Edit a Pivot Table: 5 Different Ways

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

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

Change Data Source to Edit a Pivot Table

  • 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

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

Change Data Source to Edit a Pivot Table

  • 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 you add new data or make any changes. Shortcut to refresh:  Alt + F5.

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

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

Add a Column/Row to Edit a Pivot Table

  • 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

  • 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


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

  • 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


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

  • 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.

  • Go to Pivot Table.
  • Click the Report Layout option.
  • 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


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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 hesitate to contact us. Please share your ideas in the comments section below.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo