Formatting cells

Formatting changes how cells look without changing what they hold. Every command on this page acts on the whole selection. For formatting that changes with the values, see Conditional formatting; for colours and fonts that apply to the whole workbook, see Themes and backgrounds.

Font

The Font group on the Home tab has:

Bold, italic and the underlines are toggles. When every selected cell already has the style, pressing the button turns it off; otherwise it turns it on for all of them.

Borders

Open Home › Font › Borders and choose an arrangement. Borders apply to the selection as a block: Outline draws one box round the whole selection, not a box round each cell.

Alignment

The Alignment group on the Home tab has:

Wrap text

After turning on Wrap text, choose Home › Cells › Format › AutoFit row height to fit the row to the wrapped text, or drag the row’s bottom edge.

Merge cells

1. Select the cells.
2. Click the merge button in Home › Alignment.

Only the top-left cell’s contents are kept. The other cells in the block are cleared. Merging does not centre the text; use Centre as well if you want that.

To unmerge, select the merged cell and click the merge button again. Selecting a block that overlaps merged cells and clicking the button unmerges them.

Merge across merges each row of the selection into one wide cell — for example, five headings each four columns wide rather than one tall cell. It needs a selection at least two columns wide; otherwise the status bar says Select at least two columns to merge across.

Number formats

A number format controls how a value is displayed. The value itself is unchanged: 0.25 formatted as a percentage shows 25.00% and is still 0.25 in formulas.

Number format list

Choose a format from the number format list in Home › Number:

When a cell has a format that is not in the list, the list shows Custom. To set a custom format, use Format cells….

Dates and times are numbers: whole days counted from 1 January 1900, with the time as a fraction of a day. The example dates above show 1234.5 read as a date.

Other number buttons

Custom format codes

A format code can have up to four sections separated by semicolons: positive numbers; negative numbers; zero; text. For example, #,##0.00;[Red]-#,##0.00;"–";@ shows 1234.5 as 1,234.50, −1234.5 as a red -1,234.50, and zero as –. With two sections, the second is for negative numbers and zero uses the first.

Cell styles

Open Home › Styles › Cell styles and choose a named look:

A style replaces the cells’ other formatting — font, colours, borders and alignment — and keeps their number format. It is applied as ordinary formatting, so cells don’t stay linked to the style.

The Format Cells dialog

The Format cells dialog holds every formatting option in one place. Open it with Home › Number › Format cells…, or with Borders › More borders…, which opens it at the Border tab.

The dialog’s title shows the address of the selection, such as Format cells — B2:D9. It starts from the current cell’s formatting. Switch between the tabs with the buttons along the top: Number, Alignment, Font, Border, Fill and Protection.

Click Apply to apply everything you set, on every tab, as one undo step. Click Cancel to close without changing anything.

Number tab

Alignment tab

Font tab

To change the font name or size, use the Font group on the ribbon.

Border tab

The Border tab works like a pen: choose a line and a colour, then click the edges to draw it on.

1. Under LINE, choose None, Thin, Medium, Thick, Dashed, Dotted or Double.
2. Under LINE COLOUR, choose a colour, or the crossed box for the default.
3. Under EDGES, click the edges to draw: Top, Bottom, Left, Right, Diagonal ↘ and Diagonal ↗. When the selection is more than one row tall, Across draws the lines between the rows; when it is more than one column wide, Down draws the lines between the columns.

Clicking an edge that already has exactly that line removes it. The preview box beside the edge buttons shows the result. The pen starts as the first line already drawn on the selection, so your first click on an existing edge takes it off.

The PRESETS buttons set several edges at once:

Edges belong to the block: Top is the top of the whole selection, not of each cell. The two diagonals are drawn corner to corner in every selected cell; if you set both with different lines, both use the Diagonal ↘ line.

Fill tab

BACKGROUND — no fill, or one of ten colours.

Protection tab

PROTECTION — Locked. Locked cells can’t be typed into once the sheet is protected. Every cell starts locked, so protecting a sheet without unlocking anything locks all of it. See Protecting sheets and workbooks.

Copy formatting

• Use the format painter in Home › Clipboard to copy formatting from one cell to others.
• Use Paste › Formatting only to paste only the formatting of copied cells.
• Use Fill › Across worksheets — formats only to copy formatting to the same cells on every other sheet.

See Entering and editing data.

Remove formatting

Select the cells and choose Home › Editing › Clear › Clear formats. The values stay.