Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 93 ยท Getting Started

Formatting Cells

Learn Excel cell formatting: number, currency, percent and date formats, custom formats, alignment, borders, fills, styles and why formats never change values.

Formatting controls how a cell looks: fonts, colors, borders, alignment, and above all how a number or date is displayed. Good formatting makes a sheet readable at a glance; bad formatting hides errors. The most important lesson in this chapter fits in one sentence: formatting changes what you see, never what is stored.

You will learn the main formatting tools, the number formats you will use daily, how custom number formats work, and how to apply style consistently. Excel cannot run here, so the menu steps are described in text, and a JavaScript stand-in implements a small number-format engine so you can see the rules at work. It is a simplified model of the real thing.

Four kinds of formatting

Number format How a value is displayed.
Font and fill Typeface, size, color, shading.
Alignment Left, center, wrap, merge.
Borders Lines around cells.
1 / 2

Where formatting lives

Most tools are on the Home tab: groups Font, Alignment, Number and Styles. Select cells first, then apply. For the full set of options press Ctrl+1 to open the Format Cells dialog, which has tabs for Number, Alignment, Font, Border, Fill and Protection.

Useful quick actions:

  • Ctrl+B, Ctrl+I, Ctrl+U: bold, italic, underline.
  • Fill color (paint bucket) and Font color: use them sparingly and consistently, for example light yellow for input cells.
  • Borders drop-down: outside borders for tables, a bottom border under headers, a double bottom border for grand totals.
  • Wrap Text shows long text on several lines inside the cell, growing the row height.
  • Merge & Center joins cells into one. Avoid merging inside data tables, because merged cells break sorting, filtering and many selections. Center Across Selection (Format Cells โ†’ Alignment โ†’ Horizontal) gives the same look without merging.
  • Indent, vertical alignment and text rotation are in the Alignment group.
  • Ctrl+Shift+1 to Ctrl+Shift+6 quickly apply number, time, date, currency, percentage and scientific formats.

If a column shows #####, the number or date is too wide for the cell. Widen the column (double-click the header edge) rather than changing the number.

Number formats

The Number group (and the drop-down showing General) applies number formats. The ones to know:

Format Example value Displays as
General 1234.5 1234.5
Number (2 decimals, separator) 1234.5 1,234.50
Currency 1234.5 $1,234.50
Accounting 1234.5 $ 1,234.50 with the symbol aligned left
Percentage 0.256 25.60%
Date (short) 45731 3/15/2025
Time 0.75 6:00:00 PM
Fraction 0.75 3/4
Scientific 12345 1.23E+04
Text 007 007 (stored as text)

The Percentage format multiplies by 100 for display: the cell holds 0.256, you see 25.6%. When you type 25 into a percent-formatted cell, Excel treats it as 25%, so the stored value is 0.25. This confuses many beginners; Percentages, Rounding and Number Precision goes deeper.

Buttons Increase Decimal and Decrease Decimal change only the shown decimals.

Formats do not change values

Say a cell holds 0.4 and you format it with zero decimals. It displays 0. Put that in three cells and SUM gives 1.2, which displays as 1, although the three visible zeros "add up" to zero. Nothing is wrong: the stored values are untouched. This stand-in makes the difference explicit by keeping value and display apart.

JS Stored value versus displayed text

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line is the "visible" sum that people expect and Excel does not compute. If you really need displayed precision, round values with ROUND (see the percentages lesson) rather than relying on formats. Excel's Set precision as displayed option forces this permanently, but it destroys data and is best avoided.

Custom number formats

Press Ctrl+1, choose Custom, and type your own format code. A code is built from symbols:

Symbol Meaning
0 A digit; shows a zero when there is none (000 shows 7 as 007)
# A digit; shows nothing when there is none
, Thousands separator (placed between digit symbols)
. Decimal point
% Multiply by 100 and show a percent sign
"text" Literal text, for example 0" kg"
@ The cell's text
yyyy mm dd mmm dddd Date parts
; Separates up to four sections: positive; negative; zero; text

Examples: #,##0.00 for money without a symbol, 00000 for ZIP codes whose zeros must be visible, 0.0,"K" to show thousands (a trailing comma divides by 1,000), and +0;-0;0 to show a plus sign for positives. The classic accounting style for negatives is #,##0.00;(#,##0.00).

Date format codes are locale-aware, and in some languages the letters differ (for example jjjj for the year in German Excel). The stand-in below uses the English codes.

A tiny number-format engine

This stand-in handles the symbols from the table, including sections and a few date codes. It is far smaller than Excel's real engine, but follows the same ideas.

JS Custom number format stand-in

Press Run (or Ctrl + Enter) to see the console output here.

No output.

You can see the same stored number turned into different texts. The date examples work because a date is a number (45731 here) and the date code just chooses how to print its parts. The negative number with the second section shows no minus sign: when you supply a negative section, you are responsible for how negatives look.

Cell styles, themes and Format Painter

Applying fonts and fills by hand to hundreds of cells is slow and inconsistent. Excel offers three tools:

  • Format Painter (the paintbrush on the Home tab) copies the formatting of one cell to others: click the source, click the brush, then click or drag over the targets. Double-click the brush to paint repeatedly until you press Esc.
  • Cell Styles (Home โ†’ Cell Styles) are named bundles of formatting such as Title, Heading 1, Input, Calculation and Check Cell. Change a style's definition and every cell using it updates. This is the proper way to keep a workbook uniform.
  • Themes (Page Layout โ†’ Themes) define the color palette and heading and body fonts for the whole workbook. If you pick colors from the theme palette rather than fixed RGB values, switching the theme restyles everything.

Paste Special โ†’ Formats (or Ctrl+Alt+V, then T) pastes only formatting, and Clear Formats (Home โ†’ Clear) removes it.

Good habits

  • Use a small, consistent palette. A common convention: blue font for typed inputs, black for formulas.
  • Right-align numbers and left-align text so columns scan easily. Never right-align headings over numbers by accident; align headings the same way as their data.
  • Show the same number of decimals within a column.
  • Prefer number formats to typing symbols: type 5, format as currency, rather than typing $5 or 5 USD as text, which can no longer be added.
  • Leave gridlines on while editing; use borders to define printed tables.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A cell holds 3.14159 and is formatted with two decimal places. What does a formula =A1*2 use?

    Formatting changes only the display. Formulas use the stored value, 3.14159.
  2. 2. Which custom format shows 7 as 007?

    Each 0 in the format forces a digit, so 000 pads 7 with leading zeros.
  3. 3. What does the format code #,##0.00;(#,##0.00) do to the value -1234.5?

    The second section handles negatives. It prints the absolute value in parentheses, with no minus sign.
  4. 4. Why should you avoid merging cells inside a data table?

    Sorting and filtering need uniform cells, and merged cells break that. Center Across Selection gives the same look.
  5. 5. Which tool lets you change one definition and update the look of every cell that uses it?

    A cell style is a named set of formatting. Editing the style updates all cells that use it, while Format Painter only copies once.

Summary

  • Formatting changes the display, never the stored value; formulas always use the real value.
  • Ctrl+1 opens Format Cells; the Number group and Ctrl+Shift+1..6 apply common formats.
  • Number format codes use 0, #, ,, ., %, quoted text and up to four sections (positive; negative; zero; text).
  • Dates are numbers with a date format, so one value can be shown many ways.
  • Use Cell Styles, themes and Format Painter for consistent looks; avoid merged cells in data.

Next: Saving, File Formats and Sharing.