Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
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 Lines From Between the Cells
Steps to Shade Every Other Row in Excel Quickly
- Select the data set where you want to add shading to every other row.
- Go to the Home tab, click the Conditional Formatting button and then click the New Rule option.
- Click the option: Use a formula to determine which cells to format
- In the input box, type this formula:
=MOD(ROW(),2)=1
- Click the Format button to choose the color to use for the shading.
- 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.
- 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
Tutorial: The MOD function is very simple but it can be used to do wonderful things in Excel. It ret...
Tutorial: How to convert numbers into the TRUE and FALSE Boolean values in Excel. This is very impor...
Tutorial: Formulas to determine if the current cell is odd or even; this allows you to perform speci...
Tutorial: How to get the row or column number of the current cell or any other cell in Excel. This t...
Macro: This free Excel macro will highlight every other row in a selection of cells with a color ...
Tutorial: How to make Excel automatically add row shading to every other row when you add data to th...