Quick Analysis in Excel: Totals, Charts and Formats Fast

The Quick Analysis tool in Excel gives you totals, charts, colour formatting and tables in two clicks. Select your data, click the small button that appears at the bottom-right corner (or press Ctrl+Q), and pick what you want from a gallery with a live preview. This guide covers each tab, the formulas it writes for you, and what to do when the button is missing or you're on a Mac.

What is Quick Analysis?#

Quick Analysis is a shortcut menu for the most common things people do with a block of numbers. Instead of hunting through the ribbon, you get one gallery with five tabs:

It's available in Excel for Windows (Excel 2013 and later, including Microsoft 365). Everything it creates is ordinary Excel: real formulas, real charts and real formatting rules that you can edit later.

How to open the Quick Analysis tool#

Quick Analysis appears whenever you select two or more cells that contain data.

1. Select your data, including the headers, for example A1:B5.
2. Click the Quick Analysis button at the bottom-right corner of the selection. Or press Ctrl+Q.
3. Pick a tab at the top of the gallery.
4. Hover over an option to preview it on your data. Click to apply it.

You can also right-click the selection and choose Quick Analysis. Press Esc to close the gallery without changing anything.

The examples use this sales table:

Formatting: data bars, colour scales and icons#

The Formatting tab adds conditional formatting, which changes how cells look based on their values. For numbers you get Data Bars, Color Scale, Icon Set, Greater Than, Top 10% and Clear Format. For text, it offers options like Text Contains and Duplicate Values instead.

Select B2:B5, press Ctrl+Q and click Data Bars. Each cell gets a bar sized to its value, so East's 1,500 stands out at a glance. Greater Than asks for a number and highlights every cell above it.

To change a rule afterwards, go to Home > Conditional Formatting > Manage Rules. The guide to conditional formatting explains how rules work and how to write your own with formulas.

Charts: a recommended chart in one click#

The Charts tab shows the chart types Excel thinks suit your data. Hover over each one to preview it, then click to insert it. For the sales table, a clustered column or a pie chart are typical suggestions.

Click More Charts to open the full Insert Chart dialog. Once the chart is in, use the buttons beside it to add titles, data labels and other elements. The article on chart basics shows how to choose the right chart type.

Totals: formulas without typing#

The Totals tab inserts formulas next to your data. Options shaded blue add a row of results below each column. Options shaded orange add a column of results to the right of each row.

• Sum puts =SUM(B2:B5) under the column: 4,000.
• Average and Count do the same with AVERAGE and COUNT.
• % Total shows how big each part is compared with the overall total.
• Running Total adds up the values row by row, so the last result equals the full total.

You can do % Total yourself in one formula. For North's share:

=B2/SUM(B2:B5)

The result is 0.3, or 30% with a percentage format. Writing the formula yourself lets you lock the total with $B$2:$B$5 and fill it down. The SUM function lesson covers this and other everyday totals, and the AutoSum shortcut guide shows the fastest way to add a total row.

Tables and Sparklines#

The Tables tab turns your range into an Excel table, with filter buttons, banded rows and automatic expansion when you add data. It may also suggest PivotTables that summarise your data in different ways. Click one to create it on a new sheet.

The Sparklines tab adds tiny charts inside cells, one per row. They work best on data with several columns of numbers per row, such as monthly sales by region. Line sparklines show a trend, column sparklines compare values, and win/loss shows positive versus negative.

Quick Analysis is handy for building trackers and models quickly. For example, data bars and a running total are a fast start on an attendance tracker or a break-even analysis.

Quick Analysis not showing? Fixes and the Mac alternative#

If the button doesn't appear when you select data, check these:

• The option is off. Go to File > Options > General and tick Show Quick Analysis options on selection.
• You selected one cell, or empty cells. Select at least two cells that contain data.
• The sheet is protected, or you're editing a cell. Press Enter or Esc first.

Excel for Mac has long lacked the Quick Analysis button, and many Mac versions still don't show it. If yours doesn't, use the ribbon instead:

On Windows, the AutoSum shortcut is Alt+=. It's also worth learning for job tests, where typing a total is faster than opening any menu.

Practise it#

Practise SUM and quick totals in a live spreadsheet: you'll add totals and shares like the ones Quick Analysis creates, and each formula is checked instantly. Then practise conditional formatting to build data bars and highlight rules by hand. Try the % Total exercise on this page first.