How to use conditional formatting to colour alternate rows in Excel

Excel 2016 & 2013 users can use conditional formatting to colour alternate rows. Let us take the following sample data

image

Now you can add background colours to alternate rows by adding conditional formatting rules.

image

Listed below are the steps for changing the background colour of alternate rows using conditional formatting.

Step 1: Select the data for which the conditional formatting needs to be applied.

image

Step 2: Click Conditional Formatting menu option and select New Rule from the menu list.

image

Step 3: In the New Formatting Value window, select the Rule Type as “Use a formula to determine which cells to format”.

Step 4: Now copy the formula “=MOD(ROW(),2)=0” inside Format values where this formula is true.

image

Step 5: Click the Format button under New Formatting Value and specify the colour for the rows matching this condition.

imageimage

Step 6: Now to add another colour for rows ending with odd number, repeat Step 1 and Step 2. But enter the formula as =MOD(ROW(),2)=1. Then choose a colour for the rows matching this condition.

imageimage

If you want to edit these rules for changing the colours then click Conditional Formatting menu and select Manage Rules option.

image

In Category: Excel 2013, Excel 2016, MS Office

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 1 Comment

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.