Learning Objectives

  • Sort on one or multiple levels
  • Filter with AutoFilter and custom criteria
  • Highlight patterns with conditional formatting
  • Split text with Text to Columns
  • Restrict inputs with Data Validation
  • Freeze panes and set repeating print titles

Sample file: sales_data_large.csv (240 rows)

Before You Start

  1. Import CSV → sheet Sales.
  2. Confirm one header row, no blank rows inside the block, no merged header cells.
  3. Optional: select data → Ctrl+T to create an Excel Table (filters and structured refs included).

1) Sort

  1. Click inside the data → Data → Sort.
  2. Example multi-level sort:
    • Level 1: Region A→Z
    • Level 2: Sales Amount Largest to Smallest
  3. Always expand the selection so entire rows move together.

2) Filter

  1. Data → Filter (or Table filters).
  2. Filter Region = West, then Units Sold greater than 5.
  3. Clear filters before building Pivot Tables if you need the full dataset (or pivot from a Table).

Custom filter tip: Number Filters → Greater Than for discount or revenue thresholds (as in Model Lab Test Set A).

3) Conditional Formatting

  1. Select the Sales Amount column (exclude header).
  2. Home → Conditional Formatting → Color Scales (Green–Yellow–Red).
  3. Add a rule on a Status column: Text that Contains Low → red fill; High → green fill.

Keep rules few and meaningful — colour is for scanning, not decoration.

4) Text to Columns

When one column holds combined values (e.g. Pokhara-PKR):

  1. Insert a blank column to the right (so split data has somewhere to go).
  2. Select the combined column → Data → Text to Columns.
  3. Delimited → choose hyphen → Finish.
  4. Rename new headers (City, Code).

Practice on 10–15 sample rows before touching the full sheet.

5) Data Validation

  1. Select the Region input cells.
  2. Data → Data Validation → List.
  3. Source: North,South,East,West (or a named range on another sheet).
  4. Optional: Input Message + Error Alert so classmates know allowed values.

6) Freeze Panes and Print Titles

Freeze top row: View → Freeze Panes → Freeze Top Row.

Print titles: Page Layout → Print Titles → Rows to repeat at top: $1:$1.

Use Print Preview to confirm headers repeat on page 2+.

Practice Task

With sales_data_large.csv:

  1. Multi-level sort (Region, then Sales Amount)
  2. Three different filter views (document what you saw)
  3. Color scale on Sales Amount + Status-style rule if you add a helper column
  4. Text to Columns on a small combined sample column
  5. Region dropdown validation
  6. Freeze header + print titles

📚 Continue to: Pivot Tables and Charts