What are some interesting facts about Microsoft Excel?

June 25, 2014 12:51 PM

I work extensively with Microsoft Excel, and I'm putting together a list of little known facts about Excel for a presentation. I haven't found much on the interwebs that's exceptionally interesting. Does anyone have any facts they can share that come from off the beaten path?

This question is broad and intended to be so. I'm game for anything Excel-related.

posted by scottso17 to Computers & Internet (48 answers total)

There's a guy that uses Excel to paint.
posted by oceanjesse at 12:55 PM on June 25, 2014 [1 favorite]

Down in the status bar you can instantly see the sum, count, and average of any numbers you highlight.

Life-changing right there.
posted by phunniemee at 12:57 PM on June 25, 2014 [16 favorites]

Response by poster: @oceanjesse: Wow, that's awesome. Found him at http://www.boredpanda.com/73-year-old-excel-paintings-tatsuo-horiuchi/
posted by scottso17 at 12:58 PM on June 25, 2014 [2 favorites]

Excel 97 had a working flight simulator included as an easter egg.
posted by The Notorious B.F.G. at 1:01 PM on June 25, 2014 [4 favorites]

Joel Spolsky might have a few interesting Excel anecdotes - http://www.joelonsoftware.com/
posted by jkaczor at 1:04 PM on June 25, 2014

Did you know that with PowerPivot, you can link Excel to all sorts of data sources?


You can calculate your age with Excel:


A very unfamiliar, undocumented but very useful Excel function is DATEDIF. This will calculate the difference between 2 dates. This will help you to calculate your age. The syntax for DATEDIF is
=DATEDIF(StartDate, EndDate, Interval)
The interval can be Y, M, D, YM, MD and YD.
Example: Just copy and paste the following in a cell in Excel and press enter

=DATEDIF("03-Dec-1988","01-Oct-2012","Y")

If you want your exact age in year-months and days then copy and paste the following

=DATEDIF("03-Dec-1988","01-Oct-2012","Y") & " Years " & DATEDIF("03-Dec-1988","01-Oct-2012","YM") & " Months " & DATEDIF("03-Dec-1988","01-Oct-2012","MD") & " Days"

Here are a couple of Easter Eggs:

Excel 2000 (Excel 9)


1.Open a new Excel workbook
2.Press F5
3.Type X2000:L2000 and press Enter
4.You should now be on row 2000
5.Press Tab once, to move to column M
6.Hold Control and Shift, then right-click the Chart Wizard icon on the Standard toolbar. Some people have reported that it works only if you left-click the Chart Wizard icon.
7.Watch the list of developers for Excel scroll by.

Microsoft Office 2000 Web Components

Now this one is very cool! Technically, it's the easter egg for the Microsoft Web Components, but you access it from an Excel workbook saved as an interactive web page. Requires Excel 2000, the Microsoft Web components, and DirectX.
1.Open a new Excel workbook.
2.Select File * Save as Web Page
3.In the Save As dialog, select 'Publish Sheet' and 'Add Interactivity'
4.Save to an htm file on your hard drive (any file name).
5.Open the htm file with Internet Explorer 5.
6.Select cell WC2000 and scroll the sheet such that cell WC2000 is the first cell on the left. Highlight the entire row.
7.Press Shift+Crtl+Alt and click the Office logo in the upper-left.

Your screen will be transformed into an auto racing game, with developer credits (and other things) visible on the roadway. Use the following keys:
•Arrow keys: to steer and accelerate
•Space bar: To fire at other cars
•O: To drop an oil slicks
•H: To turn on your headlights

ANGRY FORMULAS-kind of like Angry Birds, but it's in Excel!
posted by Ruthless Bunny at 1:06 PM on June 25, 2014 [4 favorites]

this is a stretch, but this guy duplicated the entire iphone OS look and feel using MS Word. Most of the techniques and tools apply equally to Excel, but like i say, possibly bit of a stretch, but it does show the surprising capabilities of the suite when you put your mind to it.
posted by kev23f at 1:09 PM on June 25, 2014 [1 favorite]

An obscure story about date formats in Excel. Little known for sure, your call whether your target audience appreciates that amount of detail.
posted by themel at 1:12 PM on June 25, 2014 [1 favorite]

