5.1 Rows, Columns & Sheets — A Quick Reference

5.2 Selecting Rows & Columns

Before you can insert or delete, you first need to select the correct row(s) or column(s). The selection determines where the insert or delete happens.

Selecting Rows

Selecting Columns

5.3 Inserting Rows

When you insert a row, it appears above the selected row. All rows below the insertion point shift down by one (or more, if you insert multiple rows at once). Formulas referencing cells in those rows automatically update their references.

Method 1 — Right-Click (Fastest)

1. Click the row number where you want to insert a row above (e.g., click row 5 to insert above row 5)
2. Right-click the selected row number
3. Click Insert
4. A new blank row is inserted above row 5. The old row 5 becomes row 6.

Method 2 — Ribbon

1. Select the row number(s)
2. Home tab → Cells group → Insert dropdown → Insert Sheet Rows

Method 3 — Keyboard Shortcut

1. Select the row number(s): click the row header or press Shift+Space
2. Press Ctrl++ (the plus key on the numeric keypad, or Ctrl+Shift++ on the main keyboard)
3. A new row is inserted above the selected row

Inserting Multiple Rows at Once

1. Select the same number of rows as you want to insert (e.g., click row 5, Shift+click row 7 to select 3 rows)
2. Right-click → Insert — 3 new blank rows appear above row 5

Inserting a Row from Within a Cell (Without Selecting the Full Row)

1. Click any cell in the row where you want to insert above it
2. Home → Cells → Insert dropdown → Insert Sheet Rows
3. A new row is inserted above the row containing the active cell

Insert Options Button

After inserting a row, a small Insert Options button (paint brush icon) appears to the left of the inserted row. Click it to choose:

• Format Same as Above — the new row inherits the formatting of the row above it
• Format Same as Below — the new row inherits the formatting of the row below it
• Clear Formatting — the new row has no formatting (plain default cells)

5.4 Deleting Rows

Deleting a row permanently removes it and all its content. Rows below the deleted row shift up. Any formulas in other cells that referenced cells in the deleted row will show #REF! errors.

Method 1 — Right-Click (Fastest)

1. Click the row number(s) to select the row(s) to delete
2. Right-click the selected row header
3. Click Delete
4. The row(s) are removed and all rows below shift up

Method 2 — Ribbon

1. Select the row number(s)
2. Home → Cells → Delete dropdown → Delete Sheet Rows

Method 3 — Keyboard Shortcut

1. Select the row header(s)
2. Press Ctrl+- (the minus key on the numeric keypad, or Ctrl+Shift+- on the main keyboard)

Deleting Multiple Rows at Once

• Consecutive rows: Shift+click row headers to select multiple rows → right-click → Delete
• Non-consecutive rows: Ctrl+click each row header → right-click → Delete (all selected rows deleted in one action)

Deleting Blank Rows in a Dataset

A fast method to find and delete all blank rows in a dataset:

1. Select the data range
2. Home → Editing → Find & Select → Go To Special… (Ctrl+G → Special)
3. Select Blanks → click OK — all blank cells in the range are selected
4. Home → Cells → Delete → Delete Sheet Rows
5. All rows containing blank cells are deleted

5.5 Inserting Columns

When you insert a column, it appears to the left of the selected column. All columns to the right shift one position to the right. Formulas referencing cells in those columns automatically update.

Method 1 — Right-Click

1. Click the column letter where you want to insert a column to the left (e.g., click column C to insert a new column to the left of C)
2. Right-click the selected column header
3. Click Insert
4. A new blank column is inserted. The old column C becomes D.

Method 2 — Ribbon

1. Select the column letter(s)
2. Home → Cells → Insert dropdown → Insert Sheet Columns

Method 3 — Keyboard Shortcut

1. Select the column header(s): click the column letter or press Ctrl+Space
2. Press Ctrl++

Inserting Multiple Columns at Once

1. Select the same number of column headers as you want to insert (e.g., select columns C, D, E by clicking C and Shift+clicking E)
2. Right-click → Insert — 3 new blank columns appear to the left of column C

5.6 Deleting Columns

Deleting a column permanently removes it and all its content. Columns to the right shift left. Formulas referencing deleted cells show #REF! errors.

Method 1 — Right-Click

1. Click the column letter(s) to select
2. Right-click the column header
3. Click Delete

Method 2 — Ribbon

1. Select the column letter(s)
2. Home → Cells → Delete dropdown → Delete Sheet Columns

Method 3 — Keyboard Shortcut

1. Select the column header(s)
2. Press Ctrl+-

5.7 Resizing Rows & Columns

Resizing Columns

Resizing Rows

5.8 Hiding & Unhiding Rows and Columns

Hiding a row or column makes it invisible on screen and in print — but the data is still there and formulas still reference it. This is useful for hiding working calculations, confidential data, or intermediate data that should not appear in the printed output.

Hiding Rows

1. Select the row number(s) to hide
2. Right-click the selected row header → Hide
3. — or — Home → Cells → Format → Hide & Unhide → Hide Rows
4. — or — Ctrl+9 (hides selected rows)
5. The row numbers skip the hidden row (e.g., rows 4 and 6 are visible with 5 hidden — the row numbers show 4 then 6)

Unhiding Rows

1. Select the rows above and below the hidden row (e.g., if row 5 is hidden, select rows 4 and 6 together by Shift+clicking)
2. Right-click → Unhide
3. — or — Ctrl+Shift+9 (unhides rows in selection)

Hiding Columns

1. Select the column letter(s) to hide
2. Right-click → Hide
3. — or — Ctrl+0 (zero — hides selected columns)
4. The column letters skip the hidden column (e.g., A, B, D visible means C is hidden)

Unhiding Columns

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

Unhiding All Hidden Rows/Columns at Once

1. Click the Select All button (top-left corner) to select the entire sheet
2. Home → Format → Hide & Unhide → Unhide Rows (or Unhide Columns)
3. All hidden rows (or columns) on the sheet are revealed simultaneously

5.9 Working with Sheets — Inserting, Deleting & Managing

A workbook can contain many worksheets. Organising your data across multiple sheets — one per month, one per department, one per product — makes large datasets manageable and reports easier to navigate.

Inserting a New Sheet

Deleting a Sheet

1. Right-click the sheet tab to delete → Delete
2. — or — Home → Cells → Delete → Delete Sheet
3. A warning dialog appears: "Microsoft Excel will permanently delete this sheet. Do you want to continue?"
4. Click Delete to confirm

Renaming a Sheet

• Method 1 (fastest): Double-click the sheet tab → the tab name highlights → type the new name → press Enter
• Method 2: Right-click the tab → Rename → type → Enter

Colouring a Sheet Tab

• Right-click the sheet tab → Tab Color → choose a colour from the palette
• Tab colours help organise workbooks visually: e.g., January-March = green, April-June = blue, July-September = orange, October-December = red
• The active sheet's tab colour appears as a colour bar at the bottom of the tab

Moving a Sheet

• Drag and drop: Click and hold the sheet tab → drag it left or right to the desired position → release. A small black triangle indicator shows where the sheet will land.
• Move or Copy dialog: Right-click the tab → Move or Copy… → select the target workbook and position → OK

Copying a Sheet

• Drag and hold Ctrl: Hold Ctrl while dragging a sheet tab → a + symbol appears on the tab cursor → release at the new position. A copy is created with "(2)" appended to the name.
• Move or Copy dialog: Right-click the tab → Move or Copy → tick "Create a copy" → select position → OK
• Copy to another workbook: Move or Copy dialog → change the "To book" dropdown to the target workbook → tick "Create a copy" → OK

Hiding and Unhiding Sheets

