Note: “Ideas” is available to Office 365 subscribers in English, French, Spanish, German, Simplified Chinese, and Japanese. If you are an Office 365 subscriber, make sure you have the latest version of Office.
It All Starts With the Data
We receive a data set like the following…
…and we need to get a quick overview of the data.
All we need to do is click anywhere in the data, then select Home (tab) -> Ideas (group) -> Ideas.
A panel opens to the right of the Excel window displaying a series of miniature charts and tables.
Examining the various charts and tables, we can quickly understand the major facets of our data in a very brief amount of time.
Scrolling to the bottom of the list of ideas, we are provided a link to see MORE ideas. Now we see even more insights into our data, in case the initial offering wasn’t enough.
Adding an Idea to Our Worksheet
If you find an idea worth preserving in your workbook, click “Insert PivotTable/PivotChart” in the lower-left corner of the idea thumbnail.
This will insert the PivotTable/Pivot Chart into your workbook on a new sheet called “IdeaX” (“X” being a sheet number). As a bonus, the table is also sorted.
“Is this helpful?”
In the lower-right of each idea is the question “Is this helpful?” By clicking this you can answer a simple “yes/no” question. This provides feedback to the machine-learning, artificial intelligence engine.
If enough people respond “no”, these forms of idea responses will likely be relegated further down the ideas list, while “yes” responses will continue showing up in the initial offerings.
Handling the Unanswered Questions
Although we are presented with a myriad of responses from “Ideas”, there’s likely to be a few unanswered questions.
This is where the Natural Language engine comes to the rescue.
Note: Natural language processing is being made available to customers on a gradual basis. It will first be available to Office 365 subscribers on the Monthly Channel in English.
The top of the “Ideas” panel contains a field where you can “Ask a question about your data”.
Type a question into this field, phrasing it the same way you would ask a co-worker a question.
- “What customer name sold the most quantity laptop bag black?”
- “What region had the least sales?”
- “How many sales for Customer ID 8020?”
- “Who sold in May 2019?”
TIP: The Natural Language engine works best when you use the official column headings as references when forming your question. Asking something like “Who’s the best” will not yield much of an answer because the question is too vague. That type of question probably wouldn’t work on a real person, either.
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.
Preferences for Tables Over Charts
Most ideas will come in the form of charts. If you prefer tables, add the word “table” to the end of your question.
“Quantity sold by Customer Name table”
The opposite is true. If you are getting tables and you need a chart, add the word “chart” to the end of the question.
“Quantity sold by Customer Name chart”
Removing an Idea
Each inserted idea is placed on an individual sheet.
If you are dissatisfied with an idea, right-click the sheet with the unwanted idea and select Delete.
Answering Questions Based On Text Fields
It’s safe to say that most questions about data revolve around the aggregation of numeric-based fields, like “sum of sales” or “ average profit”.
But what about aggregations of text-based fields, like “how many customers bought laptop bag black”? These types of questions default to count aggregations.
Combining Fields In the Question
Try to get creative with your questions by combining fields to form more complex queries, such as “which Region had the highest Sales USD in July”.
“Which Customer Name had the highest Sales USD in September?”
Defining ‘Interesting’ Fields
There may be times when a field, like ‘part number’, is treated as a number and aggregated using sum, average, or some other unwanted logic.
We can fine-tune “Ideas” to help it understand text versus values.
In the “Ideas” panel, click “Which fields interest you the most?”
In the included fields list, we can define the 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 select from Sum, Average, or “Not a value”. “Not a value” would treat a field as text.
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.
NOTE: As of April 2020, the interface for this configurator (yep, it’s a real word) could use a bit of refinement. The checkboxes to the left of the field names give you the impression that you can select or deselect fields, possibly for bulk configuration of text/values definition. These checkboxes act as a toggle for field inclusion during summarization. The checkboxes don’t display checks, so it can be a bit confusing as to what is being defined.
Dynamic Updates
Because inserted ideas take the form of PivotTables and PivotCharts, the “Ideas” results can be refreshed when the data changes. The user need only right-click the PivotTable/PivotChart and click Refresh.
However, if the source data is in a plain table, not an official Excel Data Table, newly added records will not be absorbed into the logic of the PivotTable/PivotChart.
To ensure newly added records are absorbed into the logic, consider “upgrading” your plain table to an official Excel Data Table by clicking in the data and pressing CTRL-T then ENTER.
You still need to manually refresh the visualizations, but new records should be included.
Where “Ideas” May Fail
There are a few limitations of “Ideas” that may impact your analysis.
- “Ideas” has a 1.5 million cell limitation (not row, but cell.) If this is a limitation, consider filtering your data, then copying the filtered results to a separate sheet. The filtered data may now be reduced enough for “Ideas” to handle. There is currently no workaround for this limitation.
- 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 something like DATE or DATEVALUE to convert the date to a proper date format.
- “Ideas” 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 “Ideas”. Consider using “Center Across Selection” instead of “Merge Cells” if you need to have the look of merged cells in your workbook.
The Continuing Evolution of “Ideas”
“Ideas” is still in its infancy. This is a feature that will likely develop throughout our lifetimes, getting smarter and more versatile with every passing day.
If you find that your experience with “Ideas” is less than what you would have hoped, you can select File (tab) -> Feedback and offer a suggestion.
With all our collective minds, we can make this an amazing feature, eliminating hours of future work.
I’m going to send a feedback note on those confusing checkboxes right now.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 5x 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.
