Pivot Table Cannot Overlap Another? How to Fix It in Excel

The message "A PivotTable report cannot overlap another PivotTable report" means one pivot table needs more space and another pivot table is in the way. It usually appears when you refresh, add a field or click a slicer. The fix is to find the pivot that grows and give it room: move it, add blank rows or columns, or give it its own sheet. This guide walks through each step for anyone who builds reports with several pivots.

What the pivot table overlap error means#

A pivot table is not a fixed block of cells. It resizes itself every time its content changes. When it needs more rows or columns, it takes the empty cells next to it. If those cells belong to another pivot table, Excel stops and shows the overlap message instead of breaking either report.

Two pivots placed side by side are the classic case:

If someone adds a new region and you refresh, Pivot 1 needs a row. That is fine, because it grows downward into empty cells. But if Pivot 1 also has a field in its Columns area and a new product appears, it needs an extra column. Column C is empty, so it takes that, and the next new item would push it into column D, where Pivot 2 starts. That is when the error appears.

If a pivot would grow into ordinary cells (values or formulas, not another pivot), you see a different prompt instead: Excel asks whether you want to replace the existing data. Say no unless you're sure, because replacing overwrites those cells.

Why pivot tables grow#

A pivot table gets bigger in a few predictable situations:

• New items in the source data. A new region, product or month adds a row or column after a refresh. The guide on how to refresh a pivot table explains when this happens.
• Adding a field. A second field in Rows adds rows for every combination. A field in Columns adds columns.
• Changing the layout. Switching from Compact to Tabular layout, or turning on subtotals, makes the pivot wider or longer.
• Ungrouping or expanding. Expanding grouped dates from years to months multiplies the rows. See how to group dates in a pivot table.
• Filters and slicers. Clearing a filter shows more items. A slicer connected to several pivots changes them all at once, so one click can push two pivots into each other. The guide to Excel slicers covers Report Connections.

Step 1: Find the pivot table that causes the error#

Refresh the pivot tables one at a time, because Refresh All doesn't tell you which one failed.

1. Click inside the first pivot table.
2. Right-click and choose Refresh (or PivotTable Analyze > Refresh).
3. Repeat for each pivot table on the sheet.
4. The one that shows the message is the one that grows. The pivot next to it, in the direction it grows, is the one in its way.

To see exactly how much space a pivot uses, click inside it and choose PivotTable Analyze > Select > Entire PivotTable. The highlighted block is its current size.

Remember that Data > Refresh All (Windows: Ctrl+Alt+F5) refreshes every pivot table in the workbook, not just the sheet you're on. If you don't see a problem on the current sheet, check the other sheets too.

Step 2: Move the pivot table or add space#

Once you know which pivot grows, give it room. You have three options, from quickest to safest.

Insert blank rows or columns#

If pivots sit side by side and one needs more columns, insert a few blank columns between them. If one sits above another and needs more rows, insert blank rows between them. Select whole columns or rows first, then insert (Windows: Ctrl+Shift+=; Mac: Control+Shift+=).

Move the pivot table#

1. Click inside the pivot table.
2. Go to PivotTable Analyze > Move PivotTable.
3. Choose New Worksheet, or type a cell far away from the other pivots.
4. Click OK and refresh again.

Moving keeps all your fields, filters and formatting.

Give each pivot its own sheet#

This is the only option that removes the problem for good. A pivot on its own sheet can grow as much as it likes. You can still show several results on one dashboard sheet with formulas or charts that point at the pivots.

Step 3: Check for hidden or forgotten pivot tables#

Sometimes the "other" pivot table isn't obvious. Look for:

• Hidden rows or columns. Unhide everything around the pivot (select the area, right-click, Unhide) and check for a second pivot.
• Old test pivots. A small pivot you built and forgot can sit a few columns away. To delete one, use Select > Entire PivotTable and press Delete.
• Pivots far below. A pivot that grows downward can reach one that starts hundreds of rows lower. Press Ctrl+End (Mac: Control+End, or Fn+Control+Right arrow on a laptop) to jump to the last used cell and see how far the sheet goes.

How to lay out pivot tables so the error doesn't come back#

Plan for growth when you place pivots. Most pivots grow down, because new items usually go in the Rows area. So:

Base every pivot on an Excel table (Ctrl+T, Mac: ⌘T) so new rows are included when you refresh. The pivot table guide for beginners shows the setup. If you get a different message while building, such as one about a field name, see the fix for "PivotTable field name is not valid".

Use formulas when the summary must stay a fixed size#

For a dashboard where every number must sit in a fixed cell, formulas are often a better tool than another pivot. A formula is always one cell, so it can't overlap anything. With the sales list in A1:C8 (Region, Product, Sales) and East typed in E2:

=SUMIFS(C2:C8,A2:A8,E2)

This returns 1,900 (450 + 1,100 + 350). Fill it down next to a list of regions and you have a summary that never changes shape. COUNTIFS works the same way for counts. The trade-off is that new regions don't appear by themselves: you have to add them to the list.

Practise it#

Pivot tables are easier to manage once you've built a few. Practise building your first pivot table on its own new sheet, then practise nested fields, report filters and updating the source, where you also see when SUMIFS is the better tool. Each step is checked in a live spreadsheet. Want a timed challenge? Try the data analyst Excel mock test.