Excel Table with Subcategories: Setup and Examples

How-to basics · Updated November 18, 2026

Yes. To make an Excel table with subcategories, put the main category and its subcategory in separate columns, with one record per row. For example, use columns named Category, Subcategory, Product, and Quantity; Excel tables do not have a special nested-subcategory feature, but this flat structure works well with filters, sorting, formulas, and PivotTables.

What “subcategories” mean in an Excel table

An Excel table is a structured range of data: each column has one heading, and each row holds one record. A category and its subcategory are simply two fields describing that record. This is different from a visual outline in which one row is nested underneath another. Keeping the data flat is usually more useful because Excel can filter, sort, summarize, and refresh it consistently.

Imagine tracking a small stockroom. “Hardware” could be a category, while “Fasteners” and “Tools” are subcategories. A fastener product might have one row, such as Hardware | Fasteners | Wood screws | 24. If a product belongs to more than one subcategory, decide whether it needs multiple records or a separate relationship table; putting several subcategories in one cell makes filtering and reporting less reliable.

A typical table might contain these fields:

  • Category: the broad grouping, such as Hardware.
  • Subcategory: the narrower grouping, such as Fasteners.
  • Product: the specific item or record.
  • Quantity: a numeric value that can be totaled or compared.

Choose a structure that fits the question

Before building the table, consider what you want to do with the information. If each row represents a product, put the product’s category and subcategory on that same row. If each row instead represents an order line, repeat the relevant category and subcategory for every line. Repeated labels are not wasted space: they let Excel identify the group for every record, even after a sort or filter.

Use consistent labels. “Office Supplies,” “Office supplies,” and “Office supply” may look similar to a person, but Excel treats them as different text values in many summaries. A subcategory should normally belong to a clearly identified category. For example, “Chargers” could exist under both “Electronics” and “Accessories”; reporting only on Subcategory would combine them, while the Category and Subcategory pair keeps the distinction.

Avoid using blank rows as separators, merged cells, or category names that appear only above a block of records. Those layouts may look like a printed report, but they make ordinary table operations harder. If you need a presentation view with headings and indentation, keep the source data in a flat table and create the display separately.

Create the Excel table

  1. Enter the column names in the first row, for example Category, Subcategory, Product, and Quantity.
  2. Enter one complete record per row. Repeat the category and subcategory on every applicable row.
  3. Select a cell in the range, then choose Insert > Table. In the Create Table dialog, check that the range is right and select My table has headers.
  4. Choose OK. Excel adds filter arrows to the headings and applies table formatting.

On Windows, Ctrl+T is another way to open the Create Table dialog after selecting a cell in the data. The Insert > Table route is also available on Mac; keyboard shortcuts can vary with the keyboard and macOS settings. In Microsoft 365 for Windows, the contextual Table Design tab appears when you select a table cell. Use its Table Name box to give the table a descriptive name such as Inventory. A clear name is helpful when you later build formulas or reports.

When you type or paste new records immediately below a table, Excel commonly expands the table to include them. Check the table boundary if you paste data farther away: records outside the boundary will not automatically appear in table filters or table-based reports.

Keep category and subcategory values consistent

Data validation can reduce typing differences. One practical setup is to keep approved category names in a separate list, then use a list-based validation rule for the Category cells. Select the cells, choose Data > Data Validation, set Allow to List, and specify the source list. Excel for Mac has the Data Validation command on the Data tab as well, though the dialog layout may differ.

For Subcategory, you can use a second approved list. A single list of all subcategories is straightforward, but it does not stop someone from pairing “Fasteners” with an unrelated category. A dependent dropdown, where the available subcategories change according to the selected category, needs additional setup. It may use named ranges or helper formulas, and the best method depends on how the workbook will be maintained. Do not assume that making the source data an Excel table automatically creates dependent dropdowns.

For a workbook used by several people, document who maintains the approved lists and how new values are added. If staff can enter free text, periodically filter the Category and Subcategory columns and check for spelling variants, trailing spaces, and outdated labels. Cleaning those values before building reports prevents one real group from being split into several apparent groups.

Filter, sort, and summarize the hierarchy

Each table heading has a filter arrow. Filter Category to show one broad group, then filter Subcategory to narrow the visible records further. You can also sort by Category and then Subcategory. For a deliberate multi-level sort, select a table cell and choose Data > Sort; add Category as the first sort level and Subcategory as the next. This changes row order without turning the table into a nested structure.

