Subtracting and Subtotaling Numbers

You can subtotal a column of numbers using subtraction instead of addition.

To negate values, and then find the subtotal:

  1. Select the cell in which you want the subtotal to appear. Note that only the values ABOVE this cell (and, in the same column) will be subtotaled. Values that have already been used in a subtotal will NOT be included. Labels, function results, and empty cells will be given a value of zero.

  2. Go to the Data menu, and click Subtract and Subtotal Above Nos. Or, if you are in Easy Mode, click the Add / Subtotal Above Numbers button (in Advanced Mode, click the icon with a red sigma symbol and a yellow line).

  3. The numbers above the selected cell will be negated, and then subtotaled. Subtotal =, followed by the subtotal, should appear in the cell you selected. Note that this subtotal is a value, and thus, can be used in calculations; however, it will not be included in row and column totals.

Note: If ######### appears in the subtotal cell, the column is not wide enough. Increase the column width, and your subtotal will be visible.

 

Negative Subtotals and Budget Sheets

This function is very useful when creating a budget sheet. If you have a group of expenses, simply apply this function to those cells and you can quickly obtain an expense total. This total will automatically be subtracted from the rest of the column, and the grand total will appear at the bottom of the sheet in the total bar.