Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Data Series in Excel
  • Managing Conditional Formatting Rules in Excel
  • Excel Frequency Function Example
  • How To Perform and Interpret Regression Analysis in Excel
  • Excel Bar Chart

References

  • How to use Excel INDIRECT Function
  • Count rows with at least n matching values
  • Convert text string to valid reference in Excel using Indirect function
  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel ROW Function

Data Validations

  • Excel Data validation date in next 30 days
  • Prevent invalid data entering in specific cells
  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Excel Data validation must contain specific text

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 approximate match lookup conditional formatting 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:   Convert inches to feet and inches 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:   How to create simple in-cell histogram 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:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart 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

Logical Functions

  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • IF, AND, OR and NOT Functions Examples in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • SWITCH function example in Excel

Date Time

  • Calculate expiration date in Excel
  • Add workdays to date custom weekends in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • Convert text timestamp into time in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers with VLOOKUP in Excel
  • Group numbers at uneven intervals in Excel
  • Map text to numbers in Excel

General

  • Convert column letter to number in Excel
  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • 44 Practical Excel IF function Examples
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Excel Default Templates
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning