highlight entire row conditional formatting excel 2016

7 Quick & Easy Ways to Number Rows in Excel.I am having difficulty writing a custom formula for a Reading Lexile.

Conditional Formatting to Create 100% Stacked Bar Chart in Excel.

How to Apply Conditional Formatting in a Pivot Table in Excel.

You May Also Like the Following Excel Tutorials: As a part of the Excl Table formatting, you can easily highlight alternate rows in Excel (all you need is to use the check the Banded Rows option as shown below): If you plan to print this, make sure you use a light shade to highlight the rows so that it is readable.īonus Tip: Another quick way to highlight alternate rows in Excel is to convert the data range into an Excel Table. These banded rows are also called zebra lines and are quite helpful in increasing the readability of the data set.

Highlight every 2nd column = MOD( COLUMN(),2)=0.

Highlight every 3rd Row =MOD( ROW(),3)=1.

Highlight every 2nd row starting from the first row =MOD(ROW(),2)=0.

You can change the formula according to your requirements. Hence, it highlights this entire row (since all the cells in this row have the same row number).

Here Row number of cell B5 is 5 and =MOD(5,1) returns 1, which meets the condition. So it moves on to the other cell in next row. Since the ROW function in B4 would return 4, =MOD(4,2) returns 0, which does not meet our specified criteria. This evaluates each cell and checks if it meets the criteria. The entire magic is in the formula =MOD(ROW(),2)=1. Now let’s take a step back and understand how this thing works. That’s it!! You have the alternate rows highlighted. Click on the Format button to set the formatting.In Edit the Rule Description section, enter the following formula in the field:.

In the dialogue box, click on “ Use a Formula to determine which cells to format” option.

Open the Conditional Formatting dialogue box (Home–> Conditional Formatting–> New Rule).

Select the data set (B4:D15 in this case).

Here are the steps to highlight every alternate row in Excel: This can easily be achieved using conditional formatting. Let’s say you want to highlight every second month (i.e., February, April and so on) in this data set. Suppose you have a dataset as shown below: Watch Video – How to Highlight Alternate (Every Other Row) in Excelīelow is the complete written tutorial, in case you prefer reading over watching a video.Ĭonditional Formatting in Excel can be a great ally in while working with spreadsheets.Ī trick as simple as the one to highlight every other row in Excel could immensely increase the readability of your data set.

Highlight entire row conditional formatting excel 2016