1. You can sum or calculate other totals automatically in Excel using the Quick Analysis Tool. This awesome tool calculates totals for Sum, Average, Count,% Total and Running Total. All you need to do is decide if you want totals generated below or to the right of the selected range. You can even see a live preview of the totals before inserting.
  2. Open Analyze Data in Excel. Assuming that you have some data prepared that you’d like to analyze, you can open the tool quite easily. Select a spreadsheet, head to the Home tab, and click “Analyze Data” toward the right side of the ribbon. This will open a nifty task pane on the right side with visuals, options, and other ways to analyze.

Once you get all of these ideas from these great tools, how do you come up with your own list of keywords that will fit in with your account? This is what we are here to go over in Excel. Back in October I wrote about an Excel build out sheet that utilized the formula: ‘Cell A & ” ” & Cell B’ in order to come up with keywords along with.

Written by Puneet for Excel 2013, Excel 2016, Excel 2019

  • It’s is a collection of some of the options for data analysis.
  • You can’t add or delete options from it.
  • It is not listed anywhere on the ribbon.

What is the Quick Analysis Tool?

The QUICK ANALYSIS TOOL is a collection of some selected options that you can use to analyze the data quickly. It includes conditional formatting, charts, pivot table, and sparklines. It also takes care of what type of data (text, dates, or numbers) you have selected and show you the options based on that.

Quick Analysis Tool introduced with Excel 2013, so if you are using Excel 2010 or any lower version, you won’t be able to find this option.

Most of the options that you have on the quick analysis tool are fixed, but few of them are based on the selected data type.

For example, in the conditional formatting tab, you will have different options when you select dates comparing if you select numeric data.

When you hover on any option from the quick analysis tool, it shows you a sneak peek on selected data.

Where the Quick Analysis Tool is Located

How Do You Use The Quick Analysis Tool In Excel

The button to open the quick analysis tool is not listed anywhere on the Excel ribbon.

When you select the data, Excel shows you a small icon on the bottom right side, and when you click on this icon it opens the quick analysis tool.

Quick Analysis Tool is in our list of Intermediate Excel Skills.

But I’m not Able to See Quick Analysis Tool Button

If somehow you are not able to see a quick analysis button when you select the data (a very little chance of this), you need to check if it is disabled from the options.

Go to File Tab ➜ Options ➜ General ➜ Tick Mark(Show Quick Analysis options on selection)

As I said, it introduced in Excel 2013; if you are using any version lower than that, then you need to upgrade to the latest version.

Keyboard Shortcut to Open Quick Analysis Tool

Yes, there is the keyboard shortcut to open the quick analysis tool.

Control+ Q

You need to select the data, and then you need to press this keyboard shortcut.

Here is one more benefit. If a quick analysis tool is disabled in the Excel options, you can still open it using the keyboard shortcut.

You can also activate the quick analysis button from the right-click menu.

How to use the Quick Analysis Tool

  1. First, select the data you want to analyze.
  2. Click on the quick analysis button from the bottom right.
  3. Go to the tab from where you want to use the option.
  4. Click on the option that you want to use.

Notes

  • Excel will not show you the quick analysis button if you select blank cells.
  • And, if you have selected the entire column or a row.

How to use the Quick Analysis Tool for Data Analysis – Tips

1. Quickly Create Data Bars

This option comes handy when you work with numeric data (conditional formatting data bars).

You need to click on the quick analysis button and the formatting tab, and then select the “Data Bars” option.

It will instantly add solid data bars (Blue)to the selected data. It is quick, right?

How Do You Get The Quick Analysis Tool In Excel

And if you want to change the color or want to make other customizations, go to Home Tab ➜ Styles ➜ Conditional Formatting ➜ Manage Rules.

The

Related:Formulas in Conditional Formatting

2. Analyze Dates with Quick Analysis Tool

If you have dates in your data, then you can quickly analyze them using the quick analysis tool.

When you open it, it shows you some quickconditional formatting options to analyze dates.

  • Last Month: With this option, you can highlight all the dates that are from the previous month.
  • Last Week: To highlight all the dates from the previous week.
  • Greater than: Highlights all the dates which are greater than the date you specify.
  • Less Than: Highlights all the dates which are lower than the date specified.
  • Equals To: Highlights all the dates which are equal to the date you specify.

3. Create a Pivot Table with Quick Analysis Tool

When you click on the quick analysis button and go to the tables tab, it shows you options to insert a pivot table.

