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

Last Update: October 2019. Applicable to Excel 2019 and below. Windows and macOS

A reader was asking about whether we know of a formula to highlight or shade 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 accomplish this (including using Visual Basic  for Applications code) but today, we will focus on two solutions that are relatively straightforward and require no coding: using conditional formats and using table designs.

Conditional formatting

Important Note: Before you go on with this tutorial, make sure that you have a backup of your Excel spreadsheet. Another option would be to create a copy of your worksheet and then follow the steps below.

  • 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.

Quick explanation: Mod is a function that returns the reminder of a number when divided by other number. In our case we want to ensure that only even  rows will be colored. The formula above allows to accomplish exactly that 🙂

2016-01-09 22_39_51-Settings

  • Hit Format, then open the Fill tab and set your background color (In our case, i selected green).

2016-01-09 22_41_10-Format Cells

  • As per the formula defined above the background color of the even rows, for example 2,4,6 will automatically become green.

2016-01-09 22_42_02-Settings

  • That’s it – we just accomplished alternate  shading with conditional formatting.

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.

Hope it helps 🙂

Leave a Comment