Excel provides you with a lot of tools to visualize your data, but sometimes it is not easy to decide which tool would work better for you. The Quick Analysis Tool is one of its kind that lets you browse through various options for data visualization in Excel.
But what is Quick Analysis? Quick Analysis Tool is a collection of functions and features in Excel that enable Excel users to quickly analyze data.
In the Quick analysis tool, most options come fixed, but some options vary depending on the selected data. The best part of the Quick Analysis tool is that you don’t need to tabulate data in order to analyze the same; instead, just hovering over the data gives you a sneak peek into it.
Must note that the Quick analysis tool was only introduced in Excel in 2013. So, if you are using any Excel version that’s older than the 2013 version, you won’t be able to enjoy this tool.
Those using Excel 2013 and onwards can use the Quick Analysis Tool to quickly perform many functions like adding up values, inserting charts and icons for data analysis. To learn more about how you can exploit the Quick analysis tool to your benefit, stay tuned.
Where to Find Quick Analysis Tool in Excel?
It is important to know that you won’t find any tab or ribbon option for accessing the Quick analysis tool. In fact, you first have to select the data range that is to be analyzed.
Consider the following example:
Once you select the data range, a small icon will appear at the bottom right of the selection. This is the same icon that goes unnoticed by 80% of Excel users.
This is a Quick analysis tool. Not many Excel users make use of this powerful tool as they don’t even know about it.
In addition to data selection, you can also access the Quick analysis tool by using the shortcut key ‘Ctrl +Q’.
Click on the Quick Analysis button at the bottom as shown above to win access to various options that the Quick Analysis Tool offers.
How to Use the Quick Analysis Tool in Excel?
You can only make the best use of this powerful tool when you are familiar with the data analysis options that it offers.
Let’s look into more details on how you can use the Quick Analysis Tool.
- First, select the data range you want to analyze.
- Click on the Quick Analysis button at the bottom right.
- Go to the tab you want to use to analyze your data.
Pro Tip:
It is worth noting that Excel will not show the Quick Access button if
- You have selected blank cells.
- You have selected an entire row or column.
Tabs:
The Quick Analysis tool has five tabs: Formatting, Charts, Totals, Tables, and Sparklines. Let us look into each of them below.
> Formatting Tab
As the name suggests, the formatting tab of the Quick Analysis Tool offers various data formatting options for your data.
1. Putting Data Bars into your selected data
Form the Quick Analysis Tool, select the Formatting tab and hover the mouse over the Data Bars option. You will get your data formatted looking like this:
2. Putting Colors Into Your Selected Data
Selecting the Color option under the formatting tab will give your data a colorful look like this.
Must Note: Pay closer attention to notice that the shading performed by Excel is based on the value i.e. the smallest value is colored in red whereas the greatest value is colored in green. The values in between are accordingly shaded depending upon how big or small they are in relation to the overall data.
3. Putting An Icon Set with Your Selected Data
Selecting the ‘Icon Set’ option will add Arrow icons to your data, and your data will look like this.
4. Using the “Greater Than” option
This option enables you to control the cell formatting according to your requirements. For example, if you want to format only those cells with a value greater than 30, select the option Greater than from the Quick Tool window.
A dialogue box will appear asking for the threshold value and formatting options as shown below.
Let’s insert the value 30 under the “Format cells that are GREATER THAN” option. From the drop-down list, you can choose the color combination for those cells which meet your condition.
The results look like as follows.
Since the condition was set to format only those cells with values greater than 30, Excel highlighted all those cells with values greater than 30.
The rest of the formatting options format the data based on the smallest and the greatest values.
> Charts Tab
The Charts tab helps you create colorful charts to visualize the selected data. Let us discover the various types of charts that this tool offers you.
1. Line Chart
This option converts your data into line charts that look like this.
2. Clustered Chart
Another useful chart offered by Excel for visualizing data is the clustered chart. Here is how a clustered chart looks like.
> Total Tab
The total tab is one of the most powerful options of the Quick Analysis tool. With a simple click, it provides you with sums, averages, percentages, and many more operation results performed on the underlying data.
1. Sum
Selecting the Sum option will instantly provide with you the Sum of sales of each category at the bottom row. Take a look below.
2. Average
The Average option instantly provides you with the average of all the data values columns. Select the Average option from the Totals tab to see the results as follows.
3. Count
The Count option provides you with the total number of values in a single column. Select the Count option from the total tab to yield results as follows.
> Table Tab
The Table tab allows you to transform the underlying data into a table in an instant. Select the options table, and you get your data transformed in a tabular format like this.
Using the Blank option will create a pivot table in the new sheet that you can use to analyze your data quickly. You can also use the Sparkline option to analyze your data with sparklines.
> Sparklines Tab
The sparklines tab of the Quick Analysis Tool bar allows you to visualize your data in the form of mini charts encapsulated within a single cell. These can be line charts, column charts or win/loss charts.
Let us see how can we add sparklines to Excel. The data in the image below represents sales made by different employees during three different months.
Adding sparkline to Excel to visualize these sale trends requires you to take the following steps.
Step 1:
Select the data to be converted into a sparkline to activate the Quick Analysis Tool icon. Alternatively, you can select the data and press ‘Ctrl + Q’.
Step 2:
Click on the Quick Analysis tool icon and go to the sparkline tab. Under the sparkline tab, select the kind of sparkline i.e. Line, column, or win/loss that you want to add to Excel.
Step 3:
Following the above two steps, Excel would add sparklines for the selected data to the activated cells, as shown below.
Seems a little dull? You can format the sparklines in different ways to add more value to them. To explore the sparkline editing options, select any sparkline and go to the Sparkline tab on the Ribbon.
You can highlight the high and low points of each sparkline to analyze the trends better. Not limited to the above, you can also change the color, density and design of your sparklines using the options under the sparkline tab.
Conclusion
The Quick Analysis Tool is like its name: quickly analyze your data without putting it into a table. You won’t see the tool on the ribbon, but it’s not hard to access and makes it easier to analyze text and numerical values especially when you are short on time.
Hope this article enables you to use the Quick Analysis Tool to quickly calculate the sums and averages, and also add pivot tables and charts. Keep exploring Excel!