Data Entry Quirks

Category: General Weirdness | [Item URL]

When you enter a numeric value into a cell, Excel normally ignores any leading space characters. For example if you type [space]123, Excel discards the leading space and the value 123 appears in the cell.

When you enter a date or time value, however, leading spaces are not ignored. Rather, the date or time is entered as a text string and the leading spaces remain. For example, if you type [space]12/5/2009, this entry is not entered as a date. Similarly, [space]5:00 is not entered as a time value. These text strings appear exactly as you typed them, including the leading space -- most of the time.

Here comes the odd part.

Type [space]:0, and what do you get? You get a value: -0.000694444444444444. Excel formats the cell as a time value, so all you see is a series of hash marks: ######.

But if you type [space]:1, you get the time value, 0:59. Type [space]:2, and you get the time value, 1:59. [space]:3 gives you, 2:59. 

Now lets add an am/pm indicator. Type [space]:0 am and you get 2982616.16248843.

On a hunch, I Googled for the integer portion of that number and found this:

Installing Norton Antivirus 2008, I encountered this wonderful dialog box. I only needed to wait 4,294,967,294 minutes for current file updates to download! That's 71,582,788 hours, or 2,982,616 days. That's 8,171 years.

So, we have a similar time-related bug in both Excel and Norton Antivirus.

2,982,616 days is pretty close to 4,294,967,295 minutes -- which is (2^32)-1. Someone who understands how time values are stored internally might be able to explain what's going on.

Me? I'll just call it a harmless Excel oddity. At least I think it's harmless. I can't imagine any situation in which this could actually cause a problem.

(Thanks to Tadeusz Jankowski for bringing this to my attention)

Two Types Of Security Warnings In Excel 2007

Category: General Weirdness | [Item URL]

If you use Excel 2007, you might have noticed that Excel provides you with two different macro warning notices.

Sometimes, you'll open a file and see this message above the formula bar:

Other times, you'll get this dialog box instead:

It took me a long time to figure out what's going on. As it turns out, if you have the Visual Basic Editor window open, you'll get the dialog box warning. If the VB Editor is not open, you'll get the warning in the worksheet.

Excel Gradually Deletes Multiple Extraneous Plus Signs

Category: General Weirdness | [Item URL]

This is kind of odd. Enter the following formula into a cell:

=++++21

Sure, it's an odd formula, but it's legal. Excel accepts it and returns 21.

Next, activate the cell and press F2 to enter EDIT mode. Don't make any changes; just press Enter. The formula now appears as:

=+++21

Excel removed one of the plus signs. Press F2 and Enter again, and you'll find that Excel removes another plus sign. Keep repeating it until all of the plus signs are removed and you're left with this formula:

=21

You get different behavior if you substitute a cell reference for the constant:

=++++A1

In this case, Excel keeps all of the extraneous plus signs when you edit the formula.

Try this one:

=++++12++++45++++96

With each edit, Excel removes one plus sign from each of the three groups of plus signs. But, similar to the cell reference example above, it won't modify this formula:

=++++A1++++A2++++A3

Contributed by Joe Rosebrock.

Sorting Oddity/Bug

Category: General Weirdness | [Item URL]

This is odd -- and I'd say that it's a bug. I tried in Excel 2003 and Excel 2007, and I get the same result.

The figure below shows data and formulas entered into three columns. The worksheet is in formula view mode, so you can see the formulas.

Here's how it looks in normal view:

And here's how it looks after performing an ascending sort on column A:

Most would agree that the formulas in Column C should behave exactly like the formulas in Column B. The only difference is that the Column C formulas have a (seemingly superfluous) worksheet qualifier. As you can see, the formulas in Column C continue to point to the original cell.

This odd behavior could could lead to problems if you're not paying attention. In actual practice, however, it's very rare to have sheet-qualified references that refer to the same sheet that contains the formula. And, based on this simple example, it's probably fair to conclude that using sheet-qualified references on the same sheet is a bad practice.

Contributed by Bob Umlas.

An Unusual Excel 2007 Help Topic

Category: General Weirdness | [Item URL]

Here's an Excel 2007 Help topic that's kind of odd:

Apparently, someone in the documentation department was having a boring day, and decided to spice up the language a bit.

Note that this Help topic appears only in the off-line help. If you specify "Show Content From Office On-Line," you'll see a corrected (and more boring) version.

How To Screw Up Your AutoFormat Dialog Box

Category: General Weirdness | [Item URL]

It's easy.

1. Select Tools - Options
2. Click the General tab
3. Specify 18 for the Standard font size
4. Exit, and then re-start Excel

After performing these steps, you'll get this beauty when you choose Format - AutoFormat.

For more fun, try it with even larger fonts. Here's what it looks like when your default font size is 48:

Submitted by Bob Umlas.

Options, Options, And More Options

Category: General Weirdness | [Item URL]

Excel is a very flexible program, and it provides the user with many options to control how it looks and works. But the problem is finding those options.

