How to use the Quick Analysis Tool in Excel

In this simple guide, we will run through how to use the Quick Analysis Tool in Excel, with some examples.

This guide will cover:

What is the Quick Analysis Tool in Excel?
How to activate it
Use cases and examples

Table of Contents

What is the Quick Analysis Tool?

The Quick Analysis Tool in Microsoft Excel is a really handy feature that provides a whole range of functions that can be accessed with a single click.

This helps users to quickly analyse data or build out visualisations without any manual work.

The Quick Analysis Tool is broken out into 5 key categories:

Formatting
Charts
Totals
Tables
Sparklines

How to activate the Quick Analysis Tool in Excel

Generally this feature should be turned on by default, but just in case it is turned off for any reason, the below steps will help you activate it for use:

First, under the File tab, click on Options in the bottom left corner:

Under the General section, tick the box for Show Quick Analysis Options on Selection:

How to use the Quick Analysis Tool with Examples

Using the Quick Analysis Tool is really easy. To bring up the tool itself simply highlight a range of cells and a small square box will appear in the bottom right corner of the bottom cell like below:

Let’s look at some examples.

Under the Formatting heading, we can overlay the cells with some visual information. For example selecting Data Bars will overlay bars on each of the values, with the size based on the overall cost of each row based on the values in overall range that was selected. See below for example:

This helps provide a nice quick snapshot of which are the highest and lowest values in the dataset without needing to sort or pivot the data and can come in quite handy.

Lets move on to the Charts tab. Let’s say we had a couple of columns of data – both date and cost, and wanted to clearly see in a visual way what the overall trend is across this period without manually making any kind of chart.

In this case we can simply select a line chart for example, for a quick and easy visualisation:

Moving on to the Totals tab, we have a range of quick analysis options to calculate values such as the sum of the highlighted cells. One handy function as an example is calculating running totals – something that can be a bit more manual to do otherwise.

This simply adds a whole new column with the overall running totals across each row:

The Tables tab is pretty self explanatory – it simply converts the range of selected cells into a table format, which provides the data with a series of additional data analysis and formatting options:

Finally, we have the Sparklines tab. This series works best across columns rather than rows as has typically been the case in previous examples.

For this example we have a quick table that has Sunday-Saturday sales across the columns, with each week being a new row. Within each cell we have a set of values:

There are 3 Sparkline options – Line, Column and a Win/Loss format:

Using the Line option, Excel fills in the cells to the right of the dataset as below:

As we can see, this provides data similar to the line chart in the Charts example, but within a single cell. This can be a great way to very quickly and easily visualise data row by row when working with larger datasets that may require multiple visualisations.

This sums up our guide on how to use the Quick Analysis Tool in Excel. This tool provides us with a range of easy analysis tools to use when looking to quickly summarise or visualise data without the need to set manual rules or create advanced visualizations.

For more tips and tutorials on using Excels built in functions be sure to check out our Excel Tips page.