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

Step-by-Step Lab Exercise: Sort, Filter, and Highlight

Enter this practice dataset (or use the first rows of the sample file):

Region Salesperson Units Sales (Rs.)
East Ram 12 24,000
West Sita 5 9,500
North Hari 8 16,800
West Gita 15 31,500
South Bikash 3 5,400
East Anita 9 18,900
West Kiran 7 12,600
North Maya 11 22,000
  1. Multi-level sort: Data → Sort → Region (A to Z), then Sales (Largest to Smallest). Expected order: East (Ram, Anita), North (Maya, Hari), South (Bikash), West (Gita, Kiran, Sita).
  2. Filter: Data → Filter → Region = West, then Units → Number Filters → Greater Than 5. Expected: 2 rows (Gita 15, Kiran 7). Sita is excluded because 5 is not greater than 5.
  3. Conditional formatting: select the Sales column → Highlight Cells Rules → Greater Than 20,000. Expected: 3 cells highlighted (Ram, Gita, Maya).
  4. Data validation: select an empty Region column → Data Validation → List → East,West,North,South. Typing “Central” should show an error.
  5. Freeze panes: View → Freeze Top Row; scroll down and confirm headers stay visible.

Common Errors and Fixes

Problem Likely cause Fix
Sorting mixes up rows (names no longer match sales) Only one column was selected before sorting Undo; click one cell inside the data and sort the whole range
Filter shows no data Filters left on from a previous task Data → Clear, then apply the new filter
Some rows are left out of sort or filter Blank rows inside the data block Remove blank rows or convert the range to a Table (Ctrl + T)
Text to Columns overwrites data No empty column to the right Insert blank columns before splitting
Headers do not repeat when printed Print Titles not set Page Layout → Print Titles → Rows to repeat: $1:$1

Key Takeaways

  • Clean data first: one header row, no blank rows, no merged headers.
  • Multi-level sorting and filters answer quick business questions.
  • Conditional formatting should highlight what matters, not decorate.
  • Data validation prevents bad inputs at the source.

Exam-Style Questions

Viva (short answer)

  1. What is the difference between sorting and filtering?
  2. How do you sort by two columns?
  3. What is conditional formatting? Give an example.
  4. What is data validation used for?
  5. What does Freeze Panes do?

Lab test tasks

  1. Sort a sales dataset by Region and then by Sales in descending order.
  2. Filter the data to show only one region with sales above a given amount, and record the number of rows shown.
  3. Apply a colour scale to sales and a rule highlighting values above Rs. 20,000.
  4. Create a drop-down list for Region using data validation and freeze the header row.

📚 Continue to: Pivot Tables and Charts