Home > Pivot > Errors > Overlap

Excel Pivot Table Overlap Errors

Debra Dalgleish - Contextures

See how to troubleshoot and fix Excel pivot table overlap errors, “A PivotTable report cannot overlap another PivotTable report.” My free macro can help you find the problem pivot tables, and the steps below will help you fix them!

Note: For help with other pivot table errors, see the Pivot Table Errors page.

pivot table overlap error

1) Video: Fix Pivot Table Problems

Usually, things go smoothly when you when you try to refresh a pivot table. Occasionally you might see an Excel error message, like "A PivotTable report cannot overlap another PivotTable report."

This video shows that overlap error, and a "field name not valid" error too. See how to find and fix those errors, using my free macro. There are written steps below the video..

2) Pivot Table Overlap Problems

Sometimes, when you try to refresh a pivot table, Excel shows an error message that warns about pivot table overlap problems.

  • “A PivotTable report cannot overlap another PivotTable report.”

You’ll see that Excel error message if:

  • there are two or more pivot tables on the same worksheet
  • new data was added to the pivot table data source
  • there’s not enough blank space for one of the pivot tables to expand for new data

pivot table overlap error

3) Move a Pivot Table

If you see the overlap error message, on a worksheet with two pivot tables that are close together, you can move one of the pivot tables, to fix the problem.

Instead of manually selecting a large pivot table and moving it, you can use a built-in command to move a pivot table. I show the steps in this short video, and the written steps are below the video.

3.1) to Move a Pivot Table

Follow the steps below, to move a pivot table to a different location:

  • First, in the pivot table that you want to move, select any cell
  • Next, on the Excel Ribbon, click the PivotTable Analyze tab
  • Note: Or, under Pivot Table Tools, click the Options tab
  • Then, in the Actions group, click the Move PivotTable command

move pivottable command on Excel Ribbon

3.2) Move PivotTable Dialog Box

When the Move PivotTable dialog box opens, it asks you to "Choose where you want the PivotTable report to be placed."

There are two location options, New Worksheet and Existing Worksheet.

move pivottable dialog box

3.2.1) New Worksheet

To move the pivot table to a new sheet, follow these steps:

  • In the Move PivotTable dialog box, click the radio button for New Worksheet
  • Click the OK button, to complete the pivot table move.

Excel inserts a new sheet, with the moved pivot table starting in cell A1

3.2.2) Existing Worksheet

To move the pivot table to an existing sheet, follow the steps below:

  • In the Move PivotTable dialog box, click the radio button for Existing Worksheet
  • Next, click in the Location box
  • Then, go to the sheet where you want the pivot table moved to.
    • Note: The sheet can be in the same workbook, or a different workbook
  • On the selected sheet, click on the cell where you want the pivot table to start
    • Note: If you select a range of cells, the pivot table will start in the first cell of that range
  • Click the OK button, to complete the pivot table move.

The pivot table moves to the selected sheet, starting in the selected cell.

3.2.3) Make Space and Try Again

If your target cell already contains a pivot table, Excel will show an error message to warn you about that problem:

  • We couldn't complete the action for the PivotTable 'PivotTable1' in the sheet 'Reg' because there's already a PivotTable 'PivotTable2' there. Make space and try again.

If you see that error message, click the OK button.

We couldn't complete the action for the PivotTable 'PivotTable1' in the sheet 'Reg' because there's already a PivotTable 'PivotTable2' there. Make space and try again.

After that, try to move the pivot table again, and be sure that there is enough room for the pivot table on the target sheet.

4) One Pivot Table on Worksheet

Sometimes, a pivot table is all alone on a worksheet, but Excel shows you that pivot table overlap error message.

When that happens, here are 4 things that I'd check first, to look for the problem pivot tables. The details are in the sections below:

  • 1) Status Bar
  • 2) Hidden Rows/Columns
  • 3) Refresh All
  • 4) Shared Pivot Cache

4.1) Status Bar

The Excel Status Bar, below the worksheet tabs, shows information that can help with pivot table error troubleshooting.

  • First, at the left end of the Status Bar, click on Worksheet Statistics.
  • If the worksheet Pivot Table count is equal to 1:
  • If the Pivot Table count is greater than 1:

Worksheet Statistics shows PivotTables count

4.2) Hidden Rows / Hidden Columns

If the pivot count was greater than 1, in Worksheet Statistics, follow these steps, to check for hidden pivot tables.

  • First, click the Select All button, at the top left corner of the worksheet
  • Next, right-click any column heading, and click Unhide
  • Then, right-click any row heading, and click Unhide

After you make those adjustments, check the worksheet again, to find the previously hidden pivot table.

  • Tip: Because Excel warned of an overlap problem, it's probably close to the other pivot table. Look to the right, and below, the other pivot table.

4.3) Refresh vs Refresh All

