Quick Analysis Tools in Excel

Excel Quick Analysis Tools

Quick analysis tools in excel are the tools provided by Excel to quickly analyze any data rather than going the older method of inserting a chart or tables etc, there are two ways to see this quick analysis tools, select the cell range data which contains values and a yellow box appears on bottom right hand side which is the quick analysis tools or we can press CTRL + Q for quick analysis tools.

Note: This feature is available from Excel 2013 onwards.

When we are analyzing the data, instead of going through various tabs, we can make use of the Quick Analysis tool to insert charts, visualizations, various formatting techniques, formulas, tables, pivot tables, Sparklines.

These can be done in a few seconds.

Quick Analysis Tools in Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Quick Analysis Tools in Excel (wallstreetmojo.com)

Where is Quick Analysis Tools in Excel?

Now all important question where is this tool. This tool appears when we select the data range in excel. For example, look at the below data.

Quick Analysis Example 1

Once we select the data, we can see a small icon to the bottom right of the selection.

Quick Analysis 1-1

I know you have seen this icon before, but you have not worried about this. Not many of the excel users make use of this wonderful tool. We will show what the various kinds of analysis tools it has are.

How to use Quick Analysis Tools in Excel? (Top 5 Tips)

You can download this Quick Analysis Tools Excel Template here – Quick Analysis Tools Excel Template

Tip #1 – Quickly Insert Formatting to the Data

Once you select the data, we can see the Quick Analysis tool icon at the bottom of the selection.

  1. Click on this icon to explore all the possible options.

    Quick Analysis Example 1-1

  2. We have “Formatting, Charts, Totals, Tables, and Sparklines”. Let’s look at formatting now. Just place a cursor on the required formatting option; we can see the immediate impact in our data.

    Quick Analysis Example 1-2

    I have placed a cursor on “Data Bars” it has inserted data bars according to the size of the numbers. Similarly, we can make use of Color Set, Icon Set in excel, Greater Than, Top Value, and more importantly, we can clear the excel formatting with the “Clear” option.

  3. Click on Color set to insert different colors.

    Quick Analysis Example 1-3

  4. Click on Icon Set to get icons for your numbers.

    Quick Analysis Example 1-4

  5. If you want to highlight all the values which are greater than 140, then click on the Greater option you will see below the window.

    Quick Analysis Example 1-5

  6. Mention the value as 140 and choose the formatting color.

    Quick Analysis Example 1-6

  7. Click on OK; we will have mentioned formatting for all the values, which are >140.

    Quick Analysis Example 1-7

Tip #2 – Quickly Analysis Inserting Chart to the Data

We can also insert a chart to the selected data by using the Quick Analysis tool. Once the data is selected, click on “Charts.”

Quick Analysis Example 2

Select the required chart here; I have selected a clustered column chart; your quick analysis is ready to use.

Quick Analysis Example 2-1

Like this, we can make use of various charts which suits our data structure.

Tip #3 – Quickly Analysis through Totals

We can also insert totals to the data by choosing TOTALS under quick analysis. Under this, we have a various variety of formulas.

Quick Analysis Example 3

We can insert SUM, AVERAGE, COUNT, % Total, Running Total, SUM to the Right, Average to the Right, count to the right, running total to the right.

Based on the requirement, we can make use of these formulas. Now I have applied RUNNING TOTAL in ExcelRUNNING TOTAL In ExcelRunning Total in Excel is also called as “Cumulative” which means it is the summation of numbers increasing or growing in quantity, degree or force by successive additions. It is the total which gets updated when there is a new entry in the data.read more.

Quick Analysis Example 3-1

We got the result for the Running total, as shown below:

Quick Analysis Example 3-2

Similarly, you can use SUM, AVERAGE, etc.

Quick Analysis Example 3-3

Tip #4 – Quickly Analysis through Tables

We can also insert the table format in excelInsert The Table Format In ExcelExcel comes with a number of table styles that you may quickly apply to a table format. In Excel, you can design and use a new custom table style of your choice. read more and pivot table to the data under Tables. Click on TABLES and choose the option you want to use.

QAT Example 4

A table will convert the range of data to table format data.

QAT Example 4-1

If you click on Blank, it will insert a pivot table in the new sheet in excelThe New Sheet In ExcelWhen we open a new workbook, there are three worksheets available by default. However, if you want to insert a new worksheet into the workbook, the shortcut key is Shift + F11.read more.

QAT Example 4-2
QAT Example 4-3

Tip #5 – Quickly Analysis through Sparklines

We can insert SparklinesInsert SparklinesSparklines in Excel are similar to a chart within a cell. They are tiny visual representations of the data's trend, whether it is increasing or decreasingread more to the right of the data under the SPARKLINES option.

QAT Example 5

Based on the selection we make, it will display the Sparkline to the left of the data.

QAT Example 5-1

I have selected Column sparkling, as shown below.

QAT Example 5-2

Like this making use of the “Quick Analysis” tool in excel, we can make a quick analysis of our data without breaking any sweat.

Recommended Articles

This has been a guide to Quick Analysis Tools in Excel. Here we discuss the top 5 tips to use quick analysis tools in excel with examples and downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion