Be able to hide and display rows and columns

Spreadsheets – Hiding and Displaying Rows and Columns (Cambridge IGCSE ICT 0417)

1. Learning Objectives (AO1‑AO3)

• AO1 – Knowledge: Identify the purpose of hiding rows/columns and recall the menu paths and keyboard shortcuts for hide/unhide actions.
• AO2 – Application: Demonstrate how to hide and unhide single, multiple‑contiguous and non‑contiguous rows/columns using the mouse, ribbon/menu commands and keyboard shortcuts in Excel, LibreOffice Calc and Google Sheets.
• AO3 – Analysis/Evaluation: Analyse the effect of hidden rows/columns on formulas, sorting, filtering, conditional formatting and printing, and evaluate when it is appropriate to hide data in a real‑world report.

2. Why Hide Rows or Columns?

Hiding data helps you to:

• Conceal intermediate calculations or raw data that should not appear in the final report.
• Reduce scrolling and visual clutter when working with large data sets.
• Protect sensitive information while still allowing it to be used in formulas.
• Prepare a worksheet for printing – hidden rows/columns are omitted from the printout.

Safety note (e‑Safety – Syllabus Sections 8‑9): Hidden cells may contain personal or confidential data. Ensure you follow your school’s data‑protection policy before sharing or printing a workbook.

3. Prerequisite: Build a Simple Data Model (Section 20 – Spreadsheets)

1. Open a new workbook and rename the first sheet SalesData.
2. Enter headers in row 1:
   

   A1 = Item B1 = Qty C1 = Unit Price D1 = Discount E1 = Total

3. Enter sample data in rows 2‑11 (10 items).
4. Apply basic formatting – bold headers, centre alignment, thin borders.
5. Insert the formula in E2: =B2*C2-D2 and copy it down to E11.

4. Hiding Rows

4.1 Using the Mouse (All Platforms)

1. Select the row heading(s) you wish to hide (click the grey number on the left).
2. Right‑click the selection and choose Hide.

4.2 Using the Ribbon/Menu (Excel / LibreOffice Calc / Google Sheets)

1. Home ► Cells ► Format ► Hide & Unhide ► Hide Rows.

4.3 Keyboard Shortcuts

Tip: In Excel for Windows the column‑hide shortcuts (Ctrl + 0) are disabled by default. Enable them via File ► Options ► Advanced ► Allow shortcuts to hide/unhide columns.

5. Unhiding Rows

5.1 Using the Mouse

1. Select the rows immediately above and below the hidden rows (e.g., to unhide row 5, select rows 4 and 6).
2. Right‑click and choose Unhide.

5.2 Using the Ribbon/Menu

1. Home ► Cells ► Format ► Hide & Unhide ► Unhide Rows.

5.3 Keyboard Shortcuts

5.4 Unhide All Rows

1. Press Ctrl + A twice to select the entire sheet.
2. Choose Unhide Rows from the menu (or use the shortcut above).

6. Hiding Columns

6.1 Using the Mouse

1. Select the column heading(s) you wish to hide (click the grey letter at the top).
2. Right‑click and choose Hide.

6.2 Using the Ribbon/Menu

1. Home ► Cells ► Format ► Hide & Unhide ► Hide Columns.

6.3 Keyboard Shortcuts

7. Unhiding Columns

7.1 Using the Mouse

1. Select the columns immediately to the left and right of the hidden column(s) (e.g., to unhide column C, select B and D).
2. Right‑click and choose Unhide.

7.2 Using the Ribbon/Menu

1. Home ► Cells ► Format ► Hide & Unhide ► Unhide Columns.

7.3 Keyboard Shortcuts

7.4 Unhide All Columns

1. Select the whole sheet (Ctrl + A twice).
2. Choose Unhide Columns from the menu.

8. Impact of Hidden Rows/Columns on Spreadsheet Features

8.1 Formulas & Functions

• Hidden cells remain part of the worksheet; any formula that references them calculates normally.
• Both relative (A2) and absolute (\$A\$2) references work with hidden cells.
• All functions are affected, e.g.:

  • =SUM(B2:B10) includes values in hidden rows.
  • =AVERAGE(C2:C10) averages hidden values as well.
  • =COUNTIF(D:D,">0") counts hidden cells that meet the condition.
  • =VLOOKUP("Widget",A:E,5,FALSE) can return a value from a hidden column.

• Quick‑check: Hide rows 3‑5, then enter =SUM(E2:E11) in any visible cell – the result still includes the hidden rows.

