Data Literacy Before Excel

Ask a question, name the rows and columns, and clean types before you SUM anything.

This week

Week 2 · Excel Ch. 1–2 · Excel Project 2. Next: formulas and references (lecture 3).

After this lecture you should be able to

Describe a dataset as observations (rows) and variables (columns)
State a question before opening a workbook
Spot common data problems: mixed types, missing values, merged cells, extra header rows
Import a CSV into Excel and turn a range into a proper Table

Excel is not a poster

Color, clip art, and merged title cells can make a sheet look finished
Analysis needs a rectangle of data: one header row, one observation per row, one variable per column
If the sheet is laid out for printing, it is often a poor layout for formulas, charts, and PivotTables

Two sheets, two jobs

Sheet role	Looks like	Good for
Data	Plain table, no merged cells	Formulas, sorts, pivots, Python later
Presentation	Titles, colors, charts, Word/PowerPoint paste	Humans reading a story

A running example

Question: Among these campus events, which category had the highest attendance in September?

You cannot answer that until you know:

Vocabulary

Write the question in a cell

Put the question in A1 of a Notes sheet, in a Word outline, or in the assignment header
Good: “What is the average lab score for students who submitted on time?”
Vague: “Do something with this spreadsheet”
SLO1 starts here: comprehend the problem before designing the solution

Units and definitions

Time in minutes or hours?
Cost with or without tax?
Grade as percent or letter?
If two people use different definitions, both can “calculate correctly” and still disagree

Classic spreadsheet problems

Why this is a science issue, not just a software issue

GE Goal 3: quantitative methods only work if the measurements mean what you think they mean
A tidy table is closer to a lab notebook than to a flyer
Garbage in, formatted-nicely garbage out

CSV is a lowest-common-denominator file

Data literacy includes what is missing

Who is not in the dataset?
Was participation voluntary?
Can a person be identified from a “anonymous” sheet (name + section + rare major)?
Course work: do not publish real student identifiers; use sample or de-identified files

Discussion

Designate a note-taker
If a club spreadsheet omits events with zero attendance, what happens to the average?
When is it honest to drop a row, and when is it cooking the data?

Lab 2 — From messy grid to Table

Download (or type) a small messy list: extra title row, mixed date formats, a blank row, a total row
Delete the decorative title from the data rectangle (move it to a Notes sheet)
Remove blank rows and the total row from the data
Make sure each column has a short, unique header (date, category, attendance)
Select the rectangle → Insert → Table (or Ctrl/Cmd + T)
Set column types: Date, Number, Text
Sort by date; filter one category
On a Notes sheet, write one sentence: the question this table could answer, and one it cannot

Lab 2b — Import a CSV without trusting Auto

Data → From Text/CSV
Check the preview: did Excel turn IDs into numbers?
If needed, set the ID column to Text before loading
Save as .xlsx so you keep Table formatting; keep the original CSV as the raw source

Optional Word companion

In Word, write a 5-line data dictionary: column name, type, allowed values, missing-value code
That document is the specification your Excel (and later Python) work should obey

Carry these into formulas week

Placeholder notes for expansion