Things I Wish I'd Known About Excel Before I Started Today

I've been working on a project in Excel for the past couple of days - let's say it's for fun and profit, but mostly for profit - and while this particular spreadsheet runs just fine for what I need it to do, I can't help adding a bunch of bells and whistles when I have a spare hour or five. This particular endeavor was meant to capture a target value (say, a vacation start date) and the value next to that (a vacation end date)*. This is what I wish I had known before I started trying to gild the lily.

* The data below in no way represents the actual data or the format of the spreadsheet I'm talking about. Trust me when I say that these issues occur no matter what data you try to apply them to. Do not panic; this is merely a simulation. Please do not try this at home. Objects in mirror are closer than they appear.

1. If you need to enter a whole bunch of conditional formatting statements for a particular column, Excel 2003 will turn vindictive after the third one and not let you enter any more until you upgrade it to the flashy new Excel 2007.

2. Once you do upgrade to the flashy "new" Excel 2007, the fancy formulas you developed to work around the three-condition limit will be laughably clunky and obsolete in the face of the newer conditional formatting tools. (Very snazzy, by the way.)

3. Returning a cell value or address will take between one and ten minutes, depending on your knowledge of Excel formulas. Returning the value *next* to that value will take 8+ hours.

4. A function exists to do what you want to do, but it will turn out that it is volatile and will explode at any moment.

Take this function, for example:

That's  =CELL("address",INDEX(A2:H2,1,MATCH(MAX(A2,C2,E2,G2),A2:H2,0))) if you didn't catch it the first time.

This will give me the name of the cell between A2 and H2 that holds the maximum value in that range. It evaluates to cell $E$2 (which is actually correct! go me!). That alone took hours, because apparently I can't visualize data structures like some programming geniuses. I believe that my brain has a three-nested-function limit; three levels down, I start thinking about Hofstadter and tortoises and Moebius strips, and it's all over. So I have to build it up from the inside out, piece by piece, usually with plenty of time to research the functions, because all college taught me was SUM(A1:A25).

That aside, this function works perfectly well. So does this one:

=OFFSET($I6,0,-7,1,1). That's what she said.

Have you ever written pages and pages and pages of code, then peeked at a forum to find that there are these things called regular expressions and that someone solved your problem in three lines? That was this formula. Excel got a stern talking to about communication after this one. Still! This was good. This formula will look at cell I6 and spit back a 1x1 (one cell) grid starting at the cell that's on the same row (that's the 0), but seven columns to the left (the -7). And it works! It works beautifully.

What happens when I combine these two? All I'm going to do is take out the cell number from this function:

=OFFSET( ___________ ,0,-7,1,1)

and replace it with this formula, which, as shown, spits out the maximum cell in the range (which should be $H$11*, giving this:

* Ok, so I fudged this one a little bit. So I didn't have to take screen captures for a fourth time, I tweaked it to look for the maximum value overall, not just in the "Start" columns. Trust me when I say that this is not the source of my problem.

You know what happens next? The whole thing explodes.

The formula works. I know it works. If this were software code, no one would bat an eye. But Excel, lovely Excel, considers CELL() and OFFSET() to be *volatile*. Volatile functions may never, never be nested inside one another, otherwise there will be a small black hole in your screen that will consume the universe. There is but one force preventing this disaster: an extraordinarily unhelpful popup message that proclaims:

You're so helpful.

What kind of error, Excel? Where is it? Did I misplace a parenthesis? (If so, commence the parentheses ritual: "open, open, open, open, closed, closed, open, closed, damn!") Oh...you mean the kind of error that only the internets know about. Helpful. Thanks for the warning.

The workaround to this problem is another story for another day. Spoiler: Excel is a bitch.

5. Do not mention macros or VBA to me. My computer is so locked down that Excel throws an error if I even try to define a function.

6. Sharing a workbook between two people and across two versions of Excel will randomly result in spontaneous data loss or someone deciding to scrap the whole giant project because "it's not the right technology."

7. If you have just typed a very very very long formula in, clicking away will replace the whole thing with a single cell reference.

8. If you have just typed a very very very long formula in, there is a little button over on the right that will let you expand the entry box so that you're not sorting through five nested if-statements one line at a time.



What are your favorite things to hate about Excel?

ETA: I'm updating the syllabus to include Google Sketch-up