I love this question. Excel animation techniques.
posted by kev23f at 1:12 PM on June 25, 2014 [1 favorite]

It's stupid how much I love this feature. But you can click the pushpin to permanently mark a workbook as a recent workbooks. No longer will my important workbooks roll off the list! MAGIC.
posted by politikitty at 1:40 PM on June 25, 2014 [9 favorites]

If you want to transfer a column that is part of a Word document into excel without copying the rest of the table or text over hold down the Alt key, which allows you to select just that column with your mouse. Then copy and and drop it into excel.

This works for columns in tables, txt files, some pdfs and most webpages too.
posted by fshgrl at 1:46 PM on June 25, 2014

Excel's automatic conversion of certain types of numbers into date values wreaked havoc with gene name databases.

Somewhat similarly, I work for a financial institution and sometimes receive data with FDIC call codes, which are mixed text/numerical codes for different types of loans. These include codes 1E10 and 1E20, which if you aren't careful to format as text, excel kindly translates on its own to 10,000,000,000 and 100,000,000,000,000,000,000 respectively. Always fun to be going through a list of four digit codes and then hitting a cell with 100 quintillion.
posted by Esteemed Offendi at 2:20 PM on June 25, 2014 [3 favorites]

Normally the Alt key brings you to the menubar in Windows. But in Excel 2003, the slash key does it too -- because that's what Lotus 1-2-3 did in DOS in 1983.

Not sure if that survived the ribbon though.
posted by smackfu at 2:20 PM on June 25, 2014 [4 favorites]

I think any discussion of spreadsheets deserves to pause for a moment to remember VisiCalc.
posted by Ruthless Bunny at 2:27 PM on June 25, 2014 [3 favorites]

How to turn a row of data into a column and vice versa:

Copy the row or column. Right click on the destination cell and select Paste Special. Check off "Transpose" at bottom of popup window. Click OK.
posted by qi at 2:36 PM on June 25, 2014 [5 favorites]

How about a music video done in excel?

AC/DC - Rock'n'roll Train
posted by trialex at 3:11 PM on June 25, 2014

Little known function is NETWORKDAYS, which allows you to calculate the number of business days between two dates. But the cool thing is being able to insert a link to a dynamic excel spreadsheet while creating a word document.
posted by jadepearl at 3:24 PM on June 25, 2014 [2 favorites]

Another stupid Paste Special trick: if you've entered/imported percentages that are integers, enter 0.01 in a cell, copy it, select the percentage range, Paste Special -> Multiply, and you've got the 0 ... 1 range that Excel likes.
posted by scruss at 3:27 PM on June 25, 2014 [1 favorite]

As a kid I loved the Excel 95 Hall of Tortured Souls easter egg, to the point where I creeped myself out. Bonus video.
posted by kyp at 4:34 PM on June 25, 2014 [1 favorite]

Maybe you can tell your audience not to use Excel for sending information between Dept's or companies if the data is going to be fodder for a different computer program. CSV files are especially troublesome because the don't carry format information. Therefore anything that looks like a number is going to be treated as a number and is at risk for losing leading zeros. SSNs for instance.

If opening a file garbles the data, you can try data import which allows you to format the columns.

Excel can be used to read data directly from most databases, especially Access and SQL Server.

Excel is the logical thing to use for the source file for a Word mail merge.
posted by SemiSalt at 4:40 PM on June 25, 2014 [3 favorites]

this was from Splotsky: Excel thinks that 1900 was a leap year. That's because this was an error in, I guess, Lotus 1-2-3, and Excel replicated it so that any Lotus users who switched would find identical behaviour.
posted by thelonius at 4:42 PM on June 25, 2014 [1 favorite]

When reviewing the technical specification for Excel Macros (later: VBA), Bill Gates used the f-word the fewest number of times in any of his technical reviews of others' work up to that point.
posted by colin_l at 5:08 PM on June 25, 2014

