How to Highlight Excel Table Rows by User or Email Group in WPS Spreadsheets
Question details
The user wants to visually group and highlight rows in an Excel table that share the same user or email address value.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a spreadsheet table that contains user names or email addresses across multiple records and needs to make each group easier to scan.
- Observed behavior
- Rows with matching user or email values are not visually distinguished, so groups are difficult to identify without sorting and conditional formatting.
Open the workbook in WPS Spreadsheets and identify the column that contains the user or email group values. Sort that column before applying a group-highlighting conditional formatting rule.
Sort and Apply Conditional Formatting to Color Rows by User or Email Group
Use this method for a clean, automatic two-color banding effect when the table can be sorted by the user or email column.
This rule assigns one fill color to even-numbered unique groups and another to odd-numbered groups. It works best when the group column is sorted so matching values are adjacent.
Select the data including the user or email column, click the Data tab, choose Sort, select the user or email column, and sort A to Z so matching records are adjacent.
Select the entire data range beginning with the first data row, for example A2:D200. Leave the header row unselected if your data starts in row 2.
On the Home tab, click Conditional Formatting > New Rule, then choose “Use a formula to determine which cells to format.”
In the formula box, enter =ISEVEN(ROWS(UNIQUE($A$2:$A2))) and adjust $A$2 to the first data cell of the user or email column. If you select from row 1, use =ISEVEN(ROWS(UNIQUE($A$1:$A1))).
Click Format, switch to the Fill tab, choose a background color, then click OK to apply the rule to the selected range.
Use a Helper Column to Assign Group Indexes
Use this when you want a visible group number or need to apply color scales or multiple formats to each user or email group.
Highlight Rows by User or Email Group in WPS Spreadsheets
WPS Spreadsheets includes sorting and conditional formatting tools that work with Excel-compatible formulas, so you can highlight groups in your .xlsx or .xls table without changing your workflow.
- 1. Download WPS Office: Go to the WPS Office download page, download the free installer, and install WPS Office on your device.
- 2. Open your workbook: Launch WPS Spreadsheets, click Menu > Open, and select the Excel workbook containing the user or email table.
- 3. Sort the group column: Select the table, click the Data tab, choose Sort, and sort the user or email column A to Z.
- 4. Apply the highlighting rule: On the Home tab, click Conditional Formatting > New Rule > Use a formula, enter the group-band formula, choose a fill color, and click OK.
- 5. Save in Excel format: Click Menu > Save As and choose .xlsx or .xls to keep the file compatible with Microsoft Excel.
Frequently Asked Questions
Why does the conditional formatting formula require the user or email column to be sorted?
The formula uses UNIQUE to count distinct groups in the range above the current row, so matching values must be adjacent for the group count to change only when a new user or email appears. Sort the column first, or use a helper column if you cannot sort.
Can I use this to highlight rows by user in an unsorted table?
Yes, but the formula-based method may not work correctly. Use a helper column that checks whether the current value appeared earlier and increments a group index, then apply formatting from that helper column.
How do I make each group a different color instead of alternating two colors?
Assign a group index in a helper column, then apply a Color Scale or multiple Highlight Cells Rules to that index. This is easiest with a small number of groups; many groups become harder to distinguish.
Can I open and save this workbook in Microsoft Excel after using WPS Spreadsheets?
Yes. WPS Spreadsheets supports .xlsx and .xls formats, so the conditional formatting and helper column formulas remain available when you open the file in Microsoft Excel.
What should I do if my email column has blank cells?
Blank cells can form an extra group and cause unexpected banding. Filter out or remove blank rows in the group column before sorting and applying the rule.
