A formula in worksheet contains one or more invalid references


  • I am opening an Excel 2000 workbook on Excel 2007. This workbook contains several worksheets with charts, shapes, formulas and defined names. When I save the workbook in Excel 2007, it gives me the message "A formula in this worksheet contains one or more invalid references". It really does nor matter what worksheet I'm on while I try to save the workbook. Excel does not pinpoint the error which is not at all useful. How can I find the source of this annoying message?

    Thursday, March 12, 2009 3:34 PM

All replies

  • Home Ribbon > Editing tab > Find And Select dropdown > Selection Pane...

    Click each chart currently displayed on the sheet, and rename it over in the pane in order to differentiate the currently displayed charts from the extras in the list.

    Once you rename each current chart, you know the rest are all old unused objects, and delete them by selecting them in the pane and pressing delete. 

    Do  this for each sheet with chart objects, and save the workbook. The errors should no longer appear.
    • Proposed as answer by KyleGW Monday, May 18, 2009 7:13 PM
    Monday, May 18, 2009 7:12 PM
  • Thanks for the tip.  However, no there were no extra charts to delete.
    Wednesday, February 09, 2011 3:51 PM
  • Hi, I have the same problem in Excel 2007 (file created with Excel 2007), did you find a solution? Thanks in advance. -<M>.
    Monday, April 04, 2011 11:52 AM
  • Hi, I have the same problem in Excel 2007 (file created with Excel 2007), did you find a solution? Thanks in advance. -<M>.

    Finally found the problem, it was in one of my data series within one of the file's charts.

    I isolated the problem by:

    1. Created a backup copy of the workbook and worked on it.

    2. Deleted sheet by sheet and saved the file each time till the error stopped appearing - Found the problematic sheet with the chart.

    3. Delete series by series in the chart till I found the problematic one and fixed the invalid reference within it.

    4. Fixed the problem in the original file.

    hope this helps.





    • Proposed as answer by JamesP77 Tuesday, February 04, 2014 10:55 PM
    Monday, April 04, 2011 1:11 PM
  • Gosh this is an irritating problem.  Its a really shame about this issue - another bug is "negative or zero values can not be plotted on log charts" keeps popping up all the time in a similarly unwarrarented manner.
    • Proposed as answer by Wagzy Thursday, September 29, 2011 1:26 PM
    • Unproposed as answer by Wagzy Thursday, September 29, 2011 1:26 PM
    Tuesday, April 26, 2011 9:43 AM
  • Hi All,

    Good tips. I had exactly the same message but in Excel 2010.

    Main difference here is that the error message only seems to pop up when you are on the tab with the error in. So there's no need to start deleting tabs etc. I may be wrong here but that's certainly what I found. 

    Next up is that the error did indeed relate to the charts that I had on that tab. Again, that removes the need to start looking at all the numbers and formulas that you have on the page. 

    Finally, and this one was bizarre, it turned out that for some reason Excel had overlaid 2 additional charts onto the ones that I had placed there. It was these 2 charts that had the errors in. All I had to do was delete the 2 charts and as if by magic, there was one chart left underneath that had no errors in it. 

    Seemed like a bizarre solution in the end, but that's how it worked for me. Find the tab (easy), look at your charts and have faith in deleting them. You may find as I did that you have to delete them twice until you reveal your original error free version hidden underneath them.


    Thursday, September 29, 2011 1:31 PM
  • Thank you so much, I had an extra chart and your solution got rid of it for me straight away. It was such an annoying problem.

    Wednesday, November 09, 2011 7:34 AM
  • Thanks, that helped me. I was able to find two more charts, I don't know how they were created, but removing them, solved my problem.

    Thanks a lot.

    Thursday, December 13, 2012 8:11 AM
  • I have this same annoying problem with a spreadsheet created in Excel 2003, imported and saved in Excel 2010 as a .xlsm file.  There are NO charts in it, only many data sheets with no formulas.  I have even gone sheet-by-sheet and checked for errors, with none found.  I have searched for errors with the 'goto' utility.  There are no links to external data.  There is a lot of vba code, but it compiles fine.  I am at a loss.

    • Edited by KalamazooKate Wednesday, March 13, 2013 2:20 PM
    • Proposed as answer by BootHill42 Wednesday, June 19, 2013 6:07 PM
    • Unproposed as answer by BootHill42 Wednesday, June 19, 2013 6:07 PM
    Wednesday, March 13, 2013 2:20 PM
  • For anyone looking for an answer that the above does not address, try this:

    Select the Formulas tab, in Defined Names section select Name Manager. Look for any invalid data (#REF) in the Value or Refers To columns.

    Wednesday, June 19, 2013 6:10 PM
  • PROBLEM:Same problem with Excel Starter 2010. Every 20 seconds or so a pop-up warning would appear saying "A formula in this worksheet contains one or more invalid references". It would only pop-up on the worksheet with the error except when I would try saving. It would also pop-up when I was viewing a cell with a formula and would press enter.


    For me, it was a problem with a chart in the specified worksheet.

    For each chart you have in your sheet, right click it, move it to a new sheet, and check if the problem still exists in the original sheet. This will pinpoint which chart the error is in.

    For me, simply moving the chart to a new worksheet fixed the problem. I then moved it right back.

    If you can save without it popping up, problem solved.

    Monday, April 21, 2014 9:11 AM
  • Thank you VERY MUCH for the tip. This solved my problem too!


    Tuesday, May 06, 2014 1:45 PM
  • You guys are just crazy! Neither you nor the original guy did solve the issue
    Wednesday, May 14, 2014 7:29 PM
  • I just came across this problem and a friend asked me to help. Here is what I discovered: 

    Firstly I am using Excel 2010, so my guess is this is not something new as people had this problem for sometime now. Secondly my friend created a spreadsheet with eleven charts and duplicated that spreadsheet several times in the workbook. 

    What did I discover:

    To make sense of the data we the users create Excel gives every object a name which is usually sequential. Thus if you create 6 charts, charts will be named chart 1, chart 2... Because of this duplication, there were 12 sheets in the workbook that contained an object called chart 1 to chart 11. Excel now appears to be confused with several objects having the same name! While the file will save and you are able to function normally you will get this error until you resolve the issue.

    KyleGW steps to show the objects in the sheet will help, but you now need to give each object a different name in order to truly correct this particular issue.

    I hope that helps!

    Tuesday, July 08, 2014 2:49 AM