To subtotal in Apple Numbers on a Mac, select your table, open Organize, add a category using the column you want to group by, then choose Sum in the summary cell for the numeric column. Numbers creates a separate summary for each group, such as a subtotal for each region or month. Before starting, make sure your table has a header row and that the values you want to add are stored as numbers, not text.
What You’ll Need
- A spreadsheet open in Apple Numbers on a Mac. Numbers is an Apple app and does not have a Windows desktop version; this guide uses the Mac interface.
- A table with one record per row, a clear header row, a column to group by, and a numeric column to subtotal. For example, use columns named Region and Sales.
- Consistent category names. If you intend to group records as North, South, and West, check for accidental variations such as trailing spaces or misspellings.
- An optional copy of the spreadsheet if you want to preserve the original table arrangement. Adding categories changes how rows are grouped and displayed, although it does not delete the underlying records.
Step-by-Step
- Arrange the source data in one table. Put field names in the top header row and each transaction or observation on its own row. Keep the group name and the amount on the same record. For example, a sales table might have Region in column A and Sales in column B, with North and 120 on one row, North and 80 on another, and South and 150 on a third. Avoid blank rows or separate mini-tables inside the data, because they can make it harder to see whether all the records are included. If your sheet contains several tables, click a cell inside the one you want to summarize.
- Check the columns before grouping. Confirm that every amount you want included is a number. Select a few amount cells and look at their appearance: numbers generally align to the right by default, while text often aligns left, though formatting can change alignment. A cell that contains a currency symbol typed as part of the text, or an amount imported with an unexpected separator, may not behave as a number. Also check that group labels are consistent. Category groups are based on the values in the chosen column, so “North” and “North ” with a trailing space may be treated as different labels.
- Open the Categories controls. With the table selected, click Organize in the Numbers toolbar. In the Organize sidebar, click the Categories tab. Choose Add a Category, then select the header for the column that defines each group, such as Region. If you do not see the expected header, confirm that the selected table has a header row and that you clicked inside the correct table. The category is applied to the table, not to the entire sheet.
- Review the groups Numbers creates. Numbers rearranges the table into groups based on the chosen category. Look through the category labels and make sure the records appear under the groups you expect. The original records remain part of the table; grouping changes their presentation rather than replacing them with a short summary. If labels that should match appear as separate groups, edit the source values to make them identical. If a group seems to be missing, check whether its rows were outside the table or whether a filter is hiding them.
- Choose the numeric column to summarize. Find the summary row associated with a group and the cell in that row under the numeric column, such as Sales. Click that summary cell. Click the disclosure arrow or function control that appears in the cell, then choose Sum. Numbers calculates a subtotal for that group from the values in the selected column. Repeat for the other group summaries if the function is not already shown for every group. If you want an average rather than a total, choose Average; for a count, choose an appropriate count option from the function menu.
- Check the result against a small example. Suppose the table contains North sales of 120 and 80, South sales of 150 and 50, and West sales of 100. The subtotal for North should be 200, South should be 200, and West should be 100. These group amounts add to 500. Checking a few values by hand is a quick way to catch a range, data-type, or grouping problem before you rely on the summary.
- Add another category only if you need nested subtotals. To break each main group into smaller groups, use Add a Category again and choose a second column, such as Product under Region. Review the result carefully: you will now see a hierarchy, so subtotals may be displayed at more than one level. Use this when you genuinely need a breakdown like region, then product. If you only need one subtotal per region, leave the table with one category to keep it easier to read.
- Keep an overall total separate when needed. A group subtotal answers “what is the total for this category?” If you also need one total for all rows, you can add a separate total cell or table. With Region in A1:A6 and Sales in B1:B6, where row 1 contains headers and rows 2–6 contain the five example amounts, enter
=SUM(B2:B6)in an empty cell outside the source data; it returns 500. Keep that total outside the grouped records so it is not accidentally treated as another sale. If you add or remove records later, check that the formula range still covers the full data set.
Common Mistakes
- Choosing the wrong category column. Grouping by Sales instead of Region creates groups based on amounts, which is rarely the intended result. Choose a descriptive field such as department, date, or product for the category, and use the numeric field for the summary.
- Using text-formatted amounts. A value that looks like 120 may be stored as text and may not contribute to a sum. Correct the source data or convert it to numeric values, then check the subtotal again.
- Expecting a single Excel-style Subtotal command. Apple Numbers uses its Categories feature to organize records and display summary calculations. Look in Organize, not for Excel’s Data > Subtotal command.
- Including a hand-typed total among the records. If a total row is inside the source table, it can be grouped or counted as though it were a normal record. Put manual totals outside the data rows, or remove them from the source range before grouping.
- Assuming the subtotal replaces the source data. The grouped view can look compact, but the individual rows are still there. Expand or inspect the groups when you need to verify a calculation or edit a record.
Tips
- Use simple, stable labels in the category column. A dropdown menu or consistent data-entry process can reduce spelling differences that split one intended group into several.
- Use a date field for monthly or other date-based analysis only after checking how the values are stored. Text that merely looks like a date may not group as expected. If you need month-level groups, decide whether to create a separate Month column in the source table.
- Choose the summary function that answers the question. Sum is suitable for totals; Average answers a different question, and a count reports how many entries there are rather than how much they add up to.
- For nested analysis, add categories one at a time and inspect the hierarchy after each change. This makes it easier to spot an unnecessary level or a misleading breakdown.
- Keep an untouched copy of important source data. You can also duplicate the sheet before changing its organization if you want both a detailed view and a categorized summary view.
- After editing or adding records, check that the table includes the new rows and that the summaries update. If a new record is outside the table, it may not appear in the category groups.
Troubleshooting
No category groups appear: Click a cell inside the table, not an empty area of the sheet, and reopen Organize > Categories. Confirm that you used Add a Category and selected a column header. If the table has no clear header row, add or identify one before choosing the category.
The subtotal is blank or zero: Inspect the source amount cells. They may be blank, contain text, or use characters that prevent Numbers from recognizing them as numbers. Correct a sample value and see whether the summary changes. Also make sure you chose the numeric column’s summary cell, rather than a category label or an unrelated column.
The result is too high: Look for duplicate records, a manually entered total included among the detail rows, or values being included from a wider range than intended. Expand each group and compare its detail rows with the source records. If you are using the separate =SUM(B2:B6) example, verify that the formula range contains only the intended amount cells.
One category appears more than once: Check for leading or trailing spaces, spelling differences, and inconsistent capitalization or punctuation in the source labels. Edit the labels so that records intended to belong together use the same value, then review the grouping.
A new row does not affect the subtotal: Check whether the row is part of the table. Add it to the table rather than placing it just below the table boundary, then check that its category and amount cells are filled in. Confirm that the displayed summary changes afterward.
The controls look different on iPad: This guide describes Numbers for Mac. Numbers for iPad has a touch-oriented toolbar and sidebar, so the controls may be in different places; select the table and look for its organization or categories controls. Numbers for iCloud can also present a different interface. If a control is not visible, check that the table is selected and consult the help available for the version you are using.
Frequently Asked Questions
Does Apple Numbers have a Subtotal button like Excel?
Not in the same way as Excel’s Data > Subtotal command. In Numbers, add a category through Organize > Categories, then choose a summary function such as Sum for the relevant column.
Can I subtotal by more than one field?
Yes. Add a category for the first field, then add another category for the next level. For example, group first by Region and then by Product. Use multiple levels only when you need subtotals within each main group.
Can I use Average instead of Sum?
Yes. Click the relevant summary cell and choose Average from its function menu. The choice depends on what you want to report: an average is not the same as a subtotal that adds all values.
Will the subtotals update when I edit the data?
They should reflect changes to values and records that are part of the categorized table. If you add a row outside the table, or a value is stored as text, it may not be included as expected. Confirm the row is in the table and the amount is numeric.
Can I subtotal on a Windows PC?
Apple Numbers is designed for Apple devices and does not have a Windows desktop app. You can use Numbers on a supported Apple device or use another spreadsheet program on Windows; its subtotal workflow will differ.
How do I show both category subtotals and one grand total?
Use the category summaries for each group, then place a separate overall calculation outside the source records. For example, with amounts in B2:B6, =SUM(B2:B6) returns 500 for the five example amounts. Make sure the range matches your actual data and excludes any total row.
Related
- Editing a Group of Sheets in Excel: Steps and Limits
- Redo in Excel: How to Restore an Undone Action
- Easiest Way to Superscript in Excel: Quick Steps and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Checkbox in Word With a TickTo put a tick in a Word checkbox, insert a checked-box symbol for a fixed mark, or add…
- How to Open an IIF File in Excel Without Losing DataTo open an IIF file in Excel, import it as a tab-delimited text file rather than double-clicking it.…
- Hanging Indent in Word: Shortcuts, Setup, and FixesIn Microsoft Word for Windows, press Ctrl+T to create or increase a hanging indent in the paragraph containing…
- Excel Shortcut to the End of Data: Keys and LimitsIn Excel for Microsoft 365 on Windows, press Ctrl+Down Arrow to jump to the edge of the current…
- How to Resolve a Corrupted Excel File Without Losing DataTo resolve a corrupted Excel file, first make a copy of it, then try Excel’s built-in Open and…
- Google Sheets Dropdown Default Value ExplainedGoogle Sheets does not have a setting that automatically inserts a default choice into every empty cell with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.