Excel 2024: Ask Excel's A.I. a Question About Your Data

A Natural Language Query feature started rolling out to Microsoft 365 in late 2019. The feature uses artificial intelligence to answer questions about your data.

The Analyze Data feature can be found towards the right side of the Home tab. The feature has the potential to help millions of people, but it is hard to discover.

Anyone can perform advanced data analysis by simply asking questions by typing a sentence. Your data set can be up to 250,000 cells. Select one cell in your data. Use the Analyze Data icon on the right side of the Home tab.

A box says to Ask A Question About Your Data and it gives you a few sample questions.

Type a question such as "Top 3 Products by 'Sales' where Category is "Bikes"". Excel restates your question and shows you a thumbnail of the report.

If this is the correct analysis, you can use the +Insert Pivot Table icon to insert the results into a new worksheet in your workbook.

Sometimes the feature will give you a chart when you want a table. Try adding "as table" to the end of your sentence.

The "Is this helpful?" link in the lower right is not being used. The original idea was to use Machine Learning to suggest better reports in the future. But the reality is that Microsoft is taking privacy very seriously and they can't learn without retaining your data.

New for 2021 is the "Which Fields Interest You the Most?". This can be used to tell Excel that they should never offer to sum fields such as Year, Part Number, or Cost Center. You can choose to Sum or Average numeric fields. Or you can uncheck the field to make sure it is not in any of the suggestions.

Even before you type a question, Excel will offer you 5-10 suggested reports and a link to load up to 30 more reports. If you aren't sure what you are looking for, it is sometimes interesting to read through these suggested reports.

My one complaint about the feature is shown in the following chart. Ideas was able to find some outliers in this data and offers to create a chart with those points called out in orange. For this chart to work correctly, Excel would have to support conditional formatting in charts and it does not. That means that the pivot chart will always call out these three points, even if the underlying data changes and new outliers emerge. You would have to re-run Ideas and hope that a similar result is offered.

---

---