Shade Every Other Row in Excel Quickly

How to shade every other row in Excel quickly without using the Table feature.

This method is for when you simply want to shade the rows and nothing more; when you do not want to convert your data to a Table type in Excel.

Sections:

Steps to Shade Every Other Row in Excel Quickly

Remove Background Shading

Remove Lines From Between the Cells

Notes

Steps to Shade Every Other Row in Excel Quickly

1. Select the data set where you want to add shading to every other row.
   
2. Go to the Home tab, click the Conditional Formatting button and then click the New Rule option.
   
3. Click the option: Use a formula to determine which cells to format
   
   
4. In the input box, type this formula:
   
   
=MOD(ROW(),2)=1

   
   
5. Click the Format button to choose the color to use for the shading.
   
6. In the window that opens, go to the Fill tab and select a color from the options there.  When you are finished, hit the OK button.
   
7. You will see the smaller window again and just hit the OK button there and you are finished!
   

Remove Background Shading

If you just created the background shading and want to remove it, hit Ctrl + Z to undo it.

If you didn't just create it, you can remove the shading by selecting the cells that contain the shading and going to the Home tab and selecting Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.

Then, you have the original unformatted data set again.

Remove Lines From Between the Cells

To remove lines from between the cells after you add the formatting, select all of the cells and click the Fill Color button and then select the color white.

Result:

Notes

This is a great feature to quickly add formatting to your spreadsheet and it will not disrupt or alter any of the contents of the cells to which you apply it.

Make sure to download the spreadsheet that accompanies this tutorial so you can try it out.