Learn Without Walls
📄 Save or print this lesson:
← Module 03: Logic & Lookup 🏠 Course Home Module 05: Pivot Tables →
Module 04 of 08  |  Excel & Google Sheets for Data

📊 Working with Data

You've got the formulas. Now learn to manage the data itself — sorting it, filtering it, locking headers in place, controlling what users can enter, and giving ranges memorable names.

⏰ ~40 minutes 🎓 Beginner–Intermediate 📋 Requires: Module 01–02
📌 Before You Start
Set up the sample dataset

You'll need a 20-row sales dataset for this module. You can type it in yourself or use the one below — it covers all the exercises. Open a new Google Sheet and enter it starting at cell A1.

📋 Sample Data — 20-Row Sales Dataset
Type or copy this into your spreadsheet
A — DateB — SalespersonC — CategoryD — ProductE — UnitsF — Revenue
2024-01-05AliceElectronicsLaptop22400
2024-01-08BobBooksPython Guide10350
2024-01-12AliceClothingJacket5445
2024-01-15CarolElectronicsTablet31350
2024-01-20BobElectronicsHeadphones8960
2024-02-03CarolBooksStats Textbook12540
2024-02-07AliceElectronicsKeyboard15750
2024-02-14DavidClothingSneakers6660
2024-02-18BobBooksData Viz Book7245
2024-02-25CarolElectronicsMonitor1450
2024-03-01DavidElectronicsWebcam10790
2024-03-05AliceClothingT-Shirt Pack20400
2024-03-10BobElectronicsUSB Hub25388
2024-03-15CarolClothingScarf30270
2024-03-20DavidBooksSQL Guide8280
2024-03-25AliceElectronicsLaptop Stand12360
2024-04-02BobClothingHat40320
2024-04-08CarolElectronicsCharger501500
2024-04-14DavidBooksAI Handbook15525
2024-04-20AliceElectronicsMouse20600
🔧 Step-by-Step
Sorting: Reorder Your Data in Seconds

Sorting rearranges your rows based on the values in one or more columns. It does not delete anything — it just reorders.

Single-column sort:

  1. Click any cell inside your data (e.g., F1 — the Revenue header).
  2. Go to Data → Sort sheet → Sort sheet by column F (A to Z) for ascending (lowest first), or Z to A for descending (highest first).
  3. Your rows are now reordered by revenue.

Multi-column sort (sort by Category, then by Revenue):

  1. Go to Data → Sort range → Advanced range sorting options.
  2. Check "Data has header row" so your headers don't get sorted.
  3. First sort column: Column C (Category), A to Z.
  4. Click "Add another sort column".
  5. Second sort: Column F (Revenue), Z to A (descending).
  6. Click Sort.

Result: rows are grouped by category alphabetically, and within each category the highest revenue rows appear first.

Excel tip: In Excel, go to the Data tab → Sort. The dialog is similar but uses a different interface. You can add multiple sort levels there too.
🔧 Step-by-Step
Filtering: Show Only the Rows You Need

Filters hide rows that don't match your criteria — they don't delete them. Click the filter icon to reveal or re-hide rows.

Turn on filters:

  1. Click any cell inside your data.
  2. Press Ctrl+Shift+L (or go to Data → Create a filter). A small dropdown arrow (▼) appears on each header cell.
  3. Click the ▼ on the Category header (column C).
  4. Uncheck everything except "Electronics". Click OK. Now only Electronics rows are visible.

Filter by condition (Revenue > 500):

  1. Click the ▼ on the Revenue header (column F).
  2. Choose "Filter by condition" → "Greater than".
  3. Type 500 and click OK.
  4. Now only rows with Revenue greater than $500 are shown.

Clear all filters:

Go to Data → Remove filter, or press Ctrl+Shift+L again to toggle the filter off entirely.

Important: Filtered rows are hidden, not deleted. All your data is still there. You can always remove the filter to see everything again. Formulas like SUM still count hidden rows — use SUBTOTAL if you want to sum only visible rows.
🔧 Step-by-Step
Freeze Rows and Columns: Keep Headers Visible While Scrolling

