Quickly Insert the SUM Function
Let’s face it, Excel users probably insert the SUM function more often than any other function. As you know, we can improve our productivity by keeping our hands on our keyboards. This post discusses my favorite keyboard shortcut for inserting the SUM function.
Keyboard Shortcut
The SUM function is easily inserted into a workbook with the Alt+= shortcut. Microsoft beautifully implemented this keyboard shortcut because it auto-detects the adjacent range and tries to guess which cells should be included in the sum.
For example, consider the worksheet pictured below.
We need a fast way to enter the SUM function in C14, and we want it to include the values in the amount column automatically. Simply navigate to C14, hold down the Alt key and hit the equal key (Alt+=). Bam, Excel inserts the SUM function and tries to guess which adjacent cells should be included, as pictured below.
Hit Enter to complete the formula…and…done.
It is interesting to note a couple of things: it works for multiple cells and it works horizontally. Let’s take them one at a time.
It works for multiple cells means that if you have multiple columns, you can first select all total row cells as pictured below.
Now that all total row cells are selected, hit Alt+= to insert the SUM functions all at once…as shown below.
Next, it works horizontally as well. So, if we wanted to populate the total column, we would use the same Alt+= shortcut…bam.
Or, we could do all total column cells at once by selecting them all first, and then hitting Alt+=, as shown below.
SUBTOTAL Function
As you may know, I prefer the SUBTOTAL function to the SUM function, as discussed in my SUM No More article (Wisconsin Institute of CPAs). As of this blog post, I’m unaware of a built-in keyboard shortcut for inserting the SUBTOTAL function in unfiltered data ranges. So, I built a macro to perform this task. Feel free to download it if you want to check it out.
Invitation
If you have any other favorite keyboard shortcuts, please share them in the comments below.
Sample File
The link below is for the sample file that was used to create the screenshots above.
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.
Hi, when I am trying to use Alt+= function, sometimes it is working, but sometimes I get x in column without any formula. (shortcut is not working). What can be possible reason for shortcut to stop working?
I’m not sure what would explain the shortcut to stop working, but, the AutoSum button on the Formulas tab is an alternative button that could help if the shortcut stops working.
Thanks
Jeff
Hi, I’ve used this shortcut for years, but sometimes when I do this, it gives me a subtotal instead. This is not what I’m asking for. This tends to happen on excel sheets where I have other tables/pivots/formulas, but I am not summing within any grouped or filtered range or column, nor within a pivot table. Very annoying! Any ideas? Thanks!
Hi Susie … this happens when you try to sum a range that is filtered. When there is an active filter on the range, Excel inserts SUBTOTAL instead of SUM. I’m unaware of a way to change this default behavior, but if you clear the filter first, the shortcut should insert SUM 🙂
Great! Works well for me even after all these years! Thanks
Great! The subtotalmacro works well for me even after all these years! Thanks