1. Select a group of cells and move your mouse to the selection border so that you get a four-way arrow cursor. Right-click on the border and drag. You'll get a menu with some of the "paste special" options, which normally take 5+ mouse clicks to get to. This includes "paste values," so it gives you a fast way to remove formulas.
2. VBA. VBA, VBA, VBA. Excel's big Easter egg isn't a flight simulator, it's VBA. You would expect that Excel's macro editor would give you only a simple scripting language for automating Excel. Nope, it's a full-fledged programming language that's virtually identical to the full version of Visual Basic. You can write programs in VBA that have nothing to do with Excel, except that Excel is the program that launches them. You can even use it to mess around with memory and run machine code.

posted by chelant at 5:14 PM on June 25, 2014 [2 favorites]

Someone made a Role Playing game in Excel that you play in Excel.
posted by destructive cactus at 5:24 PM on June 25, 2014 [2 favorites]

Oh lord, I know way too many of these.

1. Excel can't handle dates earlier than 1900. Dates are just a number format, with the number 1 corresponding to January 1st 1900. You're on your own for doing date math earlier than that.

2. Excel is sometimes overzealous in auto-detecting things that might be dates. The stock ticker MAR, for example, often gets picked up as the month "March" in Excel and thus turned into a number. (this might have been fixed in later versions but was incredibly annoying in Excel '97-'03, which is still the gold standard at many places). Also see odinsdream's comment above.

3. Array formulas are used rarely enough that they almost count as Excel esoterica. They will not evaluate correctly unless you hit CTRL+SHIFT+ENTER after entering the formula. You can recognize them by a formula surrounded by curly brackets.

4. Sometimes Excel formulas will not evaluate completely on an F9. To do a full recalc, do CTRL+ALT+SHIFT+F9.

5. Very rarely, a blue bar appears at the left of the window, blocking view of the sheet. To get rid of it, drag something from the toolbar over it.
posted by pravit at 5:34 PM on June 25, 2014 [1 favorite]

Dates are just a number format, with the number 1 corresponding to January 1st 1900.

The Date/Time datatype in VBA is a floating point number. The integer part represents the date, and the fractional part represents the time. Dates previous to Jan 1 1900 are just decreasing integers: 0, -1, etc. Access date/time fields seemingly use this datatype; you can ask for CDbl([some_date_field]) in a query, and it hands over a double.

Excel really won't use a date in a cell before Jan 1 1900? That is surprising to me.
posted by thelonius at 6:08 PM on June 25, 2014

How big is an Excel worksheet?

It seems that prior to Excel 2007 it was 256 columns X 65,536 rows for a total of 16,777,216 cells.

Excel 2007: 16,384 columns and 1,048,576 rows for 17,179,869,184 cells.

Here are a bunch more Excel 2003 limits. Similar list of limits for Excel 2007.

A few interesting ones from Excel 2007:

Total number of characters that a cell can contain 32,767 characters
Hyperlinks in a worksheet 66,530 hyperlinks
Undo levels 100
Smallest allowed positive number 2.2251E-308
Largest allowed positive number 9.99999999999999E+307
Dependency on a single cell 4 billion formulas that can depend on a single cell
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered 9999:59:59

posted by flug at 6:17 PM on June 25, 2014

I came in to say that of you are seeing ##### it means your column is too narrow.
But that seems pretty mundane now.
posted by SLC Mom at 6:42 PM on June 25, 2014 [1 favorite]

I use array formulas all the time, but I am surprised I didn't know about DATEDIF - for a calculation I use regularly I end up subtracting date 2 from date 1 and formatting the result as days or months to find the difference, so hooray for allowing me to simplify that formula.

As for CSV files and auto formatting, wrap the entries that might be affected in =" " to keep it from changing.

(Wish I knew more about why they name specific functions as they did. Still get annoyed that I have to type AVERAGE instead of MEAN, it would save me a few keystrokes...)
posted by caution live frogs at 8:25 PM on June 25, 2014

It's really fascinating to see people's knowledge of Excel in this thread - it feels a lot like driving. Everyone who knows more than me is using Excel too much and everyone who knows less than me is slow.

FYI, in the most recent version of Excel, paste values/format/formula cone with a single right click when you have data on the clipboard anyway. I'm not sure if that's what chelant is describing?

My fights over replicate graphs on Excel recently taught me that if you select a series, the status bar at the top tells you what range that series covers - you can edit the range from there, without going through Select Data.

