Excel Quick Analysis tool with examples
In this tutorial, you will learn how to make your daily tasks easier and quickly understand your data using the Excel Quick Analysis tool.
Microsoft Excel is a powerful program that helps you work with different types of data. However, sometimes you need quick answers without using complicated formulas. That’s where the Quick Analysis tool helps. In this article, we’ll explain what the Quick Analysis tool is, where to find it in Excel, how to turn it on, and how to use it to complete tasks quickly with just a few clicks.
Table of Contents
What is Excel Quick Analysis tool?
The Quick Analysis tool in Microsoft Excel helps you quickly analyze and organize your data. With just a few clicks, you can turn raw data into easy-to-read tables, charts, pivot tables, sparklines, and other visuals. Instead of searching through different menus and settings, this tool gives you a simple and fast way to see important patterns and insights in your data.
Where is the Quick Analysis tool in Excel?
The Quick Analysis tool in Excel works differently from the usual buttons and menus you see in the ribbon. It appears only when you select data, making it easy to use without taking up space on the screen.
To use Quick Analysis, first highlight a group of cells in your worksheet. In the bottom-right corner of your selection, you’ll see a small icon that looks like a box with a lightning bolt. This is the Quick Analysis button. Click it to open a menu with different options to analyze your data.
If you don’t see the Quick Analysis button after selecting your data, press Ctrl + Q on your keyboard to open it.
Remember, this button only works when you select actual data. It won’t appear if you highlight blank cells, entire columns, or full rows.
How to enable / disable Quick Analysis tool in Excel?
Sometimes, the Quick Analysis icon may not appear when you select your data. This usually means the feature is turned off in Excel. You can turn it on by following these steps:
- Click on the File tab in the top-left corner of Excel.
- Scroll down and select Options from the menu.
- In the Excel Options window, go to the General section.
- Find the checkbox that says Show Quick Analysis options on selection and tick it.
- Click OK to save the changes.
Now, the Quick Analysis tool should appear when you select your data.
.
Now, the Quick Analysis button should be visible whenever you select a range of data, providing quick access to its functionality.
How to use Quick Analysis tool in Excel
Using the Quick Analysis tool is simple and easy. Follow these steps:
- Select your data – This can be a table, a list of numbers, text, or any useful data.
- Find the Quick Analysis button – Look at the bottom-right corner of your selected data and click the small icon. If you don’t see it, press Ctrl + Q on your keyboard.
- Choose an option – A menu will open with different analysis tools. Hover over each option to see a preview of how it will change your data. Click on the one you want to apply.
- Try different features – Experiment with the tools to find the best one for your needs. If you make a mistake, you can easily undo or redo changes using the Undo button or keyboard shortcuts.
This tool helps you quickly analyze your data without complicated steps!
Below is an overview of the 5 main categories within the Quick Analysis tool. For those who prefer to navigate apps without using the mouse, keyboard shortcuts are available for each category.
Formatting
This category helps you make your data easier to understand by using conditional formatting. The options you see will depend on the type of data you have:
- Numbers – You can add Data Bars, Icon Sets, or highlight values like the Top 10% or numbers Greater Than a certain amount.
- Text – You can highlight Duplicate or Unique values, find Exact Matches, or highlight Cells That Contain specific words.
- Dates – You can highlight dates from Last Month, Last Week, or those that are Greater Than, Less Than, or Equal To a specific date.
These tools help you quickly spot important patterns in your data!
Keyboard shortcut for Quick Analysis Formatting: Ctrl + Q then F
Charts
Besides the usual way of creating charts in Excel, you can also use the Quick Analysis tool to insert a graph easily. This tool suggests the best chart types for your data, making the process faster and simpler.
When you select your data, Quick Analysis will show the most suitable chart options to help you see patterns, trends, and relationships. Hover over each chart type to preview how it will look in your worksheet.
If you don’t see the chart type you need, click More Charts to open the Insert Chart window. Here, you can choose from other options like scatter plots, histograms, and radar charts.
This feature helps you create clear and attractive charts without spending too much time on formatting. But if you want more control over how your chart looks and works, you can use the Chart Tools tab for advanced settings.
Keyboard shortcut for Quick Analysis Charts: Ctrl + Q then C
Totals
With Totals, you can quickly calculate and display important summaries of your data, like sum, average, count, percentage total, and running total.
The options you see depend on the type of data you selected:
- If your data has numbers, you will see different calculation options.
- If your data has only text, you will only see the Count option, which shows how many cells contain text.
You can calculate totals for both rows and columns:
- For column totals (going down), the options will appear in blue.
- For row totals (going across), click the right arrow first, then choose from the yellow options.
This feature makes it easy to get quick summaries without using complex formulas!
Keyboard shortcut for Quick Analysis Totals: Ctrl + Q then O
Sparklines
Sparklines are small, simple charts that fit inside a cell to show trends in your data. They help you see patterns quickly and clearly without using big charts. You can choose from three types:
- Line – Shows data trends over time.
- Column – Compares values as small bars.
- Win/Loss – Highlights positive and negative changes.
Sparklines are useful for spotting patterns, seasonal changes, outliers, or progress toward a goal. They also make reports and dashboards look clearer and more professional without taking up too much
Keyboard shortcut for Quick Analysis Sparklines: Ctrl + Q then S
Examples of using Quick Analysis tools in Excel
Now let’s explorer some practical examples of how you can use the Quick Analysis tools in Excel to analyze your data faster and easier. We will use a sample dataset of monthly sales of different items.
Change a regular range into Excel table
The first step is to turn your data into an Excel table. Making a table has many benefits, such as automatically expanding when you add new data. This means any formatting or formulas you apply will automatically update for new entries—no extra work needed!
Here’s how to quickly create a table using the Quick Analysis tool:
- Select your data.
- Click the Quick Analysis button (or press Ctrl + Q).
- In the Quick Analysis menu, go to the Tables category and click Table.
Excel will create a table with a default style. If you want, you can change the table’s look using Table Styles or remove styles like banded rows, shading, and borders while keeping all the useful table features.
Calculate percentage total for columns and rows
Let’s say you want to find out two things in your dataset:
- What percentage of total sales comes from each item?
- What percentage of total sales happens in each month?
To do this, you need to calculate percentage totals for both columns (months) and rows (items). Here’s how:
- Select your data and click the Quick Analysis button.
- Go to the Totals category.
- Click on % Total (shown in blue) to calculate the percentage for each column.
Excel will then add a new row showing the sales percentage for each month. This helps you see how much each month contributes to total sales!
To add percentage total for rows, follow the same steps, but select the % Total option in the yellow color. This will insert a new column displaying the percentage-wise sales for each item.
To check the formula created by the Quick Analysis tool, click on any cell that shows the % Total and look at the formula bar. In our example, the formula uses table names and looks like this:
If you work with a regular range of cells, the formula is simpler, like:
This formula finds the percentage of each row’s total compared to the grand total of all rows. The top part (numerator) adds the values in the current row. The bottom part (denominator) adds all the values from all rows. The dollar signs in the denominator lock the range so it doesn’t change when you copy the formula to other cells.
Highlight cells greater than a specified value
To highlight cells with values above a certain number, follow these simple steps:
- Select the cells you want to format and click the Quick Analysis button.
- Go to the Formatting group and choose Greater Than.
- A box will appear—enter the number you want to compare.
- Choose a highlighting style (the default is light red fill with dark red text).
- Click OK to apply.
Now, all cells with values higher than your chosen number will be highlighted automatically!
Create a pie chart
To make a pie chart using the Quick Analysis tool, follow these easy steps:
- Select your data, including labels and values.
- Click the Quick Analysis button.
- In the menu, go to the Charts tab and hover over the Pie chart option to see a preview.
- Click on Pie chart to add it to your sheet.
- If you want more options, click More Charts to explore other graph types.
This helps you quickly create a pie chart to visually compare parts of your data!
.
As we’ve created a chart based on a table, it is dynamic by nature – every time you add or remove new records to the table, the chart will automatically update to reflect the changes. This is a very useful feature when you want to visualize data that is constantly changing or growing.
That’s how to use the Quick Analysis tool to simplify common data analysis tasks. So, next time you’re working with data in Excel, keep this handy tool in mind. We hope you found this tutorial useful and learned something new today. 🙂
