Data Organization and Table Structure

The Spreadsheet Nobody Could Use

The sales report had merged cells spanning three columns. Region names were in row headers, not a column. Some months were abbreviated (“Jan”), others spelled out (“January”). Two products had trailing spaces in their names, making them invisible duplicates. Every formula broke. Every pivot table failed.

The data was all there. The structure made it unusable.

By the end of this lesson, you’ll organize data so that formulas, pivot tables, charts, and AI analysis all work flawlessly.

🔄 Quick Recall: In the previous lesson, we mastered IF, SUMIF, and text functions. Remember how SUMIF matches criteria in a range? If that range has inconsistent data (“North,” “north,” “NORTH “), your formula won’t catch all entries. Today we prevent those problems at the source.

The Rules of Good Data Structure

Rule 1: One Row = One Record

Every row should represent a single observation, transaction, or entry. No merged cells spanning rows. No summary rows mixed with data rows.

Bad:

Good:

The “good” version has more rows but works with every spreadsheet tool: SUMIF, pivot tables, filtering, and charts.

Rule 2: One Column = One Attribute

Each column should contain one type of information. Don’t mix names and titles in one column. Don’t put city and state in one cell.

Rule 3: No Blank Rows or Columns

Blank rows break data ranges, table references, and pivot tables. If you need visual separation, use formatting (borders, colors), not blank rows.

Rule 4: Headers in Row 1 Only

One clear header row. No multi-line headers. No merged header cells. Headers should be unique, descriptive, and short.

✅ Quick Check: You receive a spreadsheet where months are columns (Jan, Feb, Mar…) and products are rows. Why is this problematic for analysis? How would you restructure it?

Converting to Proper Tables

Both Excel and Google Sheets have formal “Table” features that add powerful capabilities:

Excel: Select your data → Insert → Table (Ctrl+T) Google Sheets: Select your data → Format → Alternating Colors (for visual table) or use filter views

Benefits of formal tables:

• Automatic expansion when you add new rows
• Structured references in formulas (=SUM(Sales[Amount]) instead of =SUM(C2:C100))
• Built-in filtering and sorting
• Automatic formatting that persists with new data
• Named ranges that update automatically

I have data organized like this:
[describe your current structure]

Help me:
1. Identify structural problems
2. Suggest the correct flat-table format
3. Write formulas or steps to restructure the data
4. Recommend column headers and data types
5. Suggest data validation rules for each column

Data Validation

Data validation prevents bad data at the point of entry.

Dropdown Lists

For columns with fixed options (Status, Department, Region):

Excel: Data → Data Validation → List → Enter options separated by commas Google Sheets: Data → Data Validation → Dropdown

Number Ranges

For columns that should contain numbers within a range (Age: 18-100, Rating: 1-5):

Both: Data Validation → Number → Between → Set min and max

Date Constraints

For date columns (Start Date must be after 2020):

Both: Data Validation → Date → After → Set the date

✅ Quick Check: You have a spreadsheet where anyone can type anything in the “Department” column. What problems could this cause for SUMIF and pivot tables? How would data validation prevent them?

Data Cleaning Essentials

Real-world data is messy. These techniques clean it up:

Remove Duplicates

Excel: Data → Remove Duplicates Google Sheets: Data → Data Cleanup → Remove Duplicates

Standardize Text

Use formulas to clean before analyzing:

• =TRIM(A2) — Remove extra spaces
• =PROPER(A2) — Standardize capitalization
• =SUBSTITUTE(A2, " ", " ") — Replace double spaces with single

Find and Fix Inconsistencies

I have a column of department names that should be
standardized. Here are the unique values I found:
[list them, e.g., "Mktg", "Marketing", "marketing",
"Marketing ", "MARKETING"]

Write a formula that converts all variations to
the correct standard name. Use TRIM, UPPER, and
IF/SWITCH as needed.

Handle Missing Data

Decide on a strategy before analyzing:

• Remove rows with missing critical data
• Fill with defaults (0 for numbers, “Unknown” for text)
• Flag for review with conditional formatting

The Data Organization Checklist

Before running any analysis, verify:

Review my spreadsheet structure:
[describe or paste sample data]

Check against best practices:
1. Is every row a single record?
2. Is every column a single attribute?
3. Are there blank rows or columns?
4. Is there exactly one header row?
5. Are there merged cells? (They must be removed)
6. Are data types consistent within each column?
7. Are there hidden duplicates (extra spaces, case
   differences)?
8. Would this data work in a pivot table as-is?

Try It Yourself

Take a messy spreadsheet you’ve worked with (or create one with deliberate problems: merged cells, inconsistent names, blank rows). Clean it up:

1. Unmerge all cells
2. Restructure into flat table format (one row per record)
3. Standardize text with TRIM and PROPER
4. Add data validation to columns with fixed options
5. Convert to a formal Table (Ctrl+T in Excel)

The cleaned spreadsheet should work perfectly with SUMIF, pivot tables, and charts without any additional adjustments.

Key Takeaways

• Proper data structure (one row per record, one column per attribute) is the foundation of all spreadsheet analysis
• Merged cells, blank rows, and multi-level headers break formulas, pivot tables, and filtering
• Formal tables (Ctrl+T in Excel) add automatic expansion, structured references, and built-in filtering
• Data validation at entry prevents the inconsistencies that cause analysis errors downstream
• Always clean data before analyzing: TRIM spaces, standardize case, remove duplicates
• AI can identify structural problems and write cleaning formulas from a description of your data

Up Next

In Lesson 4: Lookup Functions, we’ll master VLOOKUP, INDEX-MATCH, and XLOOKUP—the functions that connect data across different tables and sheets.