Computer Tutoring Logo
Est. 2002
Menu Button

Create alternative coloured rows using conditional formmating in Excel

Using a formula with Conditional Formatting

Why oh why would you use a formula with Conditional Formatting? Well if you've seen our previous videos you can see how you can use a formula to change the background colour of an entire row based on the value of a single cell within that row.

Now it's time to take the next step, albeit a step to the side. This is how you can create alternating row colours with conditional formatting.

First of all you will want to download this exercise file which will contain the file we'll be using as a starting point for this exercise.

So having opened the file you should be looking at this:

Excel Spreadsheet

Great start! So now you can select the data you want to add the alternative row colour to. Click once on any cell in the middle of the data then hold down the Control key and press A on the keyboard. The only cells selected should be the cells with content.

Selecting data in Excel

Cool eh? So now the conditional formatting formula you enter will only apply to the selected cells.

Next we need to open the Conditional Formatting window. From the Home Tab Click Conditional Formatting then New Rule from the drop down list.

Selecting data in Excel

You should see the following box appear.

Conditional Formatting Formula Box

Enter the following formula:

=MOD(ROW(),2)

Now click on Format and change the background, or fill, colour to blue. Alternative, you can choose any colour you want.

Click OK and you should see.

Alternating rows completed file

Making alternative rows a little more flexible

As you've probably noticed the above formula could do with a little more flexibility. I mean what if you have no idea how many rows of data you will add and you don't have the desire to use tables? If that is the question you are now asking then do the following:

Select the whole worksheet.

From the Home Tab Click Conditional Formatting then on Manage Rules.

Delete the current rule then add another rule using the formula:

=AND(MOD(ROW(),2),NOT(ISBLANK(A1)))

Conditional Formatting box in Excel

Click on Format and change the fill colour as you did previously, then click OK.

So you should now have a sheet where you can enter in as many rows of data as you like, and the alternative colours will only appear when data has been entered. If you are unsure as to how this should work then you can download the completed exercise file.

More Excel Tips