This guest post is contributed by Puneet Gogia from Excel Champs

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”

activate quick analysis from excel options

  • 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

 

DOWNLOAD OUR FREE “QUICK ANALYSIS CHEAT” SHEET

 

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).

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.

create a pivot table with quick analysis

  • 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.

create a chart with quick analysis

  • 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.

data to add product share with quick analysis

  • Scroll to “%Total” (yellow icon) and click on it.

click on total in quick analysis

  • Once you click on it, it will instantly insert a new column to your table with product share percentage.

product share table with quick analysis

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.

data for sparklines with quick analysis

  • After that, click on the type of sparkline you want to add.

select column sparkline from quick analysis

  • Once you click on it, it will insert a Sparkline in the cell after the last cell.

table with sparkline with quick analysis

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.

data to highlight greater than values with quick analysis

  • 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.

add greater than value with quick analysis

  • In the end, click OK.

table with heighligted values with quick analysis

 

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

Puneet is a blogger at Excel Champs. You can find him online, tweeting about excel, on a running track, or sometimes hiking up a mountain.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+