NOTE: Analyze Data was first introduced in Excel as Ideas (you can find my original video here). It has since been rebranded (if you are on the Semi-Annual Enterprise Channel you may still see the old name) and it keeps improving – in no small part thanks to user feedback.

Jumpstart Data Analysis

Imagine you open your Inbox one morning to find this urgent email from your boss:

Fragment of an Outlook email with the subject "NEED ASAP!" and an attached Excel file called Analyze Data.xlsx. The contents of the email says:
See attachment and provide:
1. Quick summary
2. How tobacco's doing
3. Something I should know

Attached, you find procurement data, including information on vendors, cost, categories and dates.

Fragment of an Excel worksheet - a data range with 6 columns and 205 rows of data, including headers: Purchasing Org., Category, Vendor number, Vendor, Date, Cost.

Wondering where to begin? Why not jumpstart the analysis and outsource the initial ‘thinking’ to AI?

All you need to do is click anywhere inside the range – it doesn’t have to be an official Excel table and it doesn’t even have to be selected in full. On the Home tab, you will find the Analyze Data button. Click it.

Excel menu ribbon with the Home tab and Analyze Data button highlighted.

It will automatically pick up the entire range and open a pane on the side where you get to ask the built-in AI a question about your data.

Excel workbook, a range in the sheet selected, a pane titled Analyze Data on the right. Text box with instruction "Ask a question about your data" highlighted. Below "Suggested questions" and "Discover insights", a pivot table showing 'Cost' by 'Vendor' and 'Date', "Insert PivotTable" button and a bar chart showing 'Cost' by 'Category'.

Not sure what to ask? You can start with some ready-made suggestions, generated based on your data. You get some ideas about the questions you could ask, as well as some pre-made pivot tables and charts, ranging from general, like ‘Cost’ by ‘Category’, to more specific, focused on interesting trends detected by the AI, like cost decrease over time for a specific vendor.

If you’re happy with any of the pre-made pivot tables or charts, you can immediately add it to your report by clicking on “Insert PivotTable” or “Insert PivotChart“.

Analyze Data pane in Excel with an arrow pointing to the highlighted button "Insert PivotTable".

It will be created for you in a new sheet.

Fragment of an Excel worksheet called Suggestion1 with a pivot table showing Sum of Cost by Date.

Edit the Inserted PivotTable

You are free to edit the pivot tables (or charts) you insert. You can format them, change their style, rename the default headers (Total Cost sounds better than “Sum of Cost”), or add more fields to this automatically generated pivot table and change how the values are summarized or displayed.

Ask for a Specific Aggregation or Chart Type

Going back to the Analyze Data pane, we also have some interesting pivot charts among the initial suggestions. But if you are looking for a specific chart type, you can simply ask for it. Same with aggregation type – if you’re interested in the average rather than sum, all you need to do is specify it in the Ask box.

Fragment of the Analyze Data pane with "Average cost by category as column chart" in the Ask box.

Analyze Data will generate it for you, and all you have to do is insert it.

Analyze Data pane in Excel with a column chart showing Average Cost by Category. Insert PivotChart button highlighted.

It is inserted in a new sheet, alongside the underlying pivot table.

Excel worksheet called Suggestion2 with a pivot table and pivot chart showing Average Cost by Category.

Cut the chart and paste it alongside the initial pivot table in the other Suggestion sheet. This will be our Summary sheet. Keep the underlying data (the pivot table that we will not be using in our final report) in a separate sheet which we can hide later. That’s in keeping with good spreadsheet design principles. Adjust the chart title and style, if needed.

Excel worksheet called Summary with a pivot table showing Total Cost and Average Cost by Date and pivot chart showing Average Cost by Category.

Now, if you would also like to include a line chart, you can ask for one specifically, or ask for “Cost trend” or “Cost over months”. AI should interpret it as a line chart, probably the most common graph to illustrate trend or change over time. If you’re not sure which chart type would be best but you understand what you are trying to visualize, tell Analyze Data and it will whip up the right chart for you.

Analyze Data pane showing cost over months and a pivot chart visualizing 'Cost' by year and month of 'Date'

Again, it will be inserted in a new sheet, so move the chart to the Summary sheet, and the pivot table to the Data Prep sheet, to keep everything together.

Featured Course

Visually Effective Excel Dashboards

Create eye-catching Excel Dashboards with actionable tips you can use right away.
Learn More
Excel dashboards course cover

Analyze Data for Specific Insights

You can also use Analyze Data to drill into specific categories. The AI will filter the dataset for you, bringing the focus to where you want it. All you have to do is ask.

So, if you look for tobacco insights, the AI will analyze the tobacco category in various contexts. If it finds a notable pattern or trend, it will even conditionally format the chart for you.

Analyze Data pane showing a conditionally formatted chart for "tobacco insights"