Based on the data that you have selected; it shows you all the possibilities to create a pivot table.

If you don’t want to go with recommendations, you can click on the more button and open the recommended PivotTables and click on the blank pivot table to create a pivot from scratch.

Related:Advanced Pivot Tables

4. Create In-Cell Charts with Sparklines

You can insert an in-cell chart using sparklines, and you can add sparklines quickly from the quick analysis tool.

  • Select the data where you want toadd the sparklines and click the quick analysis button.
  • Go to the sparklines tab and select the type of sparkline that you want to insert.

You will instantly get the sparklines in the cells corresponding to the data that you have selected.

5. Quickly Add Grand Totals

In the totals tab, you have multiple ways to add totals, and it also gives you options for both columns and rows.

  1. Sum: To add a total row or column to the table.
  2. Average: To add an average row or column to the table.
  3. Count: To add an average row or column to the table.
  4. % Total: To add an % total row or column to the table.
  5. Running Total: To add a running total row or column to the table.

6. Highlight Unique and DuplicateValues

When you select text data, you will have the option to identify unique and duplicate values.

The moment you click on any of these two options, it will highlight the cells with unique or duplicate values with the red color.

More on Excel

Totals | Tables | Formatting | Charts | Sparklines

Use the Quick Analysis tool in Excel to quickly analyze your data. Quickly calculate totals, quickly insert tables, quickly apply conditional formatting and more.

Totals

Instead of displaying a total row at the end of an Excel table, use the Quick Analysis tool to quickly calculate totals.

1. Select a range of cells and click the Quick Analysis button.

2. For example, click Totals and click Sum to sum the numbers in each column.

Result:

3. Select the range A1:D7 and add a column with a running total.

Note: total rows are colored blue and total columns are colored yellow-orange.

Tables

Use tables in Excel to sort, filter and summarize data. A pivot table in Excel allows you to extract the significance from a large, detailed data set.

1. Select a range of cells and click the Quick Analysis button.

2. To quickly insert a table, click Tables and click Table.

Note: visit our page about Tables to learn more about this topic.

3. Download the Excel file (right side of this page) and open the second sheet.

4. Click any single cell inside the data set.

5. Press CTRL + q. This shortcut selects the entire data set and opens the Quick Analysis tool.

6. To quickly insert a pivot table, click Tables and click one of the pivot table examples.

Note: pivot tables are one of Excel's most powerful features. Visit our page about Pivot Tables to learn more about this topic.

Formatting

Data bars, color scales and icon sets in Excel make it very easy to visualize values in a range of cells.

1. Select a range of cells and click the Quick Analysis button.

2. To quickly add data bars, click Data Bars.

Get

Note: a longer bar represents a higher value. Visit our page about Data Bars to learn more about this topic.

3. To quickly add a color scale, click Color Scale.

Note: the shade of the color represents the value in the cell. Visit our page about Color Scales to learn more about this topic.

4. To quickly add an icon set, click Icon Set.

Note: each icon represents a range of values. Visit our page about Icon Sets to learn more about this topic.

5. To quickly highlight cells that are greater than a value, click Greater Than.

6. Enter the value 100 and select a formatting style.

How To Get Quick Analysis Tool In Excel On Mac

7. Click OK.

Result. Excel highlights the cells that are greater than 100.

Note: visit our page about Conditional Formatting to learn much more about this topic.

Charts

You can use the Quick Analysis tool to quickly create a chart. The Recommended Charts feature analyzes your data and suggests useful charts.

1. Select a range of cells and click the Quick Analysis button.

2. For example, click Charts and click Clustered Column to create a clustered column chart.

Note: click More to view more recommended charts. Visit our chapter about Charts to learn more about this topic.

Sparklines

Sparklines in Excel are graphs that fit in one cell. Sparklines are great for displaying trends.

How Do You Get The Quick Analysis Tool In Excel For A

1. Download the Excel file (right side of this page) and open the third sheet.

How Do You Get The Quick Analysis Tool In Excel For Mac

2. Select the range A1:F4 and click the Quick Analysis button.

How Do You Get The Quick Analysis Tool In Excel

3. For example, click Sparklines and click Line to insert sparklines.

How To Use The Quick Analysis Tool In Excel On Mac

Customized result:

How Do I Find The Quick Analysis Tool In Excel

Note: visit our page about Sparklines to learn how to customize sparklines.

⇐ ⇐ Auto Tune Efx No Ilok
⇒ ⇒ Quit The Game To Win