BeginnerLesson 5 of 305 min read

Formatting numbers and making sheets readable

Format numbers, percentages and dates, fit columns, style headers and freeze rows so your sheets are easy to read without changing the data.

In this lesson you will

  • Understand that formatting changes the look only
  • Apply number, percent and date formats
  • Fit columns and style headers
  • Freeze the header row

About 14 minutes

Same numbers, easier to read

Dev has built his monthly expense sheet. The numbers are right, but the sheet is hard to read: amounts like 1234.5 and 0.256 sit beside dates like 46086, columns are squashed and every cell looks the same. His sister takes one look and says, ‘I cannot tell what anything is.’ Formatting fixes that. It does not change your data, only how it looks, and a few minutes of formatting makes a sheet something other people will actually use.

Formatting changes the look, not the value

This is the single most important thing to understand. If a cell holds 0.256 and you format it as a percentage, you see 25.6%, but the cell still holds 0.256. A test sheet was really calculated to check: A1 holds 0.256, and =A1*100 returned 25.6, while =A1+0.1 returned 0.356. Formulas always use the real value, whatever is displayed.

Number formats

Select the cells, then use the Home tab or press Ctrl+1 to open Format Cells. The Number tab offers the formats below.

Format Typed value Looks like
General 1234.5 1234.5
Number with commas 1234.5 1,234.50
Currency 1234.5 A currency symbol plus 1,234.50
Percentage 0.256 25.6%
Date 46086 A readable date

The TEXT function can show how formats turn a value into display text. These results were really calculated:

Formula Result
=TEXT(1234.5,”#,##0.00″) 1,234.50
=TEXT(0.256,”0.0%”) 25.6%
=TEXT(DATE(2026,3,5),”dd-mmm-yyyy”) 05-Mar-2026
=TEXT(DATE(2026,3,5),”dddd”) Thursday
=TEXT(7,”000″) 007
=TEXT(1234567,”#,##0″) 1,234,567

Notice the last pattern: #,##0 means ‘show thousands separators and no decimals’. The 0s are digits that are always shown, and # marks optional digits. Patterns like these are how custom formats work. Note that TEXT returns text, so use it only for display, never for numbers you still need to calculate with.

Good to know

Money and thousands separators follow your computer’s regional settings. The same value may appear with different punctuation on another machine.

Making columns readable

  • Double-click the line between two column letters to fit the column to its longest entry.
  • Drag the line to set a width by hand.
  • If you see ##### in a number cell, the column is too narrow. Widen it.
  • Wrap Text (Home tab) shows long text on several lines inside one cell.
  • Alignment: left for words, right for numbers. Centre headings if you like.

Making a header stand out

  1. Select the header row.
  2. Press Ctrl+B for bold, and choose a light fill colour from the Home tab.
  3. Add a bottom border to separate it from the data.
  4. Choose View, Freeze Panes, Freeze Top Row so the headings stay visible when you scroll down.

Keep it simple

  • Use one or two fill colours at most, and keep the text dark on a light background.
  • Do not rely on colour alone. A person who cannot see the colour difference should still understand the sheet.
  • Do not merge cells inside the data; use Centre Across Selection for titles if you must.
  • Show decimals only where they matter. Money usually needs two, counts need none.

Copying formats

The Format Painter (the paintbrush on the Home tab) copies the look of one cell onto others. Click the source cell, click the paintbrush, then click or drag over the target cells. Double-click the paintbrush to paint several places in a row.

Practice

Open the sheet from the previous lessons or make a new expense list with dates, descriptions and amounts. Format the dates as day-month-year, the amounts as numbers with commas and two decimals, make the header bold with a fill colour, widen the columns to fit and freeze the top row. Then type 0.2 in a new cell and format it as a percentage.

Practice answer

0.2 should display as 20%. If it displays 0.2%, you typed 20 and applied the percent format after typing, or the cell was already formatted as a percentage before you typed. In that case the real value is 0.2 percent, so retype it as 20 or 20% as needed.

Common mistake

Trying to format by typing symbols, such as typing 1,234 or Rs1234 as text. Type the plain number and let the number format add commas or symbols.

Number formats you will meet at work

Beyond the basics, a few formats are worth knowing. Accounting lines up currency symbols and decimal points in a column, which is easier to read in financial lists. Short Date and Long Date change how dates look without changing the day. Custom lets you write your own pattern, such as 0.0 for one decimal or 000 for numbers padded with zeros, like order numbers 007. Remember that all of these only change the display, so a formula that refers to the cell still sees the full value.

A clean sheet checklist

  • Headers are bold and clearly different from the data.
  • Numbers have a consistent number of decimals and the right symbol.
  • Columns are wide enough and nothing shows #####.
  • The top row is frozen if the list is longer than a screen.
  • The sheet has a title or a clear sheet tab name, such as Expenses 2026.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

A cell shows 25.6% after formatting. What does it hold?

  1. 25.6 as text
  2. 256
  3. 0.256
  4. A picture

Formatting changes the look, not the value.

What do ##### in a number cell mean?

  1. The column is too narrow
  2. The formula is wrong
  3. The file is damaged
  4. The number is negative

Widen the column.

Which key opens Format Cells?

  1. Ctrl+9
  2. Ctrl+1
  3. Ctrl+Q
  4. Ctrl+0

It opens the dialog for number formats and more.

What does Freeze Top Row do?

  1. Locks the file
  2. Deletes row 1
  3. Sorts the data
  4. Keeps headings visible while scrolling

Headings stay in view.

What kind of value does TEXT return?

  1. A number you can add
  2. Text
  3. A date serial
  4. A chart

Use TEXT for display only.

Summary

  • Formatting changes how a value looks, not what it is.
  • Use number formats instead of typing symbols.
  • Fit columns, style headers and freeze the top row.
  • Keep the design simple and not colour-dependent.

Next lesson

Next: copying formulas and fixing cell references.

Questions

Why does my percentage look wrong?

Check whether the real value is 0.2 or 20. Percent format multiplies by 100 for display only.

Can I change formats for the whole sheet?

Yes. Click the square at the top left of the grid to select everything, then apply the format.

Does formatting affect printing?

Yes, what you see is what prints, so check Print Preview before printing.

Progress is saved in this browser.