Transform data in seconds with the new Quick Analysis tool


by Kara Hiltz

Application: Microsoft Excel 2016/365

Operating System: Microsoft Windows

You have your raw data, but now you need to make sense of it. Sometimes you might not know where to start, but the new Quick Analysis tool offers suggestions based on the nature of your data—and lets you apply those suggestions in seconds.

To explore the Quick Analysis tool, we’ll:

  • Get our raw Excel data ready for analysis.
  • Select the data to access the Quick Analysis tool, new to Excel 2016 and Excel 365.
  • Navigate the Quick Analysis tool and select an analysis option that fits our needs.

Collecting data is the easy part; making meaning of your Excel data can prove a challenge. When you aren’t sure where to start with data analysis, the new Excel 2016/Excel 365 Quick Analysis tool can give you the guidance you need.

In only a few short minutes, you can add formatting, create charts, calculate totals, build a table, or produce sparklines, as shown in Figure A, all based on the nature of the Excel data you’ve selected.



You can add data bars or other formatting options, chart your data, or add sparklines in seconds all from the Quick Analysis tool.

Get your raw data

The beauty of the Quick Analysis tool is that you don’t need to have any particular data setup or data type. The Quick Analysis tool works with any kind of data, and the options presented in the Quick Analysis tool will adjust based on your data. It is a good idea, however, to make sure that there are no empty cells, blank rows, or blank columns splitting up your data. When we select the data later, it will be easier if it’s contiguous.

In our example, we have a simple chart of daily expenses versus income, along with daily profit, as shown in Figure B. We’ll see what the Quick Analysis tool suggests based on the data we’ve provided.



This data has some number formatting applied and a formula in the Profit column, but otherwise there isn’t much data analysis.

Access the Quick Analysis tool

The Quick Analysis tool displays as a Smart Tag when you select a set of data. Many users quickly dismiss Smart Tags, so keep your eyes open for this helpful tool.

To open the Quick Analysis tool:

  1. Select any cell within your data range.
  2. Press [Ctrl]A to select all contiguous cells within your data range. (This won’t work if you have blank cells or empty rows/columns.)
  3. Note the Smart Tag that displays in the lower-right corner of your selected data and hover your cursor over the Smart Tag to see the description, as shown in Figure C.

Tip: If the Quick Analysis tool’s Smart Tag doesn’t display, you can use the keyboard shortcut [Ctrl]Q to access it manually.



The Quick Analysis tool offers data analysis options based on the kind of data in your selected data range.

Check out the Quick Analysis tool options

When you click on the Quick Analysis tool, you’ll see a palette with a few tabs that include different data analysis options. While these features are spread out in differe[...]

Join NowClose
Return to the ExcelSkillsSociety's homepage