How to Summarize Category and Subcategory Amounts in Excel
Question details
The user needs to create an outline-style summary of amounts grouped by main categories and subcategories.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing raw spreadsheet data containing Amount, Category, and Subcategory columns into a structured, easily readable financial or quantitative summary.
- Observed behavior
- The data is currently listed in standard rows and columns, and requires a flexible method to group, expand, filter, and automatically calculate the total amounts for each category tier.
Ensure your dataset has clear, unique column headers (e.g., Category, Subcategory, Amount) and remove any completely blank rows or columns within the data range before proceeding.
Use an Excel PivotTable to Group and Summarize Data
Creating a PivotTable is the most flexible and efficient way to summarize amounts by multiple categories, allowing you to expand or collapse data effortlessly.
PivotTables are built-in Excel tools designed to extract significance from a large, detailed data set. By placing fields in specific areas, you can create a tiered outline that automatically calculates subtotals and grand totals.
Click on any single cell inside your data range, or manually highlight the columns containing your Category, Subcategory, and Amount headers and data.
Navigate to the 'Insert' tab on the top ribbon and click on 'PivotTable'. In the dialog box that appears, choose to place the PivotTable on a 'New Worksheet' or select a destination in your 'Existing Worksheet', then click OK.
Look at the PivotTable Fields pane on the right side of your screen. Drag the 'Category' field down into the 'Rows' area. Next, drag the 'Subcategory' field and drop it immediately below 'Category' in the same 'Rows' area to create your outline structure.
Drag the 'Amount' field into the 'Values' area. Excel should automatically summarize this as 'Sum of Amount'. Your PivotTable will now display grouped amounts that you can expand or collapse using the +/- buttons next to the category names.
Group and Summarize Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet provides a robust and completely free PivotTable feature, making it incredibly easy to summarize, filter, and analyze multi-layered data like categories and subcategories without complex formulas.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the Category, Subcategory, and Amount columns.
- 2. Insert a PivotTable: Go to the 'Insert' tab on the ribbon and click 'PivotTable'. Confirm your data range is correct and click 'OK'.
- 3. Configure the PivotTable fields: In the side panel, drag 'Category' and 'Subcategory' into the Rows box, and drag 'Amount' into the Values box to instantly generate your tiered summary.
Frequently Asked Questions
Can I sort the summarized amounts from highest to lowest?
Yes. Right-click any numerical value inside the Amount column of your generated PivotTable, hover over 'Sort', and select 'Sort Largest to Smallest'. This will organize your categories based on their total amounts.
How do I update the summary if I add new data to the original spreadsheet?
PivotTables do not update automatically when source data changes. To update your summary, click anywhere inside the PivotTable, navigate to the 'PivotTable Analyze' (or 'Options') tab on the ribbon, and click 'Refresh'.
Can I collapse the subcategories to only show the main category totals?
Yes. You can click the minus (-) icon next to any main category name in the PivotTable to hide its subcategories. To collapse all of them at once, right-click a main category, select 'Expand/Collapse', and choose 'Collapse Entire Field'.
Is it possible to display the amounts as percentages instead of currency or numbers?
Yes. Right-click an amount value in your PivotTable, select 'Show Values As', and choose '% of Grand Total' or '% of Column Total' to view the proportion each category contributes to the whole.
