Preparing Clean Tables for the Data Model

You have worked with Excel tables before. You know how to filter, sort, and build a PivotTable. So why dedicate a chapter to clean data?

Because the Data Model is less forgiving than a standard PivotTable. When you load a table into Power Pivot and define relationships, every structural problem in the source data becomes a model problem. Title rows prevent a table from loading correctly. Blank rows inside the data confuse relationships. Dates stored as text break time-based calculations entirely. Numeric columns formatted as text produce a sum of zero.

Fixing these issues before loading is fast. Finding and fixing them after building a model on top of them is slow and frustrating. This chapter is a focused refresher on the specific cleaning steps that matter most before data modeling.

A table is ready for the Data Model when it meets all of the following conditions:

You do not need to memorise this list. Use it as a checklist before loading any table into the Data Model.

Converting to a named Excel Table

Once the data is clean, the final step is converting the range into a formal Excel Table and giving it a name. This is not optional — Power Pivot works with named Excel Tables, not plain ranges.

• Click anywhere inside the clean data range;
• Go to Insert → Table;
• Confirm that My table has headers is ticked. Click OK;
• On the Table Design tab, find the Table Name field on the far left;
• Replace the default name (Table1, Table2, etc.) with a meaningful name — for example: Sales, Customers, or Products.

Naming conventions:

• Use a single descriptive word where possible: Sales, not SalesData2025;
• No spaces. If you need two words, use PascalCase: SalesOrders, not Sales Orders.
• Avoid generic names like Table1 or Sheet2 — they are meaningless in the Power Pivot field list.

A named table automatically expands when new rows are added, which means refreshed data will flow into the model without you needing to adjust any ranges.

Transform the provided messy export into a single, clean, named Excel Table that is ready to be loaded into the Data Model.

Step 1 — Fix the structure

Open the Messy_Export sheet and fix the following structural problems:

1. Delete the title rows and any blank rows above the real header row, so that row 1 becomes the column header row.
2. Find and delete any blank rows inside the data. Use Ctrl+G/Fn+F5 → Special → Blanks if you want to locate them quickly.
3. Delete the TOTAL row at the bottom of the data.

When done, your sheet should have exactly one header row followed by one row per order, with nothing above and nothing below the data.

Step 2 — Fix data types

1. Select the Order Date column. Apply a consistent short date format (DD/MM/YYYY). Any value that stays left-aligned after formatting is stored as text — fix those cells using the DATEVALUE() function or Text-to-Columns (Data → Text to Columns → Finish).
2. Check the Quantity, Unit Price, and Total columns. If any values are left-aligned, they are stored as text. Fix them using Paste Special → Multiply by 1, or the VALUE() function.

When done, all dates should be right-aligned and formatted consistently, and all numeric columns should be right-aligned.

Step 3 — Convert to a named Excel Table

1. Click anywhere inside the clean data range.
2. Go to Insert → Table. Confirm that "My table has headers" is ticked. Click OK.
3. On the Table Design tab, rename the table from the default (Table1 or similar) to Sales.