Structuring Data in Rows and Columns

Why Spreadsheet Structure Matters

A spreadsheet can look orderly to a person and still be difficult to analyze. A heading stretched across several columns, a subtotal placed among observations, or several measurements combined in one cell can hide what each entry represents. Before using data, a reader should be able to identify the observational unit for each row and the variable for each column.

This arrangement is useful because it gives every recorded value a clear location: the row identifies the unit, and the column identifies the variable measured on that unit. A person can then compare values down a column or inspect several variables across a row without having to infer the table’s meaning from formatting.

The appropriate observational unit depends on the question and the data collection. If a clinic is measured once, a row might represent a clinic. If patients are measured at several visits, a row might represent a patient-visit rather than a patient alone. The key is to choose a unit that matches what one record describes, then apply that choice consistently.

A spreadsheet is not well structured merely because it has gridlines or because its contents fit on one screen. A useful data table has a clear header row, one value per cell, and no presentation formatting that must be interpreted to discover which observations belong to which variable.

From the Study to the Table

Start by naming the observational unit, then list the variables recorded for each unit. A data dictionary—the list of variables and their meanings—can help keep the column names and interpretations consistent. For instance, if a table describes one row per student, columns might include student ID, study group, and quiz score. The identifier distinguishes rows; the other columns record characteristics or measurements.

Each row should describe one unit, not a group average or a mixture of several units. Each column should have one meaning throughout the table. If a column is called score, its entries should all be scores for the same assessment and on the same scale, rather than scores in some rows and comments in others.

An identifier can be a useful column when it distinguishes units, especially if a person’s name is not appropriate or not unique. It is still important to ask what each row represents: an ID does not by itself establish that the table has one row per person. For example, the same patient ID could appear in several rows if each row represents a separate visit.

Keep Records Separate From Summaries

A summary answers a different question from a row of recorded data. For example, a total, average, or count describes multiple observations; it is not another patient, student, or clinic. Placing summary rows inside a table of individual records can make software or a reader treat the summary as if it were an observation.

Keep a table of records limited to the units being described. If a summary is useful, place it outside that table or in a separate summary table whose purpose is clear. This preserves the distinction between the original units and a calculation based on them.

Choose the Row Unit for Repeated Measurements

When the same unit is measured more than once, decide what one row should represent. One option is a row per person with separate columns for each occasion. Another is a row per person per occasion, with a column identifying the occasion. The second choice makes the observational unit a person-occasion record. Either arrangement can be coherent if it is explicit and fits the intended data use; the important point is not to leave repeated measurements hidden in a single cell or unclear from the row structure.

A Structural Check: One Value for Each Unit and Variable

The table structure can be understood as a mapping from a unit and a variable to a value. For every recorded unit-variable pair, there should be one clearly located value. If a cell contains “12 minutes; late arrival,” it combines values for potentially different variables. If one column contains waiting times for patients in some rows and waiting-time averages for clinics in others, the column does not have one consistent meaning.

This gives a short justification for the row-and-column rule: a value can be interpreted only when its unit and its variable are identifiable. With one unit per row and one variable per column, the cell at their intersection identifies both. When a table relies on merged formatting, embedded summaries, or combined values, that mapping is no longer reliably visible without extra interpretation.

Common Mistakes and Practical Checks

A common mistake is to treat spreadsheet appearance as data structure. For example, someone may reason, “The category label is shown above these rows, so it is obvious which rows it applies to.” The correction is to store that category explicitly in each relevant row. Formatting can aid presentation, but it should not carry information needed to interpret the records.

Another mistake is to leave a total row in place because “it is just one more row.” It is not one more observational unit. The correction is to separate summaries from the records they summarize. Similarly, placing two variables in one cell because the information fits—for example, “Morning, 12”—makes later interpretation depend on splitting that text. Store the two values in separate columns.

Before treating a small spreadsheet as a data table, check the following:

• Can you state exactly what one row represents?
• Does each column have one consistent meaning and a clear heading?
• Are category labels recorded in the rows to which they apply, rather than only shown through merged cells or layout?
• Does every cell hold one value for one variable, rather than a list or a combined description?
• Are totals and other summaries outside the rows of individual records?

These checks concern structure, not whether the recorded values are correct. A well-structured table can still contain inaccurate or incomplete data, and a table with correct values can still be hard to interpret if its rows and columns do not clearly represent units and variables. Clear structure is the foundation for assessing and using the values responsibly.