Be able to enhance a spreadsheet including text colour, cell colour, bold, underline, italic, shading

Topic 20 – Spreadsheets: Enhancing a Spreadsheet

Cross‑reference

This lesson covers only the spreadsheet component of the Cambridge IGCSE ICT syllabus (Section 20). For web authoring (Section 21) see Topic 21 – Website Authoring.

Learning objectives (aligned with AO1‑AO3)

• AO1 – Recall: Identify the purpose of text formatting, cell colour, number formats, borders, styles and page‑setup options.
• AO2 – Apply: Demonstrate how to apply formatting, write basic formulas, use absolute/relative references, named ranges and lookup functions, and set up a worksheet for printing.
• AO3 – Analyse/Evaluate: Use conditional formatting, sort/filter data, and error‑checking functions to highlight trends, out‑liers and potential mistakes in a data set.

Pre‑task checklist (File‑management & proofing)

1. Save the workbook in the correct folder (e.g., ICT/Year 12/Spreadsheets/Topic20.xlsx).
2. Use a consistent cell style for headings, sub‑headings and data (see Section 4).
3. Run Spell Check on any text fields (Review → Spelling in Excel; Tools → Spelling in Google Sheets).
4. Lock cells that must not be edited (Section 6) and protect the sheet if required.

1. Why enhance a spreadsheet?

• Readability: Colour, alignment and number formats let the eye locate information quickly.
• Emphasis: Highlight key figures, trends or out‑liers.
• Professional presentation: Consistent styles, borders and a print‑ready layout are required for exam tasks and real‑world reports.

2. Text formatting

Changes the appearance of the characters inside a cell without affecting the background.

1. Select the cell or range.
2. Use the toolbar buttons or the keyboard shortcuts in the table below.

2.1 Applying multiple styles

You can combine styles (e.g., bold + italic + red text) by selecting the cell and clicking each toolbar button or using the corresponding shortcuts.

3. Cell (background) colour & shading

• Cell colour: fills the whole cell background – useful for grouping or drawing attention.
• Shading (patterned fill): adds a pattern over the background – handy when colour printing is unavailable.

1. Select the cell(s).
2. Click the Fill colour (paint‑bucket) button for a solid colour.
3. For patterns, open Format Cells (right‑click → Format Cells or Ctrl + 1) → Fill/Pattern tab → choose a pattern and foreground/background colours.

4. Number formatting & alignment

• Currency: Format Cells → Number → Currency (e.g., £1,200.00).
• Percentage: Format Cells → Number → Percentage (e.g., 75%).
• Decimal places & thousand separators: Use the “Increase Decimal” / “Decrease Decimal” buttons or set the exact number in the Format Cells dialog.
• Alignment:

  • Text – left‑align (default).
  • Numbers – right‑align.
  • Currency/percentage – right‑align with the symbol displayed.
  • Keyboard shortcuts (Excel): Alt + H, A, L (left), Alt + H, A, C (centre), Alt + H, A, R (right).

5. Cell styles & themes

Cell styles apply a predefined set of formatting (font, colour, borders, number format) with one click.

1. Open the Cell Styles gallery (Home → Styles).
2. Choose a built‑in style such as “Heading 1” for column headings.
3. To create a custom style: right‑click a style → New Cell Style… → define the desired formatting.

6. Borders & protection

• Borders: Select cells → Border button → pick line style (outline, inside, thick, double).
• Cell protection: Format Cells → Protection tab → check “Locked”. Then protect the worksheet (Review → Protect Sheet) with a password if required.

7. Formulae, functions & cell‑referencing

7.1 Order of operations

All formulas start with =. Excel follows the standard hierarchy (PEMDAS):

= A1 + B1 * C1          // multiplication before addition
= (A1 + B1) * C1        // parentheses change the order

7.2 Absolute vs. relative references

7.3 Common functions required by the syllabus

7.4 Hands‑on lookup example (VLOOKUP)

Suppose you have a product list (A2:B6) and you need to retrieve the price for a product entered in D2.

A2:A6   = Product Code
B2:B6   = Unit Price (£)
=VLOOKUP(D2,\$A\$2:\$B\$6,2,FALSE)

The formula searches column A for the code in D2 and returns the corresponding price from column B. Replace VLOOKUP with XLOOKUP for a more flexible solution.

7.5 Error‑checking functions (AO3)

