1 Spreadsheet Data Foundations

Organize data as a table

Organize a simple data list with one record in each row and one field in each column. Use a clear header row to name each field, such as Order ID, Date, Product, Quantity, and Unit Price. Keep each column consistent: dates belong in the Date column, quantities in Quantity, and so on.

Avoid blank rows or columns inside the data, merged cells, and multiple header rows because they can interfere with selecting, sorting, or analyzing the complete list. Keep notes and totals outside the data table so they are not mistaken for records. In Excel, a contiguous data range can be formatted as a table to help manage related data.

Enter and format values carefully

A cell can contain text, a number, a date, a time, or a formula. Choose a format that matches the value’s meaning, such as a date format for dates or a currency format for prices. Formatting controls how a value appears, but does not necessarily change its stored value; for example, displaying two decimal places can hide additional decimal places stored in the cell.

Identifiers such as phone numbers, ZIP codes, and product codes are usually labels rather than quantities. Store them as text when needed to preserve leading zeros or prevent unwanted calculations. In Excel, formatting empty cells as Text before entering these values can help preserve them.

When importing or pasting data, check that values were interpreted correctly. A value that looks like a date might actually be text, and numbers may contain unwanted spaces or characters. Use consistent spelling, capitalization, units, and date conventions within each field. If values are missing, represent that consistently and document what a blank means rather than using different placeholders in different rows.

Clean values without losing information

Before analysis, inspect data for extra spaces or hidden characters that make otherwise identical text differ; inconsistent entries; values in the wrong field; duplicate records; and numbers or dates stored as text. For example, North has leading and trailing spaces and may not match North in a comparison. Entries such as “NY,” “New York,” and “New York City” may also be inconsistent when they are intended to represent the same category.

Spreadsheet tools can trim extra spaces. Google Sheets also provides data-cleanup tools for trimming whitespace and removing duplicates. Review duplicates before deleting them: two rows with the same customer name might represent different orders, so check the fields that identify a record. Keep an unchanged copy of important data before making irreversible edits.

Understand cell references and ranges

A cell address uses a column letter and row number; B3 identifies the cell in column B, row 3. A cell reference points to a cell so a formula can use its value. For example, =D2*E2 refers to the quantity and unit price in row 2.

A range is a group of cells. A colon means “from the first cell through the last cell,” including the cells between them:

• A1:A5 represents cells A1 through A5 in one column.

• B2:E10 represents the rectangle from B2 through E10.

• A2:A5,C2:C5 represents two separate ranges in spreadsheet applications that accept comma-separated range arguments.

Control what changes when formulas are copied

References behave differently when a formula is copied. A relative reference, such as A2, adjusts to its new position; copying a formula one row down changes it to A3. An absolute reference, such as $A$2, keeps both the column and row fixed. A mixed reference, such as $A2 or A$2, fixes only the column or only the row.

Suppose E2 contains a unit price and F1 contains a tax rate used for every row. A formula in another column can refer to the row’s price with a relative reference and the shared rate with an absolute reference: =E2*$F$1. When copied down, E2 adjusts to E3, while $F$1 stays fixed.

Prepare data for analysis

Prepare data in a deliberate order:

1. Keep an untouched copy of the original data.

2. Add one clear header for each field and arrange one record per row.

3. Check that each column contains a consistent kind of value.

4. Apply appropriate number, date, currency, or text formats.

5. Look for blanks, inconsistent labels, extra spaces, duplicates, and values stored as text.

6. Use cell references and ranges deliberately, and confirm that a formula points to the intended cells before copying it.

A tidy table, values entered in forms that match their meanings, careful cleaning, and precise references help make spreadsheet data easier to sort, filter, summarize, and calculate with.