What Is The Quick Analysis Tool In Excel? And How To Use It

By Indeed Editorial Team

Updated 22 September 2022

Published 14 May 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

When analysing data, rather than switching between various tabs and entering countless formulas, you can use the Quick Analysis tool in excel. Using this tool, you can insert formulas, tables, charts, Sparklines and pivot tables. If you have an interest in data analysis or if your job requires frequent analysis, knowing how to use the Quick Analysis tool is beneficial for a rewarding career. In this article, we discuss what the Quick Analysis tool is, explore its advantages, understand the steps to use the tool and discover how you can analyse dates and identify unique and duplicate values.

What Is The Quick Analysis Tool In Excel?

The Quick Analysis tool in Excel allows you to format your data into a table, chart or Sparkline for analysing it. With this tool, you can calculate totals, insert tables and even apply conditional formatting. You can only use the Quick Analysis tool with Excel 2013 or higher versions. If you use Excel 2010 or the lower version, you cannot use this tool. You can access the quick analysis tool in many ways:

  • Select the cells that contain the data you want to analyse. After selecting the data, you can see the Quick Analysis tool button at the bottom right corner of your selected data.

  • The keyboard shortcut for accessing the Quick Analysis tool is pressing Ctrl + Q after selecting the data you want to analyse.

  • You can even activate the Quick analysis tool from the right-click menu after selecting the cells containing the data.

If you do not find the Quick Analysis tool even after following these three methods, focus on enabling the tool. To enable it, click on the ‘File' menu and choose ‘Options' from the drop-down list. Locate the 'General' tab and check the tick box ‘Show Quick Analysis option on selection'.

Related: 20 Advanced Excel Skills For The Workplace (With Examples)

What Are The Benefits Of Using A Quick Analysis Tool?

Excel provides many tools and features for accessing, analysing and organising data and one such tool is the Quick Analysis tool. Some benefits of using this tool are:

  • Formats data: Using this tool, you can format your data into tables or charts in a few steps. It helps provide valuable data insights that can be useful for making important decisions.

  • Improves efficiency and productivity: It is a vital tool for speeding up productivity and increasing efficiency, especially when working with large and complex datasets.

  • Organises data: Apart from analysis, this tool helps you organise your data and allows you to use the data and analysis the way you want.

  • Makes it easier to see trends: With the Quick Analysis tool, it becomes easy to evaluate and analyse a large dataset. This tool lets you create charts and graphs that help you see trends and relationships with data points.

Related: 30 Of The Most Advanced Formulas And Functions In Excel

How To Use The Quick Analysis Tool In Excel

To use the Quick Analysis tool, follow these steps:

1. Select the data you want to analyse

The first step of using the Quick Analysis tool is selecting the data you want to analyse. Next, click on the Quick Analysis tool button on the bottom right of your selected data. Based on your data analysis requirement, you can choose ‘FORMATTING', ‘CHARTS', ‘TOTALS', ‘TABLES' and ‘SPARKLINES'.

2. Choose the ‘FORMATTING' option

Conditional formatting or formatting helps you highlight a part of data by adding data bars, colours, icon set and greater than features. This allows you to visualise the values in your data. The ‘FORMATTING' option comprises of:

  • 'Data Bars': When you select the ‘Data Bars' option, it adds solid blue data bars to the selected data based on the size of the numbers.

  • 'Color Scale': You can use the ‘Color Scale' to insert different colours into your data.

  • 'Icon Set': You can use the ‘Icon Set' to get different icons for your numbers.

  • 'Greater Than': If you want to highlight all numbers greater than 50, click on the ‘Greater Than' option, enter the value 50 and choose a colour in the pop-up window ‘Greater Than'. On clicking the ‘OK' button, all cells with a value greater than 50 get highlighted in the specified colour.

  • 'Clear Format': You can clear the formatting using the ‘Clear Format' option.

In the FORMATTING option, you have different options when selecting dates, texts and numeric values. For instance, when you select dates, your options include ‘Last Month', ‘Last Week', ‘Greater Than', ‘Less Than' and ‘Equal To'. The option you get on selecting rows or columns containing texts includes ‘Text Contains', ‘Duplicate Values', ‘Unique Values' and ‘Equal to'.

