Look Who it Was Random video

Excel - Mastering Excel Macros: Fixing Issues with AUTOSUM Button in Macro Recording - Episode 812

634 views · Published 13 January 2009 · 3:40 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Mastering Excel Macros: Fixing Issues with AUTOSUM Button in Macro Recording.

Welcome back to the MrExcel netcast! In this video, we're going to tackle a common issue that many Excel users face when recording macros - the AUTOSUM button. Yesterday, we recorded a macro and used relative references to get the totals in the right spot. However, upon closer inspection, we noticed a green triangle in the lower left-hand corner, indicating a huge problem - the formula was omitting adjacent cells. This is because the AUTOSUM button does not get recorded correctly by Microsoft, even with relative references turned on. Frustrating, right?

But fear not, there is a solution to this problem. In this video, I'll show you how to write a formula that will always include the rows you need, regardless of the number of rows in your data set. By using the $ sign to lock in the starting row, we can ensure that the macro recorder will record the correct code. And don't worry, you won't have to type this formula in real life - it's just a trick to get the macro recorder to work properly.

To demonstrate this, we'll record a new macro and use the AUTOSUM button again. But this time, we'll type in the formula ourselves instead of relying on the button. By using the fill handle, we can easily copy the formula to the right and ensure that it includes all the necessary rows. And to make sure we always start at the top of our data, we'll use the CONTROL+G shortcut to go to cell A1 before running the macro.

With this new method, we can confidently use the macro recorder to automate our tasks without worrying about the AUTOSUM button causing issues. So next time you're recording a macro, remember to use this trick and you'll be on your way to becoming a macro master. Thanks for watching and don't forget to subscribe to the MrExcel netcast for more helpful tips and tricks. See you next time!

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/


Today, a closer look at the macro from Episode 811. While it LOOKED like it worked, the recorded macro leaves a lot to be desired. Your totals will be wrong if you rely on the AutoSum button during macro recording. Episode 812 shows you the workaround.

Table of Contents:
(00:00) Issue with recorded macro 
(00:15) Green triangle indicating problem 
(00:25) Solution using formula 
(01:00) Recording a new macro 
(01:30) Using relative reference button 
(01:41) Entering formula instead of using AUTOSUM button 
(02:06) Formatting and testing the macro 
(02:39) 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:
Excel formula for summing adjacent cells
Excel relative references and macro recording
Fixing issues with AUTOSUM in Excel macro recording
Formatting cells and autofit in Excel macro recording
Mastering the relative reference button in Excel macros
MrExcel netcast tutorial for macro recording
Testing Excel macros
Tips for working around issues with the AUTOSUM button in Excel macros
Troubleshooting green triangle in Excel
Using the fill handle in Excel macro recording
Using the STOP RECORDING button in Excel
YouTube macro recording tutorial

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

More from this channel