Super easy subtotals in a Microsoft Excel databaseThe Subtotals command in Excel offers a very quick of getting, well, subtotals – sums, averages and more- without the use of functions and formulas.

If you can read these instructions or watch the video, you will know how to calculate subtotals for the hugest, most humongous lists. But you must sort the table or database first. I said, you must sort the table. That’s right, don’t forget to sort first.

Video: Super easy subtotals in a Microsoft Excel database

Step 1 – Sort the database

Make sure you have a database first and then if you want to have a total by Region, sort by Region.

A database not sorted by Region - Super easy subtotals in a Microsoft Excel database

As long as you have no blank rows or blank columns in your list or table, just click inside the column to sort by and then click the Sort command.Data menu, Sort Command- Super easy subtotals in a Microsoft Excel database

The same database after we sort it by Region - Super easy subtotals in a Microsoft Excel database

Step 2 – Subtotals

Click any cell within your database and select Subtotals from the Data menu

Subtotals command in the Data menu - Super easy subtotals in a Microsoft Excel database

Now read carefully the instructions in the dialog box.

  • Under At each change in make sure that you select the same field name (column heading) as in Step 1.
  • For Use function choose from the dropdown. Usually, Sum is used but you can easily get an average or other calculation of your choice.
  • Add subtotal to lets you choose which column(s) you want to add up or average so choose as few or as many as you need.

Subtotals dialog - Super easy subtotals in a Microsoft Excel database

Click OK to see the result. Notice the outlining on the left hand side. Subtotals applied to a database - Super easy subtotals in a Microsoft Excel database

Step 3 – Outlining

Toolbar Screenshot - 23_07_2010 , 00_27_00Click the symbols (-, + and the figures 1, 2, 3 to the left hand side of the spreadsheet) to display or hide various levels of details in the database.

Using the outline of the database - Super easy subtotals in a Microsoft Excel database

Step 4 – Remove All Subtotals

To remove the subtotals, select Subtotals in the Data menu. Click Remove All in the dialog box.

Remove subtotals - Super easy subtotals in a Microsoft Excel database