Be able to adjust row height, column width and cell sizes so that all data, labels, and formulae is fully visible

20 Spreadsheets – Adjusting Row Height, Column Width and Cell Size

Learning Objective (AO1‑AO3)

• AO1 – Knowledge and understanding: Identify the purpose of row height, column width and cell‑size adjustments and describe the units used (character units, points, pixels).
• AO2 – Application of skills: Demonstrate how to change column widths, row heights and overall cell size using drag, Auto‑Fit, the Ribbon and numeric entry. Apply related tools (Wrap Text, Merge, Shrink to Fit, Alignment) to ensure all data, labels and formula results are fully visible.
• AO3 – Analysis and evaluation: Evaluate a worksheet for hidden or truncated information, decide which formatting technique is most appropriate, and justify choices for print‑layout (scaling, margins, gridlines, headers/footers, page breaks).

Why Adjust Sizes?

• Prevents hidden or truncated data (e.g. “####”).
• Improves readability and the professional appearance of reports.
• Ensures formula results are displayed in full, aiding auditing.
• Controls how the sheet prints – avoids unwanted line‑breaks, cut‑offs, or extra pages.

Key Units

• Column width: measured in character units. One unit = the width of the digit “0” in the default font.
• Row height: measured in points. One point ≈ 1⁄72 inch.
• Conversions (optional):

  • Width (inches) = Pixels ÷ 96
  • Height (inches) = Points ÷ 72

Changing Column Width

1. Drag the column border – Hover over the right edge of the column heading until the cursor becomes a double‑arrow, then drag.
2. Auto‑Fit (optimal width) – Double‑click the right edge of the column heading or select the column(s) and choose Home ▶ Format ▶ AutoFit Column Width. The width adjusts to the longest entry.
3. Set a specific width – Select the column(s), go to Home ▶ Format ▶ Column Width, and type a numeric value (character units).
4. Width box on the Ribbon – In the Home tab, the “Width” field lets you type the exact character‑unit value.

Changing Row Height

1. Drag the row border – Hover over the bottom edge of the row number until the cursor becomes a double‑arrow, then drag.
2. Auto‑Fit (optimal height) – Double‑click the bottom edge of the row number or select the row(s) and choose Home ▶ Format ▶ AutoFit Row Height. Height adapts to the tallest cell content (including wrapped text).
3. Set a specific height – Select the row(s), choose Home ▶ Format ▶ Row Height, and enter a numeric value in points.
4. Height box on the Ribbon – The “Height” field in the Home tab allows direct entry of point size.

Adjusting Both Dimensions Simultaneously

Select the required rows and columns together (click the top‑left corner of the sheet or drag across the headings) and apply any of the methods above. The chosen setting is applied to every selected row and/or column, giving a uniform grid.

Formula‑Focus Box (AO2)

Entering a formula

=C2*D2   // total price = unit price × quantity

Absolute vs. relative references

• Relative: C2 changes when copied.
• Absolute: \$C\$2 stays fixed.

Naming a range (useful for clarity and for later reference)

1. Select the cells to name (e.g., C2:C16).
2. Enter a name in the Name Box left of the formula bar, e.g., UnitPrice.
3. Use the name in a formula: =SUM(UnitPrice*Quantity).

Data‑Manipulation Tools (AO2)

• Sort A‑Z / Z‑A – Select a column, then Data ▶ Sort A‑Z (or Z‑A) to reorder rows.
• Filter – Choose Data ▶ Filter. Click the dropdown arrow in a column heading to display only rows that meet a condition (e.g., totals > £100).
• Conditional Formatting – Highlight cells that meet criteria:

  1. Select the range (e.g., column E).
  2. Go to Home ▶ Conditional Formatting ▶ Highlight Cells Rules ▶ Greater Than….
  3. Enter 100 and choose a format (e.g., light red fill).

Presenting Data – Page Layout (AO3)

• Gridlines – Show or hide via View ▶ Gridlines. For printed reports, enable Page Layout ▶ Sheet Options ▶ Print ▶ Gridlines.
• Headers & Footers – Insert via Insert ▶ Header & Footer. Common items: file name, sheet name, page number, date.
• Page Breaks – Insert manually with Page Layout ▶ Breaks ▶ Insert Page Break or view existing breaks in View ▶ Page Break Preview.
• Print Area – Select the range and choose Page Layout ▶ Print Area ▶ Set Print Area.
• Scaling – Use Page Layout ▶ Scale to Fit. Options:

  • Fit to 1 page(s) wide by 1 tall
  • Adjust % scaling manually.

• Orientation & Margins – Choose Portrait or Landscape; set margins (Normal, Wide, Custom) via Page Layout ▶ Margins.
• Print Preview – Always check File ▶ Print before printing; adjust column/row sizes if any data is hidden.

Additional Cell‑Formatting Tools

• Wrap Text – Forces long text onto multiple lines; row height expands automatically.
• Merge Cells – Combine adjacent cells (useful for titles). Avoid merging cells that contain data or formulas that need to be referenced.
• Shrink to Fit – Reduces font size so content fits the current cell dimensions. Use only when other size adjustments are impractical.
• Alignment – Horizontal (Left, Centre, Right) and vertical (Top, Middle, Bottom) alignment affect appearance without changing size.

Practical Checklist (AO3)

1. Select the whole sheet (Ctrl + A).
2. Apply Auto‑Fit Column Width (Home ▶ Format ▶ AutoFit Column Width).
3. Apply Auto‑Fit Row Height (Home ▶ Format ▶ AutoFit Row Height).
4. If any cell still truncates data, enable Wrap Text for that column or cell.
5. Check for unintended merged cells; un‑merge if they hide data needed for calculations.
6. Review formulas for error symbols (e.g., #VALUE!) – hidden or truncated cells can be the cause.
7. Set the print area, orientation, margins, scaling, gridlines, headers/footers and page breaks as required.
8. Use Print Preview to confirm that everything is visible on the printed page.

Common Pitfalls and How to Avoid Them

Summary Checklist

• All columns set to optimal width (Auto‑Fit or specific character‑unit value).
• All rows set to optimal height (Auto‑Fit or specific point value).
• Wrap Text applied where necessary.
• No unintended merged cells hiding data.
• Formulas display full results, not truncated symbols.
• Print area, orientation, margins, scaling, gridlines, headers/footers and page breaks configured to avoid cut‑offs.

Cross‑Reference

See Sections 11‑16 of the Cambridge IGCSE ICT 0417 syllabus for related concepts such as file management, basic formatting, and the use of the Ribbon.

Practice Activity (AO1‑AO3)

Complete the tasks below in a new worksheet. Record the exact steps you used for each item and justify any formatting choices.

1. Enter a list of 15 product names of varying length in column A. Adjust the column width so every name is fully visible without wrapping.
2. In column B, enter a description for each product that requires at least three lines of text. Apply Wrap Text and ensure each row height expands automatically.
3. Enter unit prices in column C and quantities in column D. In column E calculate the total price with the formula =C2*D2. Make column E wide enough to display the currency symbol and two decimal places.
4. Merge cells A1 to D1 to create a title “Product Inventory”. Centre the title horizontally and vertically, and verify that it is fully visible.
5. Set the print area to include only the data table, choose Landscape orientation, enable gridlines, add a footer with “Page &[Page] of & [Pages]”, and apply “Fit to 1 page wide”. Use Print Preview to check that no data is cut off, then adjust any column or row sizes that are still problematic.
6. Sort the table by total price (column E) in descending order, then apply a conditional format to highlight any total > £100 in green.