It gives you quick access to some of the commonly used data analysis functionalities in Excel so that you don’t have to go into the ribbon and find the correct option (hence saving some clicks and a little bit of time). In this tutorial, I will tell you everything you need to know about the Quick Analysis Tool in Excel. I’d also cover different use cases where you can use this tool to be more productive and save time.
Where is the Quick Analysis Tool in Excel?
Excel Quick Analysis tool was introduced in Excel 2013, so you won’t find it in versions before Excel 2013. If you’re searching for the Quick Analysis Tool in the ribbon in Excel, you won’t find it. It appears automatically whenever you select a range of cells an icon in the bottom right part of the selection. You can also use the keyboard shortcut Control + Q to show the Quick Analysis Tool icon, in case it doesn’t show up. By default, this tool is enabled in all the Excel versions (in and after Excel 2013). In case you still can’t access the Quick Analysis Tool, you can follow the steps here to enable it.
What Can You Do with Quick Analysis Tool in Excel?
Quick Analysis Tool tries to give you some commonly used functionalities in Excel. Note that the data analysis options you see in the Quick Analysis Tool are not unique and you can also find these in the ribbon in different tabs in Excel. The point of showing this tool is to enable you to access these faster. Let’s now look at all the options you have available in the Quick Analysis Toolbar:
Conditional Formatting
The first option you get in the Quick Analysis Toolbar is Conditional Formatting. Within this, you will see five commonly used Conditional Formatting options. Note that these options would change based on the data you have selected. For example, if you have numeric data, you will see options such as Data Bars, Icon set, Greater than, Top 10%, etc. If you have text data, then the options would be to highlight duplicate or unique values or highlight cells that contain specific text or matches a specific string. And similarly, in case you have dates data, the options would accordingly change.
Charts
Based on the data in your selected range, you will be shown some commonly used chart types in Excel. In case you don’t find the chart type you’re looking for, you can click on the More Charts option, which will open the ‘Insert Chart’ dialog box, where you get all the charting options.
Totals (Calculations)
This could be quite useful if you quickly want to get calculations such as sum, count, average, running total, etc. Again, the options you see here would be dependent on the data in the range you have selected. If you only have text data in the selected range, then this would only show you the option to get the Count.
Insert Table/Pivot Table
If you have tabular data that you quickly want to convert into an Excel Table, it can be done using the Quick Analysis Tool (option in the Table’s header). You also get an option to quickly insert a new Pivot Table in a new sheet using the selected data as the source.
Sparklines
And lastly, you have the sparkline option where you can quickly insert these in-cell charts that can do wonders in making your data more comprehensible. You can use it to quickly insert the Line, Column, or Win/Loss sparklines.
Using Quick Analysis Toolbar in Excel – Some Practical Examples
Now let’s look at some practical examples where using the Quick Analysis Toolbar can save you some time.
Get the SUM or COUNT of Multiple Rows/Columns in One Go
Below I have a dataset where I have the sales of different product lines (printers, scanners, laptops, and projectors) for seven different states in the US. I want to get the sum of each row and each column. This will tell me what was the total sale value of each product line as well as the total sale value for each state. Below are the steps to do this using Quick Analysis Tool: The above steps would instantly add a new row that will show you the sum of total sales of each product. Similarly, if you want to get the sum by states, you can repeat the same steps, and in step 4, instead of the first Sum option (the one in blue color), select the second Sum option (the one in the yellow color). This will add a new column that will show the sum of all values for each state. Note that when you use this option, Excel creates and enters the formula for these in the cells. If you click on any of these cells filled by the Quick Analysis tool and check the formula bar, you will see the formula it has used. In this example, I have shown you how to get the sum of values in rows or columns, and you can follow the same process to get the count of values in rows or columns. Just change the selection in Step 4 (and select the Count options instead)
Calculate Percentage Total For Multiple Rows/Columns
Just like we added the sum and count values for rows/columns, you can also add the % Total values. Below I have the same dataset and I want to get the percentage total value for each product as well as each state. This will tell me what percentage of total sales is because of the Printers or Scanners. Similarly, having the percentage total value in the column would tell me what percentage of sales is contributed by which state. Below are the steps to do this: The above steps would insert a new row that will show the percentage total of all the products. Similarly, if you want to get the percentage total of all the states, you can repeat the same steps, and in step 4, choose the second % Total option (the one in the yellow color). This will insert a new column that will show the percentage-wise sales by each state.
Highlighting All the Cell With Value Greater Than a Specified Value
If you’re working with numeric data and want to quickly highlight all the cells with a value greater than a specific value, you can do that with a few clicks using the Quick Analysis Tool. Below I have a dataset where I have the sales figures for different states, and I want to highlight all the cells where the value is more than 100000. Here are the steps to do this: The above steps would instantly highlight all the cells that have a value greater than 100,000. Note that the above steps have used the Conditional Formatting option to highlight the cells. If you want to modify the conditional formatting rule, you can do that by clicking on the Conditional Formatting option (in the Home tab) and then clicking on ‘Manage Rules’
Add a Running Total in Column or Row
Below I have month-wise sales data and I want to add a running total column for the sales values. Having a running total column is useful when you want to track how the sales are progressing. For example, I can quickly scan through this column and figure out when the total sales value crossed 30,00 or 50,000. Below are the steps to add a running total column using the Quick Analysis tool: The above steps would instantly add a new column that will show the running totals. Note that for this to work, the adjacent column (where the running total values get filled) needs to be blank. In case it’s not blank, you will see a prompt that will ask you to overwrite the data or cancel the operation In case you see the hash symbols instead of the values in the running total column, expand the column width to make sure it’s wide enough to accommodate the numbers. Just like I showed you how to add a running total column, you can also add the row that shows the running total. For this, you need to select the blue-colored Running Total option (there are two running total options – the blue one for rows and the yellow one for columns)
Add a New Column to Get Sum of Rows
Another useful use of the Quick Analysis tool can be to add a row or column that gives the sum of the values in that row/column. Below I have the sales data for different product lines and different states in the US. It would be useful to have an additional row and an additional column that shows the sum of all the values. This would help me know what’s the total sales for each product line and total sales for each state. Below are the steps to do add a row that shows the sum of all values in each column: The above steps would instantly add a new row with the title ‘Sum’ that will give you the sum of all the columns that have product-wise sales data. Similarly, if you want to add a column that shows the sum of all values in the row, repeat the same steps, but after step 3, click on the small arrow icon at the right to get more options, and then click on the Sum option (the one in Yellow color) Note that in case you see hash symbols instead of the values, the column width needs to be increased to make the values visible.
Highlight Dates That Occur Last Month or Last Week
Quick Analysis tool has some useful options when working with dates. One that I find particularly useful is the option to highlight dates that occur in the last week or the last month (where Excel picks up the current date value from your system’s setting). Below I have a dataset where I have the dates in column A and the task list in column B, and I want to know what dates occurred in the past month or the past week. Here are the steps to highlight all dates in the previous month: The above steps would instantly highlight all the dates that occur in the previous month. Note that the dates being highlighted are using the current date of my system at the time of taking this screenshot A few things you should know when using the Quick Analysis tool to highlight cells using Conditional Formatting:
When you use any of the options in the Formatting group, it uses conditional formatting to create a rule for the selected cells. This rule remains in place unless you remove it by clicking on the ‘Clear Format’ option (it’s also there in the Formatting group)When you click on more than one option, more than one rule is applied to the selected cells. For example, if I first click on the option to highlight cells with dates occurring in the last week and then click on the option to highlight cells with dates occurring in the last month, both the rules would be in place.
Highlight All Cells That Contain a Specific Text
So far, we have seen examples of using the Quick Analysis tool with numeric data and dates. But you can also use this with text data. Below I have a dataset where I have the product id in column A and the price in column B. I want to quickly highlight all the cells where the product id contains the text ‘KL’. Below are the steps to do this using Quick Analysis options: The above steps would instantly highlight all the cells in column A that contain the text ‘KL’
Highlight Cells with Duplicate Text
Identifying duplicates in a dataset is a common task for many Excel users. While there are multiple ways to highlight duplicates in Excel, the Quick Analysis tool makes it really quick. Below I have a dataset where I have employee names in column A and the training dates assigned to them. Here are the steps to quickly highlight all the cells with duplicate names: That’s it! Done. It will highlight all the cells that have the names that have been repeated. For this to work, the text in the cells needs to be exactly the same. So make sure there are no leading or trailing space characters in the cells. If there are extra space characters in one cell with a name, and not there in the other one, Excel would consider these as different.
Highlight Cells with Unique Text
Just like we highlighted cells with duplicate text, you can also highlight cells that have unique values. Let’s take the same example (dataset below), where I have the employee names in column A and their training date in column B, and I want to identify names that only occur once. Below are the steps to do this using the Quick Analysis tool: The above steps would instantly highlight all the names that only appear once in the list.
Remove Conditional Formatting from the Selected Range of Cells
Conditional Formatting is an amazing tool that I use quite often, and a lot of times, I need to remove the Conditional Formatting rules that have already been applied. Sometimes I just don’t need these, or I need to start from a blank slate that requires removing all the previously applied rules. Quick Analysis tool makes it really easy to remove conditional formatting by giving you access to that option with a single click. Below are the steps to remove Condition Formatting using the Quick Analysis tool: Note that the above steps would only remove the Conditional Formatting, and not the regular formatting such as borders or cell colors or font size/type, etc.
Quickly Insert a Cluster/Stacked chart, Pie Chart, or Scatter Chart
Apart from all the other ways to insert charts in Excel, you can also use the Quick Analysis tool to insert a chart. While you only get some limited options in the Quick Analysis tool, it could be faster if you want to insert common chart types such as a line chart or a clustered column chart. And in case you want more charting options, there is a More Charts option available as well. Below I have a dataset where I have the month names in column A and the sales values in column B, and I want to create a line chart using it. Here are the steps to do this: The above steps would insert a line chart using the selected dataset. Note that when you hover over the charting options in the Quick Analysis tool, it will show you a preview of how each chart would look like using the selected data.
Quick Analysis Tool Not Showing Up in Excel – How to Fix?
In case you’re using the latest Excel version (Excel 2013, 2016, 2019 or Excel with Microsoft 365), And you do not see the Quick Analysis Tool when you select a range of cells, most likely it’s disabled. And this has an easy fix – you enable it. Below are the steps to enable the Quick Analysis Tool in Excel: Note that even if the Quick Analysis tool is disabled in your workbook, it would still show up when you use the keyboard shortcut Control + Q (hold the Control key and press the Q key)
Can I add More Options to the Quick Analysis Tool?
At the time of writing this article, unfortunately, you cannot add more options to the Quick Analysis Tool. However, just like many features or functionality in Excel, there is a good possibility that they might allow users to add custom options to the Quick Analysis Tool. Even if it doesn’t allow users to add custom options, I am confident that based on user feedback, the team at Microsoft Excel would add more useful options. As an alternative, you can consider using the Quick Access Toolbar which allows you to add custom options and even macros that can be accessed with a click or a keyboard shortcut.
Can I Disable the Quick Analysis Tool?
If you don’t want the Quick Analysis Tool icon to show up whenever you select any range of cells, you can disable it. The steps are exactly the same as I covered above in the ‘Quick Analysis Tool Not Showing Up in Excel – How to Fix?‘ section: Even when it’s disabled, you can still access it using the keyboard shortcut Control + Q In this article, I showed you how to use the Quick Analysis tool to get access to some useful options. I also covered some practical examples and how these can be used in your day-to-day work. Overall, it’s quite useful and can help you become more productive when working with Excel. And although at the time of writing this article, there are limited options in the tool and it does not allow adding custom options to it, I have seen continuous improvements being made to the tool and it may allow this functionality in the future. Other Excel tutorials you may also like:
Excel Quick Access Toolbar – 5 Options You Should Consider AddingApply Conditional Formatting Based on Another Column in ExcelSearch and Highlight Data Using Conditional Formatting
