Types of Data and How to Structure a Table

Before you calculate anything, you need to know what kind of data each column holds. The type tells you which operations make sense: you can average revenue, but averaging ZIP codes is nonsense. And before you analyze a table, it needs a clean structure: one row per record, one column per variable. This lesson covers both, so every later lesson (formulas, pivots, SQL, pandas) works the first time.

The two big families: categorical and numerical

Almost every column you'll meet belongs to one of two families, with two sub-types each:

Two more kinds show up in almost every business table. Dates and times are special: you can sort them, group them by month or quarter, and subtract them (days between order and delivery). Identifiers such as order numbers, ZIP codes or phone numbers look like numbers but aren't quantities. Boston's ZIP code 02108 stored as a number becomes 2108, losing the leading zero. Store identifiers as text.

Classifying the practice dataset

Here are the 10 columns of the sales table, classified. This is exactly the thinking you should do with any new dataset:

Half of the columns are categories you'll group by, and three are numbers you'll calculate with. That mix is typical of sales data:

The 10 columns of the sales table by type: 1 identifier, 1 date, 5 categorical and 3 numerical.

How tools store data types

Each tool has its own names for data types, but the ideas are the same. Knowing them helps you diagnose the most common beginner bug: a number stored as text, which won't sum, or sorts as "1, 10, 2."

A detail worth knowing: Excel stores a date as the number of days since its starting point, so January 1, 2025 is really the number 45658 formatted as a date. That's why you can subtract two dates to get the days between them. It's also why a date typed as text (like "10/15/2025" in our raw file) can't be grouped by month until you convert it.

Tidy data: the three rules

Statistician Hadley Wickham popularized the term "tidy data" for a structure that makes analysis easy. It comes down to three rules:

1. Each variable is a column. Revenue in one column, date in another, never "revenue in Q1" and "revenue in Q2" as separate columns of the same measure.
2. Each observation is a row. In our table, one row is one order. That's called the grain of the table.
3. Each value is a cell. One product per cell, one number per cell, no "Smart Watch, Yoga Mat" in the same cell.

A tidy table also has a primary key: a column whose value is unique for every row. Here it's order_id. If the same order ID shows up twice, either the grain is different from what you think or there are duplicate rows (our raw file has three; you'll remove them in Lesson 5). The product column also links to the separate Products sheet, where each product appears once with its cost. A column that points to another table's key is called a foreign key, and it's what makes JOINs possible in SQL.

Wide vs. long tables

The same information can be laid out in two shapes. Here's 2025 revenue by quarter for two regions:

Wide format:

Long format (first four of eight rows):

Wide tables are great for humans; long tables are great for tools. If you add a fifth quarter, the wide table needs a new column and every formula has to change. The long table just gets new rows. The rule of thumb: store and analyze in long format, present in wide format. A pivot table (Lesson 7) is exactly a machine that turns long data into a wide summary.

By the way, look at those numbers: the West sold just $593 in Q1 but $15,465 in Q4, while the South stayed between about $4,100 and $5,500 every quarter. The South is steadier; the West is spikier. You'll find out why in later lessons.

Table layouts that sabotage analysis

These habits are common in spreadsheets made for printing. Each one breaks sorting, filtering, pivots or imports:

Document it: the data dictionary

A data dictionary is a short table that explains every column: name, type, meaning, an example and known issues. It takes 15 minutes to write and saves hours of "what does this column mean?" messages. The practice file includes one; for example, it explains that revenue equals units × unit_price before tax and shipping. That single sentence tells you how to recover a missing revenue value, which you'll do in Lesson 5.

Summary

• Categorical data (nominal or ordinal) is for grouping and counting; numerical data (discrete or continuous) is for calculating.
• Identifiers like order IDs and ZIP codes look numeric but should be stored as text.
• Excel, SQL and pandas use different type names for the same ideas; a number stored as text is the most common bug.
• Tidy data: each variable a column, each observation a row, each value a cell, with a unique primary key.
• Store and analyze in long format; present in wide format.
• A data dictionary explains every column and its known issues.

Next lesson: Excel and Google Sheets for Data Analysis. You'll answer real questions about the dataset with SUMIFS, COUNTIFS, XLOOKUP and more.