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

This article describes how you can alternate row colours in MS Excel to quickly highlight every different row or column in your spreadsheet. Please check out the two probable techniques below…

How Excel Color Every Other Row Use the "Format as Table" option to color the alternate row

1. On a new spreadsheet. Choose the cell range that you want to colour 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.  Underneath 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. In the               example below, I've selected table style medium.

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

How Excel Color Every Other Row 2

How Excel Color Every Other Row Use the "Conditional Formatting" option to color the alternate row

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 colour alternate columns instead of alternate rows you can use the exact same             formula. Only change ROW() purpose by COLUMN() function. Please try and try to find the               difference!

  • 4. Then on 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 preview of 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 colouring of alternate rows in Excel. Easy and exciting, right! And thank you for reading.