Why won autosum work in excel

Now Column 1 is going to work. I want to enter , and the number changes. So, what we're running into there, is Excel is extending that formula. If you have three or more cells that the formula is referring to, and you insert a fourth cell, they'll extend it; but they only extend it if you add a number. And, well, we didn't add a number here, that causes all sorts of problems.

So, my suggestion is that, we're going to insert a blank row here, and I'm going to change the color of this blank row-- you don't have to change the color of the blank row, but I like to change the color of the blank row.

We, like, go to a nice light gray and then put your total here, alright? In this total, when we use the AutoSum, it's going to include the blank row. Copy that across. And I-- this is a pro tip here-- I even make for my row height, like, tiny, like, maybe 3, alright? So it just looks like we're just putting, you know, an accounting underline there almost. And then you have to make sure to always insert the new rows above the blank row.

So, "Iceberg", "", "", great. Insert a new row: Jicama, spelled wrong, "", great, nothing. And then insert a new row: Kale, "", "", BAM! And it keeps working. So, if you're going to have those blank cells, just a better overall approach all the time, is to include a blank row in your AutoSum, and then train the people using the spreadsheet to always insert the extra new rows above the blank row, and your AutoSum will keep going on.

Episode recap: we have two columns of numbers; the AutoSum at the bottom of the second column stops working, intermittently; once it breaks, it's broken forever; it's when you're inserting rows and we've figured out that it's when you sometimes leave out a number; so the best practice is to always leave a blank row between the AutoSum and the numbers; and then insert new rows above that blank row; you can make the blank row a little bit smaller, change the color, it'll actually look like an underline.

Small and Medium Business. Humans of IT. Green Tech. MVP Award Program. Video Hub Azure. Microsoft Business. Microsoft Enterprise. Browse All Community Hubs. Turn on suggestions.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for. Show only Search instead for. Did you mean:. Sign In. New Contributor. I've been using Excel with AutoSum for many years. Recently installed Office and pre-existing spreadsheets are working as usual. Labels: Labels: Formulas and Functions. Haytham Amairah. Hi Margot, I frequently faced this issue before! The problem is: The cells are in Text format!

I suggest in this case to select the entire sheet from the triangle in right-hand corner, then select General Format as follows: After that, go to the formula and update it. I hadn't reformatted the cells into which the totals were to be entered, via AutoSum. I'm greatly relieved! Joseph Foley. I tried same. Didn't work! Tiffany McCarroll. Have you updated existing spreadsheet to current Excel version? Hi Deleted , I hope you next time to choose more appropriate terms because the term misleading is often used for deceptive and intended fraud.

Margot Rylah I just encountered this too. Fix: You can either force a manual recalculation, or turn the calculation back to automatic. Note that manual calculation is usually set when the workbook is very large and unwieldy to speed up calculation times.

Method 1: You can force a manual recalculation either by pressing the Calculate button in the bottom left most corner of Excel, or by pressing F9. This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics.

In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. ICAEW's Finance in a Digital World is a suite of online learning modules to support members to develop awareness and build understanding of digital technologies and their impact on finance. Skip to content.

Possible cause 1: Cells are formatted as text. Excel community This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. Join the Excel Community.