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