The Quick Analysis Tool in Excel is super handy! It lets you quickly turn your data into charts, tables, summaries, and even sparklines. Plus, it can handle conditional formatting like a pro. It saves you so much time because you don’t have to hunt through different tabs and menus for all these options. It is especially great for folks who are new to Excel because it suggests visualization options that fit your data.
This feature is available in Excel 2013 and later versions. Let’s take a look at how to use it to add charts, visuals, various formatting options, summary formulas, tables, and sparklines to your spreadsheets.
Where to Find the Quick Analysis Tool
You won’t find the Quick Analysis Tool in the Excel Ribbon. It only pops up after you select the data you want to analyze.
To access it, just select a range of cells or a data table, and a small button will appear in the bottom-right corner of your selection.
Click on that little Quick Analysis icon, and you’ll see all the options. Alternatively, you can also press Ctrl+Q (Windows) or Command+Q (Mac) after selecting the data.
The tool will present a range of data analysis options. You can then switch between the tabs and click on the option to get your visual.
Important Note: The Quick Analysis tool won’t show up if you select an entire row or column or if you have empty cells selected.
The tool is organized into five main categories:
- Formatting
- Charts
- Totals
- Tables
- Sparklines
Keep in mind that the options you see can vary based on what type of data you’ve selected, but you can’t add or remove options.
Quick Analysis Icon is Missing?
If the Quick Analysis Tool button isn’t appearing when you select the data, check if it’s been disabled in Excel’s settings.
To do this: go to the “File” tab and click on “Options” in the bottom left corner.
In the Excel Options, look for the “General” tab. On the right side, make sure the “Show Quick Analysis options on selection” checkbox is checked. Then, hit “OK.”
Also, if you’re using a version of Excel older than 2013, you’ll need to upgrade to get this tool.
How to use the Quick Analysis Tool
This tool is basically a collection of simple ways to understand and manage your data. Each option also gives you a quick preview as you hover your mouse over it.
Conditional Formatting Options
Formatting tab
The Formatting tab provides five formatting options and one clear format option which allow you to apply conditional formatting to highlight important parts of your data.
To access it, select a range of cells, click the ‘Quick Analysis’ button, and select the ‘Formatting’ tab.
This smart tool suggests conditional formatting options based on the data type. For example, if you have numbers, you will see options like Data Bars, Color Scale, Icon Set, Greater Than, Top 10%, and Clear Format.
As you hover over each option, you’ll get a preview. Just click on the one you like to apply it.
Data Bars
The data bars option adds horizontal bars to the cells which represent the relative size of each value.
To visualize your data in data bars, select the data set, click the ‘Quick Analysis’ icon and select the ‘Data Bars’ option under the Formatting tab.
Color Scales
The ‘Color Scales’ option applies background colors to your cells based on the size of each value to visualize the data distribution.
Select the ‘Color Scales’ option under Formatting.
The largest values will be in dark green while the smallest values will be colored in dark red.
Icon Set
The Icon Set option displays icons to categorize your data based on thresholds.
To add an icon set, click ‘Icon Set’ under the Formatting tab.
Greater Than
This option highlights cell values greater than a specific number.
To use it, click the ‘Greater Than’ option under Formatting.
Enter the number and choose formatting in the dialog box, and click ‘OK’.
Then click ‘OK’ to apply the formatting.
Here’s how it looks when applied.
Top 10%
This option highlights the top 10% of values in your selected range.
To highlight the top 10%, in the Formatting tab, simply click ‘Top 10%’.
If you select a range of cells with text values, the formatting options will change.
Text Contains
This rule highlights cells that contain specific text.
To use it, select a range, click the Quick Analysis icon, and select the ‘Text Contains’ option.
Enter the text to search for, choose formatting, and click ‘OK’.
Here’s the result.
Duplicate Values
This option colors cells that contain values which occur more than once in the selected range.
Select the range, and click the ‘Duplicate Values’ option under Formatting.
Unique Values
This option colors cells that contain values which occur only once in the selected range.
To highlight the unique values in a data set, choose the ‘Unique Values’ option.
Equal To
This option is similar to the Text Contains option but it colors cells only when they exactly match the specified value.
To highlight equal values, select the range, and select the ‘Equal To’ option.
Enter the text in the dialog box, choose formatting, and click ‘OK’.
Now, the cells that exactly match the value are formatted.
Clear Format
To remove any conditional formatting, use the ‘Clear Format’ option.
Select the range with conditional formatting, and click ‘Clear Format’ under the Formatting tab.
Analyzing Dates
You can also analyze dates using the Quick Analysis tool. If you select a range of dates, the tool will show you specific date-related formatting options in the formatting tab.
Last Month
Highlights dates from the previous month.
Select the range of dates and click on ‘Last Month’.
Last Week
Highlights dates from the previous week.
Select the range of dates and click on ‘Last Week’.
Greater Than
Highlights dates that come after a specific date.
Select the dates and click on ‘Greater Than’.
Then, enter a date and choose formatting, and click ‘OK’.
The result:
Less Than
Highlights dates that come before a specific date.
Select the dates and click on ‘Less Than’.
Enter a date, choose formatting and click ‘OK’.
The result:
Equal To
Highlights dates that are equal to a specified date.
Select the dates and click on ‘Equal To’.
Enter a date, choose formatting, and click ‘OK’.
The result:
Inserting Charts
Charts tab
Charts are an easy way to visualize your data. The ‘Chart’ tab displays suggested charts based on the selected data such as bar charts, column charts, line charts, pie charts, scatter etc.
To insert charts: select the data, click the ‘Quick Analysis’ button, or press Ctrl+Q, then go to the ‘Charts’ tab.
As you hover over each chart option, you’ll get a preview. To insert a chart, just click on it.
Here’s the selected chart inserted.
The tool will recommend different charts based on the data.
If you don’t see the chart you want, click ‘More Charts’.
This will open the Insert Chart dialog. You can select from all available chart types here.
Select your desired chart and click ‘OK’.
Quickly Analyzing Data using Formulas
Totals tab
The Quick Analysis Tool provides formulas to calculate numbers in selected columns or rows including sum, average, count, % total, and running total.
To quickly analyze data, select the data set, click the ‘Quick Analysis’ icon, and go to the ‘Totals’ tab.
Click the arrow to the right to view more formulas.
The blue icons calculate columns, and the yellow ones calculate rows.
Sum
The Sum formula provides the total of all values in each column.
Ensure there’s an empty row below your data before you sum up the column(s).
Select the columns and click the blue colored ‘Sum’ option under ‘Totals’.
The sum of each column appears in the row right below.
To sum rows, click the yellow-colored ‘Sum’ button.
This will show you the sum of each row.
Average
To calculate the average of each column, click the blue-colored ‘Average’ button.
The result appears below the table.
To calculate average of each row click on the arrow button on the right side.
Then, click the yellow-colored ‘Average’ button.
Here’s the average for each row.
Count
To count the number of items in each column, click the blue-highlighted ‘Count’ button.
To count the items in each row, click the small arrow button.
Then click the yellow-highlighted ‘Count’ button.
% Total
This adds a % total row/column showing the percentage of each value’s contribution.
To get the percentage of each column’s total, click the ‘% Total’ (blue colored) button.
To get the percentage of each row’s total, click the small arrow and select the ‘% Total’ (yellow colored) button.
Running Total
A running total is the partial sum of data set, the sum of the values so far.
The running total differs from the total sum because the total sum is the sum of all values, and the running total is the cumulative sum of the range from the first value to current cell value.
Here are four different ways to calculate running totals with the Quick Analysis tool:
Running Total for a Single Column
To calculate a running total in a column, select the range, and click the ‘Quick Analysis’ icon.
Click on the arrow button on the right side to see more options.
Then, select the ‘Running Total’ (yellow highlighted button).
This adds a column with the running total.
The formula for the running total is the SUM function.
Running Totals for a Single Row
To calculate a running total for a row, select the range, and click the ‘Quick Analysis’ button.
Then, select the ‘Running Total’ option with the blue highlighted color.
This calculates the running total for the row in a new row below the selected range.
Running Total for Multiple Columns
To calculate the running total for multiple columns, select the range, and click the ‘Quick Analysis’ icon. Click the arrow to reveal more options.
Then, click the ‘Running Total’ option with the yellow highlighted icon.
This will show you the running total of multiple columns at the end of each row in the newly created column.
The first running total adds the values from January.
And the last running total contains all four bank deposits.
Running Total for Multiple Rows
To calculate running totals for multiple rows, select the range, click the ‘Quick Analysis’ tool and select ‘Running Total’ (blue highlighted color).
This adds a new row at the end of each column and calculates the running total.
While you can do this manually, the quick analysis tool automates it for you.
Creating Tables
Tables tab
The tables tab lets you convert data into a regular table or a pivot table.
The table tab has two options – Table and Pivot table. When creating a table, you will see a preview, however, there won’t be any preview for the ‘PivotTable’ option.
Table
To insert a table, select the range of cells and click the ‘Quick Analysis’ button.
Go to the ‘Tables’ tab and click the ‘Table’ icon.
Your selected range will now be a table.
Press Ctrl+Z to reverse the change, if you make a mistake.
Blank PivotTable
The second option is the 'Blank PivotTable’ to create a pivot table using the selected range.
To create a pivot table, select the data range, and click ‘Blank PivotTable’ under the Tables tab.
Excel creates a blank pivot table in a new worksheet, which can be customized.
Creating In-line Sparklines
Sparklines tab
Sparklines are small charts that fit within single cells. It shows single trend of your data without axes or coordinates.
If the options under the ‘Sparkline’ tab are greyed out, you’re likely using an older file format such as ‘Excel 97-2003 workbook (.xls)’.
Sparklines can only be used in these file formats:
- Excel Workbook (.xlsx)
- Excel Macro-Enabled Workbook (.xlsm)
- Excel Binary Workbook (.xlsb)
Line
Line sparklines help you see trends in your data.
To insert a line sparkline, select the data and click the ‘Quick Analysis’ icon.
Then, go to the ‘Sparkline tab’ and click on the ‘Line’ chart type.
The line sparklines will now be within each cell to the right.
Column
The column sparkline chart is like a line chart but uses bars to display the changes in data.
To insert a column sparkline, click the ‘Column’ option under the ‘Sparklines’ tab.
Win/Loss
Win/Loss shows if a value is positive (blue upward bar) or negative (red downward bar).
To insert a Win/Loss sparkline, select the data, go to the ‘Sparklines’ tab, and select the ‘Win/Loss’ option.
The sparklines will be added to the left.
After adding a sparkline, you can customize them from the Sparkline tab on the Ribbon.
