Formatting cells and number formats

Introduction

A sheet of raw numbers is hard to read. Formatting makes it clear: headings stand out, money shows its currency, percentages look like percentages and negative numbers can turn red. The most important idea in this lesson is that formatting only changes how a value is displayed. The value Excel calculates with stays exactly the same, which explains many surprises, such as a total that seems one off.

How it works

The Home tab holds the everyday formatting tools: font, size, bold, fill colour, borders, alignment and wrap text. Format Painter (the paintbrush) copies the format of one cell onto others: click it once to paint one range, or double-click it to keep painting until you press Esc.

Number formats change how numbers appear:

  • General, the default, shows the number as typed.
  • Number with a chosen count of decimal places: 3.14159 can show as 3.14.
  • Currency and Accounting add a currency symbol; Accounting lines up the symbols and decimal points in a column.
  • Percentage multiplies the display by 100: the value 0.25 shows as 25%.
  • Date and Time formats show the same underlying date in different styles.
  • Text makes Excel treat what you type next as text.

All of them are in the Format Cells dialog, opened with Ctrl+1 on Windows or Command+1 on Mac.

Because formatting only affects display, a cell holding 2.456 formatted to two decimals shows 2.46 but still calculates with 2.456. Three such cells can show 2.46 each yet add up to a displayed 7.37 rather than 7.38. If you need the rounded value in calculations, use the ROUND function, covered later.

Custom formats are written in the Custom category of Format Cells. Codes include 0 (always show a digit), # (show a digit only if needed), a comma for thousands separators and text in double quotes. For example, the custom format #,##0" kg" shows 12500 as 12,500 kg while the value stays 12500. A format can have up to four sections separated by semicolons, for positive numbers, negative numbers, zero and text, so 0.00;[Red]-0.00;"-" shows negatives in red and zeros as a dash.

Cell styles on the Home tab apply a named group of formats in one click and keep a workbook consistent.

Example

A sales report needs to look professional:

ABC
1RegionSalesShare
2North1250000.4167
3South1750000.5833

Line by line

  1. Select A1:C1, press Ctrl+B for bold and add a fill colour.
  2. Select B2:B3, open Format Cells with Ctrl+1, choose Currency with 0 decimal places.
  3. Select C2:C3 and click the Percent Style button (or press Ctrl+Shift+%), then click Increase Decimal once.
  4. Select A1:C3 and add All Borders from the Borders menu.

Expected result

The headings are bold on a coloured fill, B2 and B3 show the sales with the currency symbol of your regional settings and thousands separators (for example $125,000), and C2 and C3 show 41.7% and 58.3%. The formula bar still shows 125000 and 0.4167: only the display changed.

In the real world

Finance teams use consistent number formats so that a reader can tell thousands from millions at a glance. Dashboards rely on custom formats to show units such as kg or km without breaking the numbers underneath. Many companies keep a template workbook with their own cell styles so every report looks the same.

Common mistakes

  • Typing the currency symbol or a percent sign into every cell instead of formatting the column.
  • Expecting formatting to round values, then wondering why totals do not match the visible numbers.
  • Formatting numbers as Text, which stops them from being used in sums.
  • Using too many colours, which hides the information formatting was meant to show.

Best practices

  • Store plain numbers and let formats add symbols, separators and units.
  • Use ROUND in a formula when the rounded value matters for later calculations.
  • Keep formatting consistent: one style for headings, one for totals, one for inputs.

Keyboard shortcuts

ActionWindowsMac
Open Format CellsCtrl+1Command+1
BoldCtrl+BCommand+B
ItalicCtrl+ICommand+I
UnderlineCtrl+UCommand+U
Currency formatCtrl+Shift+$Control+Shift+$
Percentage formatCtrl+Shift+%Control+Shift+%
Number format with two decimalsCtrl+Shift+!Control+Shift+!
Date formatCtrl+Shift+#Control+Shift+#
General formatCtrl+Shift+~Control+Shift+~

Summary

Formatting controls appearance: fonts, fills, borders, alignment and number formats. Number formats show currency, percentages, decimals and dates without changing the stored value, and custom formats add units and special rules for negatives and zeros. Format Cells (Ctrl+1 or Command+1) holds every option, and Format Painter and cell styles keep a workbook consistent.

Quick check

1. A cell holds 0.25. What does it show with the Percentage format and no decimals?
2. A cell holds 2.456 formatted to 2 decimals. Which value does a formula use?
3. What does the custom format #,##0" kg" show for the value 12500?
4. Which shortcut opens Format Cells on Windows?

Key takeaways

  • Formatting changes how a value looks, never the value itself.
  • Number formats show money, percentages, decimals and dates without retyping data.
  • Ctrl+1 (Command+1 on Mac) opens Format Cells, where every format option lives.
My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.