21.1 Hiding & Unhiding Rows, Columns, and Sheets

Hiding makes rows, columns, or sheets invisible without deleting them. Hidden data is still used in formulas and calculations — it is simply not displayed on screen or printed. This is useful for intermediate calculation columns, confidential data, or simplifying a view for end users.

Hiding Rows

When rows are hidden, the row numbers skip (e.g., you see 4, then 6 — row 5 is hidden). The row numbers on either side of hidden rows appear in blue as an indicator.

Unhiding Rows

1. Select the rows on both sides of the hidden rows: e.g., to unhide row 5, click row 4 header, hold Shift, click row 6 header
2. Right-click the selection → Unhide
3. — or — Ctrl+Shift+9

Hiding Columns

Unhiding Columns

1. Select the columns on both sides of the hidden columns (e.g., click column B, Shift+click column D to unhide column C)
2. Right-click → Unhide
3. — or — Ctrl+Shift+0

Hiding and Unhiding Sheets

• Hide a sheet: right-click the sheet tab → Hide
• Unhide a sheet: right-click any visible sheet tab → Unhide… → select the sheet from the list → OK
• Hidden sheets are still fully functional — formulas on other sheets can reference them
• A workbook must always have at least one visible sheet — you cannot hide the last visible sheet

21.2 Sorting — Basic Single-Column Sort

Sorting rearranges rows in a dataset based on the values in one or more columns. Excel keeps all data on each row together during sorting — the integrity of your records is maintained.

Quick Sort Methods

Sort Order for Different Data Types

21.3 Multi-Level Sorting — Sorting by Multiple Columns

Multi-level sorting sorts by one column first, then breaks ties using a second column, then a third, and so on. For example: sort an employee list first by Department, then within each department by Salary descending.

Using the Sort Dialog

1. Click anywhere inside your data range
2. Data tab → Sort & Filter group → Sort…
3. The Sort dialog opens showing current sort levels
4. Set the first level (primary sort): Column dropdown → choose column; Sort On → Cell Values; Order → A to Z / Largest to Smallest
5. Click Add Level to add a second sort criterion
6. Set the second level (sort within ties of the first)
7. Add more levels as needed (up to 64 levels)
8. Click OK

Sort Dialog Options

Sort On — Sorting by More Than Values

The "Sort On" dropdown lets you sort by things other than cell values:

21.4 Custom Sort Orders

Excel's default alphabetical sort puts "April" before "January" and "Wednesday" before "Monday" — alphabetically correct but meaningless for dates and days. Custom Lists define a sort order that Excel will follow when sorting data.

Built-In Custom Lists

Excel includes four built-in custom lists:

• Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
• Sun, Mon, Tue, Wed, Thu, Fri, Sat
• January, February, March, April, May, June, July, August, September, October, November, December
• Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec

Applying a Custom Sort Order

1. Data → Sort → set the column to sort by
2. In the Order dropdown: select Custom List…
3. The Custom Lists dialog opens → select a built-in list (e.g., January, February…) or create a new one
4. Click OK → OK to apply

Creating a New Custom List

1. File → Options → Advanced → scroll to General → click Edit Custom Lists…
2. In "List entries", type each item on a new line (e.g., CEO, COO, CFO, Director, Manager, Senior, Junior, Intern)
3. Click Add → OK
4. The list is now available in the Sort dialog's Custom List dropdown for this Excel installation

21.5 AutoFilter — Filtering Data with Dropdown Menus

AutoFilter adds dropdown arrows to your header row and lets you show only the rows that match criteria you choose — without deleting any data. Filtered-out rows are simply hidden.

Enabling AutoFilter

• Click any cell inside your data range → Data tab → Sort & Filter → Filter
• — or — Ctrl+Shift+L (toggle AutoFilter on/off)
• If your data is in an Excel Table (Ctrl+T), AutoFilter is enabled automatically and cannot be turned off while the Table exists

Using the Filter Dropdown

1. Click the dropdown arrow ▼ on any column header
2. A panel opens with: sort options at the top, search box, "Select All" checkbox, and a list of all unique values in the column
3. Select/deselect checkboxes to show/hide rows with specific values
4. Click OK
5. Rows not matching the filter are hidden. The dropdown arrow changes to a funnel icon ☰ to indicate an active filter on that column.

Filter Types (by Data Type)

Filtering for Top 10

1. Click the numeric column dropdown → Number Filters → Top 10…
2. The Top 10 AutoFilter dialog opens:
   • Top or Bottom
   • Number of items (e.g., 10, 5, 20)
   • Items (specific count) or Percent (e.g., top 10%)
