Excel Quick Analysis tool with examples
In this tutorial, you will learn how to make your daily work easier and understand your data faster by using the Excel Quick Analysis tool.
Microsoft Excel is a powerful program that helps you work with all kinds of data. But sometimes, you just want quick answers without using hard formulas. That’s when the Quick Analysis tool can help.
In this article, we’ll show you what the Quick Analysis tool is, where to find it in Excel, how to turn it on, and how to use it to finish tasks faster with just a few clicks.
Table of Contents
What is Excel Quick Analysis tool?
The Quick Analysis tool in Microsoft Excel helps you quickly look at and organize your data. With just a few clicks, you can turn plain data into clear tables, charts, pivot tables, sparklines, and other visuals. Instead of going through many menus, this tool gives you an easy and fast way to see patterns and useful information in your data.
Where is the Quick Analysis tool in Excel?
The Quick Analysis tool in Excel works a bit differently from the regular buttons and menus at the top (called the ribbon). It only shows up when you select some data, so it doesn’t take up space on your screen.
To use it, first select 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. That’s the Quick Analysis button. Click it to see different options to help you understand your data.
If you don’t see the button, you can press Ctrl + Q on your keyboard to open it.
Keep in mind: this button only shows up when you select real data. It won’t appear if you select empty cells, whole columns, or full rows.
How to enable / disable Quick Analysis tool in Excel?
Sometimes, the Quick Analysis icon doesn’t show up when you select your data. This usually means the feature is turned off. You can turn it back on by following these steps:
- Click the File tab in the top-left corner of Excel.
- Scroll down and click Options.
- In the window that opens, go to the General section.
- Find the box that says Show Quick Analysis options on selection and make sure it’s checked.
- Click OK to save your 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 easy. Just follow these steps:
- Select your data – This can be a table, numbers, text, or any useful information.
- Find the Quick Analysis button – Look in 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.
- Pick an option – A menu will pop up with different tools. Move your mouse over each one to see a preview. Click the one you like.
- Try different tools – You can test different features to see what works best. If you make a mistake, just click Undo or use keyboard shortcuts to fix it.
This tool makes it fast and simple to understand your data without doing anything too hard!
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 understand your data better by using conditional formatting (adding colors or icons based on the values). The options you see will depend on the kind of data you select:
- 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 certain 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 find patterns and important details in your data!
Keyboard shortcut for Quick Analysis Formatting: Ctrl + Q then F
Charts
Besides the normal way of making charts in Excel, you can also use the Quick Analysis tool to add a chart quickly. This tool suggests the best chart types for your data, making it faster and easier.
When you select your data, Quick Analysis will show chart options that help you see patterns, trends, and connections. Just move your mouse over each chart type to see a preview of how it will look.
If you don’t see the chart you want, click More Charts to open the full Insert Chart window. There, you can pick from other types like scatter plots, histograms, or radar charts.
This feature helps you make clear and good-looking charts without spending a lot of time. But if you want more control over your chart’s design, you can use the Chart Tools tab for extra options.
Keyboard shortcut for Quick Analysis Charts: Ctrl + Q then C
Totals
The Totals option in Quick Analysis helps you quickly show important summaries of your data, like sum, average, count, percent of total, and running total.
What you see depends on the type of data you selected:
- If your data has numbers, you’ll see different types of calculations.
- If your data has only text, you’ll only see the Count option, which shows how many cells have text.
You can add totals for columns (up and down) or rows (side to side):
- For column totals, the options will show up in blue.
- For row totals, click the right arrow first, then choose from the yellow options.
This tool helps you get quick answers without using hard formulas!
Keyboard shortcut for Quick Analysis Totals: Ctrl + Q then O
Sparklines
Sparklines are tiny charts that fit inside a single cell. They help you quickly see trends and patterns in your data without using big charts. There are three types you can choose from:
- Line – Shows how values change over time.
- Column – Uses small bars to compare values.
- Win/Loss – Shows positive and negative changes.
Sparklines are great for finding patterns, spotting unusual values, tracking progress, or seeing seasonal changes. They also make your reports and dashboards look neat and easy to understand—without taking up much space.
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. Tables are helpful because they grow automatically when you add new data. Any formatting or formulas you’ve added will also update on their own—so you don’t have to do it again!
Here’s how to quickly make a table using the Quick Analysis tool:
- Select your data.
- Click the Quick Analysis button (or press Ctrl + Q).
- In the menu, go to the Tables section and click Table.
Excel will create a table with a default style. If you want, you can change how it looks using Table Styles. You can also remove styles like banded rows, shading, or borders, while still 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 data:
- What percent of total sales comes from each item?
- What percent 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 get the percentage for each column.
Excel will add a new row showing the sales percent for each month. This helps you see how much each month adds to the 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 see the formula made by the Quick Analysis tool, click on any cell that shows the % Total and look at the formula bar at the top.
In our example, the formula uses table names and looks like this:
If you use a normal range of cells instead of a table, the formula is simpler, like this:
This formula calculates the percentage of each row’s total compared to the grand total of all rows. The top part (the numerator) adds up the values in the current row. The bottom part (the denominator) adds all the values from every row. The dollar signs $ in the denominator make sure the range stays the same when you copy the formula to other cells.
Highlight cells greater than a specified value
To highlight cells with values above a certain number, just follow these easy steps:
- Select the cells you want to format and click the Quick Analysis button.
- Go to the Formatting group and click Greater Than.
- A box will pop up — type the number you want to use.
- Pick a highlighting style (the default is light red fill with dark red text).
- Click OK to apply.
Now, all the cells with values bigger than your 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 both labels and numbers.
- Click the Quick Analysis button.
- Go to the Charts tab and move your mouse over the Pie chart option to see a preview.
- Click on the Pie chart to add it to your worksheet.
- If you want more choices, click More Charts to see other types of charts.
This lets you quickly create a pie chart to easily compare parts of your data!
Since the chart is made from a table, it is dynamic—this means every time you add or remove data in the table, the chart updates automatically. This is very helpful when your data keeps changing or growing.
That’s how you can use the Quick Analysis tool to make common data tasks easier. Next time you work with data in Excel, remember this handy tool. We hope this tutorial was helpful and you learned something new today! 🙂
