Excel 2007 losing comments

Question

In the last few months some of the comments in my spreadsheet have disappeared. They are not just 'hidden' they no longer exist in the spreadsheet. Some comments are still there. Any way to prevent this from happening (or fix this)?

PC Technical details

Windows 7 Pro SP1 64-bit

RAM = 16 Gb

CPU = 3.80 GHz Intel Core i7-3820

Hard drive = 2 Tb

Excel Technical details

Excel 2007

Spreadsheet details

26,789 rows

10 columns

The spreadsheet is NOT shared. It sits on my personal PC at home and I am the ONLY one that uses it.

The spreadsheet is not used for any major types of calculations. It is a simple tracking spreadsheet that has rows of data that I can sort, filter, etc.

There are NO calculation formulas in the cells.

The only expressions / formulas in the spreadsheet are the ones used in the Conditional Formatting to set the highlight colors of cells.

I have been using this spreadsheet for several years (maybe 5) and I just noticed this in the last few weeks - since just after the beginning of 2013. That doesn't necessarily mean it wasn't happening before, but in the last month or so I noticed it because a LARGE number of the comments no longer exist in the spreadsheet. For some of them they were obvious and were easy to add again. For others, they are gone forever because I don't remember what it was that I added.

Any ideas?

Answer 1

This is really annoying.   I have company purchase orders on spread sheets and insert comments as to what the purchase was for.  Each year I add another tab and carry on the process.  I have found that on my 2014 spread sheet, every comment has disappeared. All other spread sheet comments are still there, right back to 2009.  They just happen to be all in column D, not that, that is of any significance.  This happened once a couple of months ago, and again two days ago.  I caught this one quite quickly and was able to right click>properties>previous versions - and go back a couple of days, open the spread sheet and there they were.  I saved it as Version 2, highlighted the 2014 column D and copied/pasted into the current spread sheet, which saved me having to go back and try to back fill.

I have no idea why this happens, or why it is only on the one spread sheet tab.  Just glad I caught it this time.  Hopefully the fix might help someone.

Answer 2

No, that post doesn't help - that was a post I read when I was doing some prior research on this issue. There are MANY posts like that about this issue on various forums. As far as I can tell, most all of them involve spreadsheets that are shared, and the replies almost always mention that shared spreadsheets have problems and this losing of comments might be one of those problems.

So that's why I specifically mentioned in the original post that the spreadsheet was NOT shared. This spreadsheet is used ONLY by me and ONLY on my local home PC, and so sharing - as far as I can tell - has nothing to do with the problem.

Also, related to the referenced post, I tried the 'solution' that was posted. Although the grammar in that other post is pretty poor, I think I figured out what the poster was trying to say. Essentially he was saying (I think) that the comments were not deleted they were just 'hidden' and his 'solution' was supposed to be a way to 'show' them again. I tried that 'solution' and it provided no help.

So that's also why I specifically stated in the original post that the comments were NOT 'hidden' - they simply don't exist in the spreadsheet any more.

It seems like no one knows why this is happening or what the fix is, so that's why I posted here with the details I provided. I thought maybe this forum would have some individuals who might have run across the real reason for this or at least a real solution.

Any additional thoughts on this? It seems to be a strange and obscure defect in Excel.

Answer 3

I am having the same trouble. As i save and it auto-recovers, no matter if i close it down and reopen. Slowly eating comments out of my spreadsheet. I NEVER had this problem with older windows OS and older MS office pro.

Answer 4

I have just started having the same problem. One thing to try: after entering the comment, to complete the entry, click on the cell that that comment applies to rather than anywhere else. This seemed to work for me, but I'll let you know if they disappear again.

Answer 5

The technique described in your post is something I typically do. Not all the time, but usually. I have not found any correlation between the comments lost from cells that I use that technique and comments lost in cells that I use a different technique, such as clicking on a cell different from the one that has the comment.

One thing that might be relevant - so any comments on this would be appreciated if you have additional information.

I typically have left the spreadsheet open all the time because I use it EVERY day, several times a day. I also don't shut down my PC every day - I restart very infrequently because Windows Update generally will download updates (scheduled each night at 3:00 AM) and many times will perform a restart after the update.

Well, when the restart occurs and the spreadsheet is open, Windows will often "remember" that Excel was running with an open spreadsheet, and Windows will restart Excel and then Excel will open the "auto-recovery" copy of the spreadsheet. Of course, I save the spreadsheet after almost every keystroke (I'm pretty obsessive about saving my work having had many bad experiences with that many years ago). So the spreadsheet always is in a 'saved' state, but Excel opens the 'auto-recovery' copy anyway. When I see the next day that a restart has occurred, I always just save the file again and Excel then just deletes the auto-recovery copy and uses the original file.

Recently I was thinking that the restart and auto-recovery sequence might have something to do with losing comments (who knows - but it's worth looking into). So now, instead of leaving the spreadsheet open all the time, I open it, do what I need, and then close it. So I never have the spreadsheet open overnight anymore. I have noticed that I am no longer losing comments. Granted, I have been doing this only for a week or so, ...

But I was wondering if anyone has any thoughts on this - whether this restart and auto-recovery might have a bug where the comments are not being saved properly and so they are lost when the auto-recovery file is used?