Add a Bottom Border to the Last Row of Each Group in WPS Spreadsheets
Question details
Add a bottom border across the final row of every consecutive group based on changes in a grouping column.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains repeated names or categories in consecutive rows, and each group needs a visual separator at its last row.
- Observed behavior
- The desired result is a bottom border on the row immediately before the value in the grouping column changes.
Identify the column that contains the group names or categories and select the entire data range that should receive borders. If your range has a header row, start the conditional-formatting rule from the first data row instead of the header.
Use conditional formatting to border the last row of every group
Use this method when group values are in one column and the borders should update automatically as names, categories, or rows change.
The formula compares the grouping-cell value on the current row with the value on the next row. When the two values differ, WPS Spreadsheets applies a bottom border to the full row within the selected range.
Select all columns and rows that should display the group separators. For example, select A1:H1000 if column D contains the group names and row 1 is your first data row.
Go to Home > Conditional Formatting > New Rule, then choose the option to use a formula to determine which cells to format.
Enter =$D1<>$D2. Replace D with your grouping column, and replace 1 with the first row of the selected data range. Keep the column absolute with $ so the comparison always uses the grouping column.
Select Format, open the Border settings, choose a bottom border style, and confirm with OK. Save the conditional-formatting rule to apply it to the selected range.
Apply a bottom border manually for fixed group sections
Use this option when the worksheet will not change and you only need one-time visual separators.
Create automatic group separators with WPS Spreadsheets
WPS Spreadsheets lets you use formula-based conditional formatting to add bottom borders whenever a value changes in a grouping column. This keeps grouped records clearly separated while allowing the worksheet to update dynamically.
- 1. Open the worksheet in WPS Spreadsheets: Open your Excel-compatible workbook and locate the column that contains the group values.
- 2. Select the full table range: Select the columns that should receive the bottom border for each group-ending row.
- 3. Add a formula rule: Choose Home > Conditional Formatting > New Rule and enter a comparison formula such as =$D1<>$D2.
- 4. Choose the bottom border format: Open Format > Border, apply a bottom border, and confirm the rule.
Frequently Asked Questions
Why does the formula use $D1<>$D2 instead of D1<>D2?
The $ before D locks the grouping column while allowing the row number to change for each row in the applied range. This ensures every selected column checks the value in column D.
How do I change the formula if my groups are in column B?
Replace D with B. For a data range beginning on row 2, use =$B2<>$B3 in Home > Conditional Formatting > New Rule.
Can I apply the bottom border across columns other than the grouping column?
Yes. Select the full table width before creating the rule, such as A2:H1000. The formula can still reference only the grouping column, such as =$D2<>$D3.
Why is the header row receiving a bottom border?
The applied range likely starts at the header. Edit the conditional-formatting rule and set the Apply to range to begin with the first data row, then adjust the formula row number to match.
Will the last row of the final group receive a border?
It will if the cell below the final group is blank or contains a different value. Ensure the rule includes the final data row and that the next-row comparison is available within the worksheet.
