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 4. Total comments: 459

  1. 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.

  2. 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.

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

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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?

  11. 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)

  12. 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.

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

  14. 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.

  15. Thanks. that was very helpful :)

  16. Thank you so much! :)

  17. 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?

  18. 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?

  19. 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

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

  21. 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

  22. Thank you so much! :)

  23. 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.

  24. Thanks! Very helpful!

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

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

  27. 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.

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

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

  29. 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.

  30. Thank you working fine !

  31. before I have excel 2008 installed now I installed 2010 excel but I face a problem when I copy the formula it is not past on the down cell. just working CTRL-D COMMAND please solve this problem
    Thanks

  32. Thank you for your help, you saved my time that make me free from trouble.

  33. Beautiful effort, I appreciate whole team best of luck.

  34. Hi,

    I have 12 sheet in a workbook, I want to 10 sheets to calculate one time (manual) per day and other 2 sheets to calculate automatic.

    if all 12 sheet calculation automatic, it will be taking time

    1. Hi,
      The calculation order is set in Excel options and applies to all workbooks and worksheets without exception.

  35. Hello I am using a SUMIFS formula to check 3 or 4 conditions from a table. All works fine until the SUMIFS formula references cells in rows 100 and beyond - and then it breaks (returns 0 for all sums).

    I have checked cell formats, formula content etc and everything is the same for row 100 as it is for the rows above. I must be missing something in the way that I'm addressing rows in my formula?

  36. Thanks. Was very helpful for me. Wish you all the best.

  37. I have simple sum formulas on 3 spreadsheets. I created a 4th document (recon) to compare the totals pages to the prior 3 spreadsheets. If I open the recon spreadsheet in one window, and the other 3 spreadsheets in another window, the formulas don't automatically update. They will only update if I have all spreadsheets open in the same window. Is there a workaround or is this just how it needs to be?

    1. Hello!
      Unfortunately I was unable to reproduce your problem. A separate window is always opened for each file. If a file is opened in Excel, then the data from it is always updated in another file.

  38. Hello, i have an excel document and in some cells, like this: =FRONTPAGE!B15. From the beginning all was ok, instead formula, the corresponding text from sheet FRONTPAGE, cell B5 was displayed. But now if I double click on that cell containing formula, the corresponding text is replaced by formula.
    Calculation option is set to Automatic.

  39. Hi, I'm trying to collate attendance to meetings that are being run each day. My colleagues are generating a 'Register' spreadsheet from a meeting attendance download in TEAMS. It just gives three columns of Name, Action, Date/time stamp. This is changed from a CSV to Excel Workbook.
    My 'Attendance' Excel work book has the following formulae:
    =IF(IFERROR((COUNTIFS('filestructure\[2021-01-11 P2 Po Hs.xlsx]meetingAttendanceList (2)'!$A:$A,A105,'filestructure\[2021-01-11 P2 Po Hs.xlsx]meetingAttendanceList (2)'!$B:$B,"Joined")),0)>=1,1,0)
    If I open the 'Register' my 'Attendance' calculations work but, if the 'Regsiter' is closed I just get 0 for attendance when it should be 1
    I have to link into a lot of different 'Registers' for each day and am currently opening all the registers in order to get some figures into my 'Attendance' spreadsheet. What have I done wrong???
    I've got the 'Attendance' and 'Register' spreadsheets in the same folder on our network. It's a lengthy location so I've reduced it to "filestructure" for your sanity. The fact that it calculates it when I open the register tells me it can find it. Very confused - HELP!!!

    1. Hello!
      If I understand your task correctly, your formula uses data from a separate file. She can get this data only when this file is open in Excel. If the file is not open in Excel, then the data from it cannot be used in formulas.

      1. That's right.

  40. Hi! I have problem with the auto calculation issue. I use an MS Form what send data to a OneDrive shared excel file. This is works fine. I use some more analysis and calculation from the received data. And here is the problem , since the data is not calculated even with F9 recalculate function. However if I'm open the cell function and enter on, than the calculation is working. It is not the way to make manual open-enter on all cell, so I really looking after where is the bug.
    Really I don't understand the issue with this shared xls and struggling with solution!
    Many thanks for suggestions!

  41. Thank you for your suggestion to check the List separator (Suggestion#7).
    I have been using vlook every day for nearly 2 years but recently my excell was updated and my Vlookup wasn't working and I was going crazy and finally realized the list separator is not comma but | in the new version
    Thank you.

  42. I am using MS Office and Excel for Mac; do the above fixes differ when trying to get Excel to calculate formulas? I came here looking for an answer to the issue of the fact that I just noticed that my Excel spreadsheet in not automatically updating and maintaining old data in cells after I have changed the data.

    My only option right now is to re-calculate every cell and every calculation. but then when I make a change I'll have to watch changes are made again.

    The fix recommendation of making sure my Formulas, Options, setting is set for "Calculate Automatically" has been tried...no different result.

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

  43. I'm having the same issue as Abby - I can't get a simple AutoSum formula to work - it just shows a zero. I've tried both ways (=B21+B40) and =(SUM(B1, B40). And when I click on prior AutoSum formulas that worked perfectly, they reset to zero.

    1. Having the sane "autosum not adding anything up and only giving zero as result" problem here, Used to work in previous versions. No idea why now not working

  44. Hi, my problem is I just Sumif formula for using one file to another file, if both files are open that formula give a correct ans. but i closed that Conciliated file that formula shows #value!. Please advise.

  45. Formula for a cell is not calculating, it just shows as zero. I'm using accounting format, but I have attempted to switch to general and it still doesn't calculate. It is an extremely basic formula - =B14+F14. I sent the email to a coworker and she is having the same issue.

  46. Would a formula not work due to an enabled editing from a protected document? the person who sent me the excel document via email can edit the worksheet and use the formula function after enable editing. although when i open the same document from the email, I do not get the "enable editing" yellow bar and the document's formulas do not work. Please help?

  47. Hallo,
    Could you help me with this formula, please?
    =IF(AND(F14=12),"Continue",IF(AND(F147),"Continue",IF(AND(F14>70,F22>=12),"Rethink","Cancel")))
    In place where supposed to appear "Rethink" stubbornly apeears "Continue". I tried many different options but even when I delete everything and left only two parts and "Cancel" as false statement , it showed me "Continue"
    There are 5 columns for this formula. Four are working good. All changes in figures cause changes in results - the text. The last column resists.This part : IF(AND(F14>70,F22>=12),"Rethink" is designed exactly for this column to met all condition. I tried everything I know and it is not working. Could you help me with this - why it is happening? I checked settings in my excel and everything is ok

    1. Hello!
      Your formula is incomplete. Try to insert spaces before and after the > and <. Then it will be recorded in full.

      1. Hallo, Thank you for response, Btw I just noticed I made the mistake when I was copying my formula to show you, here is right one =IF(AND(F1412),"Continue",IF(AND(F147),"Continue",IF(AND(F14>70,F22>12),"Rethink","Cancel"))), I added spaces as you advised and got info that typo was found and excel corrected it back with no space :( still not working

        1. Hello!
          Unfortunately, I cannot figure out your formula. I forgot to say that spaces should not be inserted in Excel, but in the comment itself. If possible, describe in words the conditions in the formula to make it easier for me to understand how it works. Thank you.

  48. Thanks so much!! I was on the verge of giving up and found an easy solution here.

  49. Hi,
    I have an own function, eg :

    Function Pipo( nRow as integer, nColumn as Integer, nVal as Integer )
    Cells( nRow, nColumn+1).Value = nVal+1
    Pipo = nVal
    End Function

    -> Update next Cell with value : nVal+1
    -> Returns : nVal

    The cell it is used in in looks like : =Pipo( ROW(), COLUMN(), 9 )

    However the return in "#VALUE" and the next cell is not updated.
    It is the Cells function that is causing this error. Even using Range() instead does not work.

    Any ideas?

    G,
    Marco

    1. Well, found it -> updating a cell only works from within a Subroutine.

      Case closed.

  50. was using excell all was ok now suddenly any new formula eg sum of 2 cells or sum of row, and all gives a $ - result. how can i fix this?

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 :)