How to Edit a Pivot Table in Excel – 5 Methods

This is a Pivot table containing a sample dataset: ordered items, unit pricing, quantities, and expenses.

Featured


Method 1 – Changing the Data Source to Edit a Pivot Table

This is the data source table.

Change Data Source to Edit a Pivot Table

This will be your pivot table.

Change Data Source to Edit a Pivot Table

  • Change the value 6 to 12 in the data source table.

Change Data Source to Edit a Pivot Table

  • Click a cell in your pivot table. Your pivot table toolbar will be active .
  • Click PivotTable Analyze.
  • Select Change Data Source.

Change Data Source to Edit a Pivot Table

  • Select the table: range B4:G12.
  • Press Enter.

Change Data Source to Edit a Pivot Table

  • Click Refresh to update the pivot table.

Change Data Source to Edit a Pivot Table

Changes are made in D5 in the pivot table.

Change Data Source to Edit a Pivot Table

Note. Refresh every time you add new data. Shortcut to refresh:  Alt + F5.

Method 2 – Add a Column/Row to Edit a Pivot Table

2.1 Add a Column

To add a Date column.

Add a Column/Row to Edit a Pivot Table

  • Select PivotTable Analyze.
  • Click Change Data Source.

Add a Column/Row to Edit a Pivot Table

  • To include the Date column, reselect the table in range A4:G12.
  • Press Enter.

Add a Column/Row to Edit a Pivot Table

  • Refresh to update the table. Select Date in the PivotTable Fields.

Add a Column/Row to Edit a Pivot Table

This is the output.

Add a Column/Row to Edit a Pivot Table

2.2 Add a Row

To add a new row to the Pivot Table, follow the procedure of Method 2.1.

Add a Column/Row to Edit a Pivot Table

This is the output.

Add a Column/Row to Edit a Pivot Table


Method 3 – Select the Displaying Fields to Edit a Pivot Table

All fields are displayed in the Pivot table below.

Select the Displaying Fields to Edit a Pivot Table

  • In PivotTable Fields,  unmark  Date and Discount.

Select the Displaying Fields to Edit a Pivot Table

Date and Discount are omitted.

Select the Displaying Fields to Edit a Pivot Table


Method 4 – Rearrange Fields to Edit a Pivot Table

Fields can be rearranged in a Pivot Table.

Rearrange Fields

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

Rearrange Fields

  • PivotTable Fields will be be displayed as in the image below.

Rearrange Fields

  • The Quantity field is rearranged in rows.

Rearrange Fields


Method 5 – Customize the Appearance to Edit A Pivot Table

  • Go to Pivot Table.
  • Click Report Layout.
  • Choose one of the three available options.

Customize the Appearance

1. In A Compact Form

Showcases items from several row segment fields in a column.

Customize the Appearance

2. Show in Outline Form

Showcases the pivot table style. Each field is shown in one column, with space for field headings. Subtotals can also be displayed at the top of the 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.


Get FREE Advanced Excel Exercises with Solutions!
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