Part IV: Using Advanced Excel Features
552
One more thing to keep in mind: If you use the Find and Replace dialog box (Home ➪ Editing ➪
Find & Select ➪ Find), characters that are displayed as a result of number formatting (for example,
a currency symbol) are not searchable by default. To be able to locate information based on for-
matting, use the Search In Value option in the Find and Replace dialog box.Automatic number formatting
Excel is smart enough to perform some formatting for you automatically. For example, if you enter
12.3% into a cell, Excel assumes that you want to use a percentage format and applies it automati-
cally. If you use commas to separate thousands (such as 123,456), Excel applies comma formatting
for you. And if you precede your value with a currency symbol, Excel formats the cell for currency.Note
You have an option when it comes to entering values into cells formatted as percentages. Access the Excel
Options and click the Advanced tab. If the Enable Automatic Percent Entry check box is selected (the default
setting), you can simply enter a normal value into a cell that has been formatted to display as a percent (for
example, enter 12.5 for 12.5%). If this check box isn’t selected, you must enter the value as a decimal (for
example, .125 for 12.5%). n
Excel automatically applies a built-in number format to a cell based on the following criteria:l If a number contains a slash (/ ), it may be converted to a date format or a fraction format.l (^) If a number contains a hyphen (-), it may be converted to a date format.
l If a number contains a colon (:) or is followed by a space and the letter A or P, it may be
converted to a time format.
l If a number contains the letter E (uppercase or lowercase), it may be converted to scien-
tific notation or exponential format.
Tip
To avoid automatic number formatting when you enter a value, preformat the cell with the desired number
format or precede your entry with an apostrophe. (The apostrophe makes the entry text, so number formatting
is not applied to the cell.) n
Formatting numbers by using the Ribbon
The Number group on the Home tab of the Ribbon contains several controls for applying common
number formats quickly. The Number Format drop-down control gives you quick access to 11
common number formats. In addition, the Number group contains some buttons. When you click
one of these buttons, the selected cells take on the specified number format. Table 24.1 summa-
rizes the formats that these buttons perform in the U.S. English version of Excel.