Quick Analysis Tool in Excel (Open and Use)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will show you the different features of the Quick Analysis Tool in Excel. There are 5 core features of the Quick Analysis Tool. They are Formatting, Charts, Totals, Tables, and Sparklines.

We can add data bars, analyze date, and highlight values using the Formatting tab. Then, we can create quick charts using the Charts tab. Using the Totals tab, we can find the summation and average of given values.

We can also create a simple pivot table quickly from the Tables tab. Lastly, we can insert sparklines from the Sparkines tab.

Overall we will try to cover most of the things associated with the Quick Analysis Tool.

Quick Analysis Tool Excel


Download Practice Workbook

You can download and practice this workbook.


What Is Quick Analysis Tool in Excel?

There is a Quick Analysis Tool in Excel that you can use to analyze data quickly. This is a collection of 5 essential features. They are Formatting, Charts, Totals, Tables, and Sparklines. Within each tool, you can find different functions. This Quick Analysis Tool can be handy for quick analysis. This tool was introduced back in 2013. So, you can’t use this tool in the older versions.


How to Open Quick Analysis Tool in Excel

It’s very simple to open the Quick Analysis Tool in Excel.

  • First, select your data table.
  • Then, click on the Quick Analysis button right beneath the selected cells.
  • And, the Quick Analysis Tool has been opened.

steps to open quick analysis tool

  • Also, there is a keyboard shortcut to open the Quick Analysis Tool.
  • Select any cell within your data table and use the keyboard shortcut Ctrl + Q.

Quick Analysis Tool in Excel: 5 Important Features

We will show you all 5 important features of the Quick Analysis Tool in the following section.


1. Add Data Bars, Analyze Dates, and Highlight Values Using Formatting Tab

In this section, we will use the Formatting tab to add bars, analyze dates, and highlight values.

1.1 Add Data Bars

We have taken the following dataset to add Bars using the Formatting tab.

dataset to be used for adding bars

  • First, select the data range C5:C16.
  • Then, click on the Quick Analysis button.
  • Now, from the Formatting tab, select Data Bars.

steps to add data bars with quick analysis tool

  • Therefore, the tool will add Bars right on the selected cells on the basis of their cell values.

showing result after inserting data bars


1.2 Analyze Dates

  • Now, we will show how you can analyze date values.
  • For this purpose, we have taken the following dataset that shows Temperature records on different days.

dataset to analyze date

Suppose, we want to find out the date values of last month. Today is 9th of August. And, we want to find out the dates of last month.

  • First, select the data range B5:B14.
  • Then click on the Quick Analysis button.
  • After that, select Last Month from the Formatting tab (you may not find the whole name. But the first option is attributed to Last Month).

steps to analyze date

  • By doing so, all the dates from last month will be highlighted as shown in the following image.

result showing last months dates

  • Similarly, you can analyze dates from Last Week.
  • First, copy the data range.
  • Then, open Quick Analysis Tool.
  • Then from the Formatting tab, select Last Week.
  • And, the date values of Last Week will be highlighted as shown in the following image.

showing last weeks date


1.3 Highlight Values

In this part, we will highlight text values with conditions. We have taken the following dataset of World Cup Winners and their Winning Years. From the dataset, we will highlight the countries that appear more than once.

Dataset to highlight values

  • First, select the data range C5:C14.
  • Then, open the Quick Analysis Tool.
  • After that, select Duplicate from the Formatting tab.

steps to highlight duplicate values

  • And, all the countries appearing more than once have been highlighted.

result showing highlighted values

You can also find unique values using the Unique option.

  • First, copy the data range C5:C14.
  • Then open the Quick Analysis Tool.
  • After that, from the Formatting tab, select Unique.
  • Now, you can see, the Unique values have been highlighted as shown in the following image.

steps to highlight unique values and showing result


2. Insert Charts for Better Visual Representation

We can insert a quick chart using the Quick Analysis Tool in Excel. For this method, we have taken the following dataset of Sales Report.

dataset to be used for inserting chart

  • First, select the whole dataset.
  • Then, open the Quick Analysis tool.
  • After that, from the Charts tab, select Line chart.

steps to insert line chart

  • And, the tool will create a line chart as shown in the following image.

result showing a line chart

  • There are other chart options that you can try out.
  • Select Clustered to create a clustered Column Chart.

