How to Apply Conditional Formatting for Every 6th or 7th Row in Excel
Question details
The user needs to set up conditional formatting to highlight recurring rows, specifically every 6th and 7th row to represent weekend days, beginning at a specific starting row instead of the top of the worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying a repeating highlight pattern to a dataset to visually separate weekend rows from weekdays within a custom selected data range.
- Observed behavior
- The user wants a precise formula applied to a custom data range so that the highlights accurately reflect every 6th and 7th row based on the defined starting point.
Identify the exact starting row number of your dataset, as the MOD formula must be adjusted based on this first row to ensure the correct sequence is highlighted.
Use the MOD function for a 7-row repeating pattern
This is the most effective way to highlight specific recurring rows, such as a two-day weekend block, starting from a custom row number.
By combining the MOD and ROW functions, you can create a repeating pattern that highlights specific rows regardless of where your data begins.
For example, if you want to highlight a repeating block of two rows (like weekends) in a 7-day sequence starting at row 10, the formula =MOD(ROW()-10,7)<=1 will calculate the offset and highlight the first two rows of every 7-row block.
Click and drag to select the entire range of cells where you want the conditional formatting applied (e.g., A10:F100).
Navigate to the Home tab on the ribbon, click on Conditional Formatting, and select 'New Rule' from the dropdown menu.
Choose 'Use a formula to determine which cells to format'. In the formula box, enter =MOD(ROW()-10,7)<=1 (replace 10 with your actual starting row number if different).
Click the 'Format' button, switch to the 'Fill' tab, select your preferred highlight color, and click OK to apply the rule.
Highlight alternating rows using ISEVEN or ISODD
Use this simplified method if you only need to highlight every other row instead of a 7-row pattern.
Easily Highlight Custom Rows with WPS Spreadsheet
WPS Office Spreadsheet provides advanced yet intuitive conditional formatting tools. You can effortlessly apply complex formulas to highlight recurring rows, schedule weekends, or track alternating data points.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your schedule or dataset.
- 2. Select the data range: Highlight the specific rows and columns you wish to apply the formatting to.
- 3. Access Conditional Formatting: Go to the Home tab, click 'Conditional Formatting', and select 'New Rule'.
- 4. Apply your formula: Choose 'Use a formula to determine which cells to format', enter your MOD formula, pick a highlight color, and click OK.
Frequently Asked Questions
How do I highlight every Nth row starting from row 2?
Use the formula =MOD(ROW()-2, N)=0 in your conditional formatting rule, replacing N with your desired interval (e.g., 5 for every 5th row).
Can I apply conditional formatting to highlight entire columns instead of rows?
Yes, you can highlight columns by replacing the ROW() function with the COLUMN() function in your MOD formulas. For example, =MOD(COLUMN()-1, 7)=0.
Why is my conditional formatting formula highlighting the wrong rows?
This usually occurs if the starting row referenced in your MOD formula does not perfectly match the top row of the range you currently have selected. Ensure your selection and formula offsets are aligned.
