Tableau Data Normalizing Tips and Tricks for Use

When an Excel data source (other than a cube) is already formatted as a cross-tabulation or is otherwise aggregated, options for viewing, aggregating, and grouping in Tableau are limited. Tableau cannot see underlying data points that have already been summarized into a higher-level group or order. To take advantage of Tableau's full functionality, you need to normalize the data before connecting to it from Tableau.

You can normalize a table manually, but Tableau provides an Excel add-in that does a lot of the work for you. The Excel add-in is available for download from the "Attachments" section of the Installing the Tableau Add-In for Reshaping Data in Excel article.

Normalize an Excel table

This example shows an Excel table before and after it is normalized. You start with a table listing students and their grades in three subjects. In a crosstab layout, you have a column for each subject.

ID	Gender	School	Math	English	Science
1	M	West	90	80	70
2	F	South	50	50	50
3	M	Central	90	80	90
4	M	Central	50	80	80
5	M	West	100	90	100
6	F	West	80	80	60
7	F	South	50	80	100
8	F	Central	80	50	100
9	M	South	70	80	80

A normalized table gives Tableau the maximum amount of flexibility for aggregation and grouping. Normalization has one row for every unique individual record. To normalize the table, you replace the three subject columns with a single Subject column, and add a column for Score.

ID	Gender	School	Subject	Score
1	M	West	Math	90
1	M	West	English	80
1	M	West	Science	70
2	F	South	Math	50
2	F	South	English	50
2	F	South	Science	50
3	M	Central	Math	90
3	M	Central	English	80
3	M	Central	Science	90
4	M	Central	Math	50
4	M	Central	English	80
4	M	Central	Science	80
5	M	West	Math	100
5	M	West	English	90
5	M	West	Science	100
6	F	West	Math	80
6	F	West	English	80
6	F	West	Science	60
7	F	South	Math	50
7	F	South	English	80
7	F	South	Science	100
8	F	Central	Math	80
8	F	Central	English	50
8	F	Central	Science	100
9	M	South	Math	70
9	M	South	English	80
9	M	South	Science	80

For additional information about normalizing Excel workbooks, see the Preparing Excel Files for Analysis article.