Sorry I can't add anything more interesting. I share the general annoyance and Excel's eagerness to convert to dates - songs times I just want to note to myself that this was sample 1 of 3, 2 of 3, and 3 of 3. I mean 3/3, not Mar-3.
posted by maryr at 11:26 PM on June 25, 2014

.xslx files from new excel are just zip files with XML data in. You can actually rename them .zip and open them as if they were zip archives.

(This is also why they are smaller than xls files and why zipping them doesn't really compress them any)
posted by Just this guy, y'know at 11:33 PM on June 25, 2014 [2 favorites]

Another Joel one - turns out its popular not for functions and arithmetic. Instead everyone uses it to store lists.

Everybody thought of Excel as a financial modeling application. It was used for creating calculation models with formulas and stuff. You would put in your assumptions and then calculate things like “if interest rates go up by 0.00001% next year, what percentage of Las Vegas homeowners will plunge into bankruptcy?” For example.

Round about 1993 a couple of us went on customer visits to see how people were using Excel.

We found a fellow whose entire job consisted of maintaining the “number of injuries this week” spreadsheet for a large, highly-regulated utility.

Once a week, he opened an Excel spreadsheet which listed ten facilities, containing the name of the facilities and the number 0, which indicated that were 0 injuries that week. (They never had injuries).

He typed the current date in the top of the spreadsheet, printed a copy, put it in a three-ring binder, and that was pretty much his whole, entire job. It was kind of sad. He took two lunch breaks a day. I would too, if that was my whole job.

Over the next two weeks we visited dozens of Excel customers, and did not see anyone using Excel to actually perform what you would call “calculations.” Almost all of them were using Excel because it was a convenient way to create a table.
posted by Admira at 1:20 AM on June 26, 2014 [4 favorites]

So many interesting things! This might not be the most interesting thing, but I feel it's important to distinguish that a spreadsheet is not a database.

Also, in the newer versions of Excel, Microsoft simplified conditional formatting - anyone can use it to find duplicates or highlight variables.

There's also auto formatting options that let you specify cell height and width, or auto adjust either to the size of the data. No more artisinal cell boundary moving.

COUNT is fun. Want to know how many people are coming to a party? Make the spreadsheet count all the yes, no and maybes and return a number.

That's all I can think of. Excel is such a great tool, good luck with your presentation.
posted by Calzephyr at 4:52 AM on June 26, 2014

And if you liked COUNT, COUNTIF is the cat's whatchamacallit.
posted by sneebler at 4:57 AM on June 26, 2014

❧ that was pretty much his whole, entire job

Joel, being a software guy, wouldn't understand the concept of fail-safe. The 0 spreadsheet guy probably had a whole bunch of procedures to maintain if there had been an accident. Don't forget, every utility rule comes about because someone died.

Calzephyr, the new conditional formatting is weird and broken and I hate it.
posted by scruss at 5:00 AM on June 26, 2014

An Excel error may have cost JPMorgan more than $2 billion.
posted by barryparr at 9:23 AM on June 26, 2014

Many people don't realise that Excel can do fractions, type 2 4/5 in one cell (the space is important) then type 3 6/7 in another. Now you can add the two together just like any other number.
posted by Lanark at 12:37 PM on June 26, 2014

from a prior AskMe
posted by j_curiouser at 12:45 PM on June 26, 2014

Following on from scruss' comment.
If you have a range of cells that you want as numbers but Excel has as text. Type 1 in another cell, select it and copy, select your range of cells you want to change, paste special tick multiply.

Don't mess with formatting cell alignment. Text left, numbers right and other stuff (TRUE/FALSE) in the middle. Makes spotting the wrong data type so much easier.

Double clicking on the Divide line between two columns <> makes the column the correct width for its contents.
posted by 92_elements at 2:44 PM on June 26, 2014 [1 favorite]

...Why is multiplying better than just taking the same click to change the cell type to Number?
posted by maryr at 7:53 PM on June 26, 2014

Presumably because you can multiply the whole column by 1 in one go?
posted by Just this guy, y'know at 5:35 AM on June 27, 2014

> Many people don't realise that Excel can do fractions

!!! Danger Will Robinson on that one! It's not doing the maths symbolically, but as floating-point approximations to N digit precision in the numerator and denominator. Default precision is N=1 (on the v crappy Excel for Mac, at least), so it comes out showing the heinously incorrect fractional part as ⅔, and not the correct answer of 23/35.

Another fun import trick: If you're not careful to set the column type to Text, any column containing a minus intended as a dash or empty value becomes 0. You may not want this ...
posted by scruss at 8:59 AM on June 27, 2014 [1 favorite]

Oh, and on that, anything decimal pointy, that's a risk, because 0.1 doesn't really exist in binary and ultimately all of excel's maths end up binary.

I have re learnt this many times, at various costs.
posted by Just this guy, y'know at 9:46 AM on June 27, 2014 [1 favorite]

1) You have some data in a column, X number of rows. You wrote a formula in first cell of the column next to the data column, you want to fill down on the formula to row X. Select the cell with the formula, move the cursor to the bottom right corner, until it turns into a black cross and then double click.

