Quick Analysis Tools In Excel

Last Updated :

21 Aug, 2024

Blog Author :

N/A

Edited by :

Nannila Jai Ratna

Reviewed by :

Dheeraj Vaidya

Table Of Contents

arrow

What Are Quick Analysis Tools In Excel?

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

  • Quick analysis tools in Excel promptly analyze any data instead of the traditional method of inserting charts or tables.
  • There are two ways to access these tools: select the cell range data that contains values, and a yellow box will appear on the bottom right-hand side, which is the quick analysis tool, or press "CTRL + Q" for quick analysis tools.
  • When analyzing the data, instead of going through various tabs, we can use the quick analysis tool to insert charts, visualizations, different formatting techniques, formulas, tables, PivotTables, and sparklines.

Note: This feature is available from Excel 2013 onwards.

When analyzing the data, instead of going through various tabs, we can use the quick analysis tool to insert charts, visualizations, different formatting techniques, formulas, tables, PivotTables, and sparklines.

These can be created in a few seconds.

Quick Analysis Tools in Excel

Where To Find Quick Analysis Tools In Excel?

Now, the important question is where this tool is. 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

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

How To Use Quick Analysis Tools In Excel?

There are five tips to use quick analysis tools in Excel. They are:

  • Quickly Insert Formatting into the data
  • Quickly Analysis inserting chart to the data
  • Quickly Analysis through totals
  • Quickly Analysis through tables
  • Quickly Analysis through sparklines

In this article, let us learn all the five methods using the following examples.

Examples

Example #1 - Quickly Insert Formatting Into The Data

Once you select the data, we can see the "Quick Analysis Tools" 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 us look at "Formatting" now. Placing a cursor on the required formatting option can see the immediate impact on our data.


    Quick Analysis Example 1-2

    We have placed a cursor on "Data Bars" and inserted data bars according to the size of the numbers. Similarly, we can use "Color Set," "Icon Set" in Excel, "Greater Than," and "Top Value," and more importantly, we can clear the Excel formatting with the "Clear" option.

  3. Click on the "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 values greater than 140, click on the "Greater Than" option 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

Example #2 - Quickly Analysis Inserting Chart To The Data

We can also insert a chart to the selected data using the quick analysis tools. Once the data is selected, click on "Charts."

Quick Analysis Example 2

Then, select the required chart here. We have chosen a clustered column chart. Then, the quick analysis tools are ready to use.

Quick Analysis Example 2-1

Like this, we can use various charts that suit our data structure.

Example #3 - Quickly Analysis Through Totals

We can also insert totals into the data by choosing "TOTALS" under "Quick Analysis." Under this, we have a variety of formulas.

Quick Analysis Example 3

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

Based on the requirement, we can make use of these formulas. Now, we have applied "RUNNING TOTAL" in Excel.

Quick Analysis Example 3-1

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

Quick Analysis Example 3-2

Similarly, we can use SUM, AVERAGE, etc.

Quick Analysis Example 3-3

Example #4 - Quickly Analysis Through Tables

We can also insert the table format in excel and PivotTable to the data under "Tables." Then, 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 "Blank," it will insert a PivotTable in the new sheet in Excel.

QAT Example 4-2
QAT Example 4-3

Example #5 - Quickly Analysis Through Sparklines

We can insert sparklines to the right of the data under the "SPARKLINES" option.

QAT Example 5

Based on our selection, it will display the sparklines to the left of the data.

QAT Example 5-1

We have selected "Column" sparkling, as shown below.

QAT Example 5-2

By making use of the "Quick Analysis Tools" in Excel, we can make a quick analysis of our data without breaking any sweat.

Important Things To Note

  • Quick Analysis is a powerful tool that helps you choose the best chart for your data with just a few clicks.
  • To get started, select a range of cells that you want to analyze.
  • Then, look for the Quick Analysis button that appears at the bottom right corner of the selected data, or press Ctrl + Q.
  • This feature will help you save time and effort by automating the process of finding the perfect chart for your data.

Frequently Asked Questions (FAQs)

1. Why is Quick Analysis not showing up?

If the Quick Analysis button does not show up after selecting your data, use the Ctrl + Q shortcut to activate it. It's important to remember that this button only becomes active for a specific data selection and does not appear when highlighting blank cells, entire columns, or rows.

2. How do I get rid of quick Analysis in Excel?

You can turn on/off the quick analysis feature in Excel in the Excel options menu. Just go to “Options” under the “File” tab. Under the General tab, check or uncheck the “Show Quick Analysis Options on Selection” tick box to toggle it on/off.

3. What conditional formatting rules can be applied using Quick Analysis?

When working with data in Excel, the formatting options available to you will depend on the type of data you are dealing with. For instance, if you are working with numeric data, you will be able to choose from formatting options such as Data Bars, Icon Set, Greater Than, Top 10%, etc. On the other hand, if you are working with text data, you will have the option to highlight duplicate or unique values, cells that contain specific text or match a particular string.

Recommended Articles

This article is 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 templates. You may learn more about Excel from the following articles: -