How To Use Subtotal In Excel

How To Articles

So, you want to learn about using subtotals in Excel? Well, you’ve come to the right place. Subtotals are a handy feature in Excel that allows you to group and summarize data in a few simple steps. Let’s dive into the details of how to use subtotals effectively in your Excel spreadsheets.

Setting Up Your Data

Before we can start using subtotals, we need to ensure that our data is properly organized. In my experience, it’s crucial to have your data sorted based on the column that you want to use for subtotals. This can be easily achieved by selecting your data range and going to the ‘Data’ tab, then clicking on ‘Sort’.

Applying Subtotals

Once our data is sorted, we can proceed to apply subtotals. To do this, go to the ‘Data’ tab and click on ‘Subtotal’. In the Subtotal window, choose the column you want to use for subtotals, the type of function (e.g., sum, average, count), and the column that you want to apply the function to. Ensure that you select the correct options to get the desired result.

Expanding and Collapsing Subtotals

After applying subtotals, you’ll notice that Excel automatically inserts subtotal rows for each group. These rows can be collapsed or expanded by clicking the numbered icons in the left margin. This feature is incredibly useful when working with large datasets, as it allows you to focus on specific sections of your data.

Removing Subtotals

If you ever need to remove subtotals, simply go back to the ‘Subtotal’ window and select ‘Remove All’ to clear the subtotal rows and restore your original data layout. This can be helpful when reorganizing your spreadsheet or if you no longer need the subtotals.


Subtotals in Excel are a powerful tool for summarizing and analyzing data. By following these steps and incorporating them into your spreadsheet workflow, you’ll be able to efficiently generate insights from your data. Remember to keep your data sorted and choose the appropriate functions when applying subtotals. So go ahead, give subtotals a try, and take your Excel skills to the next level!