I have Excel 2007 running on Windows 7 Pro (64 bit) SP1. I have recently experienced a really strange problem that hopefully someone can help me diagnose.
My spreadsheet has about 5700 rows (and growing over time) and 10 columns. I create 5 charts with this data. Up until 6 months ago, I ran this on an XP computer, and because many of my associates had older versions of Excel, I created all my spreadsheets, including this one, in the .xls format.
When I moved to my Windows 7 computer 6 months ago, I decided to change the Excel format, because this .xls file was very slow to update (size now about 2800 KB), and more folks had upgraded their Excel, or had readers. So I used Save As to create both .xlsx (size now about 1000 KB) and .xlsb files (size now about 750 KB) from the .xls file. I have been updating the spreadsheet in the newer formats (mainly .xlsb) ever since.
I ‘think’ this problem started when I changed the formats, but not 100% sure. In any case, on two of the five charts, frequently no data plots! Attachment ExcelBP1.gif is what the BP chart should look like [because this is my blood pressure chart spanning 6+ years, I have blanked out the text box entries that convey important events].
I usually enter data about once/month. For a month or so, everything is fine. Then suddenly, the BP chart appears as shown in ExcelBP2.gif. No data is plotted, nor does the X-axis time scale appear. However, if you look at the bottom of the chart, the Legend and trend lines entries, as well as the colors they are plotted with, appear correctly. If I right click, all the associated information (chart format, chart type, data range, Format Axis, etc, are all correct).
I have 5 charts plotted with this data. Only two (BP & Art. BP) show this problem, and both always at the same time. The other three always plot correctly.
Because I keep backups, when the problem occurs, I open the last correct file, Copy & Paste the new month’s data that I had entered over to the backup copy, and Save As with the latest name. Then the charts plot correctly. When the problem occurs again in a month or so, I repeat the process.
I used Excel’s compatibility checker, and neither the .xlsx or .xlsb formats show any problems relative to .xls. The spreadsheet does not use macros. I don’t have the problem with my other spreadsheets, but they don’t have this many rows of data.
Has anyone ever had this type of problem, or have any suggestions as to what might be causing it? Any help would be greatly appreciated, since this is very frustrating.
Harry
