MS Excel: How to highlight every fifth cell in a column

A tip on using Conditional Formatting to highlight every fifth cell in a column:

In MicroSoft Excel, whenever there is a requirement to format cells based on a condition, it's always Conditional Formatting that comes to mind. To solve this question too, we will use Conditional Formatting only.

Let's say, we want to highlight every 5th cell in a column. We're going to use a formula to first identify every fifth row so as to apply Conditional Formatting. This formula can be altered to select any number of subsequent rows – i.e.,
every second row, every third row, every sixth row, etc. And this formula will be part of our condition within the Conditional Formatting.

Here's the formula:

=MOD(ROW(),5)=1

Let's break this formula to understand it better:

MOD is a mathematical function in MS Excel which will return the remainder, after the number is divided by the divisor. The result will have the same sign as the divisor.

Here's the actual syntax for MOD function:

=MOD(number,divisor)

Number is the number for which we want to find the remainder.

Divisor is the number by which we want to divide number.

Number Part:- In =MOD(ROW(),5) formula, by having the 'Number' part of the syntax written as ROW(), we are letting Excel decide to bring the Row number of a given reference into MOD. By leaving the reference blank within ROW(), we are also letting Excel automatically pick and use it for every cell that this formula is attached to.

Divisor Part:- This will be 5 in our example. If you want to highlight every 4th cell, then the divisor will be 4.

=1 => here, we're saying that if the remainder is '1', then highlight based on the conditions given.

Thus, by performing the above steps, we will have every 5th cell in selection highlighted in Grey color.