If you have ever stared at a spreadsheet and whispered a prayer the answer is probably simple. Rows run left to right and hold individual records. Columns run top to bottom and hold attributes that repeat across records. That is the main idea and it is boring and useful at the same time.

Visual orientation

Look left to right for rows. Look top to bottom for columns. If the group of cells reads like one story about one thing you are looking at a row. If the group is a stack of similar facts you are looking at a column. This trick saves time and dignity when cleaning messy data in excel or when reading tables from a database.

Headers and identifiers

Put column headers on top to name attributes such as name age email or amount. Give each row a unique identifier in a column to make joins and lookups reliable. In databases this is usually a primary key column. In spreadsheets a unique id column is your friend when formulas start acting possessed.

Simple example

Operations and common pitfalls

Aggregations like sum average and count usually apply to columns. Filters and edits usually target rows. If you want to sum sales apply the function to the sales column. If you want a particular transaction find the row that represents that transaction. When writing sql you select columns to return and you filter rows with where clauses.

select id name amount
from sales
where id = 123

Pivot tables and charts get confused when rows and columns are swapped. The first fix is to check orientation before blaming the spreadsheet gremlin. The second fix is to make a cup of coffee and then check orientation again.

Performance and indexing

In databases you create indexes on columns that you use for lookups joins and sorting. Indexing speeds row retrieval when the database needs to find matching records. Do not over index everything because that slows writes and makes your backups heavier than they need to be.

Design tips that will make future you grateful

• Use columns for consistent attributes and rows for single records
• Give rows a unique id column for joins and lookups
• Keep one type of data per column to avoid weird formula behavior in excel
• Freeze headers when scrolling to keep context in tables
• Index columns that are frequently used in where clauses and joins in sql

Know this and you will waste less time unpicking accidental transposes strange aggregations and awkward joins. And when a pivot table misbehaves you can point at it and say with confidence that the problem is human not mystical.