3. Click OK → only the top/bottom N rows are shown

Searching Within the Filter Dropdown

• The search box in the dropdown filters the list of values as you type — useful when a column has hundreds of unique values
• Supports partial text: type "Cape" to show Cape Town, Cape Winelands, Cape Agulhas, etc.
• Press Enter after typing in the search box to filter to matching values only

Filtering Multiple Columns Simultaneously

• Apply a filter to one column, then apply another filter to a second column — the filters are additive (AND logic: rows must match all active filters)
• Example: filter Region = "Gauteng" AND Status = "Active" → only rows matching both criteria appear

Clearing Filters

21.6 Calculations on Filtered Data

When AutoFilter is active, SUM and other standard functions still include hidden (filtered-out) rows in their results. To calculate only the visible rows, use SUBTOTAL or AGGREGATE.

SUBTOTAL — Filter-Aware Calculations

21.7 Advanced Filter — Complex Criteria Ranges

Advanced Filter allows you to filter using a separately defined criteria range, which enables complex multi-row OR conditions and criterion combinations that AutoFilter cannot handle. It can also copy the filtered results to a different location on the sheet.

Setting Up a Criteria Range

The criteria range is a separate table above your data (or in a blank area of the sheet) with:

• Row 1: column header(s) — exactly matching the header(s) in your data table
• Row 2+: the criteria values. Multiple values in the same row = AND condition; values on separate rows = OR condition

Running Advanced Filter

1. Click anywhere in your data table
2. Data tab → Sort & Filter group → Advanced
3. The Advanced Filter dialog opens:
   • Action: "Filter the list, in-place" (hides non-matching rows) OR "Copy to another location" (copies matches elsewhere)
   • List range: your data table including headers (auto-detected if you clicked inside it)
   • Criteria range: your criteria table including its header row
   • Copy to: (only if copy to another location) — destination cell
   • Unique records only: tick to remove duplicate rows from results
4. Click OK

Extracting Unique Values with Advanced Filter

1. Data → Advanced
2. Action: "Copy to another location"
3. List range: the column you want unique values from
4. Criteria range: leave blank
5. Copy to: a blank destination cell
6. Tick "Unique records only"
7. Click OK → a deduplicated list appears at the destination

21.8 Practical SA Sorting & Filtering Scenarios

Employee Report — Sorted by Department then Salary

Invoice List — Filter Overdue Unpaid Invoices

Sales Data — Top 10 Transactions

Staff List — Filter by Multiple Provinces using Advanced Filter

21.9 Quick Self-Check

Q1: Column H in your spreadsheet contains helper calculation values that should not be visible in the printed report. How do you hide it, and how does a colleague unhide it if needed?

✓ To hide: click the column H header to select the entire column → right-click → Hide (or Ctrl+0). Column H disappears — you will see G then I in the headers. To unhide: select columns G and I together (click G header, hold Shift, click I header) → right-click the selection → Unhide. Column H reappears. If many columns are hidden and you need to reveal all at once: click the Select All button (top-left corner) → Home → Format → Hide & Unhide → Unhide Columns. All hidden columns on the sheet are revealed simultaneously.

Q2: You have an employee dataset with Department (column B) and Salary (column C). You want to sort so that all Finance employees appear first, then IT, then HR, then Operations, and within each department the highest-paid employees are at the top. How do you set this up?

✓ This requires a custom list for the department order AND a secondary sort for salary. First, create the custom list: File → Options → Advanced → Edit Custom Lists → add "Finance, IT, HR, Operations" as a new list. Then: Data → Sort → Level 1: Column = Department, Sort On = Cell Values, Order = Custom List → select the Finance/IT/HR/Operations list → OK. Click Add Level → Level 2: Column = Salary, Sort On = Cell Values, Order = Largest to Smallest. Click OK. The data sorts Finance first (highest paid first within Finance), then IT (highest paid first), then HR, then Operations.

Q3: Your sales data has Region in column B, Amount in column C, and Date in column D. You need to find the total amount for only the visible (filtered) rows when a filter is applied to Region. Your current formula =SUM(C2:C100) always shows the full total regardless of the filter. How do you fix this?

✓ Replace =SUM(C2:C100) with =SUBTOTAL(9,C2:C100). The number 9 tells SUBTOTAL to perform a SUM, but unlike the standard SUM function, SUBTOTAL automatically excludes hidden (filtered-out) rows from the calculation. When no filter is active, SUBTOTAL(9,...) returns the same total as SUM. When a filter is applied (e.g., only Gauteng), SUBTOTAL updates to show only the Gauteng total. The Excel Table Total Row uses SUBTOTAL internally — converting your range to a Table (Ctrl+T) and enabling the Total Row is the simplest way to get this behaviour automatically.

