How to use SUBTOTAL function in Excel spreadsheets [Tip]

SUBTOTAL is 11 Excel functions wrapped into one. It supports a total of 11 functions. So SUBTOTAL provides you with a list of alternative functions that you can add to the spreadsheet by entering the function number.

First, enter a few numbers into spreadsheet cells that you can set up some functions with. Click a cell to enter the SUBTOTAL function in, and press the fx button. Then select SUBTOTAL to open the window below.


First, enter a function number in the Function_num text box. There are 11 function numbers you can enter there. These are the function numbers:

  • 2 COUNT
  • 3 COUNTA
  • 4 MAX
  • 5 MIN
  • 7 STDEV
  • 8 STDEVP
  • 9 SUM
  • 10 VAR
  • 11 VARP

For example, enter 9 in the Function_num text box to add numbers. Click the button beside the Ref1 text box to select some cells to add together. Press the OK button to close window and add the SUM function to the selected cell. In the shot below I’ve added the cells in column F with SUBTOTAL.


Or you can enter the function directly into the formula bar. Select a cell to add SUBTOTAL to, and click inside the address bar. Input =SUBTOTAL( and select a function from the list shown below. Then enter the cell references in the brackets.


So SUBTOTAL gives you a handy shortcut for entering eleven functions into Excel 2010/13 spreadsheets. Check out this YouTube video for further SUBTOTAL details.

Related Posts