4 Tables and Data Preparation
Learn how to structure, format, validate, sort, and filter spreadsheet data while preserving its meaning and the relationships between values.
Structure the data
A well-prepared dataset is easier to check, sort, filter, summarize, and analyze. A consistent structure and careful preparation help ensure that each value means what it appears to mean.
A typical analysis has one header row and one record per row. Each column holds one field, such as a date, product, region, or amount. Use short, distinct column names, and keep related values in separate columns—for example, use separate First Name and Last Name fields when they may need separate analysis.
Create a spreadsheet
In Excel, select a cell in the data range and choose Insert > or Format as . Confirm that the selected range includes the headers, then give the a meaningful name, such as Orders.
headers provide built-in controls for and . can use and column names instead of fixed cell ranges. Avoid blank header cells, merged cells, and extra title or subtotal rows inside the data range.
Format values consistently
Formatting should make values readable without changing their meaning. Use a consistent date format in a date column, format monetary values as currency and rates as percentages, and use the same units and number of decimal places within comparable fields.
Use text formatting for identifiers such as ZIP codes or product codes when leading zeros must be preserved. A cell’s affects how its value is displayed, but does not necessarily change the stored value. For instance, showing two decimal places may hide additional decimal places, so check the underlying value when precision matters.
Use restrained styles for readability, and do not rely on color alone to convey information.
Prepare and validate entries
Prepare data systematically before analyzing it. Preserve an untouched copy or source file so corrections can be checked or reversed. Check that field meanings and data types are appropriate: dates should be dates, quantities should be numbers, and identifiers should not be mistaken for quantities.
Standardize category entries by choosing one spelling and format, such as North rather than a mixture of N, north, and North. Remove accidental spaces and check for inconsistent units.
Review missing and implausible values. Decide whether blanks are allowed, need correction, or should be marked explicitly. Do not silently replace a missing value with zero, because zero may be a real measurement.
Review duplicates and control entries
Check for duplicates using the correct key. A repeated customer name may be valid, while a repeated transaction ID may signal a problem. Preview duplicates before deleting them, because removing duplicates changes the data.
Where appropriate, use controlled lists or to limit entries to approved categories and reduce spelling variations.
Sort and filter without breaking records
rearranges rows, while temporarily hides rows that do not meet selected criteria. Use header controls to sort or filter by a field.
When , make sure the entire record moves together. only one column can mismatch values across a row. After , clear the filter to see the full dataset again.
Follow a preparation workflow
For a sales dataset containing transaction date, item, region, units, and revenue, a practical preparation workflow is:
Save a copy of the original file.
Convert the data range into a named with clear headers.
Set date, whole-number, and currency formats for the appropriate columns.
Standardize region names and inspect blanks, unexpected values, and repeated transaction IDs.
Apply filters to investigate particular dates or regions, then clear them before reporting results.
Record any corrections or exclusions so another person can understand how the analysis-ready was prepared.
The underlying habits are to use one header row, one record per row, and one type of field per column; format fields consistently; investigate missing values and duplicates; preserve the original data; and use and without breaking row relationships.