Click What Button?

Category: General Weirdness | [Item URL]

This might be one of Excel's most confusing dialog boxes.

The text refers to the OK and Cancel buttons -- which are actually Yes and No buttons. So does Yes mean OK or Cancel

?

Also, notice the "this this" typo.

Hijri Dates

Category: General Weirdness | [Item URL]

If you use the English language version of Excel 2002, try entering the text below into a cell:

A1-1

You'll get the result shown below!

Furthermore, check the formula bar and you'll see that Excel interpreted your entry as a date -- March 25 of the current year. Take a look at the cell's number format, and you'll see that it's a custom format: B2d-mmm

This same weirdness occurs with other text strings that begin with A1 followed by a hyphen. This, I've been told, is a Hijri (Islamic) date. But I haven't been told why a Hijri date pops up in an English language version of Excel.

If your company uses part numbers with this format, that's a good reason to avoid upgrading to Excel 2002.

(contributed by Bob Umlas)

Excel As A Drawing Tool?

Category: General Weirdness | [Item URL]

This is not so much an oddity, but an AMAZING feat.

One day I received a package from someone named Debbie Gewand. In the package, was a floppy disk that contained an Excel workbook. For some reason, I decided to open the file, and I was astounded by what I saw.

It turns out that Debbie is an artist, and she uses Excel as her primary drawing software (now that's odd!). The figure below shows an example. The image on the left was scanned from a catalog. The image on the right was created by Debbie, using Excel's drawing tools. The drawing consists of hundreds of individual shapes, combined together.

According to Debbie, "Most of my drawings do not take longer than two hours or four hours max to get the outlines done and the fill colors put in. I often use photographs that I've scanned and inserted into Excel, then I use the drawing tools to change the photographs into drawings. As you have already noticed I've become quite proficient at drawing on Excel, so it doesn't take me as much time as it did when I first started, now that I've figured out all the tricks.

More examples

Click the links below to see some more examples of Debbie's work.

• Example #2
• Example #3
• Example #4

BAHTTEXT?

Category: General Weirdness | [Item URL]

In Excel 2002, Microsoft introduced a new worksheet function: BAHTTEXT. This function converts a number to Thai text, and adds a suffix of "Baht." The figure below shows this function in action.

Now, I'm sure there are many people who welcome this new function. But here's why BAHTTEXT is listed in the "Oddities" section: Excel does not have a similar function to convert numbers to English text. In fact, BAHTTEXT is the only function that converts numbers to text.

A function to convert numbers to words is a common request in the Excel newsgroups. Yet, for some reason, Microsoft chose to use its development resources to create a BAHTTEXT function rather than a more generally useful NUMBERTEXT function.

So why did they choose Thai?

It's been suggested (by an anonymous Excel MVP) that the Excel programmers enjoy Thai food, and they created this function to facilitate email orders to Redmond Thai restaurants. This theory has not yet been confirmed -- but then again it hasn't been denied either.

Excel Easter Eggs

Category: General Weirdness | [Item URL]

An easter egg, as it relates to software, refers to a way of displaying a list of the software developers. Most major software products include an easter egg, but viewing it almost always involves performing a series of non-intuitive steps. This document shares the secret of viewing Excel's easter eggs.

Note: Easter eggs were present in Excel 5 through Excel 2000. Apparently, Microsoft has received complaints about the waste of resources and file bloat.

Excel 2000 (Excel 9)

I can't get this to work, but others claim that it works for them:

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

Excel 97 (Excel 8)

Excel's 97's easter egg is the most impressive that I've ever seen. Follow these steps:

1. Open a new workbook
2. Press F5
3. Enter X97:L97 and press Enter
4. Press Tab
5. Press Ctrl+Shift and click the Chart Wizard button on the toolbar.

You'll be greeted with a full-screen animated image. Use the mouse to move around.

Trigger the egg. You'll be looking at a purple 3D landscape. Use the mouse to "fly" over the landscape (the mouse changes direction, left button moves forward and left button moves backwards). Fly around a bit and you'll see a grey stone pyramid. Fly to the side of the pyramid that's black and you'll see the credits scroll by.

NOTE: If this doesn't work, select the Tools Options command, then click the Transition tab. Remove the checkmark from Transition navigation keys and click OK. Then try it again.

Excel 95 (Excel 7)

Excel's 95's easter egg is quite impressive. To get to it, follow these steps:

1. Open a new workbook
2. Scroll down and select row 95
3. Press Tab
4. Choose the Help / About Microsoft Excel command
5. Press Ctrl+Shift and click the Tech Support button

You'll be greeted with a new non-Excel window (fans of Doom will feel right at home). Explore the window using the arrow keys and a few other keys.

NOTE: If you haven't seen the photos, you haven't explored the entire window! Hint: type excelkfa.

Excel 5

To see the Excel 5 easter egg, follow these steps:

1. Choose the View / Toolbars command
2. Click the Customize button
3. Scroll down the Categories list and choose the Custom category
4. Drag the second button (with a deck of cards image) to any visible toolbar
5. Click Cancel if you are asked for a macro
6. Click Close to close the Customize dialog
7. Press Ctrl+Shift+Alt, and then click the new toolbar button.

Don't give up too soon. It gets better. Press Escape when you've seen enough.

How Big Is A Worksheet?

Category: General Weirdness | [Item URL]

It's interesting to stop and think about the actual size of a worksheet. Do the arithmetic (256 * 65,536), and you'll see that a worksheet has 16,777,216 cells. Remember, this is in just one worksheet. A single workbook can hold more than one worksheet.

If you're using the standard VGA video mode with the default row heights and column widths, you can see nine columns and 18 rows (or 162 cells) at a time. This works out to less than 0.001 percent of the entire worksheet. In other words, nearly 104,000 VGA screens of information reside inside a single worksheet.

If you entered a single digit into each cell at a relatively rapid clip of one cell per second, it would take you about 194 days, nonstop, to fill up a worksheet (longer, if you break for coffee).

To print the results of your efforts would require more than 36,000 sheets of paper - a stack about six feet tall

Oh... and if you're wondering: No, it is not possible to increase the size of a worksheet. The number of rows and columns is fixed. Despite what must amount to thousands of requests to Microsoft, you simply can't coax Excel into displaying more rows or column. No way. It can't be done.

Update: Performing similar calculations for Excel 2007 is left as an exercise for the reader. An Excel 2007 worksheet has 1,048,576 rows and 16.384 columns.

The 256-Column Limitation

Category: General Weirdness | [Item URL]

Every Excel worksheet is limited to 256 columns. Despite what must amount to thousands of requests over the years, Microsoft refuses to increase the number of columns in a worksheet. Beginners often discover this limitation when they want to set up a spreadsheet that contains data for each day in a year. If they store the data horizontally, they run out of column in mid-September.

So we're stuck with 256. Why such a weird number? Why not 250? Or 365? The number of rows and columns is a by-product of the binary number system. 256 is 2, raised to the eight power (2^8), which is the maximum value that can be stored using eight bits. The number of rows in a worksheet is 65,536, which is 2^16. Older versions of excel contained only 16,384 rows, which is 2^14 power.

The reason for the 256-column limitation is probably due to the fact that Excel is so old, and it contains lots of code that would be broken if the number of columns were increased.

Update: The 256-column limitation was lifted in Excel 2007. In addition, that version has 1,048,576 rows in a worksheet.

The Intentional Date Bug

Category: General Weirdness | [Item URL]

A primary goal of computer programming is to write bug-free code. But did you know that Excel programmers created an intentional bug?

It's true. According to Excel, the year 1900 is a leap year. So if you enter the following formula, Excel won't complain, even though 29 February, 1900 is not an actual date:

=DATE(1900,2,29)

The reason for this error is compatibility. In the early days of personal computing, Lotus 1-2-3 was the most popular software available. Lotus programmers made the leap year mistake, and Microsoft programmers reproduced it so they could use the same date serial number scheme as 1-2-3.

Therefore, the days of the week prior to 1 March, 1900 are incorrect (e.g., 28 February, 1900 is really a Wednesday, not a Tuesday as report by Excel). In actuality, this is not a big deal because Excel doesn't even support dates prior to 1 January, 1900 -- which itself is an oddity.

Microsoft claims, perhaps rightfully so, that fixing the bug would create many more additional problems.

The DATEDIF Function

Category: General Weirdness | [Item URL]

The DATEDIF function calculates the difference between two dates, and expresses the result in terms of months, days, or years.

If you have Excel version 5.0 or later, you can make use of the handy DATEDIF function in your formulas. But if you want to read about this function in the Help, you must have Excel 2000. For some reason, the function was never documented until Excel 2000. And the documentation was removed from versions that followed.

The DATEDIF function has its origins in Lotus 1-2-3. Apparently, Microsoft included it in Excel for compatibility purposes. Why is it not documented? Who knows? But it's likely that lawyers are involved.

Chip Pearson has some good information about DATEDIF at his web site.

Copying And Pasting A Multiple Selection

Category: General Weirdness | [Item URL]

If you try to copy non-contiguous cells (i.e., a multiple selection), you'll probably get a pop-up message:

That command cannot be used on multiple selections.

But there are some situations in which Excel does allow you to copy a multiple selection. The rules don't seem to be documented, but copying a multiple selection is allowed if all of the range selections occupy the same column(s) or row(s). For example, the multiple selection shown in the figure below can be copied.

But when you paste the copied selection, the results may not be what you expect. The formulas are converted to values, and the ranges are pasted as a single range (see the figure below).

Page 2 of 4 pages
[Previous page]   [Next page]