2) While typing out the function name in the formula bar, you can use the TAB key to auto-complete the function name based on what you've typed so far.

3) Hold down the ALT key for a hard return inside a cell.
posted by jyorraku at 8:30 PM on June 27, 2014

I only found out about "goal seek" a couple of months ago. Wish I'd known about that much earlier. Perfect for that "what if I want the result to be x?" type questions.

To zoom your work to the exact width of the screen, highlight all the cells you want to see but only one row, then do "fit to selection".

There is a great function which can generate a lookup table of results based on the numbers in the first row and column. I can't remember how to do it now :(
posted by mr_silver at 12:34 AM on June 28, 2014 [1 favorite]

oceanjesse: There's a guy that uses Excel to paint.

But to be clear, he uses the vector art capabilities built into Excel, if you're talking about who I think you are (Tatsuo Horiuchi).

That post has more fun Excel tricks and facts, including the fact that you can play Tetris in Excel. There was also a mention of Excel Pacman, which lead me to 50 free-to-download Excel games.
posted by filthy light thief at 5:35 PM on July 2, 2014 [1 favorite]

Puerto Rico has been implemented on Excel, including an "evolver" which generates strong computer opponents.
posted by DevilsAdvocate at 6:34 AM on July 10, 2014 [1 favorite]

maryr: "FYI, in the most recent version of Excel, paste values/format/formula cone with a single right click when you have data on the clipboard anyway. "

In the most recent Windows verson of Excel. Mac has been left out in the cold on the context menu, which annoys me. I really, really want simple keyboard shortcuts for paste special options (I use a stats program that includes cmd-shift-T for "Paste Transpose" and like the cmd-shift-V option for "paste without formatting" in Thunderbird... why Excel buries these in submenus using a floating options popup window is beyond me. Could I create keyboard shortcuts for these? Yes, I could, but they'd only work on my machine, and I'm not always using my machine. Likewise, the refusal to implement standard shortcuts for other things in Office bugs the hell out of me - I keep trying to use Adobe keyboard shortcuts for group/ungroup in Powerpoint or Word and it never works, but my brain keeps telling me "No, no, that's the right shortcut, just try again.")

More Excel fun: COUNTIF(S), AVERAGEIF(S)... those are great. Have a huge table of data? Only want to see the number of cells in column Y for which the corresponding cell in column X is a specific value? COUNTIF. Need more than one option? COUNTIFS. AVERAGEIF(S) does the same thing, so you can build a formula that averages all cells in a table where the date in column A is a Monday and the value in column B is greater than 0 and so on and so forth.

Even more fun - relative references using INDIRECT! This one is pretty useful, if a bit awkward to set up. I have some data processing scripts that dump data as a CSV file, adding formulas to get Excel to do calculcations in the process. INDIRECT allows me to use R1C1 style references in the script, counting from the cell in question, so that I can build a formula to say things like "The value of this cell is equal to the value of the cell two columns to the left divided by the value of the cell one column to the left" or etc. For example, the formula for that action would be =((INDIRECT("C[-2]",0))/(INDIRECT("C[-1]",0)).
posted by caution live frogs at 10:14 AM on July 11, 2014 [3 favorites]