Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel file with "ghost" data in cells

Excel file with "ghost" data in cells

Excel file with "ghost" data in cells

I was working on an excel file and was getting some incorrect results with formulas. After some troubleshooting I found what seemed to be empty cells that have some sort of "ghost" data in them. I was able to copy this ghost data to the attached spreadsheet in Cells A2 and A3. Whatever is in there affects formulas, for example "Count" and > Greater than (as used in the B column).
The ghost data can be cleared by either hitting delete on the cell or double clicking on the cell and hitting enter.
Can anyone tell me what is in cells A2 and A3 and possibly how it got there?

RE: Excel file with "ghost" data in cells

I looked at the xml file and it looks like a broken index to the Shared Strings portion of the xlsx file. I don't know what Excel is supposed to do when the property is an index but no index value is given.

It isn't clear how it can be reproduced except by editing the xml file directly.

RE: Excel file with "ghost" data in cells

Thanks for that 3DDave.
I finally figured out how the ghost cells were created.
If you use an IF formula to clear out zeros, using double quotes like this: =IF(A10>0,A10,"")
Then you copy and paste the VALUES from the results of that formula, you end up with the broken shared strings index like you said.
I wonder if there is some way to report this to Microsoft, I don't think it would be expected behavior. "" should not be greater than 0.
Do you have a better way to leave an empty cell when using an IF statement? Something other than ""? Leaving it blank after the comma will give a 0, not an empty cell.


RE: Excel file with "ghost" data in cells

Thank you cowski. I actually just finished reading that entire discussion. It looks like this problem has been around for a long time. I guess I just never came across it until today.
I guess I just have to be very careful in the future when using the ="" in any formulas. It does not return an truly empty cell and may have negative affects later if someone uses copy and paste values. ie =if(xx>0), or selecting the cells and looking at the count at the bottom footer. Strangely the =count(xx:xx) formula doesn't count them, it only counts numbers, not text or empty strings.

I used the following VBA script to clear the ("") empty strings out of my worksheets. Just select the range you want to fix and run the VBA.

Sub RefreshCells()
For Each r In Selection
r.Formula = r.Formula
End Sub

RE: Excel file with "ghost" data in cells

Huh, interesting; there are a couple of Excel tests that do show that those cells are different. Note that using "" essentially is an empty string, so a cell with "" ought to be different than a truly empty cell, as shown by testing the cell in Excel itself:

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: Excel file with "ghost" data in cells

I just came across this Microsoft user voice link. It looks like Microsoft has no plans to fix the issue and give us a way to return a nul or empty cell from a formula.


RE: Excel file with "ghost" data in cells

Thanks for the rabbit hole; https://stackoverflow.com/questions/23433096/using... is especially deep. Not only is the subject a fun one, there's this part of a comment that's a standout:

"My real beef with those people is that they do not take MS Office feedback seriously. I don't know how many feedback I have left on the MSDN KBs in the last couple of years but none of them have been actioned! It's as if they bloody don't care!"

RE: Excel file with "ghost" data in cells

I put some expressions in the adjacent columns as indicated in row 1, FYI.

Notice that your rows 2&3 my rows 3&4, that if you need to identify empty cells then if you select (Blanks) in a filter then those 4 rows will be displayed, even though ISBLANK() indicates FALSE.

How it got that way??? Maybe you could shed some light on how you got to this point.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel file with "ghost" data in cells

XX:XX is a substitute for A1:A2. It's a range.

RE: Excel file with "ghost" data in cells

Some time ago, needing to get to the bottom of this sort of thing, I created for myself a spreadsheet that applied all Excel's ISxxxx() functions to as many different types of cell entries I could think of.  I later extended it to include VBA's ISxxxx() functions.  See attached.  (It is in a zip file because - for obvious reasons - it includes VBA code.)

RE: Excel file with "ghost" data in cells

Thanks 3DDave,
you are correct, I was attempting to reference a range while being to lazy to actually type a real one.

Thanks Skip,
I figure out the issue in my reply. It is an empty string. One way to get into the situation is by using the formula ="" Then copy / pasting the result as values.

Thanks Denial,
That is a very useful spreadsheet. I'll need to study it for a bit and look up what some of the is functions do.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close