Microsoft Office Tutorials and References
In Depth Information
Formatting Cells and Ranges
The commands in this group allow you to pick from a list of predeined number
formats, apply a custom currency style, display a value in Percentage format, show or hide
thousands separators (a comma for systems using U.S. regional settings), and increase or
decrease the number of visible decimal points.
INSIDE OUT Create custom formats with cell styles
The Number group on the Home tab mixes commands for predeined number formats
and cell styles, which can be used to apply number formats. It also provides access to
the Format Cells dialog box, where you can customize formats manually. The three
buttons below the Number Format list apply the Currency, Percent, and Comma styles,
respectively. (Confusingly, the Currency style actually uses the Accounting number
format. Go figure.) You can see these styles in their normal location by expanding the Cell
Styles list (from the Styles group on the Home tab) and then looking in the Number
Normally, clicking the Percent button applies the Percentage format with no decimal
places. If you want to replace that default result with one that applies the Percentage
format with one decimal place, right-click the Percent style in the Cell Styles list and
click Modify. Click the Format button and adjust the value in the Decimal Places box.
Save your changes, and the result is complete. You can use this same technique to
create additional styles, which you can add to the Quick Access Toolbar or access from the
Cell Styles list. Note that this change applies only to the current workbook unless you
save these changes to the default workbook template.
If none of the predeined formats work for you, try rolling your own. We explain how to do
this in “Creating Custom Cell Formats” on page 459.
It’s important to understand that most of the formatting options affect only the display of
the data stored in that cell. You can change the number of decimal places and add a
currency symbol to a number format, but the underlying data remains unchanged. Likewise,
changing the date format for a cell does not change the date serial number displayed in
If you enter a number or text, Excel applies the General number format. In most cases, this
means you see exactly what you typed. If a number you type is too wide to it in the
current cell and the width of the current column has not been manually set, Excel expands the
column width to try to accommodate your entry. If you enter a formula, the General format
displays up to 11 digits, including the decimal point, rounding the displayed result if
necessary to show fewer decimal places than are actually stored for the value.