Create SUBTOTAL Formulas with the AutoSum Button or Keyboard Shortcut - Excel Campus
13

Create SUBTOTAL Formulas with the AutoSum Button or Keyboard Shortcut

Bottom line: Learn how to use the AutoSum feature and keyboard shortcut to create SUBTOTAL formulas.

Skill level: Beginner

Use AutoSum to Create SUBTOTAL Formulas in Excel

AutoSum Typically Creates SUM Formulas

Excel’s AutoSum feature is a great shortcut that automatically creates a formula with the SUM function.  It’s pretty smart at figuring out the range we want to sum up.  We can select a blank cell below a column of data, press the AutoSum button, and the entire formula will be created for us.  Huge time saver!

AutoSum Typically Creates a SUM formula in Excel

 

AutoSum also works by selecting a blank cell to the right of a row of data.

The keyboard shortcut is Alt+=.  Hold down the Alt key and press the equal sign.

How to use AutoSum to Create SUBTOTAL Formulas

We can also use the AutoSum feature to create formulas with the SUBTOTAL function.

Auto Subtotal with AutoSum Feature in Excel

The trick is to first apply a filter to the column that you want to subtotal.

AutoSum Create SUBTOTAL Formula with Filters Applied

Here are the steps:

  1. Apply a filter to the range.
  2. Select a cell in the column or the blank cell below the filtered range.
  3. Press the AutoSum button or keyboard shortcut (Alt+=).  The SUBTOTAL formula will be automatically inserted in the cell.
  4. Press Enter.

The range reference in the SUBTOTAL formula will include all the cells in the column of the filtered range, even if they are hidden by the filter.

Auto Subtotal Shortcut Top and Bottom Rows Included

The advantage of using the SUBTOTAL formula is that is only displays the calculation (sum) of the visible rows.  It allows us to quickly see the sum, average, count, min, max, etc. of the visible rows after filters are applied.

Checkout my article on the Excel SUBTOTAL function for more details.  I also have an article on how to create a SUBTOTAL summary report with a macro.

If you are using Excel Tables, then you don’t have to worry about this because the Total Row uses the SUBTOTAL function by default.  When you press the AutoSum button or keyboard shortcut in the Total Row of a Table, the SUBTOTAL function will be created.  Checkout my article on how to sum all columns in the Total Row of a Table.  I also have a video on a beginner’s guide to Excel Tables.

This is a bonus tip from my Excel Pro Tips eBook.  The eBook is a free download, so make sure you grab a copy.

Excel Pro Tips eBook Cover 150x150

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 13 comments
Anahita - August 24, 2017

Jon, with your lessons, I’ve been able to convert my worksheets to automatically update each month, as soon as I load the new information. The PROBLEM with your lessons is that, even though I’ve been given additional assignments, I am BORED OUT OF MY MIND while at work!!

Reply
imran ali - August 22, 2017

Sir I have 100 ledgers in excel I want to sum of all ledgers in one click can this is possible?
Plz sir I am waiting of your answer thanx

Reply
    Anahita - August 24, 2017

    Imran Ali,
    I have no affiliation with Jon or Excel Campus, but I’d assume anyone who wants to help you would need additional information. Are all the “ledgers” in one file? When you say you want to sum them in one click, what do you mean exactly? So you need a consolidated (the resulting sum of the 100 ledgers) as a new sheet in your worksheet or in a new file?. If that is what you want, and if you still need help, I can help you so please reply to this posting with more information.

    Reply
Ahmed - August 19, 2017

Excellent jon & very helpful for me

Many thanks

Reply
Lelani - August 19, 2017

Thank you for the great tips and shortcuts. They’re a real time save!

Reply
Grace - August 18, 2017

Thanks for sharing these tips. They really make work much easier in excel. Am enjoying my excel.

Reply
Kamal Kumar - August 18, 2017

I am also know This shortcut key. But you explained so good. I’m so happy because i am touch you.
Thanks

Reply
Faizan - August 18, 2017

Excellent work jon. Thank you so much

Reply
Sajjad - August 17, 2017

Dear Jon,

I need to TLB.731 / TLB-731 as TLB 731. Why it works?

Reply
Francois - August 17, 2017

This is actually awesome on my database I created on excel. With my losses register I can now efficiently subtotal on my types on losses and amounts.

Thanks

Reply
Linda - August 17, 2017

This is a great shortcut. I can’t wait until I need to break out some subtotals for one of huge lists.

Reply
Rajeev - August 17, 2017

Hi Jon,May I know that How to match 2 or 3 words from 2 or 3 excel sheet in one place from formula?

Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x