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.
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.
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.
If you have any other favorite keyboard shortcuts, please share them in the comments below.
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.