When you refreshed the pivot table, which method did you use?

  1. Right-click on a pivot cell, and click Refresh
    • OR
  2. On the Excel Ribbon, go to the Data tab, and click the Refresh All command
4.3a) Click Refresh

If you right-click on a pivot cell, and click Refresh, Excel refreshes:

  1. the pivot cache for that pivot table
  2. all pivot tables that use that same pivot cache

Tip: I recommend that you use this method to refresh a specific pivot table.

4.3b) Click Refresh All

If you click the Refresh All command, Excel refreshes more items:

  • all pivot caches in the workbook
  • all pivot tables in the workbook
  • all external data ranges in the workbook

Because so many items are refreshed, it can be more difficult to find the problem. Pivot tables on one or more worksheets could be causing the overlap error message to appear..

Tip: I recommend you do not use Refresh All, if you only want to update a specific pivot table.

4.4) Shared Pivot Cache

If the pivot table count was equal to one, and Excel warned of an overlap problem, the problem was caused by multiple pivot tables on a different worksheet.

  • Every pivot table is based on the data in a pivot cache.
  • When you refresh a pivot table, Excel automatically refreshes all other pivot tables that use the same pivot cache.
  • If any one of those pivot tables doesn't have room to expand, Excel shows the error message.
    

Tip: Further down this page, you can see my tips for finding those problem pivot tables.

5) Find Problem Pivot Tables

In a small Microsoft Excel file, it might be easy to find and fix the problem pivot table:

  • Go to each sheet in the Excel workbook
  • If a sheet has multiple pivot tables, insert blank columns or blank rows between them
  • Also check for hidden sheets, and see if those have multiple pivot tables

After adding extra space, try to refresh the pivot table again, to see if that solved the problem.

If you still see the overlap error message, try the macro shown in the next section

6) Macro - List Pivot Table Details

In a big workbook, with lots of pivot tables, and different data sources, it can be tricky to pinpoint the overlap problem. Instead of looking through the file, sheet by sheet, I use a macro, to help me find the potential problems.

My macro lists each pivot table in the workbook, with information about its location, size, and source data.

Macro - List Pivot Table Details

6.1) Source Data

Also, if the source is a worksheet list or Excel table in the same Excel workbook, the macro shows details about that source data.

Macro - List Pivot Table Details source data

6.2) Get the Macro

If you'd like to get the free macro, here's how to do that:

  1. To learn more about the macro, and to see the VBA code, go to the Pivot Table Macros page. Scroll down to the section named, List All Pivot Table – Details.
  2. The macro code is also in the List Pivot Table Macros sample file, that you can get in the Download section below.

7) Prevent Pivot Table Overlap

I recommend that you set up your Excel file with only one pivot table per sheet, so there's no possibility for pivot table overlapping.

However, if you need to see pivot tables side-by-side, here’s how you can create two or more pivot tables on an Excel sheet, and avoid potential problems.

7.1) Prepare for Second Pivot Table

Before you create a new pivot table on a sheet that already has a pivot table, do these preparation steps:

  • First, to the right of the first pivot table, select about 10 columns
    • TIP: As you drag across the column buttons, to select the columns, the tool tip shows how many columns have been selected.

tool tip shows how many columns have been selected

  • Next, reduce the column width of the selected columns, to about 2.00 (19 pixels)

Reduce the column width

This creates space between the two pivot tables, to prevent accidental overlapping, if you refresh them.

    

Tip: If the columns are hidden, you won’t notice if the first pivot table expands slightly, to fill one or more of the “spacer” columns.

It’s better to make the columns narrow, so you can see any problems immediately.

8) Overlap Other Data on Sheet

Another pivot table overlap error, that you might see occasionally, is the one shown below.

This message appears if you try to refresh a pivot table, or change its layout, and there is data in nearby cells, where the pivot table will expand to.

  • This action will overlap the PivotTable 'PivotTable1' with data already in the sheet 'PivotNormal'. Do you want to replace the data?

This action will overlap the PivotTable 'PivotTable1' with data already in the sheet 'PivotNormal'. Do you want to replace the data?

8.1) Click Yes or No

Click the Yes or No button in the information message:

  • Yes: If you click the Yes button, Excel will update the pivot table, and delete any data in the cells that the pivot table overlapped.
  • No: If you click the No button, the pivot table is not updated. Later, you can move the worksheet data, and try again, if you want to go ahead

9) Download the Sample Files

No Macros: To see the pivot table error message, download the Pivot Table Errors sample file. The zipped file is in xlsx format, and does not contain macros.

List Pivot Tables Macros: To see how the macros work, and to get the sample code, download the Pivot Table List Macros workbook. The zipped file is in xlsm format, and contains macros. Enable macros when you open the workbook, if you want to test the macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Subscribe.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

10) More Pivot Table Tutorials

Pivot Table Errors

Hide Pivot Error Values

Pivot Table Detail List Macros

Clear Old Items in Pivot Table

Last updated: June 24, 2024 2:13 PM