The Quick Analysis Tool is one of the most useful tools in Microsoft Excel, which helps you instantly analyze and convert data into charts, tables, summaries, and sparklines.
The Quick Analysis tool simplifies searching through various tabs and saves time accessing various analysis options. Using the Quick Analysis tool makes it easier to quickly analyze and visualize datasets of any size. So this tool is helpful for users who are not familiar with Excel or who are new to Excel.
However, Quick Analysis Tool in Excel is only available in Excel 2013 and later. Next, we’ll take a look at how to analyze data using the Quick Analysis tool in Excel.
Where is the Quick Analysis Tool in Excel?
The Quick Analysis tool is not found in the Excel Ribbon, and only appears when the data to be analyzed is selected.
Follow the steps below to access the quick analysis tool:
Select a cell or data table and the Quick Analysis button will appear in the lower right corner of the selection.
Click the Quick Analysis icon or press Ctrl+Q (Windows) or Command+Q (MAC) after selecting data to open the Quick Analysis tool.
Then you will see a series of data analysis options. Switch between tabs and click options to create visualizations.
Note: If you select an entire row or column and an empty cell, the Quick Analysis tool will not appear.
In the Quick Analysis Toolbar, there are 5 categories:
Formatting
Charts
Totals
Tables
Sparklines
The above options may vary depending on the data you select.
Why is Quick Analysis tool Not Showing Up?
If the quick analysis tool is disabled, the quick analysis tool will not appear even after selecting data. To find the quick analysis tool, please review the following steps:
Go to the “File” tab, click “Options”.
Check the “Show quick analysis options on selection” option on the right side of the “General” tab. Then, click OK.
Note: If you use Excel version lower than Excel 2013, you need to upgrade to Excel 2013 or later to use Quick Analysis Tool in Excel.
How to Use Quick Analysis Tool in Excel?
Quick analysis tool help understand and manage data. As mentioned above, there are five tabs of Quick Analysis tool. Next, we’ll show you how to use each of the Quick Analysis tool’s five tabs.
Apply formatting using Quick Analysis tool
The Formatting tab in the Quick Analysis tool is a smart tool, just select the range of cells you want to format, then click the Quick Analysis button below the selection and select the Formatting tab. It will suggest 6 conditional formatting options based on the data you select, including data bar, color scale, icon set, greater than, top 10% and clear formatting.
As you hover over each option, you’ll see a preview of the data. Click and apply the option formatting you want.
Insert Charts using Quick Analysis tool
Charts can help you better understand and analyze data. Quick Analysis tools help you quickly insert different types of charts and graphs into your spreadsheets. The “Charts” tab in the Quick Analysis Toolbar will recommend different charts for you based on the data you select, including bar charts, column charts, line charts, pie charts, scatter charts, and more. Follow the steps below to insert a chart using the Quick Analysis tool:
First, select the data you want to create a chart, click the “Quick Analysis” button in the lower right corner, and then switch to the “Charts” tab in the toolbar.
Hover over each chart option, pick the chart type that best suits your data, and click Insert that chart or graph.
Quickly analyze data with totals
The Totals tab in the Quick Analysis tool provides various formulas to calculate numbers, including Sum, Average, Count, % Total, and Running Total. You can directly use the calculation formula in the Totals tab to calculate your selected data and display the result.
Please select the dataset you want to calculate, click the Quick Analysis icon and go to the Totals tab to analyze the data.
Create a table using quick analysis tool
Using tables in Excel makes it easy to sort, filter, and analyze data. Using the Tables tab of the Quick Analysis tool, you will be able to quickly convert selected rows or columns into tables or pivot tables.
The Tables tab has two options: Tables and PivotTables. When you create a table, you can see a preview, but a pivot table doesn’t.
Create sparklines using Quick Analysis
You can insert small charts in a single cell by creating sparklines. Using the Sparklines tab under Quick Analysis Tools, you can insert line, column, and win/loss sparklines to the right of your data.
Sparklines are only available in the following Excel file types:
Excel workbook (.xlsx)
Excel Macro-Enabled Workbook (.xlsm)
Excel Binary Workbook (.xlsb)
If the options under the Sparklines tab are dimmed, you may be using an Excel 97-2003 Workbook (.xls) file.
That’s all about how to to insert charts, visualizations, various formatting techniques, formulas, tables, pivot tables, Sparklines by using Quick Analysis tool in excel. It is the same to use Quick Analysis tool in excel on Mac.