• IFERROR(value, valueiferror) – returns a friendly message instead of #N/A or #DIV/0!.
• ISNUMBER(value) – tests whether a cell contains a number (useful before performing arithmetic).

Example: =IFERROR(VLOOKUP(D2,\$A\$2:\$B\$6,2,FALSE),"Code not found")

7.6 Named ranges

1. Select the cell or range.
2. Enter a name in the Name Box (left of the formula bar) and press Enter. Names must start with a letter and contain no spaces.
3. Use the name in formulas, e.g., =SUM(Sales) where “Sales” refers to the range B2:B10.

8. Conditional formatting (AO3)

Automatically changes a cell’s appearance based on its value.

1. Select the range.
2. Home → Conditional Formatting → choose a rule:

   • Highlight Cells Rules → Less Than 0 → red text (for negative numbers).
   • Data Bars, Colour Scales, Icon Sets for visual trends.

3. Use Manage Rules to edit conditions, change colours or set stop‑if‑true.

9. Data manipulation – sort, filter & search

9.1 Sorting

• Select any cell in the data range.
• Data → Sort → choose column, order (A‑Z / Z‑A) and confirm “Expand the selection”.

9.2 Filtering

1. Select the header row.
2. Data → Filter → drop‑down arrows appear in each header cell.
3. Basic filter: choose “Number Filters → Greater Than…” and enter 2000 to show sales > £2 000.
4. Advanced filter (multiple criteria): Data → Advanced → set “Criteria range” with separate rows for each condition (e.g., Sales > 2000 and Profit ≥ 0).

9.3 Searching

• Press Ctrl + F (Windows) / ⌘ + F (macOS) to open the Find dialog.
• Enter the text or number, click “Find All” for a list of matches, or use “Replace” to edit multiple cells.

10. Preparing a spreadsheet for printing (AO2)

Follow the checklist before sending a workbook to the printer.

• Page orientation: Page Layout → Orientation → Portrait or Landscape.
• Margins: Page Layout → Margins → Normal / Wide / Custom.
• Scaling: Page Layout → Scale to Fit → “Fit Sheet on One Page” or set a specific percentage.
• Print area: Select the range → Page Layout → Print Area → Set Print Area.
• Headers/Footers: Insert → Header & Footer → add page numbers, file name, date, or custom text.
• Print‑preview tip: Always open Print Preview (File → Print) and verify that no columns or rows are hidden; use “Fit to page” if the sheet is wider than the paper.

11. Practical example – applying multiple enhancements

The table below shows a simple sales report before formatting. Follow the numbered steps to produce the “After” version.

Formatting steps

1. Header row: Apply the built‑in “Header” style (bold, dark‑grey fill, centre‑aligned).
2. Revenue column: Make the numbers bold and set text colour to dark green.
3. Best performer note: Apply a light‑yellow fill to the cell containing “Best”.
4. Target met note: Underline the text.
5. Units Sold column: Use a light‑blue diagonal‑stripe shading pattern (Format Cells → Fill → Pattern).
6. Number format: Apply Currency (£) with no decimal places to the Revenue column.
7. Borders: Add a thin bottom border to the header row and a box border around the whole table.

12. Keyboard shortcut summary

13. Checklist for students

1. Can you change text colour, apply bold/italic/underline, and combine multiple styles?
2. Can you set a cell’s background colour and add a patterned shading?
3. Do you know how to format numbers as currency, percentages and control decimal places?
4. Can you add borders and apply a cell style for consistent headings?
5. Are you able to write basic formulas, use absolute/relative references, and employ common functions (SUM, IF, VLOOKUP, etc.)?
6. Can you create and use named ranges?
7. Do you know how to apply conditional formatting (e.g., red text for negative values)?
8. Can you sort, filter (including advanced filter) and search data efficiently?
9. Are you comfortable preparing a worksheet for printing (orientation, margins, scaling, headers/footers, print‑preview)?
10. Have you saved the file correctly, applied a consistent style, and run spell‑check before submission?

14. Assessment question

Using the data below, format the spreadsheet to meet all of the following requirements.

• Header row – bold with a light grey fill.
• All negative numbers – red text (use conditional formatting).
• Total row – bold, underlined and light green fill.
• “Profit” column – apply a diagonal‑stripe shading pattern.
• Currency format (£) with no decimal places for Sales, Cost and Profit.
• Right‑align all numeric cells.

15. Suggested diagram