How To Edit Pivot Table In Excel: A Step-by-Step Tutorial

How to Edit Pivot Table in Excel: A Step-by-Step Tutorial

by

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.

selecting pivot table
  1. Select any cells in your current worksheet.
  2. Go to “Insert tab” in the menu bar.
  3. Click “Tables,” and it will show the list of tables.
  4. Select “From Pivot Table/Range.” A dialog box will pop up.
  5. In “PivotTable from or range,” dialog box you can select the range that you want to use and choose where you want to put your pivot table, either in a new worksheet or an existing worksheet.
PivotTable from or range dialog box

  1. 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.”
  2. But if you choose “New Worksheet,” it will open a new worksheet.
  3. Don’t forget to check the checkbox “Add this data to the Data Model.”
  4. Then click “OK.”

This is the result:

inserting a pivot table in Excel

Finally, you’re done inserting your pivot table, and now it’s time to add data and edit your pivot table.

adding data to 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

  1. Check the fields under “Choose fields to add to the report.“
  2. Or else, simply drag the data in “Drag fields between areas below:” from the Field List to the Rows or Columns area.
add and edit pivot tables in Excel

To remove or delete a field

  1. Click on the drop-down arrow next to the field name and select “Remove Field.”
To remove or delete a field

Result: The sum of debt has been successfully removed from your pivot table.

successfully delete the data

Or you can do:

  1. Right-click the data, and a context menu will display.
  2.  Select “Remove.” It will automatically remove the data.
right-click to remove data

To change the calculation or value type

You can also right-click the data and choose “Value Field Settings.”

To change the calculation and value type

Another way to change the value type.

  1. Right-click the data that you want to change its calculation type.
  2. After you right-click, a context menu will pop up.
  3. Select “Summarize Values By,” and it will display different value types.
  4. If you want more value types, just click “More Options.” A “Value Field Settings” dialog box will display.
  5. Then, click the “OK” button. It will automatically change the value of your selected row of data. Refer to the image below.

To change the calculation type

  1. Click on the drop-down arrow at the right side of the field name and select Value Field Settings.
  2. A “Value Field Settings” dialog box will pop up, and you can choose what value type you want.
  3. Click the “OK” button. Refer to the image below.
To change the value type

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

Change Pivot Table Data Source

  1. Click “Analyze,” which in our case is “PivotTable Analyze” because of our worksheet name.
  2. Select the “Change Data Source” icon or drop-down arrow to select “Change Data Source.”
Change Pivot Table Data Source
  1. 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.
  2. 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:”
Change Pivot Table Data Source

Conclusion

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

Leave a Comment