Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Approximate match with multiple criteria in Excel
  • Last row number in range
  • Extract all partial matches in Excel
  • How to get last row in text data in Excel
  • Offset in Excel

Data Analysis Examples

  • Data Series in Excel
  • How to add Trendline to a chart in Excel
  • Randomize/ Shuffle List in Excel
  • How to Sort by Color in Excel
  • Number and Text Filters Examples in Excel

Data Validation Examples

  • Excel Data validation allow weekday only
  • Excel Data validation number multiple 100
  • Prevent invalid data entering in specific cells
  • Excel Data validation specific characters only
  • Excel Data validation require unique number

Highlight multiples of specific value in Excel

by

This tutorial shows how to Highlight multiples of specific value in Excel using the example below;

Formula

=MOD(A1,value)=0

Explanation

To highlight numbers that are multiples of a another number with conditional formatting, you can use a simple formula based on the MOD function.

In the example shown, the formula used to highlight multiples of 9 is:

=MOD(B4,$E$2)=0

Note: formula is entered relative to the “active cell” in the selection, cell B4 in this case.

Worked Example:   Get work hours between dates and times in Excel

How this formula works

Conditional formatting is evaluated for each cell in the range, relative to the upper left cell in the selection. In this case, the formula uses the MOD function to check the remainder of dividing the value in each cell, with the value in cell E2, which is 9. When the remainder is zero, we know that the value is an even multiple of the number 9, so the formula checks the result of MOD against zero.

Worked Example:   Excel Get number at place value Example

When MOD returns zero, the expression returns TRUE and the conditional formatting applies. When MOD returns any other result, the expression returns FALSE conditional formatting is not applied.

Worked Example:   Conditional Formatting Rules in Excel

Note E2 is input as an absolute address ($E$2) to prevent the reference from changing as the conditional formatting rule is evaluated across the selection B4:G11.

Post navigation

Previous Post:

Highlight missing values in Excel

Next Post:

Highlight every other row in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to use double quotes inside a formula in Excel
  • TEXTJOIN function: Description, Usage, Syntax, Examples and Explanation
  • How to check cell contains which things in Excel
  • Convert Numbers to Text in Excel
  • How to remove trailing slash from url in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get day name from date in Excel
  • Display Days in month in Excel
  • How to Calculate Age in Excel
  • Assign points based on late time in Excel
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • ODDFYIELD function: Description, Usage, Syntax, Examples and Explanation
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • FV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles