Look Who it Was Random video

Excel - Excel Macro Tutorial: Overcoming AUTOSUM Button Limitations - Episode 823

373 views · Published 12 January 2009 · 3:31 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Excel Macro Tutorial: Overcoming AUTOSUM Button Limitations.

Welcome back to the MrExcel netcast! In this video, I want to send a special thanks to Scott Fox of scottfoxradio.com for having me on his show last week. We talked about the early days of MrExcel and why I give everything away for free. If you're interested in hearing our conversation, head over to scottfoxradio.com to listen.

Now, onto today's topic. We have a question from Mark in New Hampshire about recording macros using the AUTOSUM button. This is a common issue that has been driving me crazy for years. When we try to record a macro using the AUTOSUM button, it only records the action of adding up the 11 cells immediately above. This can be frustrating because we expect the macro to record the actual action of pressing the AUTOSUM button.

So, is there a way to get around this? Well, it's not the most intuitive solution, but there is a way. We need to think about the formula that the AUTOSUM button creates. We want the macro to always go back to the cell in row 1, no matter what. To do this, we need to type in the formula ourselves and lock in the top row with a $ sign. This will teach Excel to do what we would have expected the AUTOSUM button to do in the first place.

Let's try it out. We'll record a new macro, set a shortcut key, and type in the formula =SUM(G$1:G11). Make sure to only have one $ sign in the formula. Now, when we run the macro, it will give us the correct answer, no matter how many cells we have selected. This may be a hassle during the recording process, but it's worth it to have the macro work correctly.

So, there you have it. A little trick to get around the limitations of the macro recorder when it comes to the AUTOSUM button. I want to thank Mark for sending in this question and thank you for watching. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Mark from New Hampshire notes that the macro recorder can not record the simple act of pressing the AutoSum button. In Episode 823, I show you the arcane workaround to solve the problem.

Table of Contents:
(00:00) Introduction and thanks to Scott Fox
(00:21) Question from Mark about recording macros with AUTOSUM
(00:37) Solution to recording AUTOSUM using relative reference and formula
(02:04) Example and demonstration of solution
(02:27) Explanation of workaround and its effectiveness
(03:07) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding up cells in Excel using formulas
AUTOSUM button in Excel
Excel macro error with AUTOSUM button
Excel macro recording issues
Excel macro shortcut keys
Fixing Excel AUTOSUM issue with formula
How to use relative reference in Excel macros
Locking rows with $ sign in Excel formulas
MrExcel netcast episode on Excel macros and AUTOSUM
Overcoming limitations of Excel macro recorder
Tips for effective Excel macro recording
YouTube video on recording macros in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152057/

More from this channel