Hide Repeated Excel Values While Keeping Rows Grouped in WPS Office Spreadsheets
Question details
A user wants to visually hide repeated employee names in a spreadsheet while keeping each employee's projects grouped during sorting and filtering.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a project table where repeated employee names clutter the view. They want the names hidden but still need rows to stay grouped when sorted or filtered.
- Observed behavior
- Excel and WPS Spreadsheets do not treat blank cells as automatically repeating the value above, so blank rows can become separated when sorting; repeated visible values make the sheet harder to read.
Make sure each row already contains the employee value, or add a helper column that copies the value into blank rows. Conditional formatting only changes the visible font color; it does not remove or alter the underlying values.
Use Conditional Formatting to Hide Repeated Values
Best for visually decluttering the sheet while keeping the actual values available for sorting, filtering, and formulas.
Conditional formatting changes only the cell appearance. The repeated names remain in the cells, so you can still sort or filter by employee without breaking the group relationship.
In WPS Office Spreadsheets, select the column or range that contains the repeated values, for example A2:A100.
Go to the Home tab and click Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format, then enter =COUNTIF($A$2:$A2,$A2)>1.
Click Format, open the Font tab, set Color to the background or table fill color, and confirm with OK.
Click OK again to apply the rule. Repeated values are visually hidden but remain in the cells.
Sort the Table by the Employee Name Column
Use this before or after hiding duplicates to make sure each employee's project rows stay together.
Use a Helper Column to Fill Blank Employee Cells
Use this when blank cells need to carry the value above before sorting or filtering.
Hide Repeated Values in WPS Office Spreadsheets Without Removing Data
Use WPS Office Spreadsheets to visually hide duplicate employee names or values with conditional formatting, keeping the underlying data intact for sorting, filtering, and Excel-compatible workflows.
- 1. Select the repeated value column: Open the workbook in WPS Office Spreadsheets and select the range containing employee names or repeated values.
- 2. Open Conditional Formatting: Click Home > Conditional Formatting > New Rule.
- 3. Enter the duplicate formula: Choose Use a formula to determine which cells to format and enter =COUNTIF($A$2:$A2,$A2)>1, adjusting the range as needed.
- 4. Make duplicates blend in: Click Format > Font, set the font color to match the background or table fill, and confirm with OK.
- 5. Sort to keep rows grouped: Click Data > Sort, select the Name column, choose A to Z, and click OK to group each person's projects.
Frequently Asked Questions
Does conditional formatting delete the repeated values?
No. It only changes the font or fill color so repeated values blend into the background. The actual values remain in the cells and can still be sorted, filtered, and used in formulas.
Why do blank cells not automatically repeat the value above in Excel or WPS?
Spreadsheets treat blank cells as empty rather than as a continuation of the cell above. To keep rows grouped, enter the value in every row or use a helper column that fills blank cells before sorting.
Which formula hides only duplicates that match the cell above?
Select the range and use a rule like =$C2=$C1. Change C to the relevant column and adjust the starting row to the first selected cell.
Can I hide repeated values and still keep the table style?
Yes. In the conditional formatting Format dialog, set the font and fill colors to match the existing table style instead of simply making the font white.
