colour alternative rows using these four methods

Who Said VLOOKUP extracts only from LEFT to RIGHT »

Dec 27

4 Ways to Colour Alternative Rows (with no time)

Formulas, CF, Tables, Fill Handle Tricks

Hi there,

I am sure, many of you are familiar about Table feature in Excel. Some time, while working on the data we feel like filling colour to an alternative rows to showcase them nicely to the management. We can achieve this using four different methods. I bet you will atleast like one of them. These tips are much easier and can save lots of time rather colouring it manually. Let us go ahead now:

Colour alternative rows using four options using excel

There are four different ways to colour alternative rows in Excel:

1. Using Conditional Formatting (more here)

2. Using Table

3. Using Auto Fill Option

4. Using Banded Rows.

Let me explain each of them:

...

1. Using Conditional Formatting:

Goto Conditional Formatting -->New Rule --> Use a formula to determine which cells to format --> enter this formula =MOD(ROW(),2)=0 --> format the rows by clicking on Format here.

Note: Make sure you have selected the data which needs to be formatted before you apply conditional formatting.

Formula to colour alternative rows using conditional formatting

2. Using Table Option:

Select the whole data set and goto INSERT ribbon to click on Tables feature. Once done, the data is automatically colour the alternative rows. That's it.

Table feature helps to colour alternative rows in excel

3. Using Auto Fill Option:

If the data is in table feature and you dragged cells manually to update the below cells, then we will loose the format. To retain the format, click on the AutoFill Option (which pop's up after dragging the cells) and choose the last option "Fill Without Formatting", that's it.

4. Using Banded Rows:

If the data is formatted by the other user by changing the font colour and fill colour as black and white, then to retain the bands for alternative rows is just a click away. Just click on the Table Tools (this ribbon will activate when the active cell is in the Table format) -->Design -->Banded Rows.