How to Highlight Every Other Row in an Excel Sheet

An excel sheet refers to a single page in Microsoft excel program. It contains cells that enable the organization of data. A cell is an intersection of a column and a row.

Shading of alternate rows could highly increase the conveyance of your data set hence boosting the readability of contents. This can be done in 3 ways, as discussed below:

1. Conditional Formatting.

This is an excellent way to format the layout of your Excel sheet. It’s by use of a Function. Below are quick steps.

  • Select the data set range as desired. Note that if you are shading the entire Sheet, you can click “select all.” Open the conditional formatting box at the right-hand side of the toolbar, then click on “New Rule.”
  • Inside the box, click “use a formula to determine which cells to format.”
  • In the Field, enter the formula: =MOD(ROW(),2=0
  • Click the Format button (set the formatting by filling the color of your choice) and then Ok.

Your Sheet is highlighted in every other row.

Here is the secret. MOD Formula gets the remainder when the row number is divided by 2.; therefore, you can change the formula to suit your wishes. E.g., to shade every third row, the formula will be =MOD(ROW(),3=1.

It’s advisable that if you intend to print your Sheet, then a light shade should be used to highlight it to ensure readability. If you do not have any sorting or filtering on your Datasheet, then this will work for you.

2. Use Excel Table.

This is a predefined way of highlighting alternate cells in Excel. What you need to do is to convert the given data into an Excel Table. Using a Table style format, Filtering and Sorting doesn’t affect the Sheet being formatted.

Here is the procedure:

  • Select the range you want to apply your alternate shade.
  • On the Home tab in the Styles group, click “Format as Table, “then select a table of your choice. Then Ok.
  • You will get a prompt for the range to format and if it has headers. (You can make changes here if need be).
  • Click Ok

This format will then be applied even when you keep adding new rows.

3. Highlight rows manually

This is the last way of highlighting every other row in excel.

  • Click on the headers of each alternating row on your Sheet.
  • Then select the “fill color button” on the ribbon.

This sounds very easy, but the issue is when you have lots of rows to shade. That means it will take time to finish your work. However, if the data set is small, then it can be used to format as needed.

Alternate shading of rows is an easy task, as shown above. You find that the Manual way of doing it is time-consuming, especially if you have a large data set. Conditional formatting is awesome, but if you don’t intend to use any sorting or filtering. The most recommendable one is the Excel table style, considering that a table has to sort and filter in it, so having one doesn’t affect your styling.

Leave a comment

Your email address will not be published. Required fields are marked *