This means that the number of the row has to be divisible by 2 so that the remainder can equal to 0. In this formula – We have set the divisor as 2 and the remainder as 0. In the formula bar, enter any one of the formulas below (both formulas will return the same result):.
This option will allow us to use the MOD and ROW functions to conditionally format our selected data.
This will open the New Formatting Rule window. From the Home tab, in the Styles section, click the Conditional Formatting From the drop-down menu, select the New Rule option.Select the dataset requiring highlighted rows.Sounds easy, doesn't it? It is when you know what to do so here is what to do: number 5, divided by 2, MOD will return the remainder 1). To accomplish this we can use the formula that we saw in the section above or we can build another formula based on the MOD and ROW functions together. The MOD function returns the remainder after a number is divided by another number (e.g. In this process, we are going to conditionally format our data using a formula based on the ROW function such that the formula returns TRUE for every alternate row. Now we can take things to the next level and make use of Excel's conditional formatting feature to highlight alternate rows.
Recommended Reading: How To Rotate Cell Text In Excel Using Conditional Formatting For Shading Every Other Row in Excel Here onwards, the table can be filtered, grouped, highlighted, unfiltered and the helper column deleted like the steps from above.
This formula will basically work as "is this row an even-numbered row?" and return TRUE or FALSE. This will work in the same way as the steps mentioned above. In the next column, enter the ISEVEN function:.Select both these cells, click and drag to fill the column with consecutive numbering. Along the last column in your dataset, type 1 in the first row and 2 in the next row.The two new test columns involving the numbering and the ISEVEN function can then be deleted. After the filter is removed, every alternate column will be highlighted. By filtering, all TRUEs (or FALSEs) can be grouped and highlighted. Then using the ISEVEN function in another column which will return TRUE for alternate rows. The process starts with numbering all the rows in a separate column. The ISEVEN function returns TRUE if the specified number is even.
Other than holding down the Ctrl key and clicking every other row, there's another manual method that involves filtering and the ISEVEN function. Using Manual Approach To Highlight Every Other Row in Excel