Weird Excel Display Behavior

I’m trying to enter a function, which evaluates other columns and concatenates text. While testing it, I’m getting weird “overstrike” display. Instead of updating the cell, it’s overwriting the cell, so I can see the previous results behind the new results.

The function is ugly and gnarly, but it ought to work. In fact, it does work! When I refresh my computer screen – say, minimize it and then restore it – the display looks exactly right. But when I make a change in one of the contributing fields, the previous result doesn’t go away, but is overwritten.

Another thing: the sheet I’m working on is fairly big: 900 rows of 30 columns. When I do exactly the same function on a new, small sheet – one row of the same 30 columns – the screen refreshes quickly and normally.

Is it usual for Excel 2010 to overwrite data in cells after data change for big ugly sheets?

It is not usual. I would even go so far to say this is an Excel bug, because I can think of no intended feature that would show a new cell value superimposed over the previous value. It’s not something you can do on purpose.

Is your function a UDF or are you using built-in functions? Can you show the function and tell us something about your data? I have 2010 and would be interested in seeing if I can reproduce it on my machine. You could email me the file if you want.

You could also post to this forum, free registration required. It allows you to attach files to your posts, although there is a size limit.

How powerful a machine are you working on and is there another one you can try it on?
I’ve had Excel do weird display things before when it just didn’t have enough memory to work with and if I switched machines (or a lot of times just rebooted) all the problems went away.
Can’t say I’ve had this particular problem though.

Cooking With Gas: I honestly wish I could send you the file, but it’s my contacts file, with lots of personal phone numbers and stuff.

The function itself is fairly ugly:

=CONCATENATE(C2,IF(D2="","",CONCATENATE(IF(C2="",""," “), D2)),IF(B2=”","",CONCATENATE(IF(C2="",""," “),B2)),IF(E2=”","",CONCATENATE(" ",E2)))

Really…it’s simple: I want to add “John” “J.” “Smith” Jr." together to get “John J. Smith Jr.” – but sometimes the middle name is blank, or there is no suffix name, and even some cases with no first name or no last name, and the function prevents double “space” characters. Ugly, huh? (There may be a better way, but I haven’t figure it!)

I think what’s happening is a bug, and may be because the sheet is so big. 900 people, with thirty columns of information. If I change the middle name to “Johnson” I get a super-imposed image of both “John J. Smith Jr.” and “John Johnson Smith Jr.” Really muddy.

(If I do a print-screen, and then paste to Paint, I can actually see the mess.)

But if I do a Page-down and then Page-up, it clears right up.

One slight possibility: I have macros enabled in the sheet, and it is saved as an xlsm file. The only macro, however, is:

=Text(Today(),"“yyyymmdd”)

I do love Excel, and I’m only just beginning to mess with macros and VBA. Fun!

Thank you for advice and offers of help; at the moment, I seem to be functioning, at least!

This tells me it is some sort of display bug either in Excel or in Windows. I am not at all knowledgeable about such things even though I have a software development background. The situation could be exacerbated by your file size but honestly your file isn’t that big. I have worked with Excel files with tens of thousands of lines of data, and 20M in size, and the issue with those tends to be calculation time.

Here is one thing you might try. I don’t know if it will solve your display problem but it might lighten the sheet up enough to resolve it. I have simplified your formula to the following which produces equivalent results as far I have tested it and compared it side-by-side to yours:

=CONCATENATE(C2,IF(C2=“”,“”," “),D2,IF(D2=”“,”“,” "),E2)

You gave the example

“John” “J.” “Smith” Jr."

I thought this might represent four columns of data (despite the missing quote), but your formula just works with three columns so I based my analysis on the formula.

Lum! That is a whole lot simpler, ain’t it! Thank you. I essentially over-logiced the problem, and entirely overlooked a simpler solution.

I’ll just figure it’s an Excel/Windows display bug, and live with it, since it clears up when I refresh the screen. Weird, though!

This may be oversimplifying matters, but have you tried an F9 refresh?

You should also look at the ISBLANK function to make things clearer.

Actually, I tried that, and it doesn’t clear up the double-image. But page-down/page-up does. Makes me think it’s a bug having to do with the computer OS and screen memory.

I’ll mess with that. Thank’ee!