Be able to insert cell(s), row(s) and column(s), delete cell(s), row(s) and column(s), merge cells

20 Spreadsheets – Inserting, Deleting, Merging & Manipulating Data

Learning Objectives (AO1‑AO3)

• AO1 – Knowledge: Define key terminology, recall menu paths and keyboard shortcuts for inserting, deleting and merging cells, rows and columns.
• AO2 – Application: Perform insert/delete/merge operations; create and edit formulas; use common functions; define named ranges; work with cross‑sheet references.
• AO3 – Analysis/Evaluation: Analyse how structural changes affect existing data and formulas and choose the most efficient method for a given task.

1. Inserting Rows, Columns and Cells

2. Deleting Rows, Columns and Cells

3. Merging & Un‑merging Cells

1. Select a contiguous range of cells in the same row or column.
2. Menu: Format → Merge Cells →
   • Merge Across – merges each row in the selection separately.
   • Merge Down – merges each column in the selection separately.
   • Merge All – creates one single cell from the whole selection.
3. Toolbar shortcut (if displayed): Merge & Center button.
4. To un‑merge, select the merged cell and choose Format → Merge Cells → Unmerge.

4. Building Formulas – Core Concepts (20.1)

4.1 Order of Operations

Excel follows the standard mathematical hierarchy:

1. Parentheses ( )
2. Exponents ^
3. Multiplication and Division * /
4. Addition and Subtraction + -

Example: =A1+B1*C1 first multiplies B1*C1, then adds A1.

4.2 Cell‑reference Types

4.3 Cross‑sheet References

To use a value from another worksheet, prefix the cell address with the sheet name in single quotes:

=‘Sheet2’!B3

When rows/columns are inserted on the referenced sheet, Excel updates the reference automatically, preserving the link.

4.4 Named Ranges

1. Select the cell or range.
2. Menu: Formulas → Define Name (or right‑click → Define Name).
3. Enter a meaningful name (e.g., Sales_Q1).
4. Use the name in any formula: =SUM(Sales_Q1).

Advantages: easier to read, reduces errors when rows/columns are inserted or deleted, and works across sheets.

4.5 Example Formula Incorporating All Concepts

=IF($B$2>0, (‘Data’!C5*$D$1)/SUM(Sales_Q1), 0)

• $B$2 – fully absolute reference (constant threshold).
• ‘Data’!C5 – cross‑sheet reference.
• $D$1 – absolute reference to a tax rate.
• SUM(Sales_Q1) – named range.

5. Common Functions (20.1)

6. Impact of Structural Changes on Formulas (AO3)

• When a row or column is inserted, Excel automatically updates **relative** references to point to the same logical data. Absolute references remain unchanged.
• Deleting a row/column removes the referenced cell; any formula that pointed exactly to that cell returns #REF!. Using absolute references for constants (e.g., tax rate) protects them from accidental deletion.
• Named ranges and cross‑sheet references are resilient to insertions/deletions because they refer to a defined set of cells rather than a fixed address.
• When merging cells, only the value in the upper‑left cell is retained. Formulas that previously referenced any of the merged cells will now refer to the merged cell’s address (the upper‑left one).

7. Practical End‑to‑End Example (Monthly Sales Report)

Scenario: Prepare a report that shows product codes, quantities sold, unit price, and total revenue. The worksheet must be easy to update each month.

1. Insert a new product row above the current total row.
   • Select the total row → Insert → Row Above (or Ctrl + +).
2. Delete the “Discount” column (no longer required).
   • Select column F → Delete → Column (or Ctrl + Shift + -).
3. Create a centered title spanning A‑E.
   • Select A1:E1 → Merge & Center.
   • Enter Monthly Sales Report – July 2025.
4. Calculate total revenue per product.
   • In E2 enter =B2*C2 (Quantity × Unit Price).
   • Copy the formula down to the last product row (drag the fill handle).
5. Summarise the whole‑sheet total.
   • In the cell directly below the merged title (e.g., A22) enter =SUM(E2:E20).
   • Format the cell as Currency.
6. Define a named range for the quantity column.
   • Select B2:B20 → Formulas → Define Name → name it Qty.
   • Now the total quantity can be shown with =SUM(Qty).
7. Apply conditional formatting to highlight high‑selling items.
   • Select the range E2:E20.
   • Home → Conditional Formatting → Highlight Cells Rules → Greater Than… → enter 500 and choose a green fill.
8. Sort the table by revenue (largest to smallest).
   • Click any cell in column E → Data → Sort Z‑A.
   • Confirm that the entire data range (A2:E20) is selected.
9. Print setup.
   • Select A1:E22 → Page Layout → Print Area → Set Print Area.
   • Orientation: Landscape.
   • Scaling: Fit Sheet on One Page.
   • Preview with File → Print → Print Preview before printing.

8. Keyboard Shortcut Summary

9. Syllabus Alignment – Quick Reference