SUBTOTAL Macro
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.
Objective
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.
Overview
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.
Edit macro
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.
Additional Resources
- Sample Excel file: SUBTOTALMacro.xlsm
- SUBTOTAL article
- SUBTOTAL posts
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
I tried creating this macro and I keep getting an error message that comes down to the firstrow is empty.
Tom,
I’m happy to help troubleshoot that with you. Please paste in a copy of your macro and I can see what may be causing the issue.
Thanks
Jeff
I am having the same trouble as Tom
Sub InsertSUBTOTAL()
currow = ActiveCell.Row
firstrow = ActiveCell.End(x1Up).End(x1Up).Row
diff = currow – firstrow
ActiveCell.FormulaR1C1 = “=SUBTOTAL(9,R[-” & diff & “]C:R[-1]C)”
End Sub
Can you help me out?
Bethany,
Happy to help, and thanks for pasting in the code. The End functions on the firstrow line should actually be xlUp (letter l) rather than x1Up (number 1). I’m hoping that if you update this your macro should work. Please let me know?
(Also, the sample Excel file has the macro in there in case you want to copy/paste.)
Thanks
Jeff
Thank you SO much! This will save me a ton of time! 🙂
Welcome 🙂
Is there a way to change the active cell after the macro runs to the current SUBTOTAL cell? The macro makes the active cell upon complete the one from the original macro recording.
Rob,
Sure, that is no problem. My best guess is that your macro has an extra line that changes the active cell after inserting the formula, which probably looks something like Range(“A1”).Select. This is easy to fix…you just want to edit the macro using the Visual Basic Editor, and then remove any lines after the line that sets the formula (ActiveCell.FormulaR1C1 = “…”).
Hope this helps!
Thanks
Jeff
Jeff,
Great post, as always :). I am using Excel 2013 and have heard that the ribbon is easier to customize in this version of Excel. I am curious if you could show this same tip or something else with the button being added to the ribbon instead of the QAT? Or have you done another post already that would show something like that?
Howdy John!
Oh yes, Ribbon Customization is indeed easy in Excel 2013. Head into the Excel Options dialog box, and instead of clicking Quick Access Toolbar (left side of dialog), select Customize Ribbon instead. You’ll be able to create a New Tab, or a New Group within an existing tab, and then Add icons for your macro or for the built-in commands as desired.
And thanks for expressing an interest in learning more about that, I will definitely consider it for a future post topic!
Thanks,
Jeff
Jeff,
This info is GREAT! I thought I’d try to add the same thing only to count the entries instead of subtotaling them, but I got an error “Can’t execute code in break mode”
What did I do wrong?
Sub InsertCOUNT()
currow = ActiveCell.Row
firstrow = ActiveCell.End(xlUp).End(xlUp).Row
diff = currow – firstrow
ActiveCell.FormulaR1C1 = “=COUNT(R[-” & diff & “]C:R[-1]C)”
End Sub
Hi Kathy!
To change the macro to do a count instead of a sum, you can continue to use the SUBTOTAL function as before, and just change the 9 to a 2. So, instead of:
ActiveCell.FormulaR1C1 = “=SUBTOTAL(9,R[-” & diff & “]C:R[-1]C)”
You would use this:
ActiveCell.FormulaR1C1 = “=SUBTOTAL(2,R[-” & diff & “]C:R[-1]C)”
To make this change, open up the Visual Basic Editor and just change the 9 to a 2 and you should be set!
Thanks
Jeff
Just to be sure I understand this one, once the macro is created, then I can place it in various places in the spreadsheet and run the macro to “magically” subtotal each range regardless if some are short (3 rows) and others are long (50 rows)?
Gary,
Ideally, you would store the macro code in the personal macro workbook, and that way the macro code is stored in one single location. Then, you can execute the macro anytime Excel is open on any workbook, and it will insert the SUBTOTAL function and include the adjacent range above the formula cell to include short ranges and long ranges. Once the macro runs, the SUBTOTAL function is stored in the workbook and there is no need to also store the macro code in the workbook as well. Essentially, the macro simply writes the formula for you.
Hope it helps!
Thanks
Jeff
Jeff, this macro for =SUBTOTAL is awesome when added to the QAT! I used your instructions to create the macro — it was my first macro. I’m looking forward to learning more about macros.
After reading Kathy Penner’s 12/2/15 comment and your reply, I used Kathy’s idea and created my second macro, a count macro. It’s identical to the first macro except — per your instructions — I changed the argument to “2” (to count cells with a value or formula) and added it to the QAT, too.
I’ve arranged the icons on the QAT so that =SUBTOTAL (to sum) is invoked by ALT+9. Since the argument for summing with =SUBTOTAL is 9 and the argument for counting with =SUBTOTAL is 2, I considered arranging the icons on the QAT, so that the “count” macro would be invoked by keying in ALT+2. In the end, I decided to make the count macro ALT+8 on the QAT. The icons are the same for both, which may be confusing, but the Alt+9 position for =SUBTOTAL to sum should help me remember which is which.
I’m so happy you shared this great macro.
Awesome 🙂
i do thing follow your instruction and it work as charm. After i have the result of subttotal macro, i want to make a macro to paste result to other sheet to make report can you please show me how to do that.
I just do record macro -> Hotkey macro of subtotal-> copy result -> paste to report sheet -> chose kind of report (value only) -> end macro. But it don’t work.
Hope here your solution soon. Tks you.
Hey Konno,
I can’t seem to replicate the issue you’re having…It works perfectly as per your instructions…Maybe download a new file and start over.
Please let me know how it works out for you
Kurt LeBlanc
Thanks Great Macro. Will it work where there either hidden rows or where there is a blank cell in the range.
Thanks again
Hey Roy
The function goes to the last data cell up so it’ll stop at a blank cell:)
As for the hidden row, whether you want hidden rows included in the calculation affect the code you’ll need in your macro. 9 will include them and 109 will exclude them. Hope that helps clarify things!
Let me know if it doesn’t and I’m happy to help:)
Kurt LeBlanc
Hi, this has been an extremely helpful exercise. However, I am having a slight issue. Whereas instead of the subtotal formula it automatically creates, capturing a column of values immediately above it, and stopping at a blank cell. The subtotal seems to skip any number of blank cells, and extends to the last value in a column of values that reside anywhere above it. Even if i create 100 blank lines above it, it seems to extend past the blank cells and to the last value of the column of values anywhere above it. Is there an additional command i can include to prevent this, keeping its focus only on the values directly above it?
Hi Johnny
Can you furnish me with a copy of your code for the macro? I can help you better after that.
Thank you,
Kurt LeBlanc
Gary:
I took your Excel 3 Seminar today, it was AWESOME
I set the macro up and it runs fine from Run Macro.
I added to the QAT according to the instructions but it doesn’t seem to want to run?
Hey Ben,
hmm…I can’t seem to replicate the problem on my end…maybe download another sample file and try again. I’m sorry I can’t help you anymore than this.
Let me know if you get it
Kurt LeBlanc
Jeff,
I took your complementary class today on excel tips through CPA Academy. I asked you if there was a quick access tool for this function and you delivered! My coworker had taken your webinar last year and told me about the excellent subtotal function, but for economy of key strokes it was still faster to click autosum(sigma) and then clck and pick the totals I needed in the grand total, rather than type all that function command language every time. Now with this macro I am giving up the Sum for good. Thanks!–Robin
SUM No More 🙂
Jeff, what is I have a title and I want it to be excluded from the “firstrow” part?
You can update the firstrow variable to add 1 row, so, something like this:
firstrow = ActiveCell.End(xlUp).End(xlUp).Row + 1
Hope it helps!
Thanks
Jeff
Dear Jeff,
how is the script if you want the row to be dynamic can be used wherever the row is so that the formula does not deviate.
here is an example, if used in row A1 it can be done but if used in row B6 & C11 it can’t be done, the formula is deviate.
I used the following script
Sub InsertSUBTOTAL()
ActiveCell.FormulaR1C1 = “=SUBTOTAL(9,R[1]C:R[-1]C)”
End Sub
1312 Correct
Qty
738
369
205
0 off the mark
Qty
738
369
205
0 off the mark
Qty
738
369
205
Thank for your help
Cecep
Hi Jeff,
Just took the free class on March 2 about the subtotal macro and come to this site to copy and past the macro into the PERSONAL.XLSB workbook. The subtotals are working wonderfully, however when I am trying to do a grand total it is only pulling the numbers for the last subtotal. Has anyone else experienced this problem and what is the fix. Thanks.
Hi! The macro is designed to only include the cells immediately above the formula that are populated, similar to the AutoSum button. So, if the formula that the macro creates improperly excludes values above, you can manually update the cell reference as needed.
Thanks
Jeff
I used your macro for Subtotals and while it worked, it is driving me crazy because every time I open a file I get a “file in use” error:
” PERSONAL.XLSB is locked for editing by ‘Debi’, Open “Read-Only’ or click ‘Notify’ to open read-ony and receive notication when the doument is no longer in use.”
So, I decided to delete the macros, which I did, but I still get this error message every time I open a file. How do I make it stop?
Debi
Hmmm…That sounds frustrating. That doesn’t happen here, so I’m thinking that a macro is trying to open the personal file. I’d suggest opening the visual basic editor and then looking for all macros in the personal file, or, perhaps just delete the personal macro workbook altogether. Excel will automatically created a new one later if needed.
Hope this helps!
Thanks
Jeff
Jeff,
this appeared to work. however, it is functioning as if the 109 code was used. i.e. it was excluding manually hidden rows.
Great macro and added tool to my QAT! Thanks!!
Thank you so much for your macro and your time. It works great!
Just asking for (maybe) some minor changes (I hope so :)), can you please help to modify the macro to subtotal the cell values that are below the active cell instead?
I noted that the macro will delete all redo and undo history. So that we cannot go back after running the macro. Just for information.
Thank you!
hello,
I want subtotal function for count the values cells in excel by vba macro and depends on the subtotal value have to hide the columns
I tried to follow your instruction and it works great!, Thanks. also how can I add the above value from the last adjacent row, the value is 2 blank row above from the last adjacent row and I want the subtotal so stop adding the value if there is 3 blank row. Thanks again.