Excel's Ideas Feature - A Great Way To Summarize And Analyze Data

Excel’s Ideas feature offers tremendous opportunities for you to summarize and analyze data automatically. With Ideas, you no longer need to write formulas or manually create formulas to get answers to your questions – Ideas does the work for you! In this tip, you will learn how you can take advantage of Ideas.

First, A Few Details

Unfortunately, not all Excel users will have access to Ideas. In fact, Ideas is available only in versions of Excel provided through Office 365/Microsoft 365 subscriptions or Excel Online. Therefore, if you are using a perpetual license of Excel, you will not have access to Ideas.

Second, while Ideas can work with large datasets, it does not presently handle large datasets. More specifically, Ideas does not currently work with a dataset greater than 1.5 million cells.

Finally, Ideas cannot analyze all types of data. For instance, if you have merged cells in your dataset, you will need to unmerge them before running Ideas. Also, if you have dates stored as strings, Ideas will consider them to be text. Therefore, you should use Excel’s DATE or DATEVALUE functions to convert the date strings into “true” Excel dates before running Ideas.

Summarizing Data With Excel's Ideas Feature

To quickly generate summarizations of your data using Ideas, begin by clicking anywhere in the dataset. Next, click Ideas on the Home tab of the Ribbon. Ideas will then generate a series of PivotTables and PivotCharts that summarize your data automatically. You will see these summarizations in the Ideas pane, which appears on the right side of your computer window. If desired, you can insert one or more of these summarizations into your workbook. To do so, click Insert PivotTable or Insert Chart in the lower, left corner of the summarization. Figure 1 below illustrates the types of automated analyses created by Ideas.

Figure 1 - Using Ideas In Excel

Getting Exactly What You Need From Ideas

In addition to generating default summarizations and analysis, Ideas supports natural language queries. These queries enable you to extract exactly the information you need from Ideas. For example, referring to the dataset shown in Figure 1, you could type “sum of order amount for employee id 4” into the Question box at the top of the Ideas pane. In turn, Ideas would create a PivotTable that summarizes the Order Amount field for the designated team member. Further, you could refine your query by entering “sum of order amount for employee id 4 in December” and Ideas would then filter the results so that they meet that criterion.

Summary

Excel’s Ideas feature is a significant step toward enabling artificial intelligence in Excel. However, it is also a practical tool to help us uncover trends, outliers, and other vital facts that we might miss otherwise with the human eye. Further, because of Ideas’ natural language query capabilities, we can “tell” the tool precisely what we want to know, and it will respond accordingly. Excel’s Ideas feature has a lot to offer, and if you have access to it, try it today.

Click here to learn more from Microsoft about Excel’s Ideas feature.

Interested in learning more about Excel? Consider joining us for a K2E Canada Inc. learning session that focuses on Excel. You can learn more by clicking here. Also, check out the video below that provides a step-by-step guide on how you can use Ideas.

Tommy Stephens