Shade Every Other Row in Excel Quickly

Add to Favorites
Author: don

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.

Question? Ask it in our Excel Forum


Excel Function: MOD(), ROW()
Downloadable Files: Excel File

Similar Content on TeachExcel
MOD Function in Excel
Tutorial: The MOD function is very simple but it can be used to do wonderful things in Excel. It ret...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
Get the Row or Column Number of a Cell in Excel
Tutorial: How to get the row or column number of the current cell or any other cell in Excel. This t...
Highlight Every Other Row in a Selection in Excel - Table Formatting
Macro: This free Excel macro will highlight every other row in a selection of cells with a color ...
Automatically Shade Every Other Row When You Add Data in Excel
Tutorial: How to make Excel automatically add row shading to every other row when you add data to th...
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to q...
Tutorial Details
Excel Function: MOD(), ROW()
Downloadable Files: Excel File
Similar Content
MOD Function in Excel
Tutorial: The MOD function is very simple but it can be used to do wonderful things in Excel. It ret...
Convert Numbers into True and False in Excel
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
Get the Row or Column Number of a Cell in Excel
Tutorial: How to get the row or column number of the current cell or any other cell in Excel. This t...
Excel Forum