You ask Copilot in Excel to calculate the average of a column, but the result does not match the numbers you see on screen. The cause is almost always hidden rows that Copilot ignores during analysis. Copilot treats hidden rows as if they do not exist, which can produce unexpected totals, averages, or counts. This article explains why Copilot behaves this way, how to verify which rows are being used, and how to adjust your data so that Copilot returns accurate results.
Key Takeaways: Copilot and Hidden Rows in Excel
- Copilot always excludes hidden rows: Filtered, collapsed, or manually hidden rows are not included in any Copilot calculation or summary.
- Data tab > Filter toggle: Use the Filter button to see which rows are hidden and confirm your data set is complete before asking Copilot a question.
- Unhide rows before analysis: Select the entire worksheet and use Home > Format > Hide & Unhide > Unhide Rows to make all data visible to Copilot.
Why Copilot Skips Hidden Rows in Excel
Copilot in Excel uses the visible data set only. When you apply a filter, collapse a group, or manually hide rows, Copilot reads the current display state of the worksheet. It does not scan the underlying data in hidden cells. This behavior is by design and matches how Excel functions like SUBTOTAL and AGGREGATE work. Those functions also ignore hidden rows when you use their hidden-row-exclusion codes.
The root cause is the way Copilot queries the Excel object model. Copilot sends a request for the visible range of the active sheet. Excel returns only the cells that are not hidden. Copilot then performs its analysis on that restricted set. If you have 1000 rows of data and rows 200 through 500 are hidden, Copilot sees only 700 rows. Any calculation it produces is based on those 700 rows.
How Filtering Affects Copilot
When you use the Filter feature in Excel, hidden rows are removed from view. Copilot treats filtered-out rows the same as rows you manually hide. For example, if you filter a Sales column to show only values above $5000, Copilot calculates the average, sum, or count using only the rows that meet that filter condition. The total you see in the status bar also reflects only the visible rows. This consistency is helpful when you want a quick subset analysis, but it causes confusion when you expect a full-data calculation.
Grouped and Collapsed Rows
Excel outline groups let you collapse sections of data. Copilot treats collapsed rows as hidden. If you collapse a quarterly summary group to see only the quarter total, Copilot ignores the individual month rows inside that group. Any question you ask about monthly averages will return incorrect results because those rows are not visible. You must expand the group before asking Copilot to analyze the detail data.
Steps to Check and Fix Hidden Row Issues in Copilot
Follow these steps to confirm which rows Copilot sees and to make all data available for analysis.
- Open the worksheet and select any cell in your data range
Click a single cell inside the table or range you want to analyze. Copilot uses the current selection to determine the active data set. - Check the row numbers on the left side of the Excel window
Blue row numbers indicate hidden rows. If you see gaps in the numbering sequence, rows are hidden. For example, row numbers jumping from 10 to 15 means rows 11 through 14 are hidden. - Clear all filters from the Data tab
Go to the Data tab and click the Filter button to turn off filtering. This action reveals all rows that were hidden by a filter. The filter icon on column headers disappears. - Expand all grouped rows
If your worksheet has outline groups, click the number 1, 2, or 3 buttons in the top-left corner of the sheet to expand all groups. You can also press Ctrl+8 to show or hide outline symbols. - Unhide all manually hidden rows
Select the entire worksheet by clicking the triangle in the top-left corner between row 1 and column A. Then go to Home > Format > Hide & Unhide > Unhide Rows. This action unhides every hidden row in the sheet. - Ask Copilot your question again
Type your request in the Copilot pane. For example, type “What is the average sales amount?” and verify that the result now includes all rows.
If Copilot Still Returns Unexpected Results
Even after unhiding all rows, Copilot may produce numbers that do not match your expectations. The following issues are the most common causes.
Copilot Returns a Different Total Than the SUM Function
If your worksheet contains a SUM formula that references a range including hidden rows, SUM includes those rows in its calculation. Copilot uses only visible rows. To make Copilot match the SUM result, you must either unhide all rows or use the SUBTOTAL function with function number 9 which includes hidden rows. Replace =SUM(A2:A100) with =SUBTOTAL(9,A2:A100) to include all rows regardless of visibility.
Copilot Ignores Data in Filtered Tables
Excel tables created with Ctrl+T have built-in filter buttons. When you filter a table, Copilot ignores the filtered-out rows. To analyze the full table, clear the filter by clicking the filter icon on the column header and selecting Clear Filter. You can also remove the table filter from the Data tab.
Copilot Shows an Error Message About Empty Data
If all rows are hidden, Copilot may display an error saying it cannot find data to analyze. Unhide at least one row. If the worksheet is completely empty except for hidden rows, Copilot will not generate any output. Make sure at least one row of data is visible before asking a question.
Copilot Behavior With Hidden Rows vs Without Hidden Rows
| Item | Hidden Rows Present | No Hidden Rows |
|---|---|---|
| Rows included in calculation | Only visible rows | All rows in the range |
| Average calculation | Average of visible rows only | Average of all rows |
| Sum calculation | Sum of visible rows only | Sum of all rows |
| Count calculation | Count of visible rows only | Count of all rows |
| Filter state | Filtered rows are excluded | No filter applied |
| Group state | Collapsed rows are excluded | All groups expanded |
| Recommended action | Unhide all rows before analysis | No action needed |
Copilot in Excel always uses visible rows for analysis. This behavior is consistent across all calculation types including average, sum, count, minimum, maximum, and custom formulas. To get accurate results, verify that your worksheet has no hidden rows before you ask Copilot a question. Use the Data tab filter toggle and the Home > Format > Hide & Unhide menu to reveal all data. If you need to analyze a filtered subset, be aware that Copilot will exclude the hidden rows intentionally. For full data analysis, clear all filters, expand all groups, and unhide all rows. This approach ensures that Copilot returns the same numbers you expect from standard Excel functions.