Q4: Using AutoFilter, how would you display only invoices where the amount is in the top 10% of all invoice amounts in column C?

✓ Enable AutoFilter (Data → Filter or Ctrl+Shift+L if not already active) → click the dropdown ▼ on the Amount column (C) header → Number Filters → Top 10… → in the Top 10 AutoFilter dialog: set the first dropdown to "Top", the number to "10", and the third dropdown to "Percent" (not "Items") → click OK. Only invoices whose amounts fall in the top 10 percent of all values in that column will be visible. To switch back: click the funnel icon on column C → "Clear Filter From [Amount]".

Q5: What is the difference between AutoFilter and Advanced Filter? Give a practical example of when you would need Advanced Filter instead of AutoFilter.

✓ AutoFilter adds dropdown arrows to a header row and filters in place using simple criteria: you pick values from a list or apply a single condition (greater than, contains, date range etc.). Multiple columns are filtered with AND logic (all filters must match). Advanced Filter uses a separate criteria range on the worksheet, enabling: (1) OR logic across values (show Gauteng OR Western Cape OR KwaZulu-Natal in one filter operation — impossible cleanly with AutoFilter); (2) AND + OR combined in complex patterns; (3) copying filtered results to a different location on the sheet without disturbing the original data; (4) extracting unique values. Practical example: you need to export all customers from Gauteng with spending over R50,000 OR all customers from Western Cape with spending over R30,000. AutoFilter can only apply one set of conditions — you would need to run two separate filters and manually combine results. Advanced Filter handles this in one operation with a two-row criteria range.

Q6: A dataset has month names in column A (January, February, March… December). When you sort column A A-to-Z, the months appear in alphabetical order (April, August, December…) instead of calendar order. How do you sort them in correct month order?

✓ Use a custom list sort. In the Sort dialog (Data → Sort), set Column = A (month column), Sort On = Cell Values, then in the Order dropdown select "Custom List…". The Custom Lists dialog opens — select the built-in "January, February, March, April, May, June, July, August, September, October, November, December" list → click OK, then OK again. Excel now sorts the months in calendar order regardless of their alphabetical order. This same approach works for abbreviated months (Jan, Feb…) and for days of the week using the built-in Monday–Sunday or Mon–Sun custom lists.

✓ Module 21 Complete — You Have Learned:

• Hiding rows (Ctrl+9, right-click, Ribbon); unhiding by selecting rows on both sides; Ctrl+Shift+9; fix for hidden row 1 (Name Box type 1:1); hidden row number indicator turns blue
• Hiding columns (Ctrl+0, right-click); unhiding by selecting columns on both sides; Ctrl+Shift+0; Select All + Unhide Rows/Columns to reveal all at once
• Hiding/unhiding sheets (right-click tab); workbook must keep at least one visible sheet; hidden sheets still work in formulas
• Basic sorting — Data tab A→Z/Z→A buttons; AutoFilter dropdown sort; right-click sort; sort order per data type (text alphabetical, numbers, dates oldest/newest, logical); always select within data
• Multi-level sort — Sort dialog (Data → Sort); Add Level, Delete Level, Copy Level, priority arrows; My data has headers; Options (case-sensitive, sort left-to-right); Sort On (Cell Values, Cell Color, Font Color, Cell Icon)
• Custom sort orders — built-in lists (months, days abbreviated/full); applying via Sort dialog Order dropdown → Custom List; creating new custom lists (File → Options → Advanced → Edit Custom Lists); SA examples (job grades, provinces, priorities)
• AutoFilter — enabling (Data → Filter or Ctrl+Shift+L); using dropdown checkboxes; Text Filters (contains, begins with); Number Filters (greater than, between, Top 10 by count or percent, above/below average); Date Filters (yesterday/today/this week/last month/quarter/custom); search box for large lists; multi-column filters = AND logic; clearing (one column, all columns, turn off)
• Calculations on filtered data — SUM includes hidden rows (problem); SUBTOTAL with function codes 1–9 / 101–109 (visible rows only); Excel Table Total Row uses SUBTOTAL automatically
• Advanced Filter — criteria range setup (headers match data headers; same row = AND; different rows = OR; combination AND+OR); running via Data → Advanced (in-place vs copy to another location); extracting unique values (Unique records only + Copy to); use cases requiring Advanced Filter vs AutoFilter
• SA office scenarios — employee report sort by department then salary; invoice overdue filter (two columns, SUBTOTAL total); top 10 transactions; multi-province Advanced Filter