The Analyze Data button is pressed, the panel opens, and the results are either empty or unrelated to the question. The first reflex is usually to blame the machine's intelligence. However, most of the rejections have clear reasons outlined in Microsoft documentation, and almost all of them relate to data format, not the quality of the question.
Format requirements: table, and one row of column headers
Analyze Data works best on data that is formatted as an Excel table. The shortcut is Ctrl+T once the cursor is within the data range.
The column headers must be a single row containing unique labels with no empty cells. Two-tiered header rows, merged cells, and cross tables are three formats that most often cause the feature to fail. For data that is indeed hierarchical, change the format first using Power Query before analyzing it.
If merged cells are used solely to center the report title, there is an alternative that does not disrupt the structure: press Ctrl+1, then select Alignment, Horizontal, Center Across Selection. The appearance is the same, and the structure remains intact.
Four hard limits that cannot be negotiated
- 1.5 million cells. Analyze Data does not support datasets above this number, and there is currently no shortcut for it. The only option is to filter the data and then copy the results to another location.
- File format. This feature does not work when Excel is in compatibility mode, which occurs when the file is still in .xls format. Save it again as .xlsx, .xlsm, or .xlsb.
- Dates in text format. Values like 2017-01-01 stored as text will be analyzed as text, not as dates. The fix: create a new column using the DATE or DATEVALUE function, then format it as a date.
- Language. Analyze Data is available for Microsoft 365 customers in English, French, Spanish, German, Simplified Chinese, and Japanese. Indonesian is not on that list, so natural language questions need to be written in one of the supported languages. Microsoft also mentions that the natural language question feature is being rolled out gradually and may not yet be available in all countries.
Sometimes nothing can be found
This is a part that is rarely mentioned. Analyze Data does not search for just anything; it looks for four specific classes of insights: ranking, trends, outliers, and majority. If your data happens not to contain any of these, the panel will understandably be empty even if all the above requirements are met. This is not a sign that your data is poor.
Old layout rules that still matter
Microsoft's guidelines for organizing data in a worksheet are much older than any AI feature, yet it is these rules that determine whether a range can be recognized by the machine:
- One column contains one type of content, consistent across all rows.
- Avoid empty rows and columns within the data range.
- Leave at least one empty row and one empty column between the data range and other data on the same sheet.
- Do not place important data to the left or right of the range, as it may be hidden when the range is filtered.
- Avoid spaces at the beginning or end of cell content. These spaces disrupt sorting, searching, and formatting. To indent text, use the Increase Indent command.
- Ensure there are no hidden rows or columns before changing the range, as data may be accidentally deleted.
If Copilot is used, not Analyze Data
Copilot in Excel writes changes directly to the workbook. Therefore, Microsoft advises caution with sensitive or shared files and reminds that changes can be undone or reverted through previous versions. Copilot works well with autosave on or off; turning off autosave is useful when you want to experiment without saving changes.
There is also a model selector in the upper right corner of the panel, with the default option set to Auto. The model selection only applies to the current session; once Excel is closed, the settings revert to the default model.
The practical conclusion is simple. Ten minutes spent tidying up the data format is more crucial for the results than polishing the command sentences, and the list of requirements above can be checked one by one before blaming the machine.
Sources
- Microsoft Support, Analyze Data in Excel, https://support.microsoft.com/en-us/excel/analyze-data-in-excel
- Microsoft Support, Guidelines for organizing and formatting data on a worksheet, https://support.microsoft.com/en-us/excel/guidelines-for-organizing-and-formatting-data-on-a-worksheet
- Microsoft Support, Copilot in Excel tips, https://support.microsoft.com/en-us/excel/copilot/copilot-in-excel-tips