You can also ask it to ask to analyze data against specified thresholds (and you can define those thresholds using natural language, not worry about counting all the zeros in a million). For example, “vendors with cost higher than 7 million”.

Analyze Data pane with the question "vendors with cost higher than 7 million" and a pivot table showing "Cost greater than 7000000 by Vendor".

The pivot tables and charts generated based on your specific requests come already filtered to show only the context you wanted.

2 filtered pivot tables. "Total Cost by Vendor for Category Tobacco" with category filter applied, and "Total Cost greater than 7000000 by Vendor" with value filter applied.

You no longer have to look blindly for different pivot table features. Not sure where to find the value filter? Simply tell the AI built into the Analyze Data feature what you want in natural language, and it’s usually able to figure it out and generate exactly what you’re after.

You can dig even further, combine multiple filters in one ‘ask’. For example, “top 5 food vendors in March”.

Featured Course

Master Excel Power Pivot & DAX (Beginner to Pro)

Transforming data into meaningful reports can feel like being in an Olympic event. It is hard! Join the course and learn how to use Excel’s Power Pivot and Data Modelling tools to make reporting easy and fast.
Learn More
Power Pivot DAX course cover

Is Analyze Data Dynamic (Testing Refresh)

Because the Analyze Data suggestions take the form of pivot tables and pivot charts, they can be refreshed when the data changes. You can actually see it happening in real time on the pane – the suggestions update automatically.

Analyze Data pane with text "Looks like you are editing the data".

However, the pivot tables and charts you’ve inserted will have to be refreshed manually, by pressing the Refresh button. You’ll find it on the PivotTable Analyze tab or the Data tab. You can also right-mouse-click on a pivot table and select Refresh there. Since all inserted pivot tables and charts share the same cache, they will update at the same time.

Pivot table and charts partially hidden by Excel context menu with Refresh highlighted.

While the source data doesn’t need to be in an official Excel table – you saw that Analyze Data works well with a range – if you expect your data to grow, it makes sense to convert it into a table after all. Simply click anywhere in the data, then press Ctrl+T and Enter. That way, new rows added below the table, will automatically be included, and all your pivot tables and charts will update at the press of a Refresh All button, without the need to update the range.

More Tips and Known Limitations

“Is this helpful?”

In the lower-right of each suggested visual you’ll find the question “Is this helpful?”  By clicking this you can answer a simple “yes” or “no”.  This provides feedback to the machine-learning, artificial intelligence engine.

Preview of a pivot table with the text "Is this helpful?" in the bottom right corner and "Yes" and "No" highlighted above the question.

If enough people respond “no”, these kinds of suggestions will likely be relegated further down the list, while “yes” responses will continue showing up in the initial offerings.

A Bit of Memory Assistance

If you don’t remember the exact naming of a column heading, typing in the first 1-2 letters of the column heading will reveal a “cheat sheet” to assist your memory.  This is especially helpful for tables with more columns than you can remember.

Defining ‘Interesting’ Fields

Excel’s AI can treat certain fields, like ‘customer id’, as a number and aggregate it unnecessarily. We can fine-tune Analyze Data to help it understand text versus values.

On the Analyze Data pane, click on the cog icon to open settings.

Pointing to a cog icon on the Analyze Data pane.

This will take you to the field list based on the column headers of your data.

There, we can define the default type of aggregation we want on a given field by clicking to the right of the field name in the column “Summarize value by”.

We can choose between Sum, Average, or “Not a value”. 

If a “Not a value” field is used with a field that is summed or averaged, the “Not a value” field acts as a row label.  If used by itself, the “Not a value” field counts unique values of that field.

We can also exclude certain fields from analysis by unchecking the checkbox to the left of the field name.


Analyze Data is available to Microsoft 365 subscribers in English, French, Spanish, German, Simplified Chinese, and Japanese. If you are a Microsoft 365 subscriber, make sure you have the latest version of Office.

Where Analyze Data May Fail

There are a few limitations of this feature that may impact your analysis.

  • The analysis is currently limited to 1.5 million cells (not rows, but cells). If your data goes beyond this limit, consider filtering your data, then copying the filtered results to a separate sheet to reduce the dataset. 
  • String dates like “2020-04-09” will be treated as text and not dates, preventing the automatic grouping feature in PivotTables from working properly. Consider adding a helper column that uses functions like DATE or DATEVALUE to convert the “text date” to a proper date format.
  • Analyze Data is not compatible with files opened in “Compatibility Mode” (there’s sweet irony for you.) Your file must be saved in the .XLSX, .XLSM, or .XLSB file format.
  • Merged cells can confuse the AI. Consider using “Center Across Selection” instead of “Merge Cells” if you need to have the look of merged cells in your workbook.

Final Thoughts

Isn’t it amazing that with just a few clicks we get to create a functioning report? You are still in control, deciding what to include, and yes, some cut and paste and formatting may be involved, but you certainly save some precious time and get to enjoy your ☕break for longer.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.