How to add a color background to every Nth row or column in a range for Powerful data presentation (Video transcript)
We have this list of large data to evaluate or print. If we can make every other row and column a different color, it will be very engaging and easier to read.
we can do this by a custom conditional formatting.let’s consider to highlight every fourth row by a different color. Let’s select the entire sheet, Press Ctrl and A and on the Home tab, go to conditional formatting. Now we can create a new rule by using a formula to determine which cells to be formatted. we will apply a simple formula here. will use the MOD function. Let’s put it (=Mod(row(),4)=0.
The MOD function do a calculation based on the row number of any cell, and divide it by what you give, either by four, five, eight or ten and it figures out the remainder. whenever the remainder is equal to zero, that means the row number is evenly divisible by the given figure, it will change the format of a cell in that row.
Click format, we can format anything we want here the font, border etc. let’s pick a color, a green one for this test. and click OK, now every fourth row is green.
If we delete a row, the formula automatically readjusts. if we want to change anything we can go to conditional formatting and manage rules, edit the rule.
In the same manner, we can do it for column also. go to conditional formatting, new rule, and new formula rule. and enter the Mod formula. =MOD(column(),3)=0. Now format and pick a color, yellow color this time. and click OK. that is it.
we have formatted the way we want it.