Can You Colour Alternative Rows with Conditional Formatting?
Need Help with Conditional Formatting and Excel Reporting?
We offer live instructor-led Excel training for teams who need practical help with formatting, analysis, dashboards and day-to-day spreadsheet work.
Is it possible to use conditional formatting to colour alternate rows in your spreadsheet? Well if you've seen our previous videos you might have seen 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. Admittedly that particular video was using Google Sheets. However, this one is with Excel.
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:
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.
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.
You should see the following box appear.
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.
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)))
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.