Most users are familiar with Excel's Options dialog box, which is displayed when you select Tools - Options. This dialog box, shown below, has 13 (yes, count 'em) tabs.

The Options dialog box is essentially Excel's junk drawer. With every new upgrade, the developers cram more options into this dialog box. This dialog box is a prime candidate for the cover of of the Journal of Bad User Interface Design.

I've been using Excel for more than a decade, and I still can't remember which tab to use. Typically, it will take two or three tab clicks to locate the desired option.

But the main problem with the Options dialog box is inconsistency. Some of the options affect only the active sheet, and others affect Excel as a whole -- and they are scattered all over the place, with no clear indication.

The Chart tab is a completely different animal. This is where you go to modify attributes of the active chart. Why these chart-related options are not accessible from the Chart menu remains a complete mystery to me.

To make matters worse, the Options dialog box contains a number of buttons that, when clicked, display other dialog boxes which contain even more options. Dig around in Excel 2002's Options dialog box and you'll find buttons that display these dialogs:

• Advanced Encryption
• Digital Signature
• Macro Security
• AutoCorrect Options
• Modify Colors
• Web Options

Some (but not all) of these dialogs are accessible via other menu options.

But wait! There's more. Don't forget about the Customize dialog box (Tools - Customize). Here you'll find still more options, which are not accessible for the Options dialog box.

I'm not finished yet. When you save a workbook, the Save As dialog box leads to even more options, hidden away on the Save Options dialog box, accessible via Tools menu. I'm sure most Excel users could never find this dialog box even if they knew what they were looking for.

We old-timers have grown accustomed to this user interface nightmare, and we tend to take it in stride. But I have deep and sincere pity for the new user who simply wants to change a few things -- and ends up on an unexpected adventure that may or may not be successful.

Every new release of Excel provides Microsoft with an opportunity to clean up this confusing mess, and they most certainly have the resources to do so. But, for some reason, they just keep cramming more stuff into the junk drawer.

Who Last Opened That File?

Category: General Weirdness | [Item URL]

Most people don't know this: Whenever you open an Excel file, your name is embedded in the file. This occurs even if you don't save the file!

Note: This behavior does not occur if the file is a read-only file.

The name that's stored is the User name listed in the Options dialog box (choose Tools - Options, then click the General tab).

A demonstration

To demonstrate, I performed the following steps:

1. I downloaded an Excel file from the Internet -- one that I have never seen before. It happened to be a file from Jon Peltier's web site.
2. I examined the Excel file using Windows Notepad. As expected, Jon's name is embedded in the file.

3. Then I opened the XLS file in Excel, and immediately closed it. I did not save the file.
4. I then examined the file again with Windows Notepad. Now, my name is contained in the file.

Although the file was clearly modified by Excel, the file modified date was not changed.

Presumably, this is how Excel keeps track of the file reservations -- but it certainly does have some "privacy" implications. A nosy employer could, potentially, see who was the last person to open the Excel files stored on a network server. So if you're snooping around in a company's Excel files, just remember to open the file as read-only if you want to cover your tracks!

What Is Truth?

Category: General Weirdness / VBA Weirdness | [Item URL]

True is true, and False is false, right?

True. Except when you use Excel, things can get a bit confusing. Take a look at the worksheet below. the Range A1:A3 contains Boolean values (TRUE or FALSE).

When you add these three cells, you can get any of three results, depending on the method you use.

Simple addition

The formula in cell A5 uses the addition operator. The sum of these three cells is 2. The conclusion: Excel treats TRUE as 1, and FALSE as 0.

The SUM function

But wait! The formula in cell A6 uses Excel's SUM function. In this case, the sum of these three cells is 0.

It is possible to "force" these logical values to be treated as values by the SUM function. It requires an array formula. Enter the formula below using Ctrl+Shift+Enter, and it will return 2.

=SUM(A1:A3*1)

Oddly, the SUM function does return the correct answer if the logical values are passed as literal arguments. The formula below returns 2:

=SUM(TRUE,TRUE,FALSE)

A VBA function

Although VBA is tightly integrated with Excel, sometimes it appears that the two applications don't understand each other. The formula in cell C7 uses a simple VBA function (listed below), and it returns -2!

Function VBASUM(rng)
    Dim cell As Range
    VBASUM = 0
    For Each cell In rng
        VBASUM = VBASUM + cell.Value
    Next cell
End Function

VBA considers True to be -1, and False to be 0.

Still more

Tom Schipper sent me a workbook that demonstrates even more logical weirdness. The figure below demonstrates. The formula being entered is:

=AND(X,Y,Z)

The names X and Z refer to empty cells (Excel considers them to be FALSE). The name Y refers to a cell that contains a 1 (Excel considers that to be TRUE). Even though two of the arguments for the AND function are FALSE, the formula evaluates to TRUE!

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.

Page 1 of 3 pages
[Next page]