Excel AutoSum Anomaly: Problem with 2 AutoSum - Episode 2199
Microsoft Excel Tutorial: AutoSum in second column stops working. Welcome back to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing a common problem that many Excel users face - the issue with two AutoSum columns. This problem was brought to my attention during a recent meeting of the IMA in Atlanta, Georgia, and it had me stumped. But after some investigation, I was able to find a solution that I am excited to share with you today. So, what exactly is the problem with two AutoSum columns? Well, imagine you have two columns of numbers and you use the AutoSum function to calculate the totals at the bottom. Everything seems to be working fine until you start inserting rows in between the two columns. Suddenly, the AutoSum in the second column stops working. This can be frustrating and confusing, but don't worry, I have a solution for you. After some trial and error, I discovered that the issue arises when a row is inserted without a number in the second column. This causes Excel to extend the formula, resulting in the AutoSum not working properly. To avoid this problem, I recommend always leaving a blank row between the AutoSum and the numbers. This way, when you insert new rows, the formula will not be affected. You can even make the blank row smaller and change its color to make it look like an underline. To ensure that the AutoSum continues to work correctly, it is important to always insert new rows above the blank row. This may seem like a small detail, but it can make a big difference in preventing the AutoSum from breaking. I also suggest training others who use the spreadsheet to follow this practice. This tip, along with many others, can be found in my new book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time. To download the workbook used in this video, check out the link in the description below. And if you want to take your Excel skills to the next level, be sure to check out my upcoming seminars. The link for that is also in the description. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more helpful Excel 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/ #excel #microsoftexcel #exceltips #excelhacks #excelbug #excelformula #evergreen This video answers these common search terms: how to sum dynamic range in excel how to automatically update sum in excel how to sum two columns excel how to sum two columns in excel how to sum two columns in excel formula how to do = sum of two columns excel how to find the sum of two columns in excel Table of Contents: (00:00) Problem with two AutoSum columns (00:20) Replicating the issue (00:35) Solution: Inserting a blank row (01:11) Explanation of Excel's formula extension (02:01) Pro tip for formatting the blank row (02:54) Recap of the solution (03:12) Clicking Like really helps the algorithm Problem from my Atlanta Power Excel seminar for the Gulf South Council of the IMA Two columns of numbers. The AutoSum at the bottom of the second column stops working sometimes. The problem happens if you are inserting rows and sometimes leave out a number One best practice is to use a blank row between the AutoSum and the numbers. Always insert the new rows above that blank row. Pro Tip: Make the blank row small and change the fill color To download this workbook: https://www.mrexcel.com/download-center/2018/05/problem-with-two-autosum.xlsx List of upcoming seminars: https://www.mrexcel.com/press-appearances/ Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154028/
Microsoft Excel Tutorial: AutoSum in second column stops working. Welcome back to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we will be discussing a common problem that many Excel users face - the issue with two AutoSum columns. This problem was brought to my attention during a recent meeting of the IMA in Atlanta, Georgia, and it had me stumped. But after some investigation, I was able to find a solution that I am excited to share with you today. So, what exactly is the problem with two AutoSum columns? Well, imagine you have two columns of numbers and you use the AutoSum function to calculate the totals at the bottom. Everything seems to be working fine until you start inserting rows in between the two columns. Suddenly, the AutoSum in the second column stops working. This can be frustrating and confusing, but don't worry, I have a solution for you. After some trial and error, I discovered that the issue arises when a row is inserted without a number in the second column. This causes Excel to extend the formula, resulting in the AutoSum not working properly. To avoid this problem, I recommend always leaving a blank row between the AutoSum and the numbers. This way, when you insert new rows, the formula will not be affected. You can even make the blank row smaller and change its color to make it look like an underline. To ensure that the AutoSum continues to work correctly, it is important to always insert new rows above the blank row. This may seem like a small detail, but it can make a big difference in preventing the AutoSum from breaking. I also suggest training others who use the spreadsheet to follow this practice. This tip, along with many others, can be found in my new book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time. To download the workbook used in this video, check out the link in the description below. And if you want to take your Excel skills to the next level, be sure to check out my upcoming seminars. The link for that is also in the description. Thank you for tuning in to this episode of the MrExcel netcast. Don't forget to subscribe to our channel for more helpful Excel 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/ #excel #microsoftexcel #exceltips #excelhacks #excelbug #excelformula #evergreen This video answers these common search terms: how to sum dynamic range in excel how to automatically update sum in excel how to sum two columns excel how to sum two columns in excel how to sum two columns in excel formula how to do = sum of two columns excel how to find the sum of two columns in excel Table of Contents: (00:00) Problem with two AutoSum columns (00:20) Replicating the issue (00:35) Solution: Inserting a blank row (01:11) Explanation of Excel's formula extension (02:01) Pro tip for formatting the blank row (02:54) Recap of the solution (03:12) Clicking Like really helps the algorithm Problem from my Atlanta Power Excel seminar for the Gulf South Council of the IMA Two columns of numbers. The AutoSum at the bottom of the second column stops working sometimes. The problem happens if you are inserting rows and sometimes leave out a number One best practice is to use a blank row between the AutoSum and the numbers. Always insert the new rows above that blank row. Pro Tip: Make the blank row small and change the fill color To download this workbook: https://www.mrexcel.com/download-center/2018/05/problem-with-two-autosum.xlsx List of upcoming seminars: https://www.mrexcel.com/press-appearances/ Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154028/