Excel: Apply partial result - it's that easy
You can use the subtotals function in Excel tables in which values are assigned to different categories. This is a useful tool for summing up values sorted by characteristic, for finding the largest or smallest value or for averaging. We will show you how to create the partial results in Excel - you can also follow the individual steps in our picture gallery.
Apply partial result: Sort Excel list
Before you can insert partial results in Excel, you have to sort your list by category. In our example, we sort a list of annual income by month and generate sales as a partial result for each month. We first sort the list by month:
- Click any cell in the list.
- Select the "Data" tab and click on "Sort" in the "Sort and Filter" area.
- Select the column you want to sort by and choose which attribute to sort by. We choose column A, in which the months are and sort by "values".
- Finally, you determine an order. We sort here by user and select the appropriate list.
- Confirm with OK". The list should now be sorted appropriately.
Excel list: Apply partial result
After sorting, you can insert the partial results. We calculate the sum of the sales for each month.
- Click again in any cell in your list.
- On the "Data" tab, click on "Partial result" in the "Outline" area.
- You will now see a window in which you can adjust the parameters for your partial results. Excel often makes the right settings here.
- In "Group by" select the category for which you want to create the partial results. We choose "Month" here.
- "Using" determines the type of partial result. In addition to an ordinary sum, you can also create an average, the product, the highest or lowest value and more. We opt for "sum".
- With "Add partial result to" you select in which column you want to apply the partial results. We select "sales" here.
- You can leave the check mark at "Replace existing partial results" activated. If you would like to add further partial results later, deactivate this option.
- If necessary, you can insert a page break after each partial result.
- Leave the check mark next to "Show results below the data" and confirm with "OK".
- Excel now shows the corresponding partial result under each category. Using the navigation on the left, you can hide areas by clicking on the numbers and show them again with the plus. This makes the list clear.
- Finally, you can format the list as you wish.
Add nested subtotals to the Excel list
If necessary, you can add further, nested partial results. In our example, we also add the totals by product type each month.
- Click on any cell in the table again and select the "Sub-result" menu item as described above.
- Now select in the dialog box the subitem for which you want to apply the partial result. We select "Type" here.
- We decide again for the sum and add the partial result to "sales".
- Deactivate the option "Replace existing partial results" and confirm with "OK".
Remove partial results from the Excel list
If you no longer need the partial results, you can delete them from the list with just a few clicks.
- Click on any cell in the table and select "Partial result" again.
- In the dialog box, click "Remove All".
- Excel now removes all partial results from the list and restores the original formatting.