How Excel Color Every Other Row

  • Home
  • / How Excel Color Every Other Row


It is just a common training to add color to alternate rows within an Excel Spreadsheet to make it simpler and to enhance readability. In a spreadsheet, if you will find several rows to be colored, and you have to do it one by one, it will soon be time-consuming and tiring as well. One of the greatest methods to make points clearer is to color every switch row in the sheet. The feature applies to MS Excel 2007 and higher versions.

This article describes how to highlight every other row in excel. You can quickly color every other row in Excel, as there are different rows or columns in your spreadsheet. Please check out the two probable techniques below…

How Excel highlight Every Other Row by Using the “Format as Table” option 

  1. On a new spreadsheet, choose the cell range that you want to color the alternate row, or press Ctrl + A to choose the whole data. For example, I have selected a variety of data from F5 to H13. Please browse the screenshot.

How Excel Color Every Other Row 1

2. Under the Home tab, locate the styles group and click Format as Table. In addition, you can use the arrow buttons to scroll through the available table styles to view them all and to check everything is insured and bonded. In the example below, I’ve selected table style.

3. And finally, your selected data will have alternate colors in a row. Please see the result.

How Excel Color Every Other Row 2

Use the “Conditional Formatting” option to color every other row in excel 

  1. Initially, open an Excel file or input specific data on your new spreadsheet. And Select the cell range that you wish to shade, or press Ctrl + A to select the complete data. In the example, I have the exact same data as explained in the previous method.
  2. Next, Under the Home tab, find the Styles group, and click Conditional Formatting, and then select New Rule… options. Please start to see the screenshot below:

How Excel Color Every Other Row 3

3. On a New Formatting Rule window, select Use a formula to determine which cells to format. In the Format values where this formula is just a true input box, type the formula = MOD (ROW (), 2) = 1 manually and then click Format open, Format Cells dialog box.


  • Here I use the formula = MOD (ROW (), 2) = 1 to color the odd rows. If you wish to color the even rows try a formula like this, = MOD (ROW (), 2) = 0.
  • If you wish to color alternate columns instead of alternate rows you can use the exact same formula. Only change ROW () purpose by COLUMN () function. Please try to find the difference!

4. Then on the Format Cells dialog box, click the Fill tab, and select the color that you want to use to color every other row, and then click OK. This will be a preview of the filled color for your previous New Formatting Rule window. Click OK to close the New Formatting Rule dialog box.

5.  And finally your result will be as:


That is all about the excel alternate row color. Easy and exciting, right? Do practice these tricks as you already know that practice makes a man perfect! Thank you for reading!

Write your comment Here