Excel for Coursework: tables, charts and lab reports

01

Set up your data as a clean table

In simple words

Think of your data like a tidy toy shelf. One shelf row per toy, one label on top of each shelf column. If the shelf is tidy, you can find anything fast. If toys are thrown everywhere, even a clever robot gets lost.

Before this course: Microsoft Excel Essentials (course 13.2). You should already know cells, basic formulas, SUM and AVERAGE, absolute references, sorting and a first chart.

Key facts

Good layout: one row per record (one plant, one trial, one student) and one column per kind of information, with a single header row at the top.
Do not leave blank rows or blank columns inside the data, and do not merge cells in a data area. Merged cells and blank gaps make sorting, filtering and tables harder.
Put the unit in the header (for example "Height (cm)") so the cells hold only numbers.
Insert > Table (or Ctrl+T on Windows) turns a range into an Excel table. Microsoft Support describes tables as a way to manage and analyze related data, with built-in filters and banded rows.
In a table, click inside it and use Table Design > Total Row to add a row of totals. Microsoft Support says each Total Row cell has a drop-down so you choose SUM, AVERAGE, COUNT and others. The default totals use SUBTOTAL, which ignores rows hidden by a filter.
A table also lets formulas use column names (called structured references), such as =SUM(Plants[Height (cm)]).
Data > Data Validation (Data Tools group) limits what can be typed in. Choose Whole Number, Decimal or List (a drop-down list). Use the Input Message tab to show a hint, and the Error Alert tab to pick how strict to be: Stop does not let the wrong value in; Warning lets the user continue after the warning.
Data > Remove Duplicates deletes repeated rows. Work on a copy first, because the deleted rows are gone.

Example (practice data)

Plant growth lab: columns Plant, Group, Week, Height (cm). Group is a drop-down list with Sun or Shade. Height accepts decimal numbers between 0 and 100.

Step by step

Open a new workbook and rename the first tab "Raw Data" (right-click the tab, Rename).
In row 1 type the headers: Plant, Group, Height (cm).
Type at least 8 rows of practice data below it. Use no blank rows and no merged cells.
Click any cell in the data and choose Insert, then Table. Tick "My table has headers" and click OK.
Select the Group column cells (not the header). Click Data, then Data Validation. Under Allow choose List and type Sun,Shade in Source.
Select the Height cells. Open Data Validation again, choose Decimal, between 0 and 100. On the Error Alert tab pick Stop and write a short message.
Click inside the table and tick Total Row on the Table Design tab. Use the drop-down in the Height cell to choose Average.
Try to type "tall" in a Height cell. Excel should stop you.
Check: your data has one header row, one record per row, no gaps, and the validation blocks wrong entries.

(Menu names can differ by version. In older versions the tab is called Table Tools > Design.)

Watch on YouTube

How to remove duplicates in ExcelKevin Stratvert · YouTube · youtube.com

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

LESSON 01Set up your data as a clean table

LESSON 01 / ANIMATED LESSON / AI VOICE00:00 / 01:00

Voice

Read the video script

[Host] "Before any chart or formula, fix the layout. One row per record. One column per kind of information."
[Screen: Insert > Table] "One click turns your range into a table with filters and banded rows."
[Screen: Data Validation, List: Sun,Shade] "Add a drop-down so nobody can type 'sunn' by mistake."
[Text on screen: Work on a copy before Remove Duplicates] "And copy your sheet before you delete duplicates. Deleted rows do not come back by themselves."

Try it now

Make a "Raw Data" sheet for a survey of 10 classmates (name or number, grade, hours of sleep). Turn it into a table, add a drop-down for grade, and a validation that only allows sleep hours between 0 and 24.

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 Coursework: tables, charts and lab reports" on The Future Market. Lesson 1: "Set up your data as a clean table".

What the lesson teaches:
- Good layout: one row per record (one plant, one trial, one student) and one column per kind of information, with a single header row at the top.
- Do not leave blank rows or blank columns inside the data, and do not merge cells in a data area. Merged cells and blank gaps make sorting, filtering and tables harder.
- Put the unit in the header (for example "Height (cm)") so the cells hold only numbers.
- Insert > Table (or Ctrl+T on Windows) turns a range into an Excel table. Microsoft Support describes tables as a way to manage and analyze related data, with built-in filters and banded rows.
- In a table, click inside it and use Table Design > Total Row to add a row of totals. Microsoft Support says each Total Row cell has a drop-down so you choose SUM, AVERAGE, COUNT and others. The default totals use SUBTOTAL, which ignores rows hidden by a filter.
- A table also lets formulas use column names (called structured references), such as =SUM(Plants[Height (cm)]).
- Data > Data Validation (Data Tools group) limits what can be typed in. Choose Whole Number, Decimal or List (a drop-down list). Use the Input Message tab to show a hint, and the Error Alert tab to pick how strict to be: Stop does not let the wrong value in; Warning lets the user continue after the warning.

The exercise I'm working on:
Make a "Raw Data" sheet for a survey of 10 classmates (name or number, grade, hours of sleep). Turn it into a table, add a drop-down for grade, and a validation that only allows sleep hours between 0 and 24.

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 "Set up your data as a clean table" from the course "Excel for Coursework: tables, charts and lab reports".

Here's what the lesson says:
Think of your data like a tidy toy shelf. One shelf row per toy, one label on top of each shelf column. If the shelf is tidy, you can find anything fast. If toys are thrown everywhere, even a clever robot gets lost.

The activity:
Make a "Raw Data" sheet for a survey of 10 classmates (name or number, grade, hours of sleep). Turn it into a table, add a drop-down for grade, and a validation that only allows sleep hours between 0 and 24.

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

Microsoft Support, Overview of Excel tables: support.microsoft.com/en-us/excel/overview-of-excel-tables
Microsoft Support, Total the data in an Excel table: support.microsoft.com/en-us/excel/get-started/total-the-data-in-an-exc
Microsoft Support, Using structured references with Excel tables: support.microsoft.com/en-us/excel/using-structured-references-with-exc
Microsoft Support, Apply data validation to cells: support.microsoft.com/en-us/office/apply-data-validation-to-cells-29fe

My notes

I learned this

02