how to add multiple rows in excel and keep column totals

Excel's Subtotal feature allows you to generate subtotals for groups quickly and easily. However, there are more advanced features of the Subtotal function that can be used to add multiple subtotaling functions to a single row or even create multiple rows with totals. In this article, we will explore how to use Excel's Subtotal feature to add multiple rows in Excel and keep column totals.

To start, you need to sort your data by the column that contains the grouping value. This is an important step because the Subtotal function evaluates groups based on the sorted data. For example, if you have a dataset with categories and sales data, you would sort the data by the category values before executing the Subtotal feature.

Once your data is sorted, you can use the Subtotal feature to display multiple Average functions for the same group. This can be especially useful when you want to see not only the total average but also the average of a specific column or set of columns within each group.

To add multiple subtotaling functions to a single row, follow these steps:

1. Go to the Data tab and click on the Subtotal button in the Outline group.

2. Select the column that contains the grouping value from the 'At each change in' dropdown menu.

3. Choose the function you want to use for the subtotal, such as Average or Sum.

4. Click on the 'Function' dropdown menu and select the function you want to use.

5. Repeat steps 2-4 for each additional function you want to add.

Alternatively, you can also add multiple rows with totals by using the Subtotal feature to evaluate more than one column using the same subtotaling function. To do this:

1. Follow the same steps as above to select the grouping value and choose a function.

2. In the 'Summarize by' dropdown menu, select the additional columns you want to include in the subtotal.

3. Repeat step 2 for each additional column.

By following these steps, you can easily add multiple rows in Excel and keep column totals using the Subtotal feature. Whether you need to display multiple Average functions or create multiple rows with totals, this feature is a powerful tool that can help you analyze your data more effectively.

For demonstration purposes, two demo Excel files are available for download below. These files show how to use the Subtotal feature to add multiple subtotaling functions and create multiple rows with totals.

How to add multiple rows in excel and keep column totals