Formatting Issues

How to Highlight Every 7th Training Day Using Excel Conditional Formatting

Amos Gikunda Oct 19, 2026 868 views

Question details

The user wants to track employee training days using 1s and 0s and needs an automatic way to highlight the cell that marks every 7th completed training day.

How to Highlight Every 7th Training Day Using Excel Conditional Formatting

Product: Excel
Device & OS: not provided
Scenario: Tracking continuous employee training schedules across a row, where training days are marked as 1 and non-training days as 0.
Observed behavior: The goal is to apply a conditional formatting rule that triggers a cell highlight whenever the running total of training days reaches a multiple of seven.

Before you start

Ensure your tracking sheet uses a clear 1 (training day) and 0 (day off) numeric system in a continuous row. Verify that your data range is correctly formatted as numbers before applying the conditional rule.

Solution 1Recommended

Use a MOD and SUM Formula in Conditional Formatting

Apply a custom formula rule to dynamically calculate the running total and highlight every 7th occurrence of a training day.

This method combines the AND, MOD, and SUM functions. It calculates the running total of training days across a row and checks if that total is a multiple of 7, ensuring only the exact 7th training day is highlighted.

Select the data range

Highlight the range containing your training data, such as B2:AG3. Ensure you note the top-left cell of your selection (e.g., B2).

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule from the dropdown menu.

Choose the formula option

In the dialog box, click on the option that says 'Use a formula to determine which cells to format'.

Enter the custom formula

Type the formula =AND(B2=1,MOD(SUM($B2:B2),7)=0) into the rule field. Adjust B2 if your selected range starts in a different cell.

Set the highlight format

Click the Format button, go to the Fill tab, choose a highlight color like green, and click OK on both windows to apply and save the rule.

Use a MOD and SUM Formula in Conditional Formatting

Understanding the Formula: The mixed reference $B2:B2 is crucial as it anchors the starting column while allowing the sum range to expand across the row. The MOD function then checks for multiples of 7.

Seamless Spreadsheet Management

Track Employee Training Effortlessly with WPS Spreadsheet

WPS Spreadsheet offers powerful conditional formatting features, allowing you to use advanced formulas to track and highlight complex data patterns exactly like Microsoft Excel.

1. Open your tracker in WPS Spreadsheet: Launch WPS Office and open your employee training tracking workbook.
2. Navigate to Conditional Formatting: Select your target data range, go to the Home tab, and click on Conditional Formatting.
3. Apply the new rule: Select New Rule, choose the formula option, and apply the custom MOD/SUM formula to automatically format your training intervals.

Fully compatible with Microsoft Excel formulas and conditional formatting.Intuitive, lightweight interface for faster data management.Advanced formatting tools perfect for custom HR and tracking templates.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong cells?

This usually happens if the formula references don't match the selected range. Ensure the starting cell in your formula (e.g., B2) exactly matches the top-left cell of the data range you selected before applying the rule.

Can I change the highlight to happen every 5th training day instead?

Yes. You simply need to change the number 7 in the MOD formula to 5. The updated formula would be =AND(B2=1,MOD(SUM($B2:B2),5)=0).

What if I use text like 'Yes' and 'No' instead of 1 and 0?

The SUM function will not calculate text values. You need to change your formula to use COUNTIF instead of SUM to count the occurrences of 'Yes'. The formula would look like this: =AND(B2="Yes",MOD(COUNTIF($B2:B2,"Yes"),7)=0).

Document & File Issues