Convert a Crosstab Table into a Database Table Format

Data in a crosstab table is easy for humans to read but is not compatible with database storage or tools like Tableau or Power BI, which expect flat table format. Excel has a feature hidden inside the Pivot Table wizard that converts crosstab data into a flat format automatically.

Add the Wizard to the Quick Access Toolbar

The easiest way to access this feature is to add the PivotTable and PivotChart Wizard to your Quick Access Toolbar:

1. Click the File tab → Options.
2. On the Excel Options screen, click Quick Access Toolbar and choose All Commands.
3. Scroll down to PivotTable and PivotChart Wizard, click Add, then click OK.

Steps to Convert

Step 1: Click the PivotTable and PivotChart Wizard icon on your Quick Access Toolbar.

Step 2: On the pop-up screen, choose Multiple consolidation ranges, then click Next.

Step 3: Leave the default Create a single page field for me selected, then click Next.

Step 4: Highlight your crosstab data range (you may want to exclude Totals columns), click Add, then click Next.

Step 5: Choose whether to place the Pivot Table in a new or existing worksheet, then click Finish.

At this point, the Pivot Table looks similar to your original crosstab — but the next steps reveal the power of this approach.

Step 6: In the Pivot Table Task Panel (usually on the right), uncheck Row, Column, and Page1 (or whatever page number appears). The Pivot Table collapses to a single value.

Step 7: Double-click that single value.

A flat table appears in a new worksheet with one row per data point. Delete the Page1 column, rename the headers to match your database field names, and the data is ready to import or use as a Power BI / Tableau data source.