Create spreadsheet structures (page setup, headers)

Resources → Subject Notes → Information Technology IT → Lesson Plan

8 Spreadsheets – Creating Spreadsheet Structures (Cambridge 9626)

Objective

Produce a worksheet that prints correctly, is easy to read and is protected against accidental changes. This involves configuring page layout, setting up headers/footers, applying readable formatting (including conditional formatting) and locking header rows.

8.1 Page Layout Settings

All settings below are named exactly as they appear in the Cambridge syllabus.

  • Orientation – Portrait or Landscape. Choose the orientation that best accommodates the width of your data.
  • Paper size – A4, Letter, etc. The size must match the printer that will be used for the exam.
  • Margins – Normal, Wide, Narrow or Custom Margins. Custom margins allow you to respect printer‑specific non‑printable areas.
  • Scaling
    • Fit to Page – prints the selected area on a specified number of pages (e.g., 1 page wide × 1 page deep). Prevents data being split across many pages.
    • Manual scaling – set a percentage (e.g., 85 %). Use when a small amount of extra space is needed but a full “fit” is not required.
  • Print area – Define the exact range that will be printed. This is mandatory for Paper 2 practical questions.
  • Print titles (repeat rows/columns) – Use the Print Titles option to repeat header rows (or key columns) on every printed page. This is a compulsory syllabus element.

Why these settings matter

Correct page layout ensures the worksheet fits the chosen paper, avoids truncation, and respects the printer’s limits. Scaling and print titles keep the information readable when the sheet spans several pages.

8.2 Header & Footer Configuration

  • Open via Page Layout ► Header & Footer (or Insert ► Header & Footer in some versions).
  • Typical elements (choose any three):
    • Page number – e.g. “Page 1 of 3”.
    • Date or time of printing.
    • File name or full file path.
    • Custom text – e.g. company name, report title, “Confidential”.
  • Use the Header/Footer Tools design tab to insert these items quickly.
  • Exam tip: Always preview the header/footer in File ► Print Preview** before submitting Paper 2. A misplaced page number or missing date can cost marks.

Example Header/Footer layout

Header (left‑center‑right)
Company Name Sales Report – Q3 2025 Date: 30‑Dec‑2025
Footer (left‑center‑right)
File: Sales_Q3.xlsx Confidential Page &[Page] of &[Pages]

8.3 Formatting for Readability

  • Header‑row styling – bold text, background colour, centre alignment and thin borders to distinguish column titles.
  • Data alignment – right‑align numbers, left‑align text, centre‑align dates for consistency.
  • Column width – auto‑fit or set a fixed width so that data does not wrap unnecessarily.
  • Named ranges – create a named range for the header row (e.g., HeaderRow) to simplify formula references and to meet AO2 requirements.

Conditional Formatting – AO2 Skill

Conditional formatting highlights cells that meet a condition, making trends or out‑liers instantly visible – a technique frequently examined in the practical.

  • When to use – flag values above a target (e.g., sales > $10 000), colour‑code overdue dates, or shade negative numbers red.
  • How to set up
    1. Select the range to format (e.g., D2:D100).
    2. Go to Home ► Conditional Formatting ► New Rule.
    3. Choose “Format only cells that contain”.
    4. Set the condition (e.g., “greater than” 10 000).
    5. Click Format… and pick a fill colour, font colour, or border.
    6. Press OK** twice** to apply.
  • Exam tip – Include a brief note on the “Setup” sheet describing any conditional‑formatting rules you have applied; examiners look for evidence that you understand why the rule was used.

8.4 Protecting Header Rows

Locking the header row prevents accidental edits while allowing data entry elsewhere. This is part of the AO2 marking criteria.

1. Select the entire worksheet (Ctrl+A) → Right‑click ► Format Cells ► Protection → **Check** “Locked”.
2. Select the data range you want users to edit (e.g., A2:G100) → Format Cells ► Protection → **Uncheck** “Locked”.
3. Go to Review ► Protect Sheet.
   • (Optional) Enter a password.
   • Ensure “Select locked cells” and “Select unlocked cells” are ticked.
   • Click **OK**.

Result: the header row (and any other locked cells) cannot be altered; all other cells remain editable.

8.5 Example Header Row – Sales Report

Product ID Product Name Category Unit Price Quantity Sold Total Revenue
001 Wireless Mouse Accessories $25.00 150 $3,750.00

Formula example: =D2*E2 calculates Total Revenue for each row.

Best Practices (aligned with the syllabus)

  • Use exact syllabus terminology – “page layout settings”, “header/footer configuration”, “print titles”.
  • Keep header rows short, descriptive and consistently formatted.
  • Define the print area and set Print titles before printing – both are examined in the practical.
  • Check the layout with Print Preview** after any change, especially after adding headers/footers.
  • Document any non‑standard page‑setup choices on a separate “Setup” sheet for future reference.
  • Employ named ranges for header rows to aid formula writing and to satisfy AO2 marking.
  • Use conditional formatting sparingly to highlight key information; always note the rule on the “Setup” sheet.
  • Lock header rows (or any cells that must stay unchanged) and protect the sheet before handing the workbook to another user or before the exam.

Assessment Questions

  1. Explain why the Fit to Page scaling option is important when printing large data sets.
  2. List three elements that can be placed in a footer and describe a situation where each would be useful.
  3. Describe, step‑by‑step, how you would protect only the header row while allowing users to edit the rest of the worksheet.
  4. Why must the Print titles (repeat rows/columns) setting be included in a multi‑page printout?
  5. How does defining a named range for the header row help during the exam?

Suggested Diagram

Screenshot of the Page Layout tab highlighting Orientation, Size, Margins, Scaling, Print Area, Print Titles and Header & Footer options.

Create an account or Login to take a Quiz

40 views
0 improvement suggestions

Log in to suggest improvements to this note.