Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jul 2013
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2007 charting problem

    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
    Attached Images Attached Images

  2. #2
    2 Star Lounger
    Join Date
    Apr 2014
    Posts
    100
    Thanks
    0
    Thanked 10 Times in 10 Posts
    Manual calculation???

    I have a bp file also. If you send your problem file to dguillett@gmail.com, I'll take a look.

    BTW, Most users do NOT use 64 bit Excel.

  3. #3
    Star Lounger
    Join Date
    Jul 2013
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dguillett@gmail.com View Post
    Manual calculation???

    I have a bp file also. If you send your problem file to dguillett@gmail.com, I'll take a look.

    BTW, Most users do NOT use 64 bit Excel.
    Thanks for the offer. I will send a file that shows the problem.

    Not sure what you mean by 'manual calculation???'.

    My version of Windows 7 is 64 bit. My Excel 2007 is 32 bit. Sorry for the confusion.


    Harry

  4. #4
    2 Star Lounger
    Join Date
    Apr 2014
    Posts
    100
    Thanks
    0
    Thanked 10 Times in 10 Posts
    By manual calculation I meant perhaps your calculation is set to manual
    Almost everyone I know or know of is using 32 bit excel in 32 bit windows. Try it.
    I am still very willing to look at your file. I have all versions of excel on 32 bit win.

  5. #5
    Star Lounger
    Join Date
    Jul 2013
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I just Emailed you the file.

    That file is set to Automatic calculation, as are all? of my Excel files. I just checked it.

    I have Windows XP (32 bit) on another computer, which also has Excel 2007. I just opened the file I sent you there. Same problem.


    Harry

  6. #6
    2 Star Lounger
    Join Date
    Apr 2014
    Posts
    100
    Thanks
    0
    Thanked 10 Times in 10 Posts
    OK. After seeing the file I found that the problem was that you were charting more cells than you should. I see you were using defined names but not referring to them. Start with the DATE for the base Dates=OFFSET(Data!$A$12,1,0,COUNT(Data!$A:$A),1)
    Do NOT use negative offsets.
    then times=OFFSET(Dates,0,1)
    etc
    then refer to the name in series by using
    =filename.xlsb!dates
    Now your charts will be SELF ADJUSTING

  7. #7
    2 Star Lounger
    Join Date
    Apr 2014
    Posts
    100
    Thanks
    0
    Thanked 10 Times in 10 Posts
    I also Highly recommend that your reinstall office in win 32 instead of win64

  8. #8
    Star Lounger
    Join Date
    Jul 2013
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dguillett@gmail.com View Post
    OK. After seeing the file I found that the problem was that you were charting more cells than you should. I see you were using defined names but not referring to them. Start with the DATE for the base Dates=OFFSET(Data!$A$12,1,0,COUNT(Data!$A:$A),1)
    Do NOT use negative offsets.
    then times=OFFSET(Dates,0,1)
    etc
    then refer to the name in series by using
    =filename.xlsb!dates
    Now your charts will be SELF ADJUSTING
    Thank you for finding the problem. You are much more experienced in setting up Excel spreadsheets that I am. So I need some more guidance.

    You said 'Start with the DATE for the base...'. Am I doing this by selecting the Date column on the Data tab, or 'Select Data Source' on the BP chart tab, or somewhere else?

    Also, I am not sure where the 'times=OFFSET' and the '=filename.xlsb!dates' are entered.

    So any help will be much appreciated.

    Harry

  9. #9
    Star Lounger
    Join Date
    Jul 2013
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dguillett@gmail.com View Post
    I also Highly recommend that your reinstall office in win 32 instead of win64
    My two Windows 7 computers are 64 bit. Excel 2007 is installed in Program Files (x86), but that is probably not what you are talking about.

    Harry

  10. #10
    2 Star Lounger
    Join Date
    Apr 2014
    Posts
    100
    Thanks
    0
    Thanked 10 Times in 10 Posts
    Couldn't seem to be able to add file?? Send me YOUR email addy

  11. #11
    Star Lounger
    Join Date
    Jul 2013
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by dguillett@gmail.com View Post
    Couldn't seem to be able to add file?? Send me YOUR email addy
    I just sent it via Email to your gmail address.


    Harry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •