Images in this article were taken using Excel 2013 on the Windows 7 OS. To follow using our example below, download Create a PivotTable.xlsx. Open the sheet with the data you want to analyze. Highlight the data you want to include in the PivotTable. When you do so, the Quick Analysis Tool icon will appear in the bottom right corner of the. Use the Quick Analysis Button. Select the cell range you want to summarize. Be careful when selecting your data. The Quick Analysis button will not appear when using the Ctrl key to make multiple selections. Click the Quick Analysis button. Select the type of analysis tools you want to use. Formatting: Highlight data using conditional formatting. The Quick Analysis tool is the Swiss knife of data analytics that offers a set of easy-to-use yet powerful tools to manage data sets of any size – everything at the tip of your fingers If you have never used it before, this seemingly simple Excel feature will quickly become your best friend to navigate the mean streets of spreadsheets with ease. Excel for Microsoft 365 Excel 2019 Excel 2016 Excel 2013 Quick Analysis takes a range of data and helps you pick the perfect chart with just a few commands. Select a range of cells. Select the Quick Analysis button that appears at the bottom right corner of the selected data.
- Quick Analysis Tool In Excel Mac
- Quick Analysis Tool In Excel 2007
- Quick Analysis Tool In Excel 2016 For Mac
- Quick Analysis Tool In Excel
The Quick Analysis tool is the Swiss knife of data analytics that offers a set of easy-to-use yet powerful tools to manage data sets of any size – everything at the tip of your fingers
If you have never used it before, this seemingly simple Excel feature will quickly become your best friend to navigate the mean streets of spreadsheets with ease.
In this ultimate beginner’s guide, you will learn everything you need to know to use the Quick Analysis tool in Excel.
Sample Data
To show you the full potential of this tool, we’re going to use the following table that contains the historical revenue data of six fictitious companies:
Without further ado, let’s dive right in.
How to Turn on the Quick Analysis Tool
To start with, make sure the Quick Analysis tool is enabled. If that’s not the case, you can turn it on by doing the following:
1. Go to the File tab.
2. Click the “Options” button.
3. In the General tab, check the “Show Quick Analysis options on selection” box.
4. Click “OK” to close the dialog box.
Where Is The Quick Analysis Tool in Excel?
The beauty of this tool is that it’s just a few clicks away at any stage of working with your data. To open the Quick Analysis tool, you need to complete a few simple steps.
1. Highlight the cell range you want to apply the tool to (A1:D8).
2. Click the “Quick Analysis” button – or simply press the Ctrl + Q shortcut.
3. Once there, the Quick Analysis tool will appear at the right bottom corner of the highlighted cell range.
In order to help you wring every last drop of value out of this handy tool, we will cover its every single feature.
The Formatting Tab
The Formatting tab contains six data visualization tools that allow you to apply conditional formatting to your data, making it easier to analyze large volumes of data.
Data Bars
Data bars are a great tool to visualize your actual values. The length of the bars reflects the relative size of each value in comparison to the largest value in a data set. The color of the bars differentiates positive and negative values.
To add data bars using the Quick Analysis tool, in the Formatting tab, click “Data Bars.”
Color Scale
Color scales are a type of conditional formatting that modifies a background fill based on the actual values of each cell.
When you apply a color scale in Excel, the largest value ($47,143,747) is colored in dark green while the smallest value is colored in dark red (-$13,120,353). The rest of the values are colored proportionally based on where they stand relative to the median (the 50th percentile).
In order to use a color scale in Quick Analysis, under “Formatting,” click “Color Scale.”
Icon Set
An icon set adds icons to all of the values in a data set based on dynamically calculated threshold values. When you do that using the Quick Analysis tool, your values are broken down into three categories.
In order to add an icon set, under “Formatting,” click “Icon Set.”
Greater Than
This conditional formatting type colors all worksheet cells that are greater than a specified value. Here’s how you can do this with your data.
1. In the Data Analysis tool, in the Formatting tab, choose “Greater Than.”
2. In the dialog box that appears, under “Format cells that are GREATER THAN:”, enter “$10,000,000” to color all of the cells in our data set greater than this value.
3. Next to “with,” choose “Green Fill with Dark Green Text” to create a conditional formatting rule.
4. Click “OK.”
As a result, all of the values that are greater than $10,000,000 will be colored in green.
Top 10%
This conditional formatting rule works similar to “Top 10 Items,” recoloring the top 10 percent of values based on the number of items in the entire data set.
To highlight such values, in the Formatting tab, simply click “Top 10%.”
Duplicate Values
This conditional formatting rule colors sifts through the highlighted data and colors all of the duplicate occurrences in a given data set.
To highlight all of the duplicate values in your data table, under “Formatting,” click “Duplicate Values.”
Unique Values
This conditional formatting rule changes the color of cells that contain the values that occur only once in the selected data table.
To highlight the unique occurrences of values in your data set, in the Formatting tab, choose “Unique Values.”
Clear Format
To remove any conditional formatting from the highlighted cells, under “Formatting,” click “Clear Format.”
The Charts Tab
If you think that conditional formatting is everything the Quick Analysis tool has to offer, then think again.
With the help of this lovely tool, you can quickly put together an Excel chart to visualize your data. Here’s how you can pull it off.
1. Highlight your data table, open the Quick Analysis tool, and switch over to the Charts tab.
2. Select the chart type that you think works best with your data using the options suggested by Excel (“Clustered Column,” “Clustered Bar,” “Line,” “Stacked Column,” or “Stacked Bar”) or click “More Charts” to access more data visualization tools.
Note: Each time you want to create a chart using the Quick Analysis tool, Excel automatically tries to analyze your data set to provide custom recommendations based on the actual values. Notice how the suggested charts change once we remove two columns from the data table.
Finally, let us walk you through the “More Charts” option for those looking to create advanced Excel charts with the help of the Quick Analysis tool.
3. In the Charts tab, click “More Charts.”
4. In the Recommended Charts tab, you can choose from a wider selection of Excel charts – alternatively, click the All Charts tab to access even more chart types.5. Click “OK” to close out of the dialog box.
Once you have followed all of the steps outlined above, you should get this lovely funnel chart.
The Totals Tab
You think we’re done, right? We’re just getting started. In this section of the tutorial, we will show you how you can use the Quick Analysis tool to perform a quick data analysis.
The Totals tab offers you two sets of five identical data analysis tools (Sum, Average, Count, % Total, and Running Total) that you can apply both to rows and columns.
Let’s break down how to use each of these tools in greater detail.
Sum
Click “Sum” to sum the values of each column using the SUM function.
Average
Click the “Average” button to calculate the average value for each column (or row) using the AVERAGE function.
Count
Click “Count” to calculate the number of rows in each column – or the number of columns in each row – using the COUNTA function.
% Total
Click “% Total” to show the relative size of the sum of values in each column within the entire data set.
Running Total
Click “Running Total” to calculate a cumulative total – when you stack the sum of values in each column on top of each other.
The Tables Tab
The Tables tab allows you to quickly convert your worksheet cells to a table or create a pivot table based on your data.
Table
Click “Table” to convert your regular range of data to a table.
At this stage, your cell range will be turned into a table:
If you want to learn how to convert your table back to a normal range, check out this tutorial.
Blank PivotTable
Another thing you can do in the Tables tab is to create a blank pivot table.
To do that, under “Tables,” choose “Blank PivotTable.”
If you do that, Excel will create a new worksheet with the corresponding PivotTable fields set up for you.
The Sparklines Tab
The last tab enables you to create simple in-cell sparkline charts to illustrate a pattern or trend in your data.
With the help of this tab, you can create line, column, and win-loss sparkline charts.
Line
To create a line sparkline in Excel using the Quick Analysis tool, under “Sparklines,” hit the “Line” button.
Column
In order to put together a column sparkline in the Quick Analysis tool, under “Sparklines,” select “Column.”
Win/Loss
Finally, armed with the Quick Analysis tool, you can create a win/loss sparkline chart to illustrate positive and negative changes in values over a certain period of time.
Quick Analysis Tool In Excel Mac
To do that, under “Sparklines,” hit the “Win/Loss” button.
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.
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.
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.
1. Download the Excel file (right side of this page) and open the third sheet.
2. Select the range A1:F4 and click the Quick Analysis button.
Quick Analysis Tool In Excel 2007
3. For example, click Sparklines and click Line to insert sparklines.
Quick Analysis Tool In Excel 2016 For Mac
Customized result:
Quick Analysis Tool In Excel
Note: visit our page about Sparklines to learn how to customize sparklines.