Ideas in Excel

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Ideas in Excel empowers you to understand your data through high-level visual summaries, trends, and patterns. Simply click a cell in a data range, and then click the Ideas button on the Home tab. Ideas in Excel will analyze your data, and return interesting visuals about it in a task pane.

Animated image of Ideas in Excel in action

Note: This feature 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, French, Spanish, German, Simplified Chinese, and Japanese. If you are an Office 365 subscriber, make sure you have the latest version of Office. To learn more about the different update channels for Office, see: Overview of update channels for Office 365 ProPlus.

What data does Ideas work on?

Ideas works best when your data is formatted as an Excel Table with a single header row at the top.  

Here's an example:

Sample Excel Table

Ideas works best with clean, tabular data.

Here are some tips for getting the most out of Ideas:

  1. Ideas works best with data that's formatted as an Excel Table. To create an Excel Table, click anywhere in your data and then press Ctrl+T.

  2. Make sure you have good headers for the columns. Headers should be a single row of unique, non-blank labels for each column. Avoid double rows of headers, merged cells, etc.

  3. If you have complicated, or nested data, you can use Power Query to convert tables with cross-tabs, or multiple rows of headers.

Didn't get Ideas? It's probably us, not you.

Here are some reasons why Ideas may not work on your data:

  • Ideas doesn't currently support analyzing datasets over 16MB (approximately 250k cells). There is currently no workaround for this. In the meantime, you can filter your data, then copy it to another location to run Ideas on it.

  • String dates like "2017-01-01" will be analyzed as if they are text strings. As a workaround, create a new column that uses the DATE or DATEVALUE functions, and format it as a date.

  • Ideas can't analyze data when Excel is in compatibility mode (i.e. when the file is in .xls format). In the meantime, save your file as an .xlsx, .xlsm, or xslb file.

  • Merged cells can also be hard to understand. If you're trying to center data, like a report header, then as a workaround, remove all merged cells, then format the cells using Center Across Selection. Press Ctrl+1, then go to Alignment > Horizontal > Center Across Selection.

Ideas works best with clean, tabular data.

Here are some tips for getting the most out of Ideas:

  1. Ideas works best with data that's formatted as an Excel Table. To create an Excel Table, click anywhere in your data and then press COMMAND +T.

  2. Make sure you have good headers for the columns. Headers should be a single row of unique, non-blank labels for each column. Avoid double rows of headers, merged cells, etc.

Didn't get Ideas? It's probably us, not you.

Here are some reasons why Ideas may not work on your data:

  • Ideas doesn't currently support analyzing datasets over 16MB (approximately 250k cells). There is currently no workaround for this. In the meantime, you can filter your data, then copy it to another location to run Ideas on it.

  • String dates like "2017-01-01" will be analyzed as if they are text strings. As a workaround, create a new column that uses the DATE or DATEVALUE functions, and format it as a date.

  • Ideas can't analyze data when Excel is in compatibility mode (i.e. when the file is in .xls format). In the meantime, save your file as an .xlsx, .xlsm, or xslb file.

  • Merged cells can also be hard to understand. If you're trying to center data, like a report header, then as a workaround, remove all merged cells, then format the cells using Center Across Selection. Press Ctrl+1, then go to Alignment > Horizontal > Center Across Selection.

Ideas works best with clean, tabular data.

Here are some tips for getting the most out of Ideas:

  1. Ideas works best with data that's formatted as an Excel Table. To create an Excel Table, click anywhere in your data and then click Home > Tables > Format as Table.

  2. Make sure you have good headers for the columns. Headers should be a single row of unique, non-blank labels for each column. Avoid double rows of headers, merged cells, etc.

Didn't get Ideas? It's probably us, not you.

Here are some reasons why Ideas may not work on your data:

  • Ideas doesn't currently support analyzing datasets over 16MB (approximately 250k cells). There is currently no workaround for this. In the meantime, you can filter your data, then copy it to another location to run Ideas on it.

  • String dates like "2017-01-01" will be analyzed as if they are text strings. As a workaround, create a new column that uses the DATE or DATEVALUE functions, and format it as a date.

We're always improving Ideas

Even if you don't have any of the above conditions, we may not find a recommendation. That's because we are looking for a specific set of insight classes, and the service doesn't always find something. We are continually working to expand the analysis types that the service supports.

Here is the current list that is available:

  • Rank: Ranks and highlights the item that is significantly larger than the rest of the items.

Line chart showing Payroll with noticeably higher Spend

  • Trend: Highlights when there is a steady trend pattern over a time series of data.

Line chart showing Spend increasing over time

  • Outlier: Highlights outliers in time series.

Scatter chart showing outliers

  • Majority: Finds cases where a majority of a total value can be attributed to a single factor.

Donut chart showing People accounting for the majority of Spend

If you don't get any results, please send us feedback by going to File > Feedback.

Because Ideas analyzes your data with artificial intelligence services, you might be concerned about your data security. You can read the Microsoft privacy statement for more details.

Ideas uses material from third-parties. If you'd like to read the details, see Licensing information for Ideas.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×