MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Ask Excel a Question About Your Data

January 06, 2020 - by

A new Natural Language Query feature is rolling out to Office 365 in late 2019 and early 2020. The feature uses artificial intelligence to answer questions about your data.

It is the #1 tip in this book because of this problem: (1) The feature has the potential to help millions of people, and (2) it is hidden where no one will find it.

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 Ideas icon on the right side of the Home tab.

The Ideas icon is relatively new, near the right side of the Home tab.
The Ideas icon is relatively new, near the right side of the Home tab.

Caution

The Ideas icon was first introduced for "#27 Get Ideas from Artificial Intelligence" on page 64. Many people tried this once or twice and then might have decided it was too random to be useful. The new question box takes this artificial intelligence feature about 100 times more useful.

XX10 (1)

A new box says to Ask A Question About Your Data.

A new box at the top of the Ideas pane says 'Ask a question about your data'.
A new box at the top of the Ideas pane says "Ask a question about your data"

Type a question such as "Top 5 customers by revenue this year". Excel restates your question as Showing Top 5 Customers by total Revenue where Date is 2019.

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

Without using this feature, a person would have to know how to use Remove Duplicates and SUMIFS or how to create a pivot table with date filter and the top 10 filter.

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

In the ideas pane, type 'Top five customers by revenue this year'. The answer shows a table of five customers and their revenue for the current year.
In the ideas pane, type "Top five customers by revenue this year". The answer shows a table of five customers and their revenue for the current year.

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.