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

Lookup and Reference Examples

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Basic INDEX MATCH approximate in Excel
  • How to use Excel FORMULATEXT function
  • Count rows with at least n matching values
  • Left Lookup in Excel

Data Analysis Examples

  • Data Series in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Subtotal function in Excel
  • Number and Text Filters Examples in Excel

Data Validation Examples

  • Excel Data validation specific characters only
  • Excel Data validation exists in list
  • Excel Data validation number multiple 100
  • Excel Data validation must not contain
  • Excel Data validation don’t exceed total

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:   Highlight cells that equal 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:   Highlight cells that contain one of many in Excel

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:   Highlight dates between 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
  • Extract last name from full name — Manipulating NAMES in Excel
  • How to get page from URL in Excel
  • Remove file extension from filename in Excel
  • How to strip html from text or numbers in Excel
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • Get work hours between dates in Excel
  • Convert Excel time to decimal minutes
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Convert decimal hours to Excel time
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • Example of payment for annuity in Excel
  • FV function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles