We sometimes get data in a crosstab table. Data in crosstab table are user-friendly to be read and understood by a human being. However, they are usually not compatible with data storage requirement in database. That causes many problems if you need to convert such data to be imported in a database or to make them the data sources of other graphical analytics tools such as Tableau or Microsoft’s Power BI.
The below 2 screenshots illustrate the difference between a crosstab table and a table format required by a database table. The first screenshot is a crosstab table. The second screenshot is the format that you need to import into a database or to be read by Power BI.
Microsoft Excel has a very neat feature in the Pivot Table that can easily convert a Crosstab table into a data file that can be imported into a database. However, this feature in Pivot Table is kind of “hidden”. If you just use “Insert” and then choose “Pivot Table”, you may not easily find out where it is. The best way is to add a “PivotTable and PivotChart Wizard” to your “Quick Access Toolbar” so you can jump in directly from there. Please follow below steps to add such wizard.
Step 1: When you are in Excel, click the “File” tab. Then choose “Options”.
Step 2: On the “Excel Options” pop-up screen, click the “Quick Access Toolbar” and choose “All Commands”. Scroll down the left panel unitl you see the “PivotTable and PivotChart Wizard” command. Click the “Add” button to add it to the right panel. Then click ok to close the pop-up screen.
Click the “PivotTable and PivotChart Wizard” icon on your “Quick Access Toolbar” after you install it. Click the icon to initiate the wizard.
On the pop-up screen, choose the “Multiple consolidation ranges” and then click “Next >” button.
Leave the default choice of “Create a single page field for me” untouched. Then click “Next >” botton.
Highlight the data range of the Crosstab table, then click the “Add” button. You should see that such range is added to the lower panel. Click the “Next >” button afterwards. Please be reminded that you may want to skip the Totals column when you highlight the data range.
In the next screen, choose to put the Pivot Table in a new worksheet or existing worksheet. It is your choice. Click the “Finish” button to complete the selections.
With despair, you will find that the Pivot Table has not changed much compared with your original Crosstab table (see below screenshot). You might wonder we have wasted all the time so far. But hold on, the next few steps will get what you want.
Go to the Pivot Table Task Panel which is usually on the right hand side of your Pivot Table. Unmark the “Row”, “Column”, and “Page1” (or whatever Page Number it is). Now you should see the Pivot Table has only one value. Double click the value.
A table of data will appear in a new worksheet. You can get rid of the filter, rename the headers to be the same as the field names of your database table. You may also want to delete the column of “Page 1” (or whatever Page Number it is). Afterwards, you can import the data or make it the data source of your Power BI or Tableau.
