How to Highlight Conflicting Exam Sessions in WPS Office Spreadsheets with Conditional Formatting
Question details
The user wants to highlight exam rows in a spreadsheet when the same person has two exams on the same date with overlapping start and finish times.
- Product
- WPS Office Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- Scheduling or reviewing an exam timetable and detecting conflicts for the same candidate.
- Observed behavior
- Rows that meet the conflict condition should be highlighted automatically with conditional formatting; without a rule, overlapping exam sessions are not visually flagged.
Prepare your worksheet so each exam row contains the person code, exam date, start time, and finish time in consistent columns, and confirm the active cell is in row 2 before creating the rule.
Highlight overlapping exam rows with a COUNTIFS conditional formatting rule
Use this when you want WPS Office Spreadsheets to automatically flag any row where the same person has exams that overlap on the same date.
This method uses conditional formatting with COUNTIFS to evaluate four conditions: same person code, same exam date, an existing start time before or at the current finish time, and an existing finish time after or at the current start time. When more than one matching row is found, the rule formats the row.
Open your exam workbook in WPS Office Spreadsheets and select rows 2 through the last row that contains exam data, including the person code, date, start time, and finish time columns.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format, then enter the formula: =COUNTIFS($A$2:$A$1000,$A2,$T$2:$T$1000,$T2,$U$2:$U$1000,"<="&$V2,$V$2:$V$1000,">="&$U2)>1.
Click Format, open the Fill tab, choose a highlight color such as red, and confirm with OK.
Click OK again to apply the rule, then check that rows containing overlapping exam sessions for the same person are highlighted.
Create a helper column to mark conflicts before formatting
Use this alternative if you prefer to see the conflict result in a column before applying the highlight.
Highlight overlapping exam sessions in WPS Office Spreadsheets
Create a conditional formatting rule in WPS Office Spreadsheets using a COUNTIFS formula to automatically highlight rows where the same candidate has overlapping exam sessions.
- 1. Open the exam workbook: Launch WPS Office Spreadsheets and open the .xlsx or .xls file that contains the exam schedule.
- 2. Select the schedule rows: Select the data range from row 2 to the last exam row, ensuring the active cell is in row 2.
- 3. Create the rule: On the Home tab, click Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =COUNTIFS($A$2:$A$1000,$A2,$T$2:$T$1000,$T2,$U$2:$U$1000,"<="&$V2,$V$2:$V$1000,">="&$U2)>1 with the correct column letters.
- 4. Choose the highlight format: Click Format, select a fill color on the Fill tab, and click OK to close the Format Cells dialog.
- 5. Apply and confirm: Click OK in the New Formatting Rule dialog and verify that conflicting exam sessions are highlighted automatically.
Frequently Asked Questions
Can I use this conditional formatting rule in WPS Office Spreadsheets if my file was created in Microsoft Excel?
Yes. WPS Office Spreadsheets supports Excel formulas and conditional formatting, so you can open the workbook and apply the same COUNTIFS rule directly.
Why does the formula compare with >1 instead of =2?
COUNTIFS includes the current row, so a non-conflicting row returns 1. A conflicting row returns 2 or more because it matches at least one other overlapping row.
How do I adjust the formula if my person code, date, start time, and finish time are in different columns?
Replace A, T, U, and V with your actual column letters and update the row range, for example $B$2:$B$1000 for person code and $F$2:$F$1000 for start time.
Will the highlight update automatically when I add or edit exam rows?
Yes, conditional formatting recalculates when cell values change, provided the rule range includes the new or edited rows.
