For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Excel's Quick Analysis tool: instant totals, charts, and formatting

QAT.B2 CTRL + Q · QUICK ANALYSIS
Excel's Quick Analysis tool: instant totals, charts, and formatting — Excel example preview

Selecting a range and pressing Ctrl+Q to preview data bars, totals, sparklines, and charts in one click.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Beginner

Excel 2013 Statistical Analysis #2: Install Data Analysis Add-in For Amazing Excel Statistical Tools

excelisfun

Open on YouTube (opens in a new tab)
excelisfun

Loading preview…

Select a range of numbers and a small icon appears at the bottom-right corner of the selection. That is Quick Analysis — a shortcut to formatting, charts, totals, and tables you would otherwise build by hand.

Nothing it produces is exclusive to it. What it saves is the back-and-forth of choosing a chart type, writing a SUM, or hunting through the Home tab for conditional formatting. For a first look at unfamiliar data, that adds up.

Opening it

Select your data — including the headers, which it uses to label whatever it generates — then either click the icon at the corner of the selection or press Ctrl+Q.

A menu opens with five tabs, and hovering any option previews it live on your actual data before you commit.

If the icon does not appear, three usual causes: the selection is a single cell (it needs a range), the sheet is protected, or the feature is switched off — File → Options → General → Show Quick Analysis options on selection. It also does not appear in Excel for the web.

Formatting

Conditional formatting, applied to the selection: data bars, colour scales, icon sets, and the common rules — greater than, top 10%, text contains, duplicate values.

The live preview is the real advantage here. Choosing between a colour scale and data bars is a judgement about your numbers, and previewing on the actual range answers it faster than applying and undoing.

Two cautions. Clear Format at the end of the row removes every conditional format from the selection, not just the one you added — easy to hit by accident. And the rules it writes contain hard-coded thresholds, which is fine for exploring and not what you want in a report; for anything recurring, put the thresholds in cells and reference them, as in conditional formatting for KPI flags.

Charts

A handful of chart types Excel thinks suit your data, each previewed on hover, inserted with one click. More Charts opens the full Recommended Charts dialog.

This is the fastest way to see the shape of unfamiliar data — whether there is a trend, whether one category dominates, whether there are outliers. The suggestions are based on the selection’s structure and are reasonable rather than clever, so treat them as a starting point. Formatting the result properly is a separate job — the beginner’s guide to Excel charts covers it.

Totals

Running sum, average, count, percentage of total, and running total — added as real formulas, not pasted values.

The tab has two sets of options, and this is where people get caught. The first group adds a row beneath your data; scroll right with the small arrow and there is a second group, with vertically-oriented icons, that adds a column to the right. The icons look similar, and picking the wrong one gives you a total across each row when you wanted a total down each column.

Check what gets written. If your selection was inside an Excel Table, you get SUBTOTAL — which respects filters, and is what you want. On an ordinary range you get SUM, which does not. That difference is the subject of Excel Table vs normal range and it matters the first time someone filters the data and screenshots the total.

Tables

Two options: convert the range to a formatted Excel Table, or jump straight to a new PivotTable built from it.

The PivotTable shortcut offers several pre-configured layouts based on your fields, which is a reasonable starting point — though it almost always needs adjusting afterwards. Anything you intend to show someone else needs the treatment in a pivot table report for managers.

Converting to a Table here is the same as pressing Ctrl+T, and it is usually worth doing before anything else on this list.

Sparklines

Tiny in-cell charts — line, column, or win/loss — one per row, placed in the column immediately to the right of the selection.

They are excellent for showing a pattern across many rows without a full chart taking up space: twelve months of sales per product, in one column. Note that sparklines are not objects sitting on the sheet; they live in the cell as a background, so you can still type in it, and the column has to be empty for Quick Analysis to use it.

Where it stops being the right tool

Quick Analysis is for the first pass, and it has clear boundaries:

  • It works on one selection. It cannot summarise across sheets or combine tables.
  • Its output is a starting point, not a deliverable. The charts need titles, axis formatting, and sorting. The conditional formatting needs its thresholds moved into cells.
  • It does not clean anything. A column of numbers stored as text will chart as nothing and total as zero — see cleaning data before a pivot table.
  • It is not in Excel for the web and is unavailable on a protected sheet.

Use it to find out what is in the data. Then decide whether the answer wants a proper pivot table, a formula from the function reference, or a query — getting started with Power Query — and build that instead.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Move from raw numbers to a defensible conclusion that someone else can reproduce. For this article, the goal is to practice: What each of the five Quick Analysis tabs actually produces, the Totals tab's row-versus-column trap, why the icon sometimes doesn't appear, and where it stops being the right tool.

Practice workbook setup

Use a small table with categories, dates, and at least one numeric measure such as revenue, quantity, or score.

Practice workflow

  1. State the question before summarizing the data.
  2. Create one quick summary with totals, averages, or counts.
  3. Check the same result with a second method such as a PivotTable, formula, or filter.
  4. Write one sentence explaining what changed and one sentence explaining what to investigate next.

Quality checks

  • The analysis uses the correct grain: one row per transaction, person, month, or item.
  • Blank rows, text numbers, and duplicate records have been checked.
  • The conclusion links back to visible evidence in the workbook.

Common mistakes

  • Summarizing before checking whether the data is clean.
  • Comparing totals across periods with different lengths.
  • Treating a chart as proof without checking the underlying numbers.

Next actions

  • Turn the result into a reusable summary block for the next reporting period.
  • Add one note describing the limitation of the analysis.

Formula focus: test SUM(), AVERAGE(), SUBTOTAL() with normal rows, blank inputs, and at least one edge case.