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:
Compared to this…
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”
- Under “New Formatting Rule” – select the “Use a formula to determine which cells to format” option
- 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
Note – this can slow down an Excel file and significantly add to the file size if you’re dealing with thousands of rows.
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
- 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.
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.
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:
