Automatically Shade Every Other Row When You Add Data in Excel

Add to Favorites
Author:

How to make Excel automatically add row shading to every other row when you add data to the spreadsheet.

This does NOT use the Table feature in Excel.  This tutorial will show you how to use a formatting method to achieve automatically alternating row colors.

Steps to Automatically Shade Alternating Rows When Data is Added to a Spreadsheet

  1. Select the area where data will be input.  Make sure to select the entire area where your data set will be present.
  2. Go to the Home tab, click the Conditional Formatting button and then click the New Rule option.
  3. In the window that opens, click the option: Use a formula to determine which cells to format

  4. In the input box, type this formula:

    =AND(MOD(ROW(),2)=0,A2<>"")


    Change A2 to be the upper left most cell of the range you selected.  This cell will be where your data will start, where you want the alternating row colors to begin.
  5. Choose the color for the rows.  Click the Format button at the bottom of the window.
  6. In the window that opens, go to the Fill tab and select the desired color for your shaded rows.  Then, click OK.
  7. Hit OK once more on the smaller window and you are finished.

You won't see anything until you add data.

Force Row Shading When the far Left Cell Has a Value

In the example above, if we go to add data to the data set, the row shading will only work on the cells that have data.

Look at this example:

If a cell in the data set is left empty, that particular cell will be void of shading.

Let's change this so that, as soon as you type something into column A, the entire row for the data set will be shaded.

Follow the same steps as above except that in step 4, use this formula:

=AND(MOD(ROW(),2)=0,$A2<>"")

The only difference between this formula and the other one is that this one has a dollar sign $ in front of A2.  This means that the column reference will not change when the conditional formatting is applied.

Look at the example now:

This way, our data set is much more readable.

Edit the Formula After it was Entered

If you need to edit the formula that we used for the conditional formatting, go to the Home tab > Conditional Formatting > Manage Rules > click the desired rule from the list > hit Edit Rule and you can change the formula from the small window that opens.

Notes

This is one of my favorite "tricks" in Excel and it allows you to make workbooks that feel interactive without having to do much at all.

This is a sure way to impress many of your colleagues and, maybe more important, it will allow you to create easy-to-read spreadsheets with little effort.

Make sure to download the accompanying spreadsheet so you can see this feature in action.


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

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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...
Dynamic Formulas that Update When you Add Data in Excel
Tutorial: I'll show you how to make formulas and functions that automatically update when more data...
Shade Every Other Row in Excel Quickly
Tutorial: How to shade every other row in Excel quickly without using the Table feature. This method...
Odd or Even Row Formulas in Excel
Tutorial: Formulas to determine if the current cell is odd or even; this allows you to perform speci...
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...
Tutorial Details
Excel Function: AND(), MOD(), ROW()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course