How to Use Excel Analyze to Visualize Data

You can now listen to this article
Voiced by Amazon Polly

There is a good argument that the computerized spreadsheet created Apple and the personal computer revolution.

analyze data

Dan Bricklin created the first computer-based spreadsheet, VisiCalc, in 1979. Initially, it only ran on an Apple II computer. Before this, companies invested time and money creating financial projections with manually calculated spreadsheets. Changing a single number meant recalculating every single cell on the sheet. VisiCalc allowed them to change any cell, and the entire sheet would be automatically recalculated. Bricklin said, “VisiCalc took 20 hours of work for some people and turned it out in 15 minutes and let them become much more creative.”

Fast forward to today, and Microsoft Excel is the de facto standard for spreadsheets. And it continues to improve.

The new “Analyze Data” capability in Excel helps you understand your data through high-level visual summaries, trends, and patterns. Click a cell in a data range and click the “Analyze Data” button on the Home tab. Excel will analyze your data and return engaging visuals about it in a task pane.

Note: This feature is rolling out to customers gradually, so your version of Excel may not yet have it.

What does Analyze Data work on?

Analyze Data works best when your data is formatted as an Excel Table with a single header row at the top. It also works best with clean, tabular data.

Here are some tips for getting the most out of the Analyze Data feature:

  1. It 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 complex or nested data, you can use Power Query to convert tables with cross-tabs or multiple rows of headers.

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

  • It 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 “Analyze Data” 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.
  • Analyze Data 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, format the cells using Center Across Selection. Press Ctrl+1, then go to Alignment > Horizontal > Center Across Selection.

“Analyze Data” may be helpful when you are working on a presentation for a client. For example, when you are working with claims data, it may be easier for the client to understand their situation when creating a better visualization of the data. It also might be a handy function to try out with your agency data. That’s where this new function may come in handy.

What other tools do you use to help clients understand data?

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *