Start Here
Formatting is what turns a plain grid of numbers into something a reader can take in at a glance. A bold heading row, the right kind of number format, a little space, and suddenly the sheet guides the eye instead of fighting it. Most of what people mean by a "professional" spreadsheet is really just this: tidy, consistent, easy to read.
The golden rule is a light touch. A single heading row in bold with one fill colour usually reads better than a riot of colours and fonts.
Clear and accessible are the same thing. A sheet that is easy for anyone to read is also easier for everyone to read.
Fonts, Colours, Borders and Fill
These are the visual basics, the same controls you would recognise from a word processor, gathered in the Font group on the Home tab. First select the cell or range you want to change, then:
- Font and size: pick a typeface and size from the two dropdowns on the left of the Font group. Bold (B), Italic (I) and Underline (U) work as you would expect, and have the shortcuts Ctrl + B, Ctrl + I, Ctrl + U.
- Font colour: the A with a coloured bar under it. Click the small arrow beside it for more colours.
- Fill (background colour): the paint-bucket icon sets a cell's background. This is the usual way to highlight a heading row or flag a cell.
- Borders: the grid icon adds lines around cells. Click its arrow for a menu: Bottom Border, All Borders, Outside Borders, Thick Box Border, and more.
Decide on a simple scheme, one heading style, one highlight colour, and reuse it. A single tidy heading row in bold with a light fill and a bottom border instantly makes a little list look organised.
Colour and contrast: making it readable for everyone
When you pick colours for text and fills, there is one rule that matters more than looking nice: there must be enough contrast between the writing and what is behind it. Pale grey text on a white cell, or white text on a light-yellow fill, is hard to read for everyone, and genuinely unreadable for people with low vision or colour blindness. A strong dark-on-light or light-on-dark pairing reads clearly for all.
This is not just good manners. There is an international standard for it, the Web Content Accessibility Guidelines (WCAG), published by the World Wide Web Consortium. WCAG sets measurable contrast levels (for normal text, a contrast ratio of at least 4.5 to 1 between the text and its background) so "enough contrast" is a number you can check, not a matter of opinion. You can read the standard at w3.org/WAI/standards-guidelines/wcag.
In Australia these guidelines have real weight. Under the Disability Discrimination Act 1992, information and services should be accessible to people with disability, and WCAG is the benchmark Australian governments and many organisations use to meet that obligation. That is why you will see the same contrast rules written into professional and organisational style guides: a workplace adopts WCAG once, in its style guide, and then every document, spreadsheet and web page that follows the guide is accessible by default. Following the house style is following the law without having to think about it each time.
Look along the status bar at the bottom of an Excel workbook and you may see "Accessibility: Investigate". That is Excel's built-in Accessibility Checker telling you it has found something that may be hard for some people to use, such as poor colour contrast, a chart with no alt text, or a sheet with an unhelpful name. Click it (or go to Review then Check Accessibility) and Excel lists each issue with a plain-language explanation and how to fix it. Treat it like a spell-check for accessibility: run it before you share a workbook, and clear what it finds.
Use strong contrast (dark text on a light fill, or the reverse), and never rely on colour alone to carry meaning. If "red" means over budget, also add a word or a symbol, so the message still reaches someone who cannot tell the colours apart. These two habits cover most of what WCAG asks of an everyday spreadsheet.
Number Formats
Here is a small idea that explains a lot of Excel's behaviour: a number format changes how a value looks, not what it is. The cell still holds the same underlying number; you are just choosing how to display it. Format 0.25 as a percentage and it shows 25%, but it is still the value 0.25 underneath, ready to calculate with.
This is why one value can appear as $1,200.00, 1200, or 1.2K depending on the format, and why the right format makes a column instantly readable. To change it, select the cells, go to Home then the Number group, and pick a format:
| A | B | |
|---|---|---|
| 1 | Format | How 1200 looks |
| 2 | General | 1200 |
| 3 | Number (2 decimals) | 1,200.00 |
| 4 | Currency | $1,200.00 |
| 5 | Percentage | 120,000% |
| 6 | Short Date | 24/04/1903 |
The everyday formats worth knowing: Currency or Accounting for money (Accounting lines the dollar signs and decimals up neatly), Percentage for rates, Short Date for dates, and the increase/decrease decimal buttons to show more or fewer decimal places.
=A1+A2 works the same whether A1 looks like 1200 or $1,200.00. The format is just a costume on top of the real number, so your formulas are unaffected by how a cell is dressed.
Resizing Rows and Columns
When text is cut off, or a column shows ##### instead of a number, it is not an error. The cell is just too narrow to show its contents. Widen it and the value reappears.
- Drag to resize: hover over the right edge of a column letter (or the bottom edge of a row number) until the cursor becomes a double-headed arrow, then drag.
- Auto-fit: double-click that same edge and Excel snaps the column to fit its widest entry exactly. To tidy a whole sheet fast, select all columns (Ctrl + A) and double-click any column edge to auto-fit them all at once.
- Wrap text: for a long label, you do not have to widen the whole column. Select the cell and click Wrap Text (Home tab, Alignment group) so the text flows onto several lines within the cell.
Seeing ##### just means "too narrow to display this number". Widen the column, or double-click its edge to auto-fit, and the value comes back. Nothing is lost.
Styles and the Format Painter
Once you have decided how something should look, you do not want to rebuild that look by hand every time. Two tools save you the effort:
- Cell Styles are saved bundles of formatting (font, colour, borders, number format) you apply in one click. On the Home tab, click Cell Styles and pick one. Built-in styles include headings, plus handy status ones like Good, Bad and Neutral, a green "Good" for on-track, a red "Bad" for over-budget, without fiddling with colours.
- Format Painter copies the look of one cell and paints it onto others. Click a cell that already looks right, click the paintbrush (Home tab, Clipboard group), then drag across the cells you want to match. Double-click the paintbrush to lock it on for several separate places, and press Esc when done.
Both keep a workbook looking consistent, which is most of what "professional" means in a spreadsheet. Format Painter copies the look only, including number format and borders, not the contents.
Turning a List into a Table
An Excel Table is more than a nicely formatted range. When you turn your data into a Table, Excel treats it as a single smart object: it adds banded rows and filter buttons, it grows automatically when you add a row, and it makes later jobs (sorting, filtering, charts) easier. If you take one formatting habit from this session, make it this one.
- Click anywhere inside your data (it should have a header row).
- Press Ctrl + T (or Insert then Table).
- Check "My table has headers" if your top row is column titles, then OK.
Your data becomes a Table: banded rows, a coloured header, and a small filter arrow on each heading, which sets you up nicely for sorting and filtering in Session 4. With a cell in the Table selected, the Table Design tab appears, where you can pick a different Table Style to recolour the whole thing in one click.
Choosing the Right Chart
Excel offers dozens of chart types, but the choice is easier than it looks, because the message you want to send points to the right one. Pick the chart that matches the question your reader is asking, and the data almost explains itself. Three types cover the vast majority of everyday needs:
Column or Bar
Best for comparing amounts across categories: spend per department, items per month, stock per product. Columns stand up; bars lie down. Use bars when the category names are long, as they read better sideways. This is the safest, most readable default.
Line
Best for showing a trend over time: sales across the year, temperature by day, hours week by week. Put time going across the bottom and the line shows whether things are rising or falling.
Pie
Best for showing how a single total splits into a few slices: how a budget divides across categories. Use sparingly: only for one whole split into a few parts, and never when you need to compare precise values.
Ask "what is the message?" Comparing things? Column or bar. Change over time? Line. Slices of one total? Pie, and only then. When in doubt, start with a column chart. And a chart should make one point clearly: if you are tempted to cram several messages into one, make two charts instead.
Making and Tidying a Chart
Making a chart is mostly a matter of selecting the right data and clicking once. The skill is in the tidying up afterwards.
Create it
- Select your data, including the row and column headings (they become the chart's labels). For most charts you want one column of categories and one or more columns of numbers.
- Go to the Insert tab and pick a chart from the Charts group. Hovering over a type shows a live preview. If unsure, click Recommended Charts and Excel suggests good fits.
- The chart appears on the sheet, linked to your data. Change a number and the chart redraws by itself.
Tidy it
Click the chart and the Chart Design and Format tabs appear, with a small + button (Chart Elements) beside it:
- Title: click the title text and rename it to something meaningful, not "Chart Title".
- Chart Elements (the + button): tick or untick Axis Titles, Data Labels, Legend, Gridlines. Add data labels when the exact values matter; remove the legend when there is only one series.
- Move and resize: drag the chart to reposition it; drag a corner handle to resize.
- Alt text: right-click the chart, choose Edit Alt Text, and write a sentence describing what it shows. This helps screen-reader users and is good practice for anything you share.
Give a chart a meaningful title and label the axes so it makes sense on its own. Remove gridlines and legends you do not need. Do not rely on colour alone to tell series apart; add labels so it reads for everyone, including people who cannot distinguish certain colours.
Try It at the Keyboard
Open the starter workbook for this session, which has an unformatted stock list and a small monthly-spend table ready to chart.
Format a stock list, then chart the spend
- Make the heading row bold, give it a light fill colour, and add a bottom border. Use the Format Painter to copy that look if you have more than one heading.
- Select the money column and set it to Currency from the Number group. Notice the maths underneath does not change.
- If you see
#####anywhere, double-click the column edge to auto-fit and the numbers reappear. - Select the monthly-spend table (headings included), go to Insert, and add a column chart. Rename its title to something meaningful.
- Turn on Data Labels from the + button, then add alt text describing the trend. Now try the same data as a line chart and see how the message changes.
Compare your sheet with the solution workbook to see one tidy way to format and chart the same data.
Knowledge Check
A few quick questions to see what has landed. Have a think first, then click "reveal". No mark, no rush.
Q1You format a cell holding 0.25 as a percentage and it shows 25%. Has the value changed?reveal
No. A number format changes only how a value looks, not what it is. The cell still holds 0.25 underneath and will calculate with that. The percentage is just a costume on top.
Q2A column shows ##### instead of your numbers. What has happened, and how do you fix it?reveal
The column is too narrow to display the number. It is not an error and nothing is lost. Widen the column by dragging its edge, or double-click the edge to auto-fit, and the value reappears.
Q3You want to compare how much each department spent this month. Which chart type is the safe default?reveal
A column or bar chart. They are best for comparing amounts across categories, and the column chart is the safest, most readable default. Use a bar (lying down) if the department names are long.
Q4When is a pie chart the right choice, and when is it not?reveal
A pie chart is right for showing how a single whole splits into a few slices, like how a budget divides across categories. It is the wrong choice when you have many slices, or when you need to compare precise values, because people are poor at comparing angles. Use a column chart instead.
Q5Name two things that make a chart both clearer and more accessible.reveal
Any two of: a meaningful title and labelled axes so it makes sense on its own; data labels when exact values matter; not relying on colour alone to tell series apart; and adding alt text describing what the chart shows. Clear and accessible are the same goal.