How to Normalize Data in Excel: The Difference Between Structured and Flat Tables

You have a spreadsheet with messy, repeated data that is difficult to analyze or update. This often happens when information is stored in a single, wide table. Normalizing your data organizes it into separate, related tables to reduce redundancy. This article explains the difference between flat and structured tables and provides steps to normalize your data in Excel.

Understanding Flat Tables vs. Structured Tables

A flat table, also called a denormalized table, contains all information in one large grid. It often has repeating values across many columns and rows. For example, a sales log might list a customer’s full name, address, and product details on every single order row. This repetition makes the file larger and prone to errors. If a customer’s address changes, you must find and update every row for that customer.

A structured table, or normalized data model, breaks this information into separate, smaller tables. One table might hold unique customer data, another table holds product details, and a third table records only the transactions, using ID numbers to link to the other tables. This structure is the core principle of database design. Excel supports this model through features like Excel Tables and the Data Model used by PivotTables.

The Goal of Normalization

The main goal is to eliminate data redundancy and dependency. Each piece of information should be stored in only one place. This makes your data more consistent, saves space, and simplifies updates. In Excel, normalization prepares your data for advanced analysis with PivotTables, Power Pivot, and formulas without manual cleanup.

Steps to Normalize a Flat Table in Excel

The most effective way to normalize data is using Power Query, a built-in data transformation tool. The following method splits one flat table into multiple related tables.

1. Load your flat table into Power Query
   Select any cell within your flat data range. Go to the Data tab and click From Table/Range in the Get & Transform Data group. This opens the Power Query Editor window.
2. Identify and extract a lookup table
   Look for columns with repeating categorical data, like Customer Name or Product Category. Select the column header for one category. Go to the Transform tab and click Extract Values > To Table. Click OK in the dialog. This creates a new query with only the unique values from that column.
3. Add an index column to create a key
   With the new table of unique values selected, go to the Add Column tab and click Index Column > From 1. This number column will serve as the primary key to link tables.
4. Replace original values with key IDs in the main table
   Go back to the query for your original flat table. Select the column with the repeating data you just extracted. Go to the Home tab, click Merge Queries. In the dialog, select the lookup table query you created. Match the original column to the text column in the lookup table. Choose the new index column as the output, and click OK. Expand the new column to show only the index values. This replaces text like “Customer A” with an ID number like “1”.
5. Load the normalized tables back to Excel
   In the Power Query Editor, select each query. On the Home tab, click Close & Load To. Choose to load the main fact table to a worksheet. For the lookup tables, select Only Create Connection and check Add this data to the Data Model. This loads them into the background Data Model where relationships can be built.
6. Create relationships in the Data Model
   Go to the Data tab and click Manage Data Model. In the Power Pivot window, go to the Diagram View. Drag the index field from your lookup table and drop it onto the corresponding ID field in your main fact table. A line will appear, creating a relationship.

Normalizing with Excel Formulas

If you cannot use Power Query, you can simulate normalization using formulas. First, manually create separate tables for your unique categories. Then, in your main transaction table, use the XLOOKUP function to pull in ID numbers. For example, if you have a Customer table with an ID and Name, use =XLOOKUP([@Customer], CustomerTable[Name], CustomerTable[ID]) in your main table to convert names to IDs.

Common Mistakes When Normalizing Data

Not Creating a Proper Key Column

A relationship requires a unique identifier in the lookup table. Using a text field like a product name can fail if names have typos or change. Always create a numeric ID column, like an Index, that will not change. This key must exist in both tables for the relationship to work.

Forgetting to Refresh Queries After Changes

Data loaded via Power Query is not automatically updated. If you change the source data, you must refresh the queries. Right-click on a resulting table and select Refresh, or go to the Data tab and click Refresh All. Failure to do this will leave your analysis using old, incorrect data.

Over-Normalizing Simple Datasets

For very small, simple datasets that will not grow, creating multiple tables can add unnecessary complexity. If you have fewer than 100 rows and only need basic sorting, a single flat table formatted as an Excel Table (Ctrl+T) may be sufficient. Normalization provides the most benefit for data that scales or needs complex reporting.

Flat Tables vs. Structured Tables: Key Differences

Item	Flat Table	Structured (Normalized) Table
Data Structure	All data in one wide worksheet	Data split into multiple related tables
Data Redundancy	High, with many repeated values	Low, each fact stored once
Update Process	Must find and edit every instance of a value	Edit once in the lookup table
File Size	Larger due to repetition	Typically smaller
Best Use Case	Simple lists, one-time analysis	Scalable data, databases, recurring reports
Primary Excel Tool	Basic cell ranges or Excel Tables	Power Query, Data Model, PivotTables

You can now transform a messy flat table into an efficient, structured data model. Use Power Query to automate the splitting and cleaning process, which is essential for building dynamic dashboards. For your next project, try creating a PivotTable from your new Data Model to see how easily it summarizes data from multiple related tables. Use the Manage Data Model interface to view and edit all table relationships in one diagram view.