29.1 Find — Locating Data Quickly

Find locates cells containing a specific value, text, formula, or formatting. It is the fastest way to navigate large spreadsheets and locate specific records.

Opening Find

• Ctrl+F — opens the Find and Replace dialog on the Find tab
• Home tab → Editing group → Find & Select → Find…

Basic Find

1. Type the search term in the "Find what" box
2. Press Enter or click Find Next to jump to the next match
3. Find All: lists every matching cell in a panel below — click any entry to navigate to it; all matching cells are simultaneously selected
4. Press Esc to close without losing the current position

Find Options — Expanding the Dialog

Click Options >> in the Find dialog to reveal advanced settings:

Wildcards in Find

Find All — Selecting Multiple Cells at Once

29.2 Find & Replace — Changing Data at Scale

Find & Replace locates values and replaces them with new ones. It can operate on the entire workbook in one click and is indispensable for bulk data corrections, terminology updates, and reformatting.

Opening Find & Replace

• Ctrl+H — opens directly on the Replace tab
• Ctrl+F then click the Replace tab

Using Replace

1. Enter the search term in "Find what"
2. Enter the replacement in "Replace with"
3. Replace: replaces one match at a time (use to review each before replacing)
4. Replace All: replaces every match simultaneously — a confirmation message shows how many replacements were made
5. All Find options (Within, Look in, Match case, Match entire cell, wildcards) apply to Replace as well

Replacing Across the Entire Workbook

1. Options >> → Within: Workbook
2. Enter Find what and Replace with
3. Replace All — every sheet is updated simultaneously

Replacing with Nothing (Deleting Text)

Find & Replace Formatting

One of the most underused features — you can find cells by their format and replace with a different format, without touching any cell values:

1. Ctrl+H → Options >>
2. Next to "Find what": click Format… → specify the format to find (e.g., yellow fill)
3. Next to "Replace with": click Format… → specify the replacement format (e.g., green fill)
4. Leave both text boxes blank (you are only changing formats, not values)
5. Replace All

Common Replace Scenarios

29.3 Find & Select Special — Selecting by Cell Type

Go To Special selects cells based on their type or content — all blank cells, all formula cells, all cells with conditional formatting, all constants, all comments, and many more. It is the fastest way to identify and act on a specific category of cells across a large range.

Opening Go To Special

• Home → Editing → Find & Select → Go To Special…
• — or — Ctrl+G then click Special…
• — or — F5 then click Special…

Most Useful Go To Special Options

Filling All Blank Cells — A Key Data Cleaning Technique

29.4 View Options — Controlling What You See

The View tab controls how Excel displays the spreadsheet on screen. These settings affect the display only — they do not change the data.

View Tab — Show Group

Workbook Views

Zoom Controls

• Status bar slider (bottom-right): drag to zoom in or out instantly
• Ctrl+Scroll Wheel: zoom in/out with the mouse wheel
• View → Zoom group → Zoom: set exact percentage
• View → Zoom group → 100%: reset to 100%
• View → Zoom group → Zoom to Selection: zooms so the selected cells fill the screen — useful for presenting a specific chart or table

29.5 Custom Views — Saving Display Configurations

A Custom View saves the current display settings — zoom level, active cell position, print settings, filter settings, hidden rows/columns, and frozen panes — so you can switch between different display configurations with a single click.

Creating a Custom View

1. Set up the display exactly as you want it (zoom level, filters, hidden rows, etc.)
2. View tab → Workbook Views group → Custom Views
3. Click Add…
4. Type a name (e.g., "Management Summary" or "Full Data Entry")
5. Choose what to include:
   • Print settings: saves paper size, margins, and print area
   • Hidden rows, columns and filter settings: saves which rows/columns are hidden and AutoFilter state
6. Click OK

Switching Between Custom Views

1. View → Custom Views → select the view name from the list → Show
2. Excel instantly restores all saved display settings

Custom View Examples

29.6 Freezing Panes — Keeping Headers Visible While Scrolling

