The SUBTOTAL is a wonderful alternative to the SUM function, and this post shows how to set up a shortcut button that inserts the SUBTOTAL function. Our new shortcut button will be similar to the AutoSum button that inserts the SUM function because it will automatically include the cells above.
Before we get started, let’s be clear about what we are trying to do. If you want to insert the SUM function, and you want it to automatically include the adjacent values above, then you can use the built-in AutoSum button or the Alt+= keyboard shortcut. This is illustrated below.
We will build a macro that inserts a SUBTOTAL function in a similar manner, as shown below.
We will also set up a button and corresponding keyboard shortcut to make it easy to run anytime. Can we do that? We can, and we will. Now, let’s get started.
The steps to create such a macro are as follows.
- Record starter macro
- Edit macro
- Set up button in the QAT
Let’s begin with the first step.
Note: if you want to get the Excel macro without all of the explanation, just shoot to the end of the post and download the Excel file which contains the working macro.
Record starter macro
We want to record a starter macro that will set up just the basics. It won’t accomplish our goal, but, it will set up a working macro that we can easily edit.
First, create a new blank workbook.
Next, enter some numbers in a few cells. Then, select the cell under the numbers. Your worksheet should look something like this.
Next, we want to tell Excel to record us entering a SUM function. To start the macro recorder, select the following Ribbon icon.
- View > Macros > Record Macro
Note: The Macros icon is split and the top half opens the Macro dialog box and the bottom half reveals a drop-down menu where you can select Record Macro.
After you select Record Macro, Excel will display the Record Macro dialog box. For the Macro name, you can use any name you’d like but avoid spaces and funky characters. I used the name InsertSUBTOTAL. Leave the Shortcut key field empty. Then, you want to store the macro in the Personal Macro Workbook. At this point, your Record Macro dialog box should look like this.
Click the dialog’s OK button and right now, Excel is watching everything you do and it is recording your mouse and keyboard commands. Thus, right now, before you click anything else, you want to tell Excel to insert the SUM function, and you can do so by clicking the Formulas > AutoSum button or by typing the Alt+= keyboard shortcut. Then press Enter on your keyboard to store the formula in the active cell.
Now, STOP the recorder. To stop the macro recorder, you can select the following Ribbon command.
- View > Macros > Stop Recording
Note: Stopping the recorder is a very important step. If you forget to stop the recorder then your subsequent Excel keyboard and mouse commands will be stored as part of the macro.
At this point, you should have inserted the SUM function, pressed Enter, stopped the recorder, and your worksheet should look something like this.
Now, we have the basic starter macro recorded, and it is time to edit it.
In the step above, we stored the macro in the Personal Macro Workbook. This is a hidden workbook (named PERSONAL.XLSB) that is open whenever Excel is open. That means we can run the macro anytime we are running Excel. To edit a macro stored in the Personal Macro Workbook, we need to unhide it. To do so, click the following Ribbon command.
- View > Unhide
In the resulting Unhide dialog box, confirm that PERSONAL.XLSB is selected as shown below, and then click OK.
We can now see the PERSONAL.XLSB workbook in Excel and so we are ready to edit the macro.
To edit the macro, we select the following Ribbon icon.
- View > Macros
In the resulting Macros dialog box, we select our new InsertSUBTOTAL macro as shown below.
We now click the Edit button to open the Visual Basic Editor, which should look something like the screenshot below.
If you have not used the Visual Basic Editor before, it operates very much like a word processor, and you can edit the text, delete text, type new lines, copy/paste, and so on. Our modifications should be fairly easy.
Rather than show screenshots of the macro as we proceed with our edits, I will simply display the macro text as shown below.
Sub InsertSUBTOTAL() ' ' InsertSUBTOTAL Macro ' ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)" Range("A5").Select End Sub
First up, let’s delete the extraneous lines. The updated macro is shown below.
Sub InsertSUBTOTAL() ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)" End Sub
Note: if the macro you recorded is different than the above, you can replace your recorded macro by copying the three lines above and pasting into your visual basic editor.
The remaining macro line is the one that inserts the SUM function. It has a strange notation. Instead of using a traditional A1 style range reference, such as A1:A3, it uses an RC (Row Column) notation. The notation R[-3]C tells Excel to go up three cells and stay in the same column as the active cell. The notation R[-1]C tells Excel to go up one cell and stay in the same column as the active cell. Since our formula was inserted into A4, we can see that A1:A3 is the same range as R[-3]C:R[-1]C.
So, we have two edits to make to this macro line: we need to use SUBTOTAL instead of SUM and we need to update the RC range so that it dynamically includes the values in the cells above the active cell.
Let’s start with the easy part. We need to tell Excel to insert the SUBTOTAL function instead of the SUM function. The SUBTOTAL function has an extra argument that the SUM function doesn’t have, and this extra argument tells Excel what type of math to apply to the range. We want our SUBTOTAL function to add the values in the range, so, we need to use code 9, because 9 is the code for sum. Thus, we want to replace “SUM(” with “SUBTOTAL(9,” as shown below.
Sub InsertSUBTOTAL() ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)" End Sub
Now, we need to replace the -3 with a variable that will adapt to the worksheet so that the SUBTOTAL function references the populated range immediately above the active cell.
First, we will make a note of the current row. This is done by inserting the macro line “currow = ActiveCell.Row” as shown below.
Sub InsertSUBTOTAL() currow = ActiveCell.Row ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)" End Sub
Next, we need to make a note of the first row with data. This is done by inserting the line “firstrow = ActiveCell.End(xlUp).End(xlUp).Row” as shown below.
Sub InsertSUBTOTAL() currow = ActiveCell.Row firstrow = ActiveCell.End(xlUp).End(xlUp).Row ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)" End Sub
Next, we need to determine the number of rows between the first row and the current row, and this is done with simple subtraction by adding the line “diff = currow – firstrow” as shown below.
Sub InsertSUBTOTAL() currow = ActiveCell.Row firstrow = ActiveCell.End(xlUp).End(xlUp).Row diff = currow - firstrow ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-3]C:R[-1]C)" End Sub
Next, we need to update the formula so that instead of using -3 it uses our computed difference, which is shown below.
Sub InsertSUBTOTAL() currow = ActiveCell.Row firstrow = ActiveCell.End(xlUp).End(xlUp).Row diff = currow - firstrow ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & diff & "]C:R[-1]C)" End Sub
Now, we want to save our macro so we save the Personal Macro Workbook. We can hide it again if desired by selecting the following icon.
- View > Hide
With our macro set up, we just need to make it easy to run. For this, we’ll set up a button in the Quick Access Toolbar (QAT).
Set up button in the QAT
To add a button the Quick Access Toolbar (QAT), use the little drop-down to the right of the QAT and select More Commands. In the resulting Excel Options dialog, you want to Choose commands from Macros. Then, you select the InsertSUBTOTAL macro and Add it to the QAT, as shown below.
Now, we should be good. To test it out, we select a cell and press the new QAT button, as shown below.
The macro inserts the SUBTOTAL function…we did it!
The QAT automatically adds Alt shortcut keys as well. Press the Alt key on your keyboard and you’ll see little numbers appear for the QAT command buttons. You can activate the SUBTOTAL macro either by clicking the icon with your mouse or by using the corresponding Alt keyboard shortcut.
With our macro in place, we can now quickly insert the SUBTOTAL function into our workbooks. If you have any other fun macros or ways to insert the SUBTOTAL function, please share by posting a comment below…thanks!
Note: When you close Excel you may receive a dialog box asking if you want to save changes to the Personal Macro Workbook. Click Yes, otherwise, your work may not be saved.