In Excel (2013 or above version), whenever you select some data from a range of cells you get a small icon on the bottom right hand corner, as you can see below:
And, I’m sure you have noticed this so many times.
It was first introduced by Microsoft in 2013 version of Excel and it is called: QUICK ANALYSIS
But, the thing I want to ask is how many times you have used it in your daily work? If you are one of those excel users who use it, please say “Yes” in the comments section below the post.
So today, I want to inspire you to use this tool, and for this, I have listed 5+1 amazing things which you can do with the quick analysis option and which will save your a lot of time!
But before we get into it, let me share with you what others are saying about this option.
According to Make Use of:
“Quick Analysis is one of the best excel features which you should use.”
And, Tech Republic says:
“You can instantly analyze your data with Quick Analysis.”
If you are not able to see the Quick Analysis option, then you need to activate it and here are the steps.
- Go to File Tab -> Options
- In Excel Options, go to General
- And, under user interface options, tick mark “Quick Analysis options on selection”
- Click OK
So now, let’s get started with the 5 + 1 Reasons You Should Use Excel’s Quick Analysis Option!
Table of Contents:
1. Add Running Total
2. Create a Pivot Table
3. Insert a Line Chart
4. Percentage Share
5. Insert a Sparkline
6. Highlight Greater than values
Table of Contents
1. Add Running Total
Instantly add a running total.
Running total is one of the best ways to analyze data and you can use quick analysis option to add it in no time. So, please follow these steps:
- Select your data and click on the quick analysis icon or you can also use shortcut key Ctrl +Q.
- Now, in quick analysis option, go to Totals and scroll to the end of the option.
- And, click on “Running” (yellow icon).
- Once you click on it, it will automatically insert a new column in your table with running total using the SUBTOTAL function.
In the Total tab, there are two types of options, one is blue which is for rows and second is yellow which is for columns.
Let’s say if you want to add a running total for a row instead of a column, you can use blue option.
2. Insert a Pivot Table
Quickly insert a pivot table.
Yes, you can use the quick analysis tool to quickly create a pivot table. Please follow these simple steps.
- First of all, select your entire data.
- After that, press Ctrl + Q and select Tables.
- Now click on “Blank” in tables.
- Once you click on it, it will instantly create a pivot table adding a new worksheet.
The best thing about this method is, it inserts a pivot table without showing you “Create Pivot Table Window”.
So, whenever you are in hurry just remember: Select your data -> Ctrl + Q & Tables -> Click Blank Button.
3. Recommended Charts
Choose right chart for your data in a flash.
Most of the times you have a specific chart in your mind to present your data and you insert it without considering that there can be an another type of a chart which you can use.
In Excel, there is an option “Recommended Charts” which you can use to get a suggestion of charts and you can use that option in the quick analysis as well.
Follow these steps.
- First of all, select your data table and press Ctrl + Q and then Charts.
- Now, you have a list of recommended charts from which you can hover over with your mouse and it will show you a live update of the chart based on your data.
- Click on the chart icon that you like and it will instantly insert that chart in your worksheet.
And, if you want to insert a chart which is not in the list, click on “More Charts”. The best part is that it will always recommend you the best suitable charts.
4. Percentage Share
Insert percentage share with just a few clicks.
Let’s say you have a list of products with their sales quantity and you want to calculate the product share. If you want to use the manual method for this, you will need to insert a formula manually.
But, you can do this automatically using the quick analysis option.
Steps:
- Select your data and press Ctrl + Q and then select Totals.
- Scroll to “%Total” (yellow icon) and click on it.
- Once you click on it, it will instantly insert a new column to your table with product share percentage.
It uses a combination of SUM function and SUBTOTAL function for this.
5. Insert a Sparkline
Insert a small trend line with sparklines.
You can also add a sparkline with the quick analysis tool. The benefit of using sparklines is that you can create in-cell charts with it, which don’t take much space. And, you can easily use them in your dashboards.
Steps:
- First of all, select the entire range of data like below and press Ctrl + Q and then select Sparklines.
- After that, click on the type of sparkline you want to add.
- Once you click on it, it will insert a Sparkline in the cell after the last cell.
After inserting a sparkling and you decide you want to change its type, then just select your data and follow the same steps.
6. Highlight Greater than values
Quickly apply conditional formatting on your data.
If you ever wanted to highlight all the values which are greater than a certain value, you can do this with the quick analysis option.
- First of all, select your values on which you want to apply conditional formatting.
- Press shortcut key Ctrl + Q and click on the Greater icon.
- Once you click on it, it will show you a pop-up window. Enter the greater than value and select a color to use for highlighting the cells.
- In the end, click OK.
Conclusion
Excel’s Quick Analysis is all about quick stuff. You can access most useful stuff in a flash. Apart from the stuff I have mentioned above, there are more than 24 options which you can use.
The only flaw which I have seen is you can’t add or delete your favourite features into Quick Analysis but it’s still can be a time saver for you.
What do you think?
I want you to share your views in the comment section with me below. I would love to hear from you.
About the Author
This guest post is contributed by Puneet Gogia from Excel Champs
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
