Excel Quick Analysis Tool – The Best Guide (5 Examples)
Note: This Guide on Excel Quick Analysis tool is suitable for Excel 2013, Excel 2016, Excel 2019 and Microsoft 365 users.
The Excel quick analysis tool is a versatile feature that allows you to instantly analyze your data. Use it when you need to quickly calculate row or column totals and apply conditional formatting. You can instantly add tables, charts, and sparklines with the quick analysis tool.
Related:
How To Find Duplicates In Excel? The Best Guide
Excel Goal Seek—the Easiest Guide (3 Examples)
Create A Pivot Table In Excel—the Easiest Guide
In this guide, I’ll explain how to easily use the quick analysis tool.
We’ll cover:
- Quick Analysis Tool in Excel – Overview
- How to Use the Excel Quick Analysis tool?
- FAQs
- Excel Quick Analysis Tool – Closing Thoughts
Quick Analysis Tool in Excel – Overview
Before I begin, let me remind you that the Quick Analysis tool Excel feature is available only for Excel versions 2013 and later.
The Quick Analysis tool is a very dynamic feature that provides you with a whole range of data analysis options. Use it to instantly analyse your data at a click of a button.
It includes options under 5 categories i.e. Formatting, Charts, Totals, Tables, and Sparklines.
One unique feature of the Excel quick analysis tool is it changes its tool suggestions based on the kind of the selected data. It also offers a quick preview of the tools you are going to use.
Where is the Quick Analysis Tool in Excel?
You’ll be in for a surprise if you are searching for the Quick Analysis tool in the Excel ribbon menu. It’s not anywhere in there.
It simply appears as a small icon whenever you select a range of data. You can also use the shortcut “Ctrl+Q” after selecting a Data range to access it.
Please note that it’ll not appear if you select blank cells or select an entire column.
How to Enable the Quick Analysis Tool in Excel?
If it is still not appearing, it might be disabled. To enable the Excel Quick analysis tool, go to the File menu and click on Options. Under options locate the General tab.
There click on the “Show Quick Analysis Options on Selection” tick box to activate it.
Also Read:
How To Use Excel Countifs: The Best Guide
Excel Conditional Formatting -the Best Guide (Bonus Video)
The Best Excel Project Management Template In 2021
How to Use the Excel Quick Analysis tool?
How you wish to use the Quick analysis tool is totally up to you. You can choose any option you need for quick data analysis. Keep in mind that it tailors its suggestions according to the selected Data.
Now, I’ll explain how to do the following things with simple examples.
How to Calculate Row or Column Totals Using Quick Analysis Tool?
Step 1
To calculate row or column totals, select the data that contains the columns and rows and click on the quick analysis button. Use Ctrl+Q as a shortcut.
Step 2
In the Quick Analysis menu, select “Totals” and choose the type of Total you need. (Blue color – Column Totals, Amber Color – Row Totals)
Step 3
You have many other options like average, percentage, running total, and count under the “Totals” menu.
How to Apply Conditional Formatting Using Quick Analysis Tool?
- To apply conditional formatting for your data select your data range and go to the quick analysis menu (Ctrl+Q shortcut).
- Under the “Formatting” section, Quick Analysis will suggest all types of suitable conditional formatting with previews. For example, if you have dates in your range it will suggest options to highlight last week, last month, or a specific date range.
- Choose the kind of conditional formatting you need among the options and click OK.
How to Create a Pivot Table Using Quick Analysis Tool?
You can also create a pivot table very quickly using the quick analysis tool Excel feature.
Just follow these steps.
- Select your data and go to the quick analysis menu. (Ctrl+Q shortcut)
- Under the “Tables” section, choose the kind of pivot table you need. Click on “More” for more pivot table suggestions.
- Your new pivot table will now appear in a separate sheet.
How to Create Charts Using Quick Analysis Tool In Excel?
- To quickly create charts using the quick analysis tool select your data and go to the quick analysis menu using “Ctrl+Q”
- Under the “Charts” section, choose the kind of chart you need. Click on “More” for more chart suggestions.
- Your chart will now appear in the same sheet. You can edit it if needed.
How to Insert Sparklines Using Excel Quick Analysis Tool?
Adding sparklines in Excel never gets easier than this.
- Select your Data range and press “Ctrl+Q”
- Under the “Sparklines” section, choose the kind of sparkline you need.
- The sparklines will appear now in the very next column to the data range. You can edit their appearance if needed.
Suggested Reads:
Create An Excel Dashboard In 5 Minutes – The Best Guide
Dynamic Dropdown Lists In Excel – Top Data Validation Guide
Predict Future Values Using Excel Forecast Sheet – The Best Guide
FAQs
How to turn on/off the Quick Analysis feature?
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.
What is the use of the quick analysis button in Excel?
The Excel quick analysis tool bundles all important data analysis tools together for handy use and gaining quick insights. You can easily add charts, pivot tables, sparklines, and calculate totals without using any formula or going through complex menus.
Excel Quick Analysis Tool – Closing Thoughts
That’s all folks. I have covered everything about the quick analysis tool in Excel in this guide. Now you know how to use it to quickly add charts, pivot tables, sparklines, and calculate sum totals. If you have any questions about the quick analysis tool or any other Excel feature, do ask them in the comment section below. We’re always happy to help.
If you need more high-quality Excel guides, please check out our free Excel resources centre.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advance Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
