alternate shade in data range in Excel

Q44. Is there a way to easily shade alternate rows in a data range for improving readability?

To do it in Excel, here is the answer:

a) Select the data range where you would like to apply alternate row shading. Under "Home" tab, click on "Conditional Formatting" -> "New Rule".

excel alternate shade in data range

b) Select "Use a formula to determine which cells to format". Enter =MOD(ROW(),2)=0 in the "Format values where this formula is true" field.

The formula selects all even numbered rows. In case odd numbered rows need to be selected, change the formula to =MOD(ROW(),2)=1.

excel excel alternate shade in data range

c) Click on Format-> Fill. Select color of your choice for shading. Click OK -> OK.

excel excel excel alternate shade in data range

d) The selected range would be formatted as required with alternate rows shaded.

excel excel excel excel alternate shade in data range


You can find similar Excel Questions and Answer hereunder

1) How can I add a Prefix to a set of numbers in a range?

2) How can I enter information in multiple cells simultaneously?

3) How can I remove hyperlinks from a range of cells?

4) How do I get the 2nd highest number in a range of numbers?

5) How can I set the Source Data of charts using VBA?

6) How can I get an estimate of Standard Deviation of a sample dataset?

7) How can I plot 2 series on the same chart with different scales / measurement unit for Values (Ex: Pareto chart)?

8) How can I identify all cells with Data Validation in my WorkSheet?

9) How can I set up a dynamic named range that expands automatically when new items are added to the list?

10) I have to retrieve data from a specific column in a Table. In Excel, how can I do that?

11) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

12) How can I quickly remove all blank cells in a data range?

13) How can I use SUMPRODUCT to summarize my raw data?

14) How can I find the slab in which a given value fits?

15) Is there a way I can average a range of numbers even if there is an error value in range?