Quick Analysis Tool

Excel’s Quick Analysis Tool is the easiest way to analyze your data instantly using different tools (Formatting, Charts, Totals, Tables, or sparklines)

Today’s lesson will explain how to use this feature in Excel.

Table of contents:

  1. How to turn on the Quick Analysis feature
  2. Formatting Ranges using Quick Analysis Tool
  3. Inserting charts using QAT
  4. Quick Analysis with TOTALS (Sum, Average, Count, Running Total)
  5. Tables
  6. Sparklines

How to turn on the Quick Analysis feature?

You can choose two methods to activate the tool.

Go to Excel Option on the General tab. Check Show Quick Analysis options on selection. From now the QAT toolbar will appear by default.

how to turn on quick analysis tool

If you like using Excel shortcuts, apply the Ctrl + Q combination.

keyboard shortcut

Click the Quick Analysis button on the bottom-right corner of the range. The custom toolbar appears. You can choose from the following tools: Formatting, Charts, Totals, Tables, and sparklines.

Formatting Ranges using Quick Analysis Tool

Now let us see how to apply conditional formatting using QAT. We strongly recommend our definitive guide if you want to learn all about conditional formatting.

quick analysis tool live preview

The key differences between QAT and regular methods:

We get a quick live preview and select the option you want. The Quick Analysis Tools provides the most used functions:

  • Data Bars, color-based highlighting, icons sets
  • ‘Greater than’ quick formulas
  • Top N percentage
  • Clean formatting rules

That is all we require! Let us assume that you prefer the common way to use all conditional formatting features. Go to the Home Tab and click on the ribbon.

classic method

Inserting charts using Quick Analysis Tool

Select the data range, then click Charts on the floating toolbar. The button appears at the bottom right corner of the selected range.

Based on the type of selected data, you’ll see the most recommended chart types with previews. If you choose another chart type, go to the Chart tab on the ribbon or click ‘More…’ on the floating toolbar.

After that, select your preferred chart type and click it.

recommended charts

Quick Analysis with Totals

The Totals function is useful if you want to perform a quick analysis. Pick one of the available options, and a new row will be inserted. The Totals tab is yours if you’re going to calculate the most common metrics!

qat totals for rows and columns

Select Quick Analysis, then click the ‘Total’ tab to use the feature.

Sum of Rows

In the example, we’ll analyze the range which contains products and periods. For the row-wise calculation, click on the ‘Sum’ icon. A new row will appear with a live preview.

calculate quick sum

In this case, the Quick Analysis Tool will show the total sales by quarter. Click the yellow icon if you want to apply a sum for each product. A new column will appear in column ‘F’.

Average

In the following example, try to calculate the average sales for each product. To do that, click on the yellow ‘SUM’ icon:

calculate average for columns using quick analysis tool

Count

If you want to count the items in a given column, click on the ‘Count’ icon.

count QAT function

Get the percentage of the total (% of Total)

In the example, we’ll show you how the function works. It’s unnecessary to use a formula that divides an amount by the total. Instead, click on the Totals tab and choose the ‘% Total’ icon.

Check the result below:

quick analyze the percentage of totals

Running Total

It’s a great feature to create a Running Total (cumulative sum) with a single click. Select the Quick Analysis Tool, go to Totals, and click on the ‘Running Totals’ icon.

Example: Take a closer look at the newly inserted row.

B6 = SUM(B2:B5)
C6 = SUM(B2:B5) + SUM(C2:C5), in other words: C6 = B6 + SUM(B2:B5)
quick analysis tool running total

The final result in cell E6 = 26 669.

Tables

Excel Tables play an important role in data analysis. Click on the Table icon to convert the current range into a table. If you have a larger initial data set, you can create Pivot tables too. Tables help you sort, filter, and summarize data.

tables

Click on the ‘Tables’ tab and select the ‘Table’ icon. Excel will transform the range into a Table.

Sparklines

Sparklines are in-cell mini charts, and with their help, we can create a line, columns, and win / loss chart in single cells. On the Quick Analysis Tools tab, locate the Sparklines section.

Choose your preferred chart type and apply them by clicking on it.

sparklines example

Final thoughts

The Quick Analysis Tool is a great time-saving feature in Excel, containing some pre-defined actions. If you are an Excel newbie, we highly recommend it.

Additional resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.