Convert a Crosstab Table to a Database Table in Excel
How to unpivot a crosstab table into a flat database format in Excel using the hidden PivotTable Wizard trick.
A crosstab table puts categories across the top and down the side, with values in the middle. It's easy to read. It's also incompatible with Power BI, Tableau, SQL, and most other tools, which expect one row per data point.
Excel has a built-in way to convert a crosstab into a flat table. It's buried inside the PivotTable and PivotChart Wizard — a legacy feature most users have never seen. The whole process takes about two minutes.
Add the Wizard to the Quick Access Toolbar
The PivotTable and PivotChart Wizard isn't on any ribbon tab. Add it to your Quick Access Toolbar first:
- Click File → Options.
- Select Quick Access Toolbar from the left sidebar.
- Set the dropdown to All Commands.
- Scroll to PivotTable and PivotChart Wizard, click Add, then click OK.
The wizard icon now appears in your toolbar at the top of the screen.
You only need to do this setup once. The wizard stays in your toolbar across all workbooks.
Convert the Crosstab
Step 1: Click inside your crosstab table, then click the PivotTable and PivotChart Wizard icon in your toolbar.
Step 2: Select Multiple consolidation ranges, then click Next.
Step 3: Leave Create a single page field for me selected. Click Next.
Step 4: With your crosstab data range highlighted (exclude any Totals rows or columns), click Add, then Next.
Including a Totals row or column will create extra rows in your flat table that double-count data. Highlight only the raw data range.
Step 5: Choose where to place the PivotTable — a new sheet is easiest — then click Finish.
The result looks like your original crosstab. That's intentional. The next two steps are where the transformation happens.
Step 6: In the PivotTable Fields panel on the right, uncheck Row, Column, and Page1. The table collapses to a single cell showing a grand total.
Step 7: Double-click that single cell.
Excel generates a new worksheet with the data fully unpivoted — one row per intersection of row label, column label, and value. Delete the Page1 column, rename the headers, and the table is ready to use.
What You Get
Given this crosstab:
| Q1 | Q2 | Q3 | |
|---|---|---|---|
| North | 120 | 95 | 140 |
| South | 88 | 110 | 102 |
The output is a flat table with one row per data point:
| Region | Quarter | Value |
|---|---|---|
| North | Q1 | 120 |
| North | Q2 | 95 |
| North | Q3 | 140 |
| South | Q1 | 88 |
| South | Q2 | 110 |
| South | Q3 | 102 |
This format loads directly into Power Query, Power BI, Tableau, or any database import tool.
If Your Data Needs Ongoing Transformation
The double-click method creates a static snapshot. If your crosstab updates regularly, use Power Query instead:
- Load the crosstab into Power Query (Data → Get Data → From Table/Range).
- Select the row-label column, then go to Transform → Unpivot Other Columns.
- Rename the Attribute and Value columns, then load the result.
Power Query's unpivot refreshes automatically when the source data changes. For a one-time conversion, the wizard is faster. For recurring data, Power Query is the right tool.
Related Functions
Count Unique Values in Excel (Ignore Duplicates)
When you need a count of distinct items in a list full of duplicates, Excel's SUM/COUNTIF array formula gives you the answer in a single cell — no pivot table, no manual counting.
How to Calculate Bond Payment Dates in Excel
Bond payment dates that fall on weekends or holidays shift to the next business day — extending the accrual period. Here's how to calculate adjusted payment dates automatically using EDATE, WEEKDAY, and IFNA.