THE FUTURE MARKETACADEMY
Your classroomRoom 07 · Tech Lab
School of Data & Tech

Excel for Jobs: From Zero to Confident

Step 2 of 8 in Tech Lab

Next in your path: 7.2 Google Sheets for Business

What you'll be able to do
  • Clean messy work data fast with Flash Fill, Remove Duplicates and tables.
  • Answer business questions with SUMIFS, COUNTIFS, IFERROR and XLOOKUP.
  • Stop bad data at the door with drop-down lists (data validation).
  • Summarize thousands of rows in seconds with a PivotTable.
  • Build a one-page dashboard with PivotCharts and slicers that a manager can click.
  • Hand over a workbook that is tidy, documented and easy for the next person to use.

This course starts where our student course "Microsoft Excel Essentials" (13.2) ends. If formulas, cell references, sorting and basic charts are new to you, take 13.2 first.

Words in this course 39Open the Vocabulary Hall
01

Work data is messy, so clean it first

In simple words

Before you bake a cake, you wash the bowl and line up the ingredients. Work data is the same: first you tidy it, then you use it. A clean list means one thing per box and no copies of the same row.

Key facts
  • Good work data has one header row, one record per row and one type of thing per column (for example, dates only in the Date column). Excel uses the first-row labels as field names in PivotTables.
  • Flash Fill: type one example of the pattern you want next to your data (for example, the first name pulled out of "Ana Lopez"), then use Data > Flash Fill or press Ctrl+E (Windows). Excel fills the rest of the column by copying your pattern.
  • Flash Fill types values, not formulas, so the results do not update if the original data changes.
  • Data > Remove Duplicates deletes repeated rows. Work on a copy of the sheet the first time so you can compare.
  • Insert > Table (Windows shortcut Ctrl+T) turns a range into a table. Tables grow automatically when you add rows, and formulas you write in one row fill down by themselves.
  • Give each table a name (Table Design > Table Name), for example Sales. You can then write formulas like =SUM(Sales[Amount]), which are easier to read than =SUM(D2:D500).
Example (cleaning a contact list)
Full nameFirst name (Flash Fill)
Ana LopezAna
Ben OkaforBen
Chloe TranChloe

Type "Ana" in the first cell, press Ctrl+E, and Excel fills "Ben" and "Chloe".

Step by step
  1. Open your data sheet and make a copy of it first.
  2. Check there is one header row, one record per row and one type of thing per column.
  3. Next to a column of full names, type the first name of the first row, then press Ctrl+E (Data > Flash Fill) to fill the rest.
  4. Select your data and choose Data > Remove Duplicates.
  5. Click inside the data and choose Insert > Table (Ctrl+T on Windows).
  6. Name the table under Table Design > Table Name, for example Sales. Check: =SUM(Sales[Amount]) works, and remember Flash Fill results do not update if the original changes.
Watch on YouTube
Microsoft Excel for beginners: learn the basicsTeacher's Tech · YouTube · youtube.com

Videos by other creators on YouTube, for reference. They are not made by or affiliated with The Future Market.

LESSON 01Work data is messy, so clean it first
LESSON 01 / APP DEMO / AI VOICE00:00 / 01:00
Read the video script
[Host] "In a real job, nobody hands you clean data. Your first skill is cleaning it."
[Screen: typing Ana next to Ana Lopez, then pressing Ctrl+E] "Type one example. Press Ctrl+E. Flash Fill does the rest of the column."
[Screen: Data > Remove Duplicates] "Same customer three times? Remove Duplicates. Work on a copy first."
[Screen: Ctrl+T, naming the table Sales] "Now press Ctrl+T and give your table a name. Clean, named, ready."
Try it now

Make a 15-row customer list with full names, emails and cities. Copy two rows so you have duplicates. Use Flash Fill to split first names, Remove Duplicates to clean it, then turn it into a table named Customers.

ClaudeStuck? Copy a ready-made prompt for Claude

Copy this, open Claude, paste it, and Claude will walk you through the lesson step by step.

For a grown-up: Claude is for adults (18+). Copy this, paste it into Claude, and use the answer to explain the lesson to your child.

I'm learning "Excel for Jobs: From Zero to Confident" on The Future Market. Lesson 1: "Work data is messy, so clean it first".

What the lesson teaches:
- Good work data has one header row, one record per row and one type of thing per column (for example, dates only in the Date column). Excel uses the first-row labels as field names in PivotTables.
- Flash Fill: type one example of the pattern you want next to your data (for example, the first name pulled out of "Ana Lopez"), then use Data > Flash Fill or press Ctrl+E (Windows). Excel fills the rest of the column by copying your pattern.
- Flash Fill types values, not formulas, so the results do not update if the original data changes.
- Data > Remove Duplicates deletes repeated rows. Work on a copy of the sheet the first time so you can compare.
- Insert > Table (Windows shortcut Ctrl+T) turns a range into a table. Tables grow automatically when you add rows, and formulas you write in one row fill down by themselves.
- Give each table a name (Table Design > Table Name), for example Sales. You can then write formulas like =SUM(Sales[Amount]), which are easier to read than =SUM(D2:D500).

The exercise I'm working on:
Make a 15-row customer list with full names, emails and cities. Copy two rows so you have duplicates. Use Flash Fill to split first names, Remove Duplicates to clean it, then turn it into a table named Customers.

I'm stuck. Please:
1. Explain the main idea in plain, simple words with one everyday example.
2. Walk me through the exercise one small step at a time. Ask me what I've done so far before giving the next step.
3. Don't just give me the final answer. Help me do it myself, and check my work when I share it.
I'm a parent helping my child (about 8 years old) with a lesson called "Work data is messy, so clean it first" from the course "Excel for Jobs: From Zero to Confident".

Here's what the lesson says:
Before you bake a cake, you wash the bowl and line up the ingredients. Work data is the same: first you tidy it, then you use it. A clean list means one thing per box and no copies of the same row.

The activity:
Make a 15-row customer list with full names, emails and cities. Copy two rows so you have duplicates. Use Flash Fill to split first names, Remove Duplicates to clean it, then turn it into a table named Customers.

Please explain this so an 8-year-old understands: short sentences, a fun everyday example, and no hard words.
Then give me 3 simple questions I can ask my child to check they understood, and a way to do the activity together, one tiny step at a time.
Open Claude
Sources
My notes
02

Formulas that answer business questions

This lesson is part of full access: every course and every level for one payment.

03

XLOOKUP like a pro

This lesson is part of full access: every course and every level for one payment.

04

Data validation: stop mistakes before they happen

This lesson is part of full access: every course and every level for one payment.

05

PivotTables: summaries in seconds

This lesson is part of full access: every course and every level for one payment.

06

Build a one-page dashboard

This lesson is part of full access: every course and every level for one payment.

07

Hand over work like a professional

This lesson is part of full access: every course and every level for one payment.

++

Final project

This lesson is part of full access: every course and every level for one payment.

??

Quiz

  1. In SUMIFS, which argument comes first?

  2. What does XLOOKUP return if there is no match and you left out if_not_found?

  3. Which XLOOKUP match_mode finds an exact match or the next smaller item?

  4. Is XLOOKUP available in Excel 2019?

  5. Where do you create a drop-down list?

  6. Why doesn't a PivotTable show new data right away?

  7. What is a slicer?

  8. Can one slicer control two PivotTables?

  9. What does Flash Fill produce: formulas or values?

  10. What shortcut turns a range into a table on Windows?

EX

Final exam

This lesson is part of full access: every course and every level for one payment.

CE

Certificate

This lesson is part of full access: every course and every level for one payment.