Related: How To Learn Excel For Office Work: A Complete Guide

3. Select the ‘CHARTS' option

You can use the ‘CHARTS' option to depict your data pictorially. With Quick Analysis, you can choose from various charts depending upon your requirement. Based on the data you select, the Quick Analysis tool recommends some charts that can help you effectively analyse your data. Some charts to choose from can include clustered columns, clustered bars, line, pie and scatter.

You can click on ‘More Charts' to see more recommended charts. Interestingly, by hovering your mouse over a chart, you can see a preview of what the chart looks like. This again depends on the data you selected. Focus on using bar charts to compare values between categories and line charts to look at data trends over a long period.

4. Choose the ‘TOTALS' option

Using the ‘TOTALS' option, you can perform various mathematical functions in rows and columns. Some common functions can include sum, average, count, percentage total and running total. You can perform these functions both for the rows and columns. The various functions are:

  • 'Sum': Use it to calculate the total of a row or column in the table.

  • 'Average': Use it to calculate the average of a row or column in the table.

  • 'Count': It counts the items in a column or row.

  • '% Total': The percentage total calculates the percentage of the total values in a row or column.

  • 'Running total': This function calculates the cumulative sum of the values in a row or column.

When using the ‘TOTALS' option, ensure that the row below the data is empty. Otherwise, you might receive an error stating that data is already present. You can either replace the existing data or cancel the operations.

5. Choose the tables option

Select the ‘ PivotTable ‘ option from the ‘Tables' tab to create a pivot table. Based on the data you select, the tool shows the possibilities of adding a pivot table. If you do not want to create a table based on recommendation, click on ‘More' and from the ‘Recommended PivotTables' window that appears, click on the ‘Blank PivotTable' and create a new table.

You can even convert the selected range into a table using the ‘Tables' icon from the ‘Tables' tab. It is an essential aspect of Quick Analysis because it allows you to sort, filter and summarise your data. You can see the preview for the table, but the pivot table offers no preview.

6. Select the ‘Sparklines' option

Sparklines are mini-charts placed in a single cell. Using Sparklines, you can create a column, line or win/loss chart in a single cell. They provide an easy way to visualise and analyse trends. Choose the preferred type from the ‘Sparklines' tab and apply them by clicking on it. Sparklines is a helpful but straightforward visualisation technique.

Using Quick Analysis Tool For Analysing Dates

In the Quick Analysis tool, you can organise dates and analyse them based on your requirement. To analyse dates, select rows or columns that contain dates. From the ‘FORMATTING' tab, you can select any of these options:

  • 'Last Month': This feature highlights all the dates from the last 30 days.

  • 'Last Week': This feature highlights all dates from the last seven days.

  • 'Greater Than': It highlights the dates that are greater than the ones you selected.

  • 'Less Than': It highlights the dates that are lesser than the ones you selected.

  • 'Equals To': It highlights the same dates as the ones you selected.

Using Quick Analysis Tool For Highlighting Unique And Duplicate Values

When you select text data using the Quick Analysis tool, you can identify duplicate and unique values. Click on ‘Unique Values' to display all unique values in the selected range from the' FORMATTING' tab. From the ‘FORMATTING' tab, click on ‘Duplicate Values' to highlight all duplicate values in the selected range.

Please note that none of the companies, institutions or organisations mentioned in this article are associated with Indeed.


Related:

  • Excel Interview Questions (With Example Answers)

  • What Are Excel Skills? (Definition And Examples)

  • 20 Advanced Excel Skills for the Workplace (With Examples)

  • Calculating Break-Even Analysis In Excel: A Complete Guide

  • How To Learn Excel for Office Work: A Complete Guide

  • 30 of the Most Advanced Formulas And Functions in Excel

  • A 7-Step Guide To Creating A Bubble Chart In Excel

  • A Guide On VBA Excel: Meaning, Uses, Benefits And Key Terms


Explore more articles