# Building a clean pivot table by hiding repeated values
**URL:** https://community.omni.co/t/building-a-clean-pivot-table-by-hiding-repeated-values/404
**Category:** Patterns
**Tags:** calcs
**Created:** [October 17, 2025, 10:46pm UTC](https://community.omni.co/t/building-a-clean-pivot-table-by-hiding-repeated-values/404 "2025-10-17T22:46:01Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@adrienb](https://community.omni.co/u/adrienb)
#### Post date: [October 17, 2025, 10:46pm UTC](https://community.omni.co/t/building-a-clean-pivot-table-by-hiding-repeated-values/404/1 "2025-10-17T22:46:01Z")
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.
1. 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)
2. 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.
3. Head back to the `Results` tab 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.
4. 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 the `OR()` clause every time.
Now you have a table that shows you a complete picture, but is much easier to interpret than with every dimension being repeated!
---
### Author:  [@stabbert](https://community.omni.co/u/stabbert)
#### Post date: [October 21, 2025, 7:07pm UTC](https://community.omni.co/t/building-a-clean-pivot-table-by-hiding-repeated-values/404/2 "2025-10-21T19:07:13Z")
Great tip! Seems like an easy enhancement that Omni could make to the Group Dimensions functionality. Perhaps some sort of Expand or Flatten Hierarchy ability.
---
### Author:  [@adrienb](https://community.omni.co/u/adrienb)
#### Post date: [October 21, 2025, 11:59pm UTC](https://community.omni.co/t/building-a-clean-pivot-table-by-hiding-repeated-values/404/3 "2025-10-21T23:59:13Z")
Thanks for the feedback, definitely something we’ll eventually make easier and/or native! In the meantime I’m continually thankful for the flexibility of Excel syntax 😉