Excel Ideas: A Smart Data Visualization Tool – Natural Self Esteem

Excel offers many ways to visualize your data – charts, conditional formatting, sparklines, pivot charts, and more. However, some of these functions are quite complex to use, and finding the right visualization to show the trends, outliers, and other useful information in your data can take time.

The new Ideas button in the Office 365 subscription versions of Excel actually creates the visualizations and charts for you, showing trends and outliers in your data.

In theory, all you have to do is select one or more cells and then click the Ideas button on the Home tab of the ribbon. You’ll then see a task pane with all suggested charts for trends, outliers, correlations, and pivot charts for what’s interesting about your data, with the chart type automatically selected and all axes, labels, and titles filled in.

These are some of the basic features of Power BI – they use the same AI, but since your Excel spreadsheet probably doesn’t have a complex data model defined, which most Power BI data sources do, the results won’t be as good. Depth. But just seeing which pieces of data don’t match the rest can help you quickly spot unusually good or worryingly bad data. This is particularly useful when you need to look at a data set that you didn’t create yourself: Ideas can quickly get you to the important highlights.

In practice, making ideas useful takes a little more preparation, and what you get out of it depends on the data you start with. To get useful labels, you first need to format your data as a table with a single header at the top and use the terms you want to see on the charts. Select your data and press Ctrl-T or choose a table style from the Format as table drop-down list on the ribbon. If the table doesn’t cover all the rows or columns you want to include, click Resize Table on the Design ribbon tab and either type or drag the letter and number of the outermost row and column make a new selection before clicking OK.

If there isn’t already a row of headings, enter them at the top of each column—don’t reuse heading names, don’t leave spaces, and stick to a single heading for each column, with no merged cells or duplicate heading rows. It’s better to use a separate sheet to make a copy of your data if you need to format it as a report with fancier headers, but you can also right-click a cell and select “Format Cells…” and then select the Alignment tab and Alignment Set Horizontal to Center Across Selection.

SEE: Software Use Policy (Tech Pro Research)

If you need to merge tables, set up nested data, or create a more complicated layout, use the Get & Transform tools (formerly called Power Query): From the Data tab, select Get Data/Combine Queries and either “Merge” or “Append”. This way the layout of the data becomes part of the data model that Excel Ideas can use instead of just being text in the spreadsheet.

The more categories you have in the spreadsheet, the more opportunities Excel has to group the data and look for patterns, trends, and correlations. So if your data is fairly flatly organized, add some extra columns to use for topics and categories.

The file must be saved as an XLSX file (or XLSM if it contains macros); Ideas will not work with the older XLS binary file format. If the ribbon icon is grayed out, check the file format. You also need to make sure your data set isn’t too large; Ideas can only work with up to 16MB of data (that’s about 250,000 cells). If your data is larger, click the drop-down arrow for each header and use Excel’s filters to zoom out. (If it’s by date, use recent years or filter out really small numbers) and make a copy to revise ideas.

You also need to verify that the cells are formatted correctly; Having cells with text formatted as dates will cause Ideas to become confused. And if you have dates that are written as text and not formatted as dates, Ideas doesn’t know they are dates. Select them and set the Cell Format to Date. Excel warns you about dates with only two digits for the year; Click on the warning icon and convert them to show the full four digits.

Before you can use Excel Ideas or any of the other recent AI-powered tools in Office like Word Editor or Outlook Focused Inbox, you need to turn on Intelligent Services (even if you’ve used earlier versions of it, you may need to turn this setting back on because it uses the content of your documents to make suggestions). You will see a popup when you select “Ideas” in the ribbon, or you can select “File/Options” and check “Turn on services” under “Office Smart Services”.

Smarter than a wizard

Initially, Ideas finds only a handful of different classes of cognitions. Trends are increases, decreases, or repeating patterns in the data, such as B. Seasonal results. Rank selects sets of numbers that are noticeably higher than the rest, and Majority finds the categories that make up the largest portion of a total. Outliers are unusually good or bad results in data organized over time or in data correlated with other data. Depending on your data, you might get multiple results for some insights. Ideas can also suggest useful groupings for organizing your data.

There are often more suggestions than will fit on the screen, and the more useful ones can be hidden, so it’s worth clicking through to see them all. The ideas area is also not dynamic; If you edit your data or select a different table to get insights, you need to click the Ideas button to generate new suggestions. This allows you to keep the charts in the task pane while you review the underlying numbers that might explain what you’re seeing.

On a suggestion, click the Insert button to place the chart or PivotChart in the table for further study. For a PivotChart, this creates a new table tab with the filtered data the PivotChart is based on and opens the PivotChart Fields task pane so you can experiment with adding more fields to see what else you think about the data can experience.

Oddly enough, charts don’t use color themes that apply to your table (instead you stick with a fairly accessible but simple palette that clearly highlights the single significant value), but PivotTables inherit your theme settings. Far more annoying is that all dates in charts are set in US format with MM/DD/YYYY; You can change this, but it should be inherited from the date format used in your spreadsheet.

The chart titles are clear and descriptive, and the charts follow data visualization best practices such as: B. Displaying data labels horizontally so they’re easy to read, and sorting bars in descending order so you can clearly see the full pattern even if you can’t make out the exact value.

“Ideas” is far more useful than the “Recommended Charts” tooltip because it doesn’t graph all the data in your spreadsheet, but only selects the data series that tell an interesting story. That’s handy for charts, but it’s phenomenally useful for PivotCharts, as it unlocks a very powerful feature that many users find off-putting – featured charts can only create blank PivotCharts, so you have to do all the hard work to get the right data series in the right position to bring place.

Even with the limited number of insights currently available and the formatting you may need to do to make your data suitable for Excel Ideas, this is a great way to quickly pull important information out of a spreadsheet. It’s also a gentle introduction to some of the more powerful features in Excel. There’s no better way to understand how tools work than to see them in action with your own data.

See also

Leave a Comment