• Hide: Right-click the sheet tab → Hide. The tab disappears but the sheet (and all its data/formulas) remains in the workbook.
• Unhide: Right-click any visible sheet tab → Unhide… → select the hidden sheet from the list → OK
• Hidden sheets are still referenced by formulas in other sheets
• To hide sheets from curious users more securely: VBA → set sheet Visible property to xlVeryHidden (requires VBA to unhide — not visible in the Unhide dialog)

Navigating Between Sheets

5.10 Selecting Multiple Sheets (Grouping)

You can select multiple sheet tabs simultaneously — called grouping sheets. Any action you perform (entering data, formatting, deleting rows) applies to all selected sheets at once. This is a powerful way to apply the same structure to multiple sheets in one step.

How to Group Sheets

• Consecutive sheets: click the first sheet tab → hold Shift → click the last tab. All tabs between are selected (highlighted).
• Non-consecutive sheets: click the first sheet tab → hold Ctrl → click each additional tab.
• All sheets: right-click any tab → Select All Sheets

Working in Group Mode

• The title bar shows [Group] when sheets are grouped
• Any data typed, formatting applied, or rows/columns inserted on the active sheet is mirrored on all grouped sheets in the same cell positions
• Use grouping to: apply a header row to multiple sheets, set the same column widths, apply the same print settings, or enter a formula that should appear on all sheets

Ungrouping Sheets

• Click any sheet tab that is NOT in the group — or —
• Right-click any grouped tab → Ungroup Sheets
• Always ungroup after finishing the grouped operation to avoid accidentally editing all sheets

5.11 What Happens to Formulas When You Insert or Delete

Understanding how Excel updates (or breaks) formulas when you restructure your spreadsheet prevents one of the most common causes of spreadsheet errors.

When You Insert Rows or Columns

• Formulas that reference cells within a range automatically expand to include the new row or column if it is inserted inside the range
• Example: =SUM(A1:A10) → you insert a row at row 5 → the formula automatically becomes =SUM(A1:A11)
• If you insert a row above the range start or below the range end, the new row is NOT automatically included in the SUM — you must extend the range manually

When You Delete Rows or Columns

5.12 Quick Self-Check

Q1: You need to insert 5 new blank rows between row 10 and row 11. What is the most efficient way to do this in a single action?

✓ Select 5 rows starting from row 11 (click row 11 header → hold Shift → click row 15 header to select rows 11–15). Right-click any selected row header → Insert. Excel inserts exactly 5 new blank rows above row 11. The number of rows inserted always equals the number selected. This is faster and safer than inserting one row at a time five times.

Q2: You deleted column C but now several formulas across the spreadsheet are showing #REF! errors. What caused this and how do you fix it?

✓ The formulas that show #REF! were directly referencing specific cells in column C — since that column no longer exists, the cell reference is invalid and Excel replaces it with #REF!. Fix: press Ctrl+Z immediately to undo the deletion if possible. If you cannot undo, click each #REF! cell → look in the Formula Bar to see the broken reference → correct it to reference the appropriate column (which may now be a different letter since all columns to the right of the deleted C have shifted left). Use Formulas → Error Checking to find all affected cells at once.

Q3: You have a monthly workbook with 12 identical sheets (January through December). You want to add a "Total Sales" header in cell A1 of every sheet at the same time. How do you do this without going to each sheet individually?

✓ Group all 12 sheets: click the January tab → hold Shift → click the December tab. The title bar shows [Group]. Now click cell A1 and type "Total Sales" → press Enter. The text is entered into A1 of all 12 sheets simultaneously. Any formatting applied while sheets are grouped also applies to all sheets. When done, right-click any tab → Ungroup Sheets — or click any sheet outside the group to ungroup.

Q4: Column B is hidden in your spreadsheet and you cannot see it. How do you unhide it?