8.2 Sorting & Filtering

• Sorting moves hidden rows/columns together with the visible data, preserving data integrity.
• Filtering automatically hides rows that do not meet the criteria; any rows you have manually hidden remain hidden.

8.3 Conditional Formatting

• Rules are applied to cells regardless of visibility.
• Example: Apply “Cell value > 1000 → fill light red” to column C (Unit Price). Hide column C; the rule stays active and re‑appears when you unhide the column.

8.4 Printing

• By default hidden rows/columns are omitted from the printed page.
• If a hidden range must appear in the printout, either unhide it first or define a Print Area that explicitly includes the hidden cells.
• Tip: Use File ► Print ► Page Setup ► Sheet ► Print → Print entire workbook and verify the preview.

8.5 External Data Links

• When data is imported from CSV files, databases or web queries, hiding columns does not break the link.
• Example: Import a CSV of employee salaries, hide the “Name” column, and still calculate =AVERAGE(D:D) for the salary column. Refreshing the data updates hidden cells automatically.

9. Hiding Non‑Contiguous Rows or Columns

1. Hold Ctrl (Windows) or Cmd (Mac) and click each row or column heading you wish to hide.
2. Right‑click any selected heading and choose Hide.
3. To unhide, repeat the selection of the surrounding rows/columns and use the Unhide command (you may need to repeat the command for each hidden block).

10. Summary of Hide/Unhide Commands

11. Common Mistakes & How to Avoid Them

• Selecting the wrong range: Always include at least one visible row/column on each side of the hidden area before using Unhide.
• Shortcut not working: In Excel for Windows column shortcuts are disabled by default – enable them via File ► Options ► Advanced ► Allow shortcuts to hide/unhide columns. LibreOffice Calc and Google Sheets have them enabled automatically.
• Accidentally hiding the entire sheet: If all rows or columns disappear, press Ctrl + A twice, then choose Unhide All from the menu.
• Forgetting hidden data in calculations: Remember that hidden cells are still part of formulas. Test with =SUM(...) or =AVERAGE(...) after hiding rows/columns.
• Printing without checking: Hidden rows/columns will not appear in the printout. Always preview before printing or unhide if required.

12. Practice Activity (Full‑Syllabus Version)

Complete the tasks below in a new workbook. Record the method you use (mouse, ribbon, shortcut) and answer the reflection questions to address AO3.

1. Create a table with 10 rows and 5 columns (Item, Qty, Unit Price, Discount, Total). Apply bold headers and borders.
2. Enter the formula =B2*C2-D2 in the Total column and copy it down.
3. Hide rows 3‑5 using keyboard shortcuts only.
4. Hide columns B‑C using keyboard shortcuts only.
5. In a visible cell, type =SUM(E2:E11). Note that the result includes the hidden rows.
6. Sort the visible data by Item (menu command). Observe that hidden rows move with the sort.
7. Save, close, and reopen the workbook.
8. Unhide all rows using the ribbon/menu (not shortcuts).
9. Unhide all columns using the ribbon/menu.
10. Preview the sheet for printing. Verify that hidden rows/columns are not shown. Then unhide everything and print a test page.
11. Extension (optional): Import a small CSV file of employee salaries, hide the “Name” column, and calculate the average salary using =AVERAGE(D:D).

Reflection Prompts (AO3)

• How did hiding rows affect the result of your SUM formula? Explain why.
• When preparing a report for a manager, which rows or columns would you choose to hide and why?
• What steps would you take to ensure that hidden sensitive data is not unintentionally printed or shared?

13. Links to Other Spreadsheet Topics (Section 20)

This lesson is part of a larger unit covering all spreadsheet competencies required for the IGCSE:

• Data modelling, cell referencing, and basic formulas (already covered in the prerequisite).
• Functions (SUM, AVERAGE, IF, VLOOKUP, etc.).
• Sorting, filtering, and data validation.
• Charts and graphical representation.
• Worksheet protection and password security.
• Printing set‑up, page layout, and print areas.

Refer to the companion notes for each of these topics to complete the full Section 20 syllabus.

14. Key Takeaway

Hiding and displaying rows and columns is a fundamental spreadsheet skill that supports data organisation, presentation, and efficient workflow. Mastery of mouse, menu and keyboard methods, together with an understanding of how hidden data interacts with formulas, sorting, filtering, conditional formatting and printing, enables you to meet all relevant IGCSE ICT (0417) requirements and produce professional‑looking spreadsheets.