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.
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
- Select the header row.
- Press Ctrl+B for bold, and choose a light fill colour from the Home tab.
- Add a bottom border to separate it from the data.
- 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.
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.
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?
- 25.6 as text
- 256
- 0.256
- A picture
Formatting changes the look, not the value.
What do ##### in a number cell mean?
- The column is too narrow
- The formula is wrong
- The file is damaged
- The number is negative
Widen the column.
Which key opens Format Cells?
- Ctrl+9
- Ctrl+1
- Ctrl+Q
- Ctrl+0
It opens the dialog for number formats and more.
What does Freeze Top Row do?
- Locks the file
- Deletes row 1
- Sorts the data
- Keeps headings visible while scrolling
Headings stay in view.
What kind of value does TEXT return?
- A number you can add
- Text
- A date serial
- 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.
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.
