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:
Attached, you find procurement data, including information on vendors, cost, categories and dates.
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.
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.
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“.
It will be created for you in a new sheet.
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.
Analyze Data will generate it for you, and all you have to do is insert it.
It is inserted in a new sheet, alongside the underlying pivot table.
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.
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.
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
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.
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”.
The pivot tables and charts generated based on your specific requests come already filtered to show only the context you wanted.
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)
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.
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.
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.
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.
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.
Availability
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.
Featured Course
Black Belt Excel Package
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.
