Subtotal Excel Shortcut: Open the Subtotal Dialog

In Excel for Microsoft 365 on Windows, press Alt+A, B in sequence to open the Subtotal dialog. It is a keyboard sequence, not a simultaneous key combination; Alt+= is the AutoSum shortcut and does not run the Subtotal command.

The Windows shortcut and what it opens

Press and release Alt, then press A, then B. Excel uses these Key Tips to navigate the ribbon: Alt activates the tips, A selects the Data tab, and B selects Subtotal. The dialog lets you insert subtotal rows into a list, grouping records according to a field such as department, region, or product category.

The shortcut is useful when you want Excel to build subtotal and grand-total rows into the worksheet itself. It is not a shortcut for entering the SUBTOTAL function into a cell. If the sequence does not work, click the Data tab and look for Subtotal in the Outline group; keyboard tips can vary if the ribbon layout or language differs.

How Excel’s Subtotal command works

The command summarizes a sorted list whenever the value in a chosen column changes. For example, if a sales table is sorted by Region, you can ask Excel to add a subtotal after each region and a grand total at the bottom. It inserts worksheet rows and formulas, rather than merely displaying a temporary calculation.

To use it, first select a cell within the list, then press Alt+A, B. In the dialog, choose the grouping field under At each change in, the calculation under Use function, and the numeric or other relevant column under Add subtotal to. Click OK to create the rows. Common functions include Sum, Count, Average, Max, and Min.

Excel’s grouping depends on consecutive records with the same value. If the rows for one region are scattered throughout the list, Excel may create multiple subtotals for that region. Sort the data by the grouping column first if you want one subtotal per group.

Prepare the data before inserting subtotals

Subtotal works best with a simple, continuous table: one header row, one record per row, and consistent columns. Remove blank rows within the data and make sure the field you plan to group by is populated consistently. For example, North and NORTH may be treated as separate values in a practical workflow, so standardize labels before summarizing.

Sort the list by the field that will define each group. With a table containing Department, Employee, and Sales, sort by Department before choosing Department in the dialog. You can sort by additional fields too, such as Department and then Employee, if you want nested grouping levels.

One important limitation: the Subtotal command is not available while the data is formatted as an Excel Table. If the command is unavailable, click inside the table and use Table Design > Convert to Range, then confirm the conversion. This changes the table into an ordinary range; save a copy first if you rely on table features such as automatic expansion or structured references.

What the Subtotal dialog options mean

At each change in: The column Excel monitors for a new group. The data should be sorted by this field.
Use function: The calculation applied at each group boundary, such as Sum or Average.
Add subtotal to: The columns that should receive results. For a sales total, select the Sales column, not the text columns.
Replace current subtotals: When checked, a new run replaces existing subtotal rows. Clear it when deliberately adding another level of grouping.
Page break between groups: Inserts page breaks between groups for printed output. It does not affect the calculation.
Summary below data: Places each group’s subtotal after its detail rows. Clear it to place the subtotal above the group.

For a basic regional sales report, select Sum for Use function and check Sales under Add subtotal to. If you also want average sales per region, run the command again with Average and clear Replace current subtotals so the earlier sum level remains. The result is an outline with expandable group levels.

Subtotal command versus the SUBTOTAL function

The ribbon command and the worksheet function are related but serve different purposes. The command automatically inserts rows and formulas into a sorted list. The SUBTOTAL function is something you enter in a cell yourself; it can calculate a range without restructuring the data and is especially handy for filtered lists.

With sales amounts in B2:B6 containing 120, 80, 150, 100, and 50, this formula returns 500:

With the amounts in B2:B6, =SUBTOTAL(9,B2:B6) returns 500 by summing the range.

The first argument, 9, specifies SUM. SUBTOTAL also supports other function numbers, including 1 for AVERAGE, 2 for COUNT, 3 for COUNTA, 4 for MAX, and 5 for MIN. Unlike a plain SUM in a filtered list, SUBTOTAL excludes rows hidden by a filter.

For example, with the same sales values in B2:B6, =SUBTOTAL(109,B2:B6) also returns 500 when no rows are hidden. The 109 variant means SUM while also excluding rows hidden manually with the Hide command. Both 9 and 109 exclude rows removed from view by a filter; the distinction is whether manually hidden rows are included.

SUBTOTAL ignores other SUBTOTAL formulas in the reference range. That makes it suitable for a grand total over ranges that already contain subtotal formulas: the group totals are not counted a second time. This behavior helps explain why Excel’s inserted subtotal rows can coexist with an overall total.

Why Alt+= is not the Subtotal shortcut

Alt+= invokes AutoSum, which typically inserts a SUM formula for a nearby range. It can save time when you want a total in a blank cell, but it does not open the Subtotal dialog, create group breaks, or add outline controls. Use Alt+A, B for the grouped-list command; use Alt+= for a quick sum.

AutoSum may suggest a range based on nearby cells, so check the selected range before accepting it. For a filtered list where hidden rows should be excluded, enter a SUBTOTAL formula or use the Subtotal command rather than assuming an ordinary SUM will behave as desired.

Excel on Mac and Excel for the web

The Alt+A, B Key Tip sequence is for Excel on Windows. On Mac, use the Data tab and choose Subtotal if the command is available in your version and ribbon layout; there is no matching Windows Alt-key sequence to rely on. Mac keyboards and menu shortcuts differ, so the visible ribbon command is the dependable route.

Excel for the web supports worksheet formulas such as SUBTOTAL, but the desktop Subtotal dialog and outline workflow may not be available in the same way. If you need Excel to insert grouped subtotal rows, open the workbook in the desktop application. If you only need a filtered total, a SUBTOTAL formula is often sufficient in a supported web workbook.

Common problems and fixes

Subtotal is disabled: Check whether the data is an Excel Table. Convert it to a range before using the command.
There are several subtotals for one category: Sort by the grouping field so identical category values are together.
The shortcut opens something else: Make sure you pressed Alt, released it, and then pressed A and B. Check that Excel is active and that the correct ribbon language is in use.
Existing totals disappear: Review Replace current subtotals. Leave it checked when replacing the current setup; clear it when adding a second subtotal level.
The grand total seems too large: Remember that a normal SUM over detail rows and inserted subtotals can double-count. Use SUBTOTAL over the full range, which ignores nested SUBTOTAL formulas, or inspect the range being totaled.

After creating subtotals, Excel displays outline controls beside the row numbers. Use the numbered outline buttons to show a summary level or expand details, and use the plus and minus controls beside groups to open or collapse individual sections. These controls change what is displayed, not the underlying data.

Frequently Asked Questions

What is the shortcut for Subtotal in Excel?
In Excel for Microsoft 365 on Windows, press Alt+A, then B. This opens the Subtotal dialog; it is a sequence of ribbon Key Tips, not keys pressed together.

Does Alt+= insert a subtotal?
No. Alt+= runs AutoSum and generally inserts a SUM formula. For grouped subtotal rows, use Alt+A, B on Windows or open Data > Subtotal from the ribbon.

How do I add a subtotal for each category?
Sort the records by the category column, open the Subtotal dialog, select that column under At each change in, choose a function, and select the value column under Add subtotal to.

Why can’t I click Subtotal?
A common reason is that the selected data is formatted as an Excel Table. Convert the table to a range, then try the command again. Also check that you are using desktop Excel and that the worksheet is not in a state that prevents editing.

Does SUBTOTAL ignore filtered rows?
Yes. SUBTOTAL excludes rows hidden by a filter. Function numbers 1–11 include manually hidden rows, while 101–111 exclude manually hidden rows as well.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.