✓ Select the columns on both sides of the hidden column: click column A header → hold Shift → click column C header (selecting both A and C, which are visible on either side of the hidden B). Right-click the selected column headers → Unhide. Column B reappears. Alternatively: select both columns A and C → Home → Format → Hide & Unhide → Unhide Columns.

Q5: What is the difference between pressing the Delete key on a selected row versus right-clicking and choosing Delete?

✓ Pressing the Delete key on a selected row clears (empties) the row's contents but leaves the row itself in the spreadsheet — it remains as a blank row and row numbers do not change. Right-clicking and choosing Delete physically removes the entire row from the spreadsheet — the row is gone, all rows below shift up by one, and row numbers renumber accordingly. Use Delete (key) when you want to keep the blank row structure. Use right-click Delete when you want to eliminate the row entirely and close the gap.

Q6: You have a formula =SUM(B2:B20). You insert a new row at row 5 (inside the range). What does the formula become, and what if you insert a row at row 21 (below the range)?

✓ Inserting at row 5 (inside the range B2:B20): Excel automatically adjusts the formula to =SUM(B2:B21) — the range expands by one row to include the newly inserted row, and the new row's value is included in the sum. Inserting at row 21 (below the range end): the formula remains =SUM(B2:B20) unchanged — the new row at row 21 is outside the original range and is NOT automatically included. If you want the new row included, you must manually extend the formula to =SUM(B2:B21). This is a common source of errors when adding rows below a SUM range.

✓ Module 5 Complete — You Have Learned:

• Rows vs Columns vs Sheets — orientation, identifiers, and maximum capacity; the fundamental rule that inserts push cells away and deletions pull cells in
• Selecting rows — single (click header), consecutive (Shift+click), non-consecutive (Ctrl+click), keyboard (Shift+Space)
• Selecting columns — single (click header), consecutive (Shift+click), non-consecutive (Ctrl+click), keyboard (Ctrl+Space)
• Pro tip: number of rows/columns selected = number inserted in one action
• Inserting rows — 4 methods (right-click, Ribbon, Ctrl++, from within a cell); inserting multiple rows at once; Insert Options button (Format Same as Above/Below/Clear)
• Deleting rows — 3 methods (right-click, Ribbon, Ctrl+-); Delete vs Clear distinction; deleting multiple consecutive and non-consecutive rows; Go To Special → Blanks for blank row deletion
• Inserting columns — 3 methods; columns insert to the left; inserting multiple columns at once
• Deleting columns — 3 methods; columns shift left after deletion
• Resizing columns — drag, AutoFit double-click, AutoFit all (Select All + double-click border), exact width, Ribbon AutoFit, Default Width
• Resizing rows — drag, AutoFit double-click, exact height, Ribbon AutoFit; AutoFit all columns shortcut (Ctrl+A → Format → AutoFit)
• Hiding rows (Ctrl+9) and unhiding (Ctrl+Shift+9); hiding columns (Ctrl+0) and unhiding (Ctrl+Shift+0); unhiding all at once; fixing hidden row 1 or column A
• Sheet management — inserting (+ button, right-click, Shift+F11), deleting (with permanent warning), renaming (double-click tab), colouring tabs, moving (drag), copying (Ctrl+drag or Move or Copy dialog), hiding and unhiding
• Sheet naming rules — 31 chars max, forbidden characters, must be unique
• Sheet navigation — Ctrl+Page Down/Up, right-click navigation arrows for full sheet list
• Grouping sheets — Shift+click or Ctrl+click tabs; [Group] shown in title bar; simultaneous edits across all grouped sheets; ungrouping; danger of forgetting grouped mode
• Formula behaviour on insert — ranges expand when rows inserted inside them; rows inserted outside the range are NOT auto-included
• Formula behaviour on delete — #REF! errors for directly referenced deleted cells; SUM ranges contract cleanly for rows deleted inside; VLOOKUP column index risks after column deletion
• Post-restructure checklist: Ctrl+End to find last cell; Formulas → Error Checking for #REF! errors