How To Format Every Other Row In Excel

Along with improving the appearance of large data tables, it also helps with the user readability of the table. This is especially true on larger data sets. Imagine trying to quickly locate and decipher the information in this table:

Unformatted table

Compared to this…

Formatted table

In this article, I’ll walk you through three ways of formatting every other row or column in Excel.

Sounds good? Then let’s get going!

How To Format Every Other Row Or Column In Excel

Conditional Formatting

The first method is via Conditional Formatting.

  • Highlight your data set
  • Under the Home tab, there’s a Conditional Formatting button, select “New Rule
Conditional Formatting menu
  • Under “New Formatting Rule” – select the “Use a formula to determine which cells to format” option
Formatting rules
This is a Mac screenshot but there’s a similar option under Windows
  • In the formula box, type in =MOD(ROW(),2)=0 to format every second row. =MOD(COLUMN(),2)=0 for every second column. Change the =0 to =1 if you want to go to odd number rows or columns.
  • Click OK
Formatted table

Note – this can slow down an Excel file and significantly add to the file size if you’re dealing with thousands of rows. Check out this post for more information on The Hidden Impact of Conditional Formatting On Excel Speed.

Create A Table

The second method is turning your data into an Excel table.

  • Highlight your data and press CTRL+T or CMD+T
  • Check “My table has headers” if appropriate
  • Click OK
Create table
  • Your data is now turned into an Excel table. There is a new “Table” tab when you click anywhere on the table. Excel provides several pre-set color options in the Ribbon.
Blue data table

It’s worthwhile noting that when you directly reference a table, the formula changes from a cell reference, e.g. B2 to =[@Column2]. This will still work but I find it a bit confusing when auditing a formula. It still works in the same way, but I personally prefer keeping my formulas with specific cell references.

Interesting read – How To Format Yes Or No In Excel

Format Painter Every Other Row Or Column

The third and final method is a semi-manual process. I find works great for one-off formatting exercises and I don’t want to utilize the table function. This also works great on columns as well.

I format the first row or column and copy-paste the format to the third row or column. Then I hit F4 on the 5th / 7th etc. Alternatively, on larger data tables, I copy the formatting from rows 1-3, paste, copy rows 1-7, paste and incrementally increase the copy-paste format.

Paste format

Yes, I do advocate for automating stuff generally but this is relatively quick for one-off workbook formatting exercises!

I hope you found this helpful and that it improves your Excel productivity!

Other Excel tutorials you may also like:

Leave a Comment