When you try to use Copilot in Excel on a table that contains merged cells, Copilot often fails to generate formulas, analyze data, or produce summaries. This happens because Copilot relies on a structured table format where each cell in a column belongs to a single row. Merged cells break this structure by spanning multiple rows or columns, which confuses the AI model. This article explains why Copilot cannot process merged cells and shows you how to restructure your data so Copilot works correctly.
Key Takeaways: Fix Copilot in Excel When Merged Cells Block Analysis
- Home tab > Merge & Center > Unmerge Cells: Restores each cell to its own row and column, allowing Copilot to read the table structure.
- Fill merged values down: After unmerging, use Ctrl+D or the Fill Down command to copy the value into every blank cell that was previously part of a merged group.
- Convert range to table with Ctrl+T: A proper Excel table with no merged cells is a prerequisite for Copilot to analyze data and generate formulas.
Why Copilot Fails on Tables With Merged Cells
Copilot in Excel processes data by examining each cell in a structured table. When you merge two or more cells, Excel treats the merged area as a single cell that spans multiple rows or columns. This breaks the one-cell-per-row rule that Copilot expects. For example, if you merge cells A1 through A3, only A1 contains a value while A2 and A3 appear blank to Copilot. The AI model cannot determine which row the merged value belongs to, so it stops processing or returns an error.
The technical root cause is that Copilot requires a flat, normalized table structure. Merged cells create gaps in the data grid. When Copilot tries to read the table, it encounters empty cells in the middle of a column and cannot infer the correct relationship between rows. This is not a bug in Copilot. It is a design limitation. Copilot works only with tables that follow the standard Excel table format, where every cell in a column is filled or consistently empty.
What Happens When You Try to Use Copilot on a Merged Table
You might see any of these symptoms:
- Copilot opens but says “I can’t analyze this table” or “This table is not supported.”
- Copilot generates a formula that references the wrong cells.
- Copilot ignores the merged column entirely and provides incomplete analysis.
- The Copilot pane shows an error message about the table format.
All these symptoms point to the same root cause: merged cells distort the logical table structure that Copilot depends on.
Steps to Remove Merged Cells and Restore Copilot Functionality
Follow these steps to unmerge cells, fill the missing values, and convert the range into a proper Excel table. Perform these steps on a copy of your workbook to avoid losing original formatting.
- Select the entire worksheet or the range with merged cells
Press Ctrl+A to select all cells in the active sheet. If your data is only part of the sheet, select that specific range with your mouse or keyboard. - Unmerge all cells in the selection
Go to the Home tab on the ribbon. In the Alignment group, click Merge & Center. From the dropdown menu, select Unmerge Cells. Excel splits every merged cell back into individual cells. The value from the original merged cell now appears only in the top-left cell of the unmerged group. - Fill the blank cells with the unmerged value
Select the column that had merged cells. Press Ctrl+G to open the Go To dialog. Click Special, then select Blanks, and click OK. Excel highlights all blank cells in that column. Without clicking anywhere else, type = and then click the cell directly above the first blank cell. For example, if the first blank is A2, click A1. Press Ctrl+Enter instead of just Enter. Excel fills every blank cell with the value from the cell above it. - Convert the range to an Excel table
Select any cell inside your data range. Press Ctrl+T. In the Create Table dialog, confirm the range is correct and check My table has headers if your data includes a header row. Click OK. Excel formats the range as a table with alternating row colors and filter arrows. - Verify Copilot works
Click inside the table. Open the Copilot pane by clicking the Copilot icon in the ribbon or pressing Alt+Windows key+Space. Type a natural language request such as “Show the total sales by region” or “Create a chart of monthly revenue.” Copilot should now respond with a formula, a chart, or a summary.
If Copilot Still Has Issues After Unmerging Cells
Sometimes removing merged cells is not enough. The table may still have other structural problems that block Copilot.
Copilot Ignores Some Columns After Unmerging
If a column that previously had merged cells still contains blank cells after you unmerge, those blanks confuse Copilot. Run the Fill Blanks step again. Make sure every cell in the column contains a value. Copilot treats blank cells as missing data and may skip the entire column.
Copilot Returns Generic Output Instead of Tenant-Specific Data
If your table includes merged header cells that span multiple columns, unmerge them as well. Copilot reads column headers to understand the data context. A merged header that says “Q1” and spans three columns prevents Copilot from knowing which column contains January, February, or March data. Unmerge the header row and give each column a unique header name.
Copilot Does Not Appear in the Ribbon
If Copilot is missing entirely, check that you have a Microsoft 365 subscription that includes Copilot. Copilot requires a Copilot for Microsoft 365 license or a Copilot Pro subscription. Go to File > Account to verify your subscription status. Also ensure that your Excel version is Current Channel or Monthly Enterprise Channel. Copilot is not available on Semi-Annual Enterprise Channel.
Normal Table vs Merged Table: How Copilot Handles Each
The table below shows the key differences between a properly structured Excel table and one with merged cells.
| Feature | Normal Table (No Merged Cells) | Table With Merged Cells |
|---|---|---|
| Cell structure | Each cell belongs to exactly one row and one column | One cell spans multiple rows or columns |
| Copilot data reading | Reads every cell in every row without gaps | Sees blank cells where merged area ends |
| Formula generation | Copilot can reference any column by name | Copilot cannot determine which row the value belongs to |
| Chart creation | Copilot can create charts from any table | Copilot skips merged columns or creates incomplete charts |
| Required action | No action needed | Unmerge cells and fill blanks |
This comparison makes it clear that unmerging cells is the only reliable way to make Copilot work with your data. There is no setting or workaround that lets Copilot bypass merged cells.
After you unmerge cells and convert your range to a table, you can use Copilot to generate formulas, create pivot tables, and summarize data with natural language commands. For advanced analysis, try asking Copilot to “Forecast this data using linear regression” or “Highlight rows where sales exceed the average.” Copilot will handle these requests correctly only when the table has no merged cells.