Sometimes table visualizations are more useful when dimensions are expanded by default, rather than grouped together, but repeated values can quickly fill up real estate. Here is how to make a clean table using excel calcs in a few quick steps.
-
First build the query you’ll want the table to reference. Bring in the dimensions and measures you need, putting the “top level” dimension as the left-most column, the second level to its right, etc. Sort the results by each of these columns in turn (hint: hold shift while clicking on column headers to quickly multi-sort)
-
At this point, click on
Chart, open the Options menu, click Group Dimensions, and consider if this standard pivot table meets your needs. If not, unclick Group Dimensions and keep reading. -
Head back to the
Resultstab and insert a column next to your first dimension. In cell B2, paste this formula:=IF(ROW() = 1, A$1, IF(A2 <> A1, A2, “”)). Note: make sure you paste this in cell B2, not B1! This formula can be interpreted as “if this is the first row, show me the first value (because the first row will never be a repeat of anything above it), otherwise only show me the value if it doesn’t match the one above it”. This effectively omits any duplicates. -
If you have additional dimensions you want to clean up, repeat this process (insert column, add formula in the second row) but this time modify it slightly to use an
OR()condition which checks both columns. For example this is my formula in cell D2:=IF(ROW() = 1, C$1, IF(OR(A2 <> A1, C2 <> C1), C2, “”)). This is so that, in this example, a category that happens to be in subsequent rows but in different brands would still get repeated to avoid confusion. Continue this process for additional dimensions, adding to theOR()clause every time.
- Finally, go back to
Chartand, in the bottom right corner, hide the original dimensions, leaving only the new calculated columns.
- Finally, go back to
Now you have a table that shows you a complete picture, but is much easier to interpret than with every dimension being repeated!
