Comments on: Excel formulas not working, not updating, not calculating: fixes & solutions

This tutorial explains the most common mistakes when making formulas in Excel, and how to fix a formula that is not calculating or not updating automatically. Continue reading →

Comments page 3. Total comments: 459

  1. Hi,

    When completing B the static date and time will populate in F. I use this formula: =IF(B1"",IF(F1"",F1,NOW()),""). The cell format in F is Custom -> dd/mm/yyyy hh:mm.

    It worked from last year until two weeks ago. Since two weeks ago, F is blank when I select anything in B.

    I managed to find this formula for a different sheet: =IF(AND(SIC!J2>=0,"",SIC!J20),NOW(),""). But it is not static and I need it to be like with the first formula when it worked.

    How can I fix it?

  2. This helped alot! Thank you!

  3. So I just corrected my issue by reading one of your other articles.

    Thank you so much for making this available to us!!

  4. I have an issue with my formula not updating the refence cells when dragged down the column.
    The formula works fine with all calculations, however, when I drag it down it does not update the reference cells so I get the same result for the entire column.
    In order to get the correct result I have to manually update the formula in each cell.

    =IF([Book1.xlsx]Sheet1!$J$3=100%,"Ready",IF([Book1.xlsx]Sheet1!$J$3=0%,"Not Started","In Progress"))

    I tried using this same formula in a brand new workbook to discard formatting issues and I got the same result.

    Any advice?

      1. I have the same issue but my cells do not have absolute values. When I drag whe formula down it does not update to the next line. This is the formula im using because i want to count differente cells that are not in a sequence and meet a criteria:

        =SUM(COUNTIF(INDIRECT({"C22","H22","M22","R22","W22","AB22","AG22","AL22"}),"x"))

        I have 146 lines, but when dragging down I need it to update to the following lines 23, 24 and so on.

  5. Bless you!
    Everytime I get frustrated with Excel, Ablebits has an array of solutions.
    thank you for making this resource available!!

  6. It's ok! I've worked it out. The data in the other excel document was not formatted to Number so I updated the format and all now works. Thank you anyway! :-)

  7. Hi, I have a simple SUM formula not working (gives 0.00 results) due to the other formulas in the cells that the Sum formula is adding up from. I know this because I experimented by removing the other formulas and just manually input some numbers and the SUM formula worked!
    But I want to keep the other formulas as they are getting data from another excel document eg
    Col D =VLOOKUP('[Reserves Leave Allowances_24.01.23.xlsx]Allowances'!A2,'[Reserves Leave Allowances_24.01.23.xlsx]Allowances'!$A:$I,9,FALSE) and similar formula in Col E (these formulae work fine).
    However, the SUM formula which is in Col I =SUM(D2:H2) is just resulting in 0.00
    All data is in number format.
    There isn't any data (at the moment) in Cols F-H.

    Surely other formulas should not be affecting further formulas especially a simple one like SUM?

    Any ideas please? Many thanks.

  8. I'm trying to get ANY formula to work currently.
    In cell E4 I enter a simple test: =CONCAT(CQ4;CQ5)
    In cells CQ4 and 5, I have "abc" and "xyz", of type General.
    On clicking enter for this formula, it just displays the formula.
    Ensuring Automatic calculation on cell E4 does not work.
    Forcing the calculation does not work.
    Manual Calculation does not work.

    Formulas do however work for cells in other columns.

    There is no Protection or locking on the E column.

    Other simple formulas like =sum(1;2), also do not work in the E column.

    Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20446) 64-bit

    1. Nevermind... I just needed to format the E column to General or similar to make the formulas work.

  9. Thank you so much

  10. Didn't work Automatic calculations in my worksheet.i just tried to all the methods of above mentioned but it not corrected.

    1. Same situation with me here, but only 1 cell has a problem. Anybody to answer, please?

  11. I really appreciate you for this knowledge. You have really save my day

  12. tried plotting scatter plot. some times accepts all the values of the series and sometimes only 1

  13. I have formulas that work on one tab (year 2019) , and on the next years tab (2020) the formula works on 2 lines but the rest of the cells are coming back zero. All references seem to be correct since 2019 worked perfectly. They are all formatted as a number, and I can see the backup that the formula should be picking up. Any ideas on how to fix this? The formula is a SMIFS.

    1. That kind of error can happen when you copy from one sheet to another. To correct it, ensure you paste it in similar row and column as the sheet you copied from

    2. Hi!
      I don't know your formula and can't see your data. So I can't give advice. Compare your data on two sheets.

  14. is get.workbook function available in excel 2016?

  15. You are my savior!!!! Thanks a lot for the information! I have been suffering so many years because it was not calculating simple data, i had to click thousands of times the F2 button to put the numbers in the right format!

    1. Sir, what did you do ?

  16. I have a list of employee data and I have cells that are dependent on others others, I used formulas to calculate values like date of retirement, years spent in service and so on. My problem is I want to use conditional formatting on rows that empkjee is either 59 years or above or has spent 34 or more years in service. When I used =$H2>=59 I got the desired result,but $O2>=34,the whole sheet gets formatted. I tried combining the 2 conditions using =OR( $O2>=34,=$H2>=59) it still didn't work

  17. A list of numbers all derived from formulae (e.g. IMPRODUCT(B3,C3) will not sum. They are not text. The only numbers included in the calculation are those not derived from a formula. What is going on?

  18. Following up on your reply to my question, cells F2 and E2 do not have any formula or circular reference, they are cells with value entered.
    e.g E2 has value 122,549,069 and F2 has value 122,548,865 and formula in 'G2' column is =F2-E2. This formula is not getting refreshed automatically, we need to open the excel and click inside cell 'G2' to see the formula being applied

  19. Hi,
    I have a excel sheet which has a formula in one column say 'G' column, the formula(F2-E2) in the 'G' column is to substract values from two other columns. But the formula is not getting automatically applied on opening the excel, we need to click inside the 'G" column after that the formula gets applied.
    I tried changing format of the column 'G' to 'Number' and also tried Calculation option to 'Automatic', still its not working.
    Please suggest any other solution which can work

  20. A clarification on my previous comment. I create the formulas through a macro, using the RefersToR1C1:= option.

    I build the formula by putting code like this in a loop.
    YTDPointsVar = YTDPointsVar & "RC" & Range("Points" & TheWeek).Column & ","

    Then I build the formula with this code:
    ActiveSheet.Cells(TeamRow(TheTeam), Range("YTD_Points").Column).Formula = "=sum(" & YTDPointsVar & ")"

    I don't have the R1C1 reference style selected in File, Options, Formulas. In the past the above code would automatically convert to this style;
    =SUM($I2,$Q2,$Y2,$AG2,$AO2,$AW2,$BE2,$BM2,$BU2,$CC2,$CK2,$CS2,$DA2,$DI2,$DQ2,$DY2,$EG2,$EO2,$EW2,$FE2,$FM2,$FU2,$GC2,$GK2,$GS2,$HA2,$HI2,$HQ2,) and it would calculate properly. Now, using the same code, not in compatibility mode, it displays in the RC format and no longer calculates the formula.

  21. I have used formulas of the following style in a workbook since 1994: =max(rc13,rc21,rc29)

    These have always worked. I have been saving in compatibility mode until a week ago. I saved the workbook as a .xlsm file instead of a .xls file and no longer use compatibility mode. Now the formulas don't calculate. I have automatic calculate on and have selected F9, etc. When I select the "R1C1 reference style" option under "File, Options, Formulas" the formulas automatically change to something like this: R[-112]C[463],R[-104]C[463],R[-96]C463]).

    In my previous versions of the workbook I did not have "R1C1 reference style" selected and the formulas still updated properly. I'm running Excel 2013 on Windows 10.

    Thanks in advance for your help.

  22. My IF formula has worked no problem for 38 rows of my spreadsheet (adding a sequential number) but now no longer works and I believe I have tried every troubleshoot option above. When I compare the formulae the only difference is the row number which has been the same for the formula in every other row. Please can you help?

    =IF(C38="","",LEFT(C38,1)&"-"&RIGHT(YEAR(D38),2)&"-"&MONTH(D38)&"-"&WEEKNUM(D38)&"-"&RIGHT(A38,4)) - This formula works for row 38 & all previous rows and returns C-22-8-35-1036

    =IF(C39="","",LEFT(C39,1)&"-"&RIGHT(YEAR(D39),2)&"-"&MONTH(D39)&"-"&WEEKNUM(D39)&"-"&RIGHT(A39,4)) - This formula works for row 39 & returns
    C-22-4-17-1037

    =IF(C40="","",LEFT(C40,1)&"-"&RIGHT(YEAR(D40),2)&"-"&MONTH(D40)&"-"&WEEKNUM(D40)&"-"&RIGHT(A40,4)) - This formula does not work for row 40 & returns C-22-4-17- where it should return C-22-4-17-1038.

    Unique identifier
    The first letter is the source from column C.
    2nd two digits are the year (column D)
    Next two are the month (column D)
    Next two are the week (column D)
    Last is (should be) the next sequential number

    Thanks

    1. Hi!
      I can't check your formula because I don't know what is written in cell A40. The RIGHT function extracts the last 4 characters from this cell. It's your sequential number.

  23. When I paste new data in a sheet excel is stopping calculation if where a row is blank

  24. Hi!

    I have a lot of data in my excel , around 160 000 rows and I have a few formulas in separate column that are supposed to read from the data. I am manually calculating it ,and for some reason it only calculates part of it and then I have to refresh and calculate again ,it happens on smaller samples as well and I never really know how many times should I recalculate to make sure it's fully calculated. Could you please let me know if you have any idea what can be causing it?

    Thanks in advance!

    Best Regards,
    Nino

    1. Hello!
      If you use the F9 key, then the formulas changed since the last calculation and the formulas that depend on them are recalculated.
      Try CTRL+SHIFT+ALT+F9 key. All formulas in all open workbooks will be recalculated, regardless of whether they have been modified since the last recalculation.
      You can read more details here: Excel calculations: automatic, manual, iterative.
      I hope my advice will help you solve your task.

  25. Dear Sirs:

    I have this formula
    =INDICE(Chiesa[Switch]|COINCIDIR(Instalaciones!$I10|Chiesa[IP]|0))

    if I edit it, for example, adding an extra space at the end, and then removing it, it will never be calculated again. although, formating to text, general, recalculate, etc...

    there is no choice for me to edit it and include an if.na , or other function.

    Please, do you have any ideas?

    Thank you

    Alfredo

    1. Hello!
      Please write the formula for the English version of Excel and explain the problem in more detail.

  26. I have at least two cells the formulae are not working in a .xlsm file. The formulae are in the cells but the displays are blank, and are on different worksheets. In both cases the adjacent formulae, the only difference being the column/row numbers, are working as intended.

    The simplest of the two formulae is =IF(MAX(Y14:Y17)>NOW()-$B46,MAX(Y14:Y17),0)

    Automatic calculation is set, format for the formula below is set to date (and number on the one below).

    I had been trying to find why the following formula wasn’t working for a couple of days, when today I realised that the date formula above also wasn’t working now.

    =INDEX(Nation2!G$4:G$9999,MATCH(INDIRECT("A"&ROW()),Nation2!B$4:B$9999,0))

    I knew that the first formula was working yesterday. For the last three months I have been saving this file as a back-up yet, when I opened two of the back-ups, the corresponding formulae in those two cells were no longer working correctly.

    On the second formula, the ‘A’ cell value date is ‘31/12/22’ [UK date], and there is one ‘31/12/22’ date in the ‘B’ range, and the corresponding ‘G’ value was not being displayed. However, if I changed the ‘A’ and ‘B’ dates to virtually any other date, the correct ‘G’ value would be displayed. This is true if the dates were any month end bar Dec 22 (including Dec 21 and Dec 23), and having seen it work with ‘15/12/22’ I changed just the ‘15’ element but to no avail.

    1. Hi!
      Unfortunately, you didn't explain and I can't guess what doesn't work in your formula.
      If in column B the desired date is in position 4, then the fourth value from column G is returned.

  27. I have a workbook with many different sheets. On one sheet (Master Project List), there are 17 columns and 365 rows of information. I have multiple other sheets that go through this list of projects and separate them into the correct ones (i.e., work plans, under budget projects, over-budget projects, etc.).

    The problem I am having is that if I delete a row from the Master Project List sheet, the data on those other sheets disappears (because it can't reference the cells in the rows that I deleted), and I have to manually copy and paste them back in for it to show data again. (refreshing the calculations doesn't work).

    FORMULAS USED ON WORK PLANS SHEET (for example):
    Column A: =IF('Master Project List'!C2="Work Plan",TRUE,FALSE)
    Column B: =IFERROR(INDEX('Master Project List'!A$2:A$674,SMALL(IF($A$2:$A$1160,ROW($A$2:$A$1160)-ROW($A$2)+1),ROWS($1:1))),"")

    Is there any way to fix it so that when I delete a row on the Master Project List, the cells with TRUE/FALSE can change their reference cell to the row below it (or just delete themselves?

    1. I've been using this workaround:
      To "delete" Row 25 from 'Master Project List'
      Highlight Row 26 through the last row that you're using, PLUS the next row or two. This will ensure that the last (previously populated) row doesn't become a duplicate of the 'new' last row.
      (If the last row you're using is 325, you'll highlight rows 26 thru 327)
      COPY
      Select cell A25
      PASTE
      That will keep all dependent formulas intact.

  28. Date....................Day.............Time In............Time Out...........Time In............Time Out...........Total Hours
    5/24/2022.........Tuesday.......08:05AM........12:00PM...........07:00PM ..........12:01 AM ...........#VALUE!

    I used this =(E9-D9)+(H9-G9) Formula. But my computer didn't work. I Go to Region-Additional Date & Time- number-list operator. But didn't work formula.

  29. Date Day Time In Time Out Time In Time Out Total Hours
    5/24/2022 Tuesday 08:05AM 12:00PM 07:00PM 12:01 AM #VALUE!

    I used this =(E9-D9)+(H9-G9) Formula. But my computer didn't work. I Go to Region-Additional Date & Time- number-list operator. But didn't work formula.

  30. I have a budget spreadsheet with a separate sheet for each region and a totals sheet.
    I want to calculate a simple percentage of the total budget used year to date - on the sheet itself it should simply be F2/G2 = xx% - but it always shows 0% as the answer.

    F2 is a sum of B2+C2+D2+E2 (and each of those values pulls from another sheet per row)
    G2 is simply pulling from another cell reference

    so effectively it is a formula calculating cells that are also results of formulas. (of course if I copy the data into a new excel file and the values become absolute, it is a very basic formula that works.

    can this work?

  31. I have Office365 Excel and my formulas don't work at all. Is there a security issue I need to fix? Getting a string value from Column A in Column Z, I used =A365 shows as = [@[COL_A]] not the value from Column A. As well, using

    =XLOOKUP($C:$C,'ExcelSheet2.xlsm]Exceptions'!$I:$I,'[ExcelSheet2.xlsm]Exceptions'!$N:$N,"No Match",0,1)

    does not convert to the expected display value but simply shows the formula itself.

    1. Hello!
      The @ symbol means an absolute structured reference to the current row.
      I can't check the formula that contains unique references to your workbook worksheets.
      However, it is written not quite correctly. Try like this -

      =XLOOKUP($C:$C,[ExcelSheet2.xlsm]Exceptions!$I:$I,[ExcelSheet2.xlsm]Exceptions!$N:$N,"No Match",0,1)

  32. I have a cell with =ROUND((2.83 * 1.02264 * 39.2/3.6)*A8,2)&"kwh"

    If I reference this cell to perform a calculation, I get #VALUE

    But if I use =ROUND((2.83 * 1.02264 * 39.2/3.6)*A8,2)&"123"

    It works fine, I thought that what is between the “” was just a sort of tag, and shouldn’t affect calculations

    If it can’t be done then that’s not a serious problem, just would like to understand why it’s happening.

  33. =IF(K2"", IF(E2="", NOW(), E2), "") formula is not working suddenly. Please advice

  34. Make sure the "show formula function" is off is the only method that makes my calculating work. Other method does not work for me. Thank you.

  35. Thanks. that was very helpful :)

  36. Thank you so much! :)

  37. I have a few Excel spreadsheet that I've been using for a few years now, suddenly the formulas don't update in any of the sheets. it returns #Value for all my formulas in all spreadsheets.
    What could possibly have changed - do you think Excel will have to be reinstalled?

  38. I'm facing a problem as a press = and press S no suggestions for function are coming to me and I have to type entire function and tab is also not working. What to do plzz help?

  39. In our office, I and my colleagues are using different versions of excel to create/rewrite/view the documents in the same drivers.

    I've created a new spreadsheet for summing up the total amount of each category for each month, but the parts of AutoSum formula have been changed after I've saved the file/ my colleagues viewed it? I am not sure. The original formula should be =sum(A1, A7), shown at A8 but the final formula now has been changed to =sum(A1, A23), shown at A8. The result on A8 is incorrect but shows figures, not zero.

    Is it a bug? As I don't think we can get figures through the formula which selected range of cells includes A8. It never happened before..... how can I fix it? Sorry for my bad English.

    1. Hello!
      You're right. A sum formula cannot contain the cell in which this formula is written. I can't see your data, but the formula needs to be manually changed to the previous one.

      1. Thanks for your reply. What data do you need to see? How can I show you?

        There are six categories in a spreadsheet but each category have their own formula for summing up the total quantity and total values. Now is just one category with the wrong result but I'm sure all in number format.

        Qty Total Amt
        (1st category)
        1.00 132,000.00
        1.00 460,000.00
        2 592,000.00

        (2nd category with wrong result)
        1.00 2,300,000.00
        1.00 390,000.00
        3 2761800

        (3nd category)
        2.00 1,600,000.00
        2 1,600,000.00
        .....

        1. Hello!
          Check the links in the sum formula. Perhaps there are references to hidden rows in the formula.
          Use SUM(J9:J10)

        2. Qty Total Amt
          (1st category)
          (I5)1.00 (J5)132,000.00
          (I6)1.00 (J6)460,000.00
          (I7 with sum=I5:I6)2 (J7 with sum J5:J6)592,000.00

          (2nd category with wrong formula & result)
          (I9)1.00 (J9)2,300,000.00
          (I10)1.00 (J10)390,000.00
          (I11 with sum=I9:I23)3 (J11 with sum=J9:J23)2761800

          (3nd category)
          (I15)2.00 (J15)1,600,000.00
          (I16 with sum=I15)2 (J16 with sum=J15)1,600,000.00

  40. Thank you! Worked for me on Excel 365. Looks like MS Office update made the calculation formula to be manual instead of automatic.

  41. I have a active-X control button which runs a routine to change values of cells in my spreadsheet. I had a problem with this not working correctly (my programming error) and my code broke in the middle. I had set auto-calculate off and back on again using the statements below at varying points in my code to make it run faster. Now the auto-calculate does not work. I tried turning it back on with the Toolbar at the top and also using the Formula tab in File-Options. Neither one worked. There are big holes in my spreadsheet which do not update now and I can't seem to fix it. Even forcing calculations with the Toolbar option or by just using F9 or even Shift-F9 will not work. I saved my file and re-opened it but nothing changed. Is this a known issue with Excel?

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True

  42. Thank you so much! :)

  43. I have been using the =getformula for a while all out of the blue it stop working i get the message #Name?

    Before I used to just type =get it would pop up.

    Regards
    Chris

    1. Hello!
      I'm assuming =getformula is a custom function. The workbook or add-in in which it is written must be open. Only then will it work.

  44. Thanks! Very helpful!

  45. Thank you very much! After two days struggling with formulas, I finally found the solution at your post!

  46. Thank you so much! You just saved my day. Couldn't resist to drop a comment for thanking you.

  47. I came here looking for an answer to the issue of the fact that I just noticed that my Excel spreadsheet is not automatically updating my totals and is maintaining old data in cells after I have changed the data. I have noticed that if I go to the print screen and then back my total is now correct. My formula is really simple =Sum(J18:J19)

    The fix recommendation of making sure my Formulas, Options, setting is set for "Calculate Automatically" has been tried...no different result. Our IT department has even went the extra step and uninstalled my Excel and reinstalled but still the same issue.

    Does anyone have any other ideas on what is wrong and a fix?

    1. I had this issue as well. I'm not sure why this worked, but I protected the sheet, then unprotected it again. Now all of the formulas are automatically calculating again.

  48. i want to calculate birthday year and formula it s doesen't working

    =datedif(B2,Today(),"Y")

  49. I have a problem with the formula working in one cell, but not in another cell which is set to draw it's data from that cell.

    So in one workbook, I have a cell with the formula =Trades!C156+'Property Services'!C128+Housing!C136+Resources!C111+Development!C26+'People, Comms, Community & Exec'!C61 This calculates a total of 165. Sometimes. Sometimes it says 0 - until I force a recalculation like suggested above.

    I have a dashboard in another workbook which is supposed to pull through the number from that cell (using ='[H&S Matrix May 21.xlsx]Dashboard'!$C$3), and it used to no problem, but now consistently pulls through a result of 0, no matter if I ask one or both workbooks to recaclulate.

    What's going on?

    1. Hi!
      Unfortunately, without seeing your data it is difficult to give you any advice. All books must be open for external links to work. Check out the way to work with them in Data> Queries & Connections> Edit Links.
      You can learn more about external links in Excel in this article on our blog.

  50. Thank you working fine !

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)