Automatic Subtotals in Excel

January 21, 2005

Today we have a list of sales by store. You need to add a subtotal for each store. This is fairly easy to do. Sort the data by store. Go to the first row of data from the next store and select Insert - Row.

Type a label in column A indicating that this is Store 100 Total. In cell C14, select Alt+Equals to put in a sum formula.

Drag the fill handle (the square dot in the lower right corner of the cell pointer) to the right to copy the formula over to column D.

This is not hard. It takes maybe 20 seconds per store. However - if you have 250 stores, it will take a really boring 75 minutes to add all of those totals. Luckily, there is an easier way!

Select a single cell in your data. From the menu, select Data - Subtotals. The following dialog box will be displayed.

The Subtotals dialog offers to insert subtotals every time that the Store changes. By default, the final column is checked in the Add Subtotal To: section. I also checked units. Click OK. In less than 15 seconds, Excel has inserted hundreds of subtotals for you!

You can also use this feature to do multiple subtotals. Be sure to start at the higher level first. If you wanted subtotals by province and store, first add them for the province.

Then, select Data - Subtotals again. This time, uncheck the box for "Replace Current Subtotals". Change the top dropdown from Province to Stores.

The result - subtotals are added for each store and province.

Look to the left of column A. There are new buttons there labeled 1, 2, 3... These are called the Group & Outline buttons. If you choose button 2, you will see just the province subtotals.

If you choose the #3 group and outline button, you will see all of the subtotals.

The automatic subtotal feature has been around since Excel 97, but it is often overlooked. In the case like today, it can turn a boring 1-hour+ process into a few seconds of work.