Level 1 · Quiz: what kind of data is in each column?

Before you summarise a column, you need to know what kind of data it holds. The same digits can be a quantity you can add up or a label you can only count. Get this wrong and Excel won’t warn you: it will average your order ids without complaint.

Numbers you count and numbers you measure

Numeric (quantitative) columns hold values you can do arithmetic with. They come in two kinds:

Kind	How you get it	Harbor & Pine examples
Discrete	by counting, whole steps only	quantity on an order line, orders per customer, pages_viewed
Continuous	by measuring, any value in a range	unit_price, refund_amount, the weight of a parcel

Money is a judgement call worth knowing: technically it moves in whole cents, but analysts treat prices and order values as continuous because the steps are tiny compared to the values. Ask yourself “can there be 2.5 of these?” A quantity of 2.5 is a data-entry error (you’ll write a rule for exactly that later in this chapter); a price of 2.5 is fine.

Categories with and without an order

Categorical (qualitative) columns sort rows into groups. Again, two kinds:

Kind	Meaning	Examples
Nominal	groups with no natural order	channel (web, app, marketplace), category, city, reason on a return
Ordinal	groups with a natural order	satisfaction (poor < fair < good < excellent), shipping speed (standard < express), size S < M < L

The order matters for what you can say. “62% of tickets were good or better” makes sense for an ordinal scale. “Most orders were web or better” does not. One more trap: Excel sorts ordinal text alphabetically, so excellent, fair, good, poor comes out in the wrong order. Sort by a helper column holding 1–4, or by a custom list (Sort dialog → Order → Custom List).

Numbers that are really labels

order_id, customer_id, product_id, zip codes and phone numbers are stored as digits, but they only name something. The test is simple: would the sum or average mean anything? The average product_id of an order is a real number Excel will happily show, and it means nothing. Treat these as nominal. Count them (COUNT, COUNTIF), match on them (XLOOKUP), never add them up.

One row per thing, one column per variable

Excel’s filters, sorts, Tables and PivotTables all assume the data is a plain grid:

• each row is one observation: one order line, one ticket, one customer;
• each column is one variable, with one header and one type all the way down;
• no blank rows or subtotal rows in the middle, no merged cells, the header in a single row.

A column that mixes numbers with notes like "n/a" or "see email" breaks this, and so does a “month” header row repeated every 30 rows. Fix the shape first, then analyse.

Data reaches you in a few common file types. CSV is plain text with one row per line and commas (or semicolons, in many European locales) between values: no types, no formatting, which is why ids lose leading zeros on import. XLSX is Excel’s own format, with several sheets, formulas and formats. JSON is the nested key-value text that web APIs return; Excel can import it through Power Query, but it usually needs flattening into rows first.

Common mistakes

• Averaging an id or a zip code because it’s stored as a number.
• Sorting an ordinal text column A→Z and presenting excellent before poor.
• Calling money discrete and then refusing a perfectly valid average order value.
• Mixing types in one column (12, "12 units", "n/a"), which makes every later formula guess.