I was using Excel 2010 in Windows 7 x64. I had been revising a worksheet containing a graph. For some time, I had been getting error messages: “A formula in this worksheet contains one or more invalid references.” There probably could have been multiple sources of this error, but in this case I verified that it came from the graph: it appeared when I saved the file, if the chart was still there, but it vanished when I deleted the chart (toggling back and forth between the two states via Ctrl-Z and Ctrl-Y). The problem seemed to be that the chart was looking for data series that no longer existed, now that I had deleted some columns in the worksheet.
From that state of affairs, we graduated to a more problematic state of affairs. I was no longer getting the “invalid references” message. But now I was also no longer able to delete the chart. Instead, my mouse cursor turned to a “do not enter” sign when I moused over what was left of the graph:
(Note the slight appearance of vertical Moire effect lines in the image here. I had to use a camera rather than screen capture to get this picture, because PrintScreen would not capture the mouse cursor, shown here as the do-not-enter sign.)
As shown in the foregoing image, Excel would continue to recognize that the cursor was located over something that had previously been the plot area, or the axis, or some other part of the graph. The problem here was just that, as the shape of the cursor suggests, there was nothing I could do with the mouse on the graph, with either a right or a left click. This appeared to be the case throughout the graph: there did not seem to be any place on the graph where I could get action with the mouse. The mouse did continue to work normally in other parts of the worksheet.
Unfortunately, I also found that Undo (i.e., Ctrl-Z) would not take me back far enough to find the point where the train had jumped the tracks. Maybe that was because I had recently saved the file; sometimes, in some programs, it seemed that a Save would wipe out the Undo memory prior to the point of saving. Microsoft Word 2010 was not like that; I did not recall at this moment whether perhaps Excel was. I could have closed the file and opened a previous version or backup, but I would have lost a bunch of work, and anyway I wanted to try to work through the problem if possible.
I thought that maybe there would be a command that would close or kill all visible charts. But for some reason, Excel’s Insert menu (where the Charts submenu appeared) was grayed out. I could select options from other menus (e.g., File > Save, or Home > Copy), but I couldn’t select anything in the Insert menu. I tried closing and reopening the file, and Excel itself, but no joy.
I made a copy of the file and opened it in Libre Office 4.1. It did allow me to delete the graph (although for some reason it took two tries with the right-click Cut option). Unfortunately, it then crashed and, in the process, apparently trashed the copy I had made: upon reopening in Excel, Excel said this:
Excel found unreadable content in Running5.xlsx. Do you want to continue to recover the contents of this workbook? If you trust the source of the workbook, click Yes.
(Running.xlsx was the name of the Excel workbook I was working on; this particular copy was named Running5.xlsx.) I saw that LibreOffice had been updated to version 4.2, so I downloaded the update and tried again, with another copy of the original worksheet. This time, I saved and closed the file before closing LibreOffice. I still got the LibreOffice Document Recovery dialog, indicating that LibreOffice had crashed, due to an unexpected error. But I didn’t care about that; I had already saved and closed the file. So now I opened the changed version in Excel. Once again, I got that “unreadable content” error. It seemed that LibreOffice was not doing a perfect job of saving in Excel format, or perhaps Excel had somehow been tooled to detect when a competing spreadsheet had worked on one of its files. This time I went ahead with it: I opened the file that LibreOffice had revised. Excel gave me another pop-up:
Repairs to ‘Running5.xlsx’
Excel was able to open the file by repairing or removing the unreadable content.
Replaced Part: /xl/worksheets/sheet2.xml part with XML error. Illegal qualified name character. Line 2, column 45292813.
To me, it looked like the problem might be with Excel: I did not have 45 million columns in my worksheet. Sadly, Excel’s “repair” involved completely wiping out all data on the worksheet in question. I tried again, this time using OpenOffice Calc Portable 3.2. After I deleted the unwanted charts, that program was able to save only in Excel XP (i.e., not Excel 2010) format, as an .xls (i.e., not .xlsx) file. But for this particular file, that may have been OK: I was not sure I needed .xlsx format. Maybe saving as an .xls would have been the solution in LibreOffice. I’m not sure. But it did seem to be the solution in OpenOffice. When I opened the revised workbook (sans graph) in Excel 2010, I got none of the errors described above. Pending further work with this large spreadsheet and its many formulas, it appeared that the problem was solved.