A PivotTable is useful when you want a compact summary organized by both levels. Select a cell in the source table and choose Insert > PivotTable. In the PivotTable Fields pane, drag Category to Rows, then drag Subcategory below it in Rows. Put a suitable field in Values: for example, Product can be counted to show how many product records are listed in each subcategory. If Excel defaults to Sum for a text field, change the summary calculation to Count in the value field settings.

The PivotTable presents the hierarchy visually and lets readers expand or collapse groups, but the underlying table remains a flat list. This separation is useful: the source is optimized for reliable data entry, while the PivotTable is optimized for browsing and summary. After adding or changing source records, refresh the PivotTable so the report reflects the current data. A slicer can also provide clickable filters; select the PivotTable and use PivotTable Analyze > Insert Slicer.

Add a readable category path with a formula

A separate display field can be handy when exporting data or scanning a long list. It combines the two labels while leaving the original fields available for filtering. If your Excel table is named Inventory and has columns named Category and Subcategory, add a column called Category Path and enter this formula in its first data row:

=[@Category]&" > "&[@Subcategory]

For a row where Category is Hardware and Subcategory is Fasteners, the result is Hardware > Fasteners. The references in square brackets are structured references: they identify the values in the current table row. Excel usually fills the formula down the calculated column. Keep Category and Subcategory as separate columns even if you add this combined label; splitting the path back into fields later is less convenient and more error-prone.

Common problems and how to avoid them

  • Blank cells in the hierarchy: If a subcategory is blank, a report may show an empty group. Fill in a meaningful value such as “Unassigned” if that accurately describes the record, or correct the source data.
  • Inconsistent spellings: A PivotTable can list “Fastener” and “Fasteners” as separate entries. Standardize the source labels before relying on the summary.
  • Several values in one cell: Text such as “Fasteners, Tools” is difficult to filter as two independent assignments. Use one value per field, or redesign the data if records genuinely have multiple category relationships.
  • Formatting mistaken for structure: Indenting text or coloring rows does not create a category relationship Excel can summarize. Store the relationship in columns.
  • New rows missing from a report: Check that the rows are inside the source table, then refresh the PivotTable. A report based on an old fixed range may not include later additions.

One frequent misconception is that outlining rows with Excel’s grouping controls is the same as adding subcategories to a table. Grouping can hide or reveal rows in a worksheet, but it does not give each record a category field, and it is not a replacement for a clean source table. Similarly, indentation is visual formatting rather than data structure.

When a different layout may be better

For most lists, two columns—Category and Subcategory—are enough. A separate category reference table can help when categories have extra details, such as an owner or description, or when you need to manage a large controlled list. In that design, the main records store a category identifier and subcategory identifier, and a report joins those identifiers to their descriptions. It is more structured, but also requires more setup than a small, self-contained workbook.

If the goal is to print a collapsible outline, worksheet row grouping may be useful as a presentation feature. If the goal is to let users choose a category and then see matching subcategories, use data validation or a filtered interface. If the goal is to count records by category, a PivotTable is usually simpler than manually inserting subtotal rows. Pick the tool based on the task; none of these presentation or interaction features changes the basic advantage of keeping source records in a consistent table.

Frequently Asked Questions

Can an Excel table have nested rows?
No. An Excel table stores records in a flat row-and-column structure. Put the main category and subcategory in separate columns, then use a PivotTable or another report to display them as a hierarchy.

How do I show subcategories under categories?
Create a PivotTable, place Category and then Subcategory in the Rows area, and expand or collapse the groups as needed. The original table remains flat; the PivotTable supplies the hierarchical view.

Can a subcategory dropdown depend on the selected category?
Yes, but it requires extra setup, such as named ranges or helper formulas. A regular table does not automatically make one validation list depend on another. Test the setup when adding new category or subcategory values.

Should I repeat the category on every row?
Yes, for a conventional Excel table. Repeating the category gives every record a complete value, so sorting, filtering, and summarizing do not depend on the row’s position or on labels above it.

Why does a PivotTable show duplicate-looking subcategories?
The source may contain small differences such as extra spaces, spelling variations, or different singular and plural forms. Standardize the source values, then refresh the PivotTable.

Can I use color instead of a Subcategory column?
Color can help readers scan a worksheet, but it is not a dependable substitute for a field. Use a Subcategory column for filtering and reporting; add color only as a visual aid.

Related

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.