Broken Excel Spreadsheet

I have an Excel spreadsheet that went bonkers on me (technical term) and I'm wondering what went wrong.

It's about 1400 rows by 7 columns, and contains only data, that is non-numerical information. I have salvaged the data by copying it to the Windows clipboard and pasting it into a new, blank spreadsheet, then using the AutoFit tool to set the column widths. The salvaged spreadsheet is as good as new, all the fill colors are right, the formulas work, everything is good.

I don't know what I did to cause the original spreadsheet to go wrong. All at once the column widths and formatting got weird, I couldn't align columns (If the data were left-justified I couldn't change it to centered, for example) and the cells with formulas in them (example: =countA(D2:D1286) to count the number of cells with data) showed the formulas rather than the result of the calculation. It's as though the spreadsheet got put into some alternate viewing mode that I couldn't get out of.

Does anybody know what happened?

tanstaafl.

I think you hit CTRL + `

EDIT: That is the CTRL + tilde (the key to the left on the 1 on most keyboards.

That didn't do it for me.

Check "Tools > Options > View" and look under "window options", be sure "formulas" is not checked.

Edit... Ah, that weird left single quote mark, yes, it does work...

Not being able to change things sounds like protection was turned on, but that's somewhat hard to do without noticing.

I have no idea how I might have done it, but doing so EXACTLY replicates what happened to my spreadsheet. Well, maybe I was doing a Shift-Tab and hit Ctrl ` instead, but that seems a stretch. Literally. I do use that Shift-Tab a lot, though, so it's not impossible.

What is the purpose of that CTRL + ` command anyway, other than to wreck my spreadsheet?

Anyway, you nailed it. Thanks!

tanstaafl.

Indeed, hitting that accursed Ctrl ` combo besides wrecking the formatting does set that "formulas" checkbox.

Unchecking that box and resetting the column widths restores the spreadsheet to its former glory.

tanstaafl.

Dose not hitting that combo a second time put it all back?

It makes it easy to check a bunch of formulas at once if you have an error somewhere. It is a lot easier than going through cell by cell checking them.

A "tilde" is the curlicue that's the shifted version of that key on US keyboards: "~". It's used in Spanish (and other Iberian languages) to modify 'n's, as in "Español" and "baño".

The '`' is correctly referred to as the grave accent, but often referred to as a "backquote" or "backtick".

[[Pedant mode off]]

I was thinking of the mathematic uses for the tilde.
Who would have thought such a small character would get such a large wikipedia article?
Fun stuff!

Hey, yes it does. I never thought of trying that. Of course at the time of the panic I had no idea what had caused it, so thinking of what might undo it wasn't obvious.

tanstaafl.

And while we're on the topic of Excel...

I'm running a pretty old version of Excel, "About" says it is Excel 2002. Before that I was running an even older version of unknown vintage.

In the older version when I opened multiple spreadsheets by clicking on the filenames (opening by file association) each spreadsheet opened in it's own Excel program. The newer (2002) version opens all the spreadsheets in a single program, all of the spreadsheet "windows" being contained in a single larger Excel window, sort of a window within a window setup.

I hate, despise, detest, etc. this.

[Diatribe]I seem to spend half my time moving those spreadsheets around inside the Excel window, trying to find the scroll bars or trying to uncover columns that are hidden by another spreadsheet. Whenever I open a spreadsheet, the main window is always whatever size the previous Excel session was, rather than the size appropriate for the particular spreadsheet, and it is ALWAYS the wrong size so I drag the window out or in to size it properly and then realize that I was supposed to drag the spreadsheet window the the right size and then drag the Excel window... and I can't tell you how many times I have forgotten that I had more than one spreadsheet open within the Excel window and closed the window, losing the changes in the "hidden" spreadsheet because I decided not to save the changes in the one on top and didn't read the warning message carefully.[/Diatribe]

Fortunately I have found the workaround, and that is to start Excel by running the program directly (not through file association) and then opening the spreadsheet from within Excel. That way I can have as many different Excels running as I need. Of course they will all still be that idiotic window within window nonsense.

Do later versions of Excel still do this window-in-window stuff? If not, I might be persuaded to upgrade. Or, is there some setting somewhere within my 2002 version of Excel to turn this "feature" off?

tanstaafl.

I don't have Excel here, but, if it helps you on your search through the preferences windows, the Microsoft name for the "feature" that you (and I) hate so much is "MDI". In theory it's an older design than the obviously right way of doing it (which Microsoft call "multi-SDI"), so if an older Excel had a setting for it, so should a newer one.

Peter

It would have helped you to find this answer if you had known that that interface was called "MDI".

This should work:

O silly me for assuming Microsoft would do something vaguely sane.

How you get so big making software of this kind?

Peter

Very splendidly, that article is only readable if you click through from a Google results page.

Peter

Of course.

By exploiting a monopoly?

I've used that on occasion. If you maximize the inner window, it effectively goes away.

No, all that does is show a separate icon in the task bar for each spreadsheet you have open. All of the spreadsheets are still contained within the single Excel window. FWIW, I already had that box checked by default.

Can anyone think of a single advantage to this window-in-window format? Oh, wait, I actually did discover one thing that works better. When cutting and pasting between separate instances of Excel, formulas are pasted as values only, whereas in the window-in-window version, the formulas are carried over. Other than that, I can't think of anything.

tanstaafl.

Good tip!!

tanstaafl.

It seems that everything that's upsetting is settable in tools>options somewhere.

Personally, I prefer to have one copy of a program running. I typically have about a dozen excel spreadsheets open at any time, and I don't want a dozen copies of excel running and burning up memory.

I'm still running office '97 as all versions after that run a separate copy of word for every open document. I heard that they did this because outlook uses word as the editor and they couldn't figure out a way to slip an email in and out of the open documents, so decided everything shall be it's own copy of word. Brilliant.

However, I use Mozilla. And on my primary machine, I run Windows 2000, which I feel is the peak of windows development. I use XP on everything else, for a variety of reasons... but 2K is the best. Less annoying behavior, and most things just work, something MS has gotten away from.

I'm hoping 7 is ok, but I'm worried about it being "not worse than xp..."

I'm still missing os/2...

Yes: it's easier to port software from the DOS era, or pre-multi-tasking Windows era, that assumes it owns the whole screen. Oh, wait, you mean advantages for the user of the software? No, not really.

Well, in fact I suppose it's also easier to "port" users' experience of software from earlier times. If you use Windows like an Iphone -- if all your windows are always maximised, and you use Alt-Tab to go and find Excel when you want to do some excelling, then Alt-Tab to go and find Word when you now want to do some wording, then MDI is the thing you need.

That's nothing to do with MDI as such, of course, it's just down to the fact that they're separate instances of Excel. A proper multi-SDI application like Word (probably) is, can run several top-level windows from the same instance.

Faking that by running new instances for each document (here's how to do that for double-clicking Excel files) will use a bit more memory than one-instance-for-all -- but far less than the N times as much one might expect, because all the read-only and/or relocatable-code memory pages, such as the program code itself, will be shared between them.

Peter

And a properly designed program wouldn't be using multiple instances anyway; it would just have one program with multiple top-level windows.

I hate that. I prefer multiple instances of the process. Because, face it, the program isn't going to be perfect, so I'd rather have each of my documents sandboxed. It also means you can look at one document while you've got a modal dialog open in another instance.

That said: Excel's implementation of multiple SDI sucks. You might get multiple task bar buttons, but it doesn't behave properly with multiple monitors.

Is that how it works? Multiple instances must be the way, then. I had sort of thought (but then I haven't done any Win32 programming for a while) that a modal dialog only disabled its owner window (and the owner's children), not other top-level windows?

Modal dialogs are IMO another reminder that your application developers prioritise their nostalgia for DOS days, over your user experience.

Peter

Those instructions are for Windows XP, and don't seem to be applicable to Vista. I only get as far as "Choose Folder --> Options --> File Type." I don't seem to have "Folder --> Options" available to me. I went into Control Panel and looked at "Default Programs" but the options available to me there weren't useful either.

I sincerely hope that this can be done in Vista!

tanstaafl.

I suppose that there's some benefit in that, and is, in fact, the reason (many years ago) I moved from Netscape as both web browser and email client to two separate programs.


Well, that's an implementation bug, IMO.

Err. You're actually correct there, provided the app follows the rules. MFC wizard-generated multi-SDI applications don't, interestingly.

One potential problem is that Win32 message loops have thread-affinity, and Win32 windows have thread-affinity (it's this way by default as a Win16 backwards compatibility thing), meaning that it's possible for the modal message loop to mess up the other windows in the thread anyway.

Similarly, if a process doesn't make good use of background threads or asynchronous operations, it'll block the UI thread, whether there's a modal window open or not (assuming a single UI thread, which is the model for most Win32 applications).

Thus, one-process per top-level window is my preferred way to implement this. You get sandboxing; you get more address space (assuming 32-bit), and you don't have to waste too much memory, because Windows shares pages when it can. You shouldn't even need to worry about using the clipboard between processes, because Windows data objects have that covered. Although, ironically, it seems that Excel's mildly broken here, too.

(Today having to actually look at two Excel spreadsheets at the same time:) AAARGH how did this thing even SHIP in that UTTERLY BROKEN state?

Peter

The secret: don't double-click the XLS file to open it. Open a fresh instance of Excel, and then open it in that.

Yes, I've been doing that for some time now. However, there is a caveat to be wary of.

If you cut and paste between spreadsheets that are opened in different instances of Excel, only the data values are pasted, formulas and comments are not copied over. If both spreadsheets are opened in the same instance of excel, then all information is pasted.

tanstaafl.

Ah! In fact 99% of the time I'm not double-clicking on them anyway, I'm clicking on them in Firefox (from an internal, actually quite groovy, document control system). I bet that means I can tell Firefox what to do with them. (As opposed to what I'd like to tell Firefox to do with them .) I'll look into that tomorrow...

Peter