Move or Change Part of a Pivot Table

November 24, 2022 - by Bill Jelen

Move or Change Part of a Pivot Table

Problem: If I try to insert a row in a pivot table, I am greeted with a message saying that I cannot change, move, or insert cells in a pivot table.

A message says: We can't make this change for the selected cells because it will affect a PivotTable.
Figure 832. Excel won’t let you insert a row in a pivot table.

Strategy: You cannot do a lot of things to a finished pivot table. While the flexibility of pivot tables is awesome, sometimes you just want to take the results of the pivot table and turn off the pivot features. If you want to take the data and reuse it somewhere else, for example, you can convert the pivot table to regular data by using Paste Values. Follow these steps:

  • 1. Select the entire pivot table.

  • 2. Press Ctrl+C to copy.

  • 3. elect Home, Paste dropdown, Paste Values.

This action will change the pivot table from a live pivot table to just values in cells. You can now insert rows and columns to your heart’s content.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ross Findon on Unsplash

Bill Jelen is the author / co-author of:
MrExcel 2022 Boosting Excel

The 6th Edition of MrExcel XL, updated with new functions released for Microsoft 365.