showing clustered column chart option

  • The following image shows how a Clustered Column Chart will look using the same dataset.

output of clustered column chart

You can find more chart options from the More option based on your dataset.


3. Perform Mathematical Analysis from the Totals Tab

To perform a quick mathematical analysis, we can use the Totals tab. We have taken the following dataset to perform some quick mathematical analysis.

dataset to find total and average amount

  • First, select the whole data table B4:C16.
  • Then, open the Quick Analysis Tool.
  • After that, select Sum from the Totals tab.

steps to find total amount

  • And, the Analysis Tool will give Total Sum right at the bottom of your data table.

output showing total amount

  • Also, you can find the Average of the Sales.
  • First, select the whole data table B4:C16.
  • Then navigate to Quick Analysis > Totals.
  • And, select the Average option.
  • Therefore, you will get the Average right beneath the data table.

steps to find average and showing average value

From this Totals tab, you can carry out mathematical analysis both row and column-wise.


4. Create Pivot Table from the Tables Tab

With this Quick Analysis Tool, we can create a pivot table. We have taken the following dataset to show how we can create a pivot table using the Quick Analysis Tool in Excel.

Dataset to create pivot table

  • First, select the whole data table i.e. B4:E16.
  • Then, open the Quick Analysis Tool.
  • After that, select the first Pivot Table from the Table tab.

steps to create pivot table by product

  • And, the tool will create a Pivot Table based on Product.
  • You can choose other values from the PivotTable Fields window.

result showing pivot table


5. Insert Sparklines Quickly Using Quick Analysis Tool

Another useful feature of the Quick Analysis Tool is to quickly insert the Sparklines. To show this procedure, we have taken the following dataset of stock prices of several companies over the last 4 years.

dataset to create Sparklines

  • First, select the whole data table i.e. C6:F12.
  • Then, open the Quick Analysis Tool.
  • After that, select Line from the Sparklines tab.

steps to insert Sparklines

  • By doing so, we have inserted Sparklines right next to the data table.

result showing Sparklines

  • Also, you can insert Column bars in Sparklines.
  • Follow the previous steps and select Column type from the Sparklines tab.
  • This will give you Columns as Sparklines.

inserting Sparklines with column


What to Do When Excel Doesn’t Show Quick Analysis Tool?

Generally, you should find the Quick Analysis Tool very easily on your PC. However, if this tool doesn’t show up, you can follow these steps to activate Quick Analysis Tool.

  • First, from your Excel workbook, navigate to File > Options.
  • Then, from the General tab, check whether the Show Quick Analysis option on selection is marked or not. If not, you should mark the option.
  • And, now, you will be able to find Quick Analysis Tool from a selection.

showing how to activate quick analysis tool


How to Clear Quick Analysis Tool Results in Excel

It’s very easy to clear Quick Analysis Tool results.

  • First, select the data range C5:C16 that has Quick Analysis result.
  • Then, open the Quick Analysis Tool.
  • After that, from the Formatting tab, select Clear.

steps to clear quick analysis tool result

  •  Your analysis result will be cleared as shown in the following image.

result after clearing analysis result


Things to Remember

  • You can carry out mathematical analysis both row-wise and column-wise.
  • While creating a Pivot Table, this tool will offer many Pivot Tables based on different criteria.
  • To highlight duplicate or unique values, you will have to select only the text values.

Frequently Asked Questions

1. Why can’t I use Quick Analysis Tool in Excel?

You can’t use the Quick Analysis Tool if you are selecting blank cells or if you are selecting entire rows or columns. Also, the Quick Analysis Tool could be deactivated. You have to activate the tool by navigating File > Options > General. From this General tab, you can activate the tool.

2. What is the benefit of using the Quick Analysis Tool?

With the Quick Analysis Tool, you can analyze your data very quickly. You can find the sum, and average of a given numeric dataset. You can create quick charts or sparklines. Also, you can create a pivot table and carry out basic formatting with this tool.

3. Where does Excel display the Quick Analysis Tool?

When you select any data range, the Quick Analysis Tool will show up right beneath your selected data table.


Conclusion

Thank you for reaching this far. We have shown you the different features of the Quick Analysis Tool in Excel. We hope you find the content of this article useful. If there are further queries or suggestions, feel free to mention them in the comment section. Have a nice day!


Quick Analysis Tool Excel: Knowledge Hub


<< Go Back to Data Analysis with Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo