Highlight multiples of specific value

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.

Basic formula

=MOD(A1,value)=0

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.

Example

In our example, the formula is

=MOD(C5,$F$3)=0

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.

  1.     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.

  1.     Click on style, and then Conditional Formatting.

Figure 2 – Selection of conditional formatting in the Styles tab.

  1.    Click “New Rule”.

Figure 3 – Creation of new rule in conditional formatting

  1.     Select “Use a formula to determine which cells to format”.

Figure 4 – Selection of rule type

 

  1. Enter the formula =MOD(c5,$f$3)=0

Figure 5 – Entering the formula in conditional formatting

  1.    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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar