Go Back

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.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

search a column which has multiples of the same value and return the minimum value of another column in the array
Solved by I. J. in 26 mins
Need a formula for if a range of cell values is equal to or greater than certain values it multiples the cell value by another value
Solved by A. A. in 20 mins
Is there a way i can highlight a cell and have that cell and its value jump to another tab to a specific location?
Solved by E. F. in 29 mins

Leave a Comment

avatar