HIGHLIGHT MULTIPLES OF SPECIFIC VALUES-EXCEL FORMULA
Sometimes, we have to check multiples of a specific value in an Excel worksheet, but it gives a headache because checking every single value and then calculating it takes a lot of time and mental energy. What if we tell you that this task can be done easily with the MOD function and conditional formatting? Let’s do it.
Explanation of the Formula
Simple MOD function based formula, along with conditional formatting, can be used to highlight the multiples of a number. Use the formula with conditional formatting to get the desired results.
In our example, the formula is
How to use this formula with conditional formatting?
Before describing the original formula, we will explain how to use it with conditional formatting in several steps.
- First, we will select the set of cells on which we want to implement conditional formatting. Check figure 1.
Figure 1. Selection of the cells for conditional formatting.
Now that we have selected the cells, apply conditional formatting on it. Follow the steps in figure 2-6.
- Click on style, and then Conditional Formatting.
Figure 2 – Selection of conditional formatting in the Styles tab.
- Click “New Rule”.
Figure 3 – Creation of new rule in conditional formatting
- Select “Use a formula to determine which cells to format”.
Figure 4 – Selection of rule type
- Enter the formula
Figure 5 – Entering the formula in conditional formatting
- Click on format and select your desired highlight color. After selecting the color, click Ok on all the opened windows.
Figure 6 – Selection of the color to highlight
You are all done.
Now, we’ll highlight multiples of 5 using this worksheet.
Figure 7 – Example of highlighted multiples of specific values
We have entered the value ‘5’ in the F3, and as a result, all the multiples of 5 have highlighted.
How this formula works
Every single cell in the selected range is evaluated by conditional formatting. In this formula, MOD function is used to check the remainder of each value after it is dividing with the value in F3.
When zero is returned by MOD, the conditional formatting applies as the expression returns TRUE. In the case of FALSE, the conditional formatting will not be applied.