Highlight Multiples of Specific Value

This post will guide you how to search and highlight multiple values of a apecific value in excel. How to check if a number is a multiple of a specific number and then highlight those searched values in excel.

Highlight Multiples of Specific Value


If you want to highlight numbers that are multiples of a specific number, and you need to find those numbers firstly and then you can use the conditional formatting feature to highlight it. You need to create a formula based on the MOD function.

Assuming that you want to highlight multiples of a specific number 6 in Cell E1, Just do the following steps:

#1 select the range of cells A1:C4

highlight multiples1

#2 go to Home tab, click Conditional Formatting command under Styles group, and select New Rule…menu from the drop down menu list. The New formatting Rule dialog box will appear.

highlight multiples2

#3 select Use a formula to determine which cells to format in the Select a Rule Type: section. And enter the formula =MOD(A1,$E$1)=0 into the Format values where this formula is true: text box, then click Format…. button. The Format Cells window will appear.

highlight multiples3

#4 switch to the Fill tab, select one background color that you want to highlight. Then click OK.

highlight multiples4

#5 click OK button.

highlight multiples5

Note: Conditional formatting is used to evaluate for each cell in the selected range with the MOD function to check the remainder of dividing the value in each cell, if the remainder is zero, the value should be an even multiple of the specific number.

Related Functions


  • Excel MOD function
    he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

 

Related Posts
How to Average Only Positive or Negative Numbers of a Range

Suppose both positive numbers and negative numbers exist in a table. If we want to know the average of only positive numbers in this table, we can create a formula to get average of all positive numbers with all negative ...

How to Sort Data but Keep Blank Cells in Excel

In daily work, if we sort data with blank cells included in the same column, these blank cells are listed at the bottom automatically after sorting. If we want to keep the positions of these blank cells unchanged and only ...

How to Copy and Paste Only Values and Ignore Formula

When we copy a cell applied with a formula, we copy the formula of the cell rather than copy the value showing in the cell. In this article we will introduce you the way to copy only value ignoring applied ...

How to Sort Date by Day of Week in Excel

Except sort data by “A to Z” (alphabetical order, for numbers from small to large), we can also sort data by date, month or year if these conditions are given. In this article, we will show you the way to ...

How to Select All Non-Blank Cells of a Range

In daily work, we may meet the cases that select all blank cells or non-blank cells of a range. You may know the way to select all blank cells as they are “blanks”. But for non-blank cells, they may contain ...

How to Sum Every Nth Column in Excel
Sum Every Nth Column 27

Sometimes for data listed in rows, we may want to sum data every Nth column, for example sum data only in ODD column or EVEN column (every 2 column). In our daily life, we may meet many cases like this. ...

How to Count Cells that Contain even or odd numbers in Excel
count cells that contain even numbers5

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use ...

How to Highlight Every Other Row or Every Nth Row in Excel?
How to Highlight Every Other Row or Every Nth Row in Excel 11

Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight ...

How to Calculate Total Work Days or Hours Exclude Weekends/Holidays in Excel
Calculate Total Work Days or Hours 15

In our daily work we often need to count the total working-days or working-hours for employees then we can pay for them the salary in some cases. But we have to count the total exclude weekends or holidays as we ...

How to Stack Data from Multiple Columns into One Column in Excel
Stack Data 12

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can ...

Sidebar