When you have 20, 100, or 1000 rows, scrolling down means your headers disappear. Freezing fixes that.

  1. Go to View → Freeze → 1 row. Row 1 is now frozen.
  2. Scroll down — your header row stays visible at the top no matter how far you scroll.
  3. To freeze a column (e.g., keep the Date visible): click the column header B to put your cursor in column A, then go to View → Freeze → 1 column.
  4. To unfreeze: go to View → Freeze → No rows (or No columns).
Excel equivalent: View tab → Freeze Panes → "Freeze Top Row". Or click a cell below/right of where you want to freeze and select "Freeze Panes".
💡 The Concept
Data Validation: Control What Gets Entered

Data Validation restricts what can be typed into a cell. This prevents errors at the source — better to prevent a typo than to clean it up later.

Create a dropdown list for Category:

  1. Select the cells where you want the dropdown (e.g., C2:C100 for future data entry).
  2. Go to Data → Data validation.
  3. Under "Criteria", choose "List of items".
  4. Type: Electronics, Books, Clothing (comma-separated).
  5. Enable "Show dropdown list in cell". Click Save.
  6. Now clicking any cell in that range shows a ▼ dropdown with your three options.

Validate a number range (Revenue must be > 0):

  1. Select F2:F100.
  2. Data → Data validation → Criteria: "Number" → "Greater than" → 0.
  3. Choose "Show warning" or "Reject input" for invalid entries.
  4. Add a custom message: "Revenue must be a positive number."
Why this matters: If you're building a sheet others will fill in, data validation prevents them from typing "Elecronics" (typo) or entering a negative revenue. It keeps your data clean automatically.
🔧 Step-by-Step
Named Ranges: Give Ranges Human-Readable Names

Instead of writing =SUM(F2:F21), imagine writing =SUM(Revenue). Named ranges make formulas self-documenting and easier to maintain.

  1. Select the range you want to name — for example, F2:F21 (all Revenue values).
  2. Go to Data → Named ranges. A sidebar opens.
  3. Click "Add a range". Type the name: Revenue. Confirm the range is correct. Click Done.
  4. Now in any formula, you can type =SUM(Revenue) instead of =SUM(F2:F21).
  5. Add another: select C2:C21 and name it Category.
  6. Now you can write: =COUNTIF(Category, "Electronics") — which is much clearer.
Naming rules: Names can't have spaces (use underscores: Total_Revenue) or start with a number. They're case-insensitive.
Excel equivalent: Select a range, click the Name Box (top left, shows cell address), type your name, and press Enter. Or use Formulas → Define Name.
🖐 Your Turn
Exercise: Sales Data Analysis

Using the 20-row sales dataset you entered at the start of this module:

  1. Freeze the header row (View → Freeze → 1 row). Scroll down to confirm it works.
  2. Sort by Revenue descending (highest revenue first). Which product had the highest revenue?
  3. Turn on filters and filter the Category column to show only "Electronics". How many Electronics rows are there?
  4. Add a second filter: within Electronics, filter Revenue to show only rows greater than 500. Which rows remain?
  5. Clear your filters (Data → Remove filter) to restore all rows.
  6. Create a Data Validation dropdown for column C (Category): choose Electronics, Books, Clothing. Try typing a new category name — it should give you a warning.
  7. Name the range F2:F21 as "Revenue". Then in a blank cell, write =SUM(Revenue) and verify it gives you the correct total.
Hint: After filtering, the row numbers shown in column headers go from gray to blue — that's your visual confirmation that a filter is active.
🧠 Brain Break
The SUBTOTAL Secret

When you apply a filter and want to sum only the visible rows, regular SUM doesn't work — it adds everything, including hidden rows. The solution is =SUBTOTAL(9, F2:F21). The number 9 tells SUBTOTAL to use SUM, but only on visible cells. Other codes: 1=AVERAGE, 2=COUNT, 5=MIN, 4=MAX.

When you use AutoSum (Σ) on a filtered range, Google Sheets automatically inserts SUBTOTAL instead of SUM — a small but thoughtful detail that saves a lot of confusion.

✅ Wrap Up
Module 04 Key Takeaways

Next: Module 05 — Pivot Tables →