Since the feature uses Microsoft’s AI service, it will only be available for customers who subscribe to Office 365 on Windows or Mac or who use Excel Online. It won’t be available to users of the perpetual licenses, such as Office 2016 or Office 2019.
DEVELOPED BY THE IDEAS TEAM
Using the Natural Language Query involves entering a question into a box labeled “Ask a Question About Your Data.” So far, my only complaint with the feature is that it will be difficult for people to discover since you have to click on the Ideas icon in order to find the box.
Microsoft started experimenting with AI in Excel back in 2017 with a feature originally called “Insights” on the Insert tab. As time went by, this feature was moved to the right side of the Home tab and rebranded as “Ideas”.
If you select one cell in your data and click the Ideas icon, Excel will generate a series of more than 30 tiles with observations about the data. I like to joke that when I tried the Ideas function, some of the 30 findings were actually useful—because many of the others made it clear that they were written by “artificial” intelligence. For example, Ideas almost always “discovers” a positive correlation between quantity and profit. This is far from a groundbreaking discovery: If you sell more, you make more. Every time Ideas gave me an obvious answer like this, I would sigh.
Disappointing results like those probably mean that if you’ve tried the Ideas icon yourself, there’s a good chance you’ll never click it again. That would mean, however, that you’d miss discovering the new “Ask A Question About Your Data” box.
To use the feature, you type a sentence describing what you want to know, and then the AI server parses your sentence and performs the analysis. This enables people who may not be comfortable creating charts and pivot tables to still get answers from their data.
TESTING SENTENCES
The feature is brand new and still in preview, but it provides impressive results.
For example, type “Top 5 Customers by Sales This Year” into the “Ask a Question…” box, and the feature will show you a summary of the top five customers with the data filtered to only records falling in 2019.
This is impressive given that the original data set included columns for Region, Category, Product, Date, Customer, Sector, Quantity, Sales, COGS (cost of goods sold), and Profit.
Change your question to specify “Top 5 Customers by Sales Last Year,” and the results will be filtered to 2018.
The data also includes a field called Name that stores the names of sales reps. When you ask a question that starts with the word “Who,” the Natural Language Query will give you results based on the Name field.
The feature already understands some synonyms. For example, when you ask for the top three categories, it knows you’re talking about the Category column. But the current release of the product isn’t smart enough to know that Sales and Revenue are the same column.
Here are some sentences that worked well:
- Which customers bought product XYZ
- Sort sector based on total sales
- Total sales and total cogs
- Sales by Sector as table
- Sales by Product as Chart
If you ask a question and like the answer, you can click the Insert icon to have Excel insert a new worksheet with the results of the question. Here’s the answer to “Percentage of total sales for each sector.”
Some results are presented as a pivot table and pivot chart, but other results are simply inserted as a static table without any SUMIF formulas. This could be good or bad. As a simple table, it’s easy to copy and paste the results elsewhere, such as Word or PowerPoint. But since the table doesn’t include formulas, it won’t update in Excel if the underlying data changes.
DATA SNAPSHOT
When you click the Ideas icon, your Excel data is sent to an AI server. Microsoft’s privacy policy is strict, and no human ever gets to see your data before it’s deleted.
There’s one thing about the snapshot nature of Ideas that can trip you up. In my testing, I asked some questions and didn’t get the answers I wanted. For each one, I quickly added a new calculation to the original data and asked again. Excel still didn’t understand what I wanted. The problem: If you change the underlying data, you have to close the Ideas pane and click Ideas again so it sees the new data in your worksheet.
A GIANT LEAP
While the old Ideas command was mildly interesting, it often couldn’t find truly useful trends. By allowing the user to type a sentence and then having the AI server calculate the answer, it seems like an order of magnitude improvement in the usability of the feature.
SF SAYS
Microsoft should do something to help more people discover the Natural Language Query.
December 2019
