Body
When to Use?
Analyze Data in Excel empowers you to understand your data through natural language queries that allow you to ask questions about your data without having to write complicated formulas. In addition, Analyze Data provides high-level visual summaries, trends, and patterns.
Watch: Analyze Data
Have a questions? We can answer it!
Simply select a cell in a data range, then on the Home tab, select the Analyze Data button. Analyze Data in Excel will analyze your data and return interesting visuals about it in a task pane.
If you're interested in more specific information, you can enter a question in the query box at the top of the pane, and press Enter. Analyze Data will provide answers with visuals such as tables, charts or PivotTables that can then be inserted into the workbook.
If you are interested in exploring your data, or just want to know what is possible, Analyze Data also provides personalized suggested questions which you can access by selecting on the query box.
I. Try Suggested Questions
Select the text box at the top of the Analyze Data pane, and you'll see a list of suggestions based on your data.
II. Just ask your question
You can also enter a specific question about your data.
Notes:
Get specific with Analyze Data
If you do not have a question in mind, in addition to Natural Language, Analyze Data analyzes and provides high-level visual summaries, trends, and patterns.
You can save time and get a more focused analysis by selecting only the fields you want to see. When you choose fields and how to summarize them, Analyze Data excludes other available data - speeding up the process and presenting fewer, more targeted suggestions. For example, you might only want to see the sum of sales by year. Or you could ask Analyze Data to display average sales by year.
Select Which fields interest you the most?
Select the fields and how to summarize their data.
Note: The Not a value option in the field list refers to fields that are not normally summed or averaged. For example, you wouldn't sum the years displayed, but you might sum the values of the years displayed. If used with another field that is summed or averaged, Not a value works like a row label, but if used by itself, Not a value counts unique values of the selected field.
Analyze Data offers fewer, more targeted suggestions.
I. Analyze Data works best with clean, tabular data.
Here are some tips for getting the most out of Analyze Data:
- Analyze Data works best with data that's formatted as an Excel table. To create an Excel table, click anywhere in your data and then press Ctrl+T.
- Make sure you have good headers for the columns. Headers should be a single row of unique, non-blank labels for each column. Avoid double rows of headers, merged cells, etc.
- If you have complicated, or nested data, you can use Power Query to convert tables with cross-tabs, or multiple rows of headers.
II. Didn't get Analyze Data? It's probably us, not you.
Here are some reasons why Analyze Data may not work on your data:
-
Analyze Data does not currently support analyzing datasets over 1.5 million cells. There is currently no workaround for this. In the meantime, you can filter your data, then copy it to another location to run Analyze Data on it.
- String dates like "2017-01-01" will be analyzed as if they are text strings. As a workaround, create a new column that uses the DATE or DATEVALUE functions, and format it as a date.
- Analyze Data will not work when Excel is in compatibility mode (i.e. when the file is in .xls format). In the meantime, save your file as an .xlsx, .xlsm, or .xlsb file.
- Merged cells can also be hard to understand. If you're trying to center data, like a report header, then as a workaround, remove all merged cells, then format the cells using Center Across Selection. Press Ctrl+1, then go to Alignment > Horizontal > Center Across Selection.