Freeze Panes locks specific rows and/or columns in place so they remain visible on screen as you scroll through the rest of the data. This is essential for any dataset that extends beyond one screen — without frozen panes, headers disappear when you scroll down and you lose track of what each column represents.

The Three Freeze Options

Freeze Panes — Active Cell Determines What Is Frozen

Visual Indicator of Frozen Panes

A thick dark line (slightly thicker than normal gridlines) appears along the freeze boundary — between the frozen and scrollable areas. On most themes this appears as a darker grey or black line separating the header row(s) from the data rows.

Unfreezing Panes

• View → Freeze Panes → Unfreeze Panes (this option replaces "Freeze Panes" in the menu when panes are already frozen)
• There is no keyboard shortcut for freeze/unfreeze — it must be done through the View menu

Common Freeze Configurations

29.7 Split Panes — Viewing Two Parts of a Sheet Simultaneously

Split divides the sheet window into two or four separate scrollable panes, allowing you to view and compare different parts of the same sheet at the same time. Unlike Freeze, both panes are independently scrollable.

Splitting the Window

1. Click the cell where you want to split (the split occurs above and to the left)
2. View tab → Window group → Split
3. The sheet divides into up to 4 panes at the active cell position
4. Each pane can be scrolled independently
5. To remove the split: View → Window → Split again (toggles off) — or — double-click the split divider bar

When to Use Split vs Freeze

29.8 Quick Self-Check

Q1: You need to find every cell across all sheets in a workbook that contains the text "Outstanding". How do you configure Find to search the entire workbook, and how do you see all matches at once without clicking "Find Next" repeatedly?

✓ Ctrl+F → Options >> → Within: change from "Sheet" to "Workbook" → type "Outstanding" in Find what → click Find All. Excel lists every matching cell (with sheet name, cell address, and content) in a panel at the bottom of the dialog. Click any entry to navigate to that cell. To select all matching cells simultaneously: click any entry in the results list, then press Ctrl+A — all matches across all sheets are selected. You can then apply formatting or note the sheet locations before closing the dialog.

Q2: You have a column of company names in column A. Some have "(Pty) Ltd" and others have "(Pty) Ltd." (with a trailing full stop). You want to remove both variants from all cells. How do you do this with two Replace operations?

✓ Ctrl+H → First operation: Find what = "(Pty) Ltd." (with full stop), Replace with = (leave completely blank) → Replace All. This removes the version with a trailing full stop. Second operation: Find what = "(Pty) Ltd" (without full stop), Replace with = (blank) → Replace All. This removes the remaining version. The order matters — always replace the longer/more specific variant first. If you replaced "(Pty) Ltd" first, the cells that had "(Pty) Ltd." would become "." (the full stop would remain). Tick "Match entire cell contents" if company names are in the cell alone; leave unticked if "(Pty) Ltd" is part of a longer company name like "Acme (Pty) Ltd Holdings".

Q3: A dataset exported from a payroll system has blank cells in the Department column — the department name only appears in the first row of each department group and the rows below are blank. How do you fill all the blanks so each row shows its department?

✓ Select the department column → Home → Find & Select → Go To Special → Blanks → OK. All blank cells in the column are now selected. Without clicking anywhere else, type = then press the Up Arrow key (to reference the cell directly above the active cell — e.g., if the first selected blank is A3, the formula becomes =A2) → press Ctrl+Enter. This fills every selected blank cell with the formula =cell_above simultaneously. Every blank now shows the department from the cell above it, propagating the group label downward. Finally, to convert the formulas to static values: select the department column again → Ctrl+C → Paste Special (Ctrl+Alt+V) → Values → OK. This prevents the values from changing if rows are later sorted.

Q4: Your spreadsheet has 2,000 rows of data with employee names in column A and salary in column B. After scrolling down to row 1,500, the header row (row 1) has disappeared and you cannot see which column is which. What is the fastest fix, and which cell should you click before applying it?

✓ Click cell A2 (the first data row, first column) → View → Freeze Panes → Freeze Panes. The rule: Freeze Panes locks everything ABOVE and to the LEFT of the active cell. Clicking A2 means "above A2" = row 1 (the header), and "to the left of A" = nothing (A is the first column). Result: row 1 stays permanently visible no matter how far you scroll down. If the data also scrolls horizontally and column A (employee names) should also stay visible, click B2 instead, which freezes both row 1 AND column A simultaneously.

Q5: What is the difference between Freeze Panes and Split, and give a real example of when you would use Split instead of Freeze?

✓ Freeze Panes permanently locks specific rows and/or columns so they do not scroll — used to keep headers visible. The frozen area and the data area scroll as one unit (headers stay, data below scrolls). Split divides the sheet into two or four independently scrollable panes — both can display any part of the sheet. Real example for Split: you have a 1,200-row payroll sheet and you want to compare the formula in row 5 with the formula in row 1,187 to check they are consistent. With Freeze, you can only see row 5 OR scroll down to row 1,187 — not both simultaneously. With Split: position one pane at row 5 and independently scroll the other pane to row 1,187 — both rows visible side by side. Freeze is for navigational headers; Split is for comparison of distant content.

Q6: You want to create two Custom Views of the same employee report: one showing all 15 columns for full data entry, and one showing only columns A, B, and J (name, ID, and salary) at a larger zoom for management review. Describe the steps.

✓ First, set up the full view: ensure all columns are visible, zoom at your preferred level (e.g., 80%) → View → Custom Views → Add → Name: "Full Data Entry" → tick "Hidden rows, columns and filter settings" → OK. Second, set up the management view: hide columns C through I and K through O (right-click each group of column headers → Hide) → set zoom to 110% → View → Custom Views → Add → Name: "Management Review" → tick "Hidden rows, columns and filter settings" → OK. To switch: View → Custom Views → select "Full Data Entry" or "Management Review" → Show. Note: this only works if the sheet does not have an Excel Table (Ctrl+T). If it does, convert to a range first (Table Design → Convert to Range).

✓ Module 29 Complete — You Have Learned:

• Find (Ctrl+F) — basic Find Next; Find All (lists all matches + Ctrl+A to select all); Options: Within (Sheet/Workbook), Search direction, Look in (Formulas/Values/Notes), Match case, Match entire cell, Format button; wildcards (* any chars, ? single char, ~* literal asterisk, ~? literal question mark)
• Find & Replace (Ctrl+H) — Replace one at a time vs Replace All; all Find options apply; replacing across the workbook (Within: Workbook); replacing with nothing to delete text; removing line breaks (Ctrl+J); replacing dollar signs in formulas (Look in: Formulas); Find & Replace Formatting (find by fill/font/format, replace with different format without touching values)
• Common replace scenarios — department rename, province misspelling, company suffix removal, cell reference type change
• Go To Special — opening (Ctrl+G or F5 → Special; or Home → Find & Select); 9 key options (Blanks, Constants, Formulas/Errors, Current region, Conditional formats, Data validation, Visible cells only, Last cell); fill blanks technique (Go To Special → Blanks → type =↑ → Ctrl+Enter then paste as values)
• View options — Show group toggles (Gridlines, Formula Bar, Headings, Ruler); 3 workbook views (Normal, Page Break Preview with draggable breaks, Page Layout with margins/headers); Zoom (status bar slider, Ctrl+scroll, View → Zoom, 100%, Zoom to Selection)
• Custom Views — View → Custom Views → Add; saves zoom, filters, hidden rows/columns, print settings; switching via Show; limitation (not available with Excel Tables)
• Freeze Panes — 3 options (Freeze Panes/Top Row/First Column); the key rule (freezes everything ABOVE and LEFT of active cell); common configurations (A2 = freeze row 1, B2 = freeze row 1 + column A, C3 = freeze rows 1–2 + columns A–B); visual indicator (thick dark border line); Unfreeze via View → Freeze Panes → Unfreeze
• Split Panes — View → Split (toggle); up to 4 independently scrollable panes; Freeze vs Split comparison (headers vs content comparison); removing by toggling or double-clicking divider