How to automatically highlight alternate rows or columns in Excel 2016?

A reader was asking about whether we know of a method or formula to highlight every other row and column in Excel. Today, we’ll learn how to apply different color schemes to alternate rows or columns in Excel. There are several ways to alternate row colors, but today, we will focus on two main ways to do that: using conditional formats and using table designs.

Using conditional formatting:

  • At first we need to select the rows that should be shaded using conditional formatting.

2016-01-09 22_36_17-Settings

  • Now go to the home ribbon, click on the drop down box of conditional formatting and select new rule.

2016-01-09 22_38_51-Postwork - Excel

  • In select a rule type, hit use a formula to determine which cells to format.
  • In the format values where formula is true, enter the formula =mod(row(),2)=0.

2016-01-09 22_39_51-Settings

  • Then click on the format, go to fill and set a background color (I assigned it to green).

2016-01-09 22_41_10-Format Cells

  • The formula describes when modulus of row (1 to n) divided by two = zero fill the background color green.
  • Modulus is the remainder value in a division.
  • So from the selected rows 2,4,6 will have the background color green.

2016-01-09 22_42_02-Settings

  • Thus alternate rows shading with conditional formatting can be done.

Fill alternates using Using Table style:

  • We can also use table style format instead of using condition formatting.
  • To do so, select the cells that has to be formatted and click on Format as table in the home ribbon and select anyone of the needed table format.

2016-01-09 22_43_13-

  • If u already have a header in the selected cells, check on the My table has headers.

2016-01-09 22_44_53-Format As Table

  • In the table style options, we can check or uncheck on the options provided.

2016-01-09 22_47_09-Postwork - Excel

  • Filter buttons will provide you with drop down symbol.
  • First column will make the fonts bold in the first column.
  • Last column will make the fonts bold in the last column.
  • Banded columns will make alternate columns shaded.
  • Headed Row will make the headed hide or visible.
  • Total Row will calculate the total number of rows in the spreadsheet.
  • Banded Rows will make alternate rows shaded.


Leave a Comment: