Instructions for coloring alternating rows and columns in Excel

Instructions on how to color alternate rows and columns in Excel using Conditional Formatting. You can do it easily on Excel versions 2007, 2010, 2013, 2016.
 

Table of Contents:
1. Done in Excel 2016
2. Done in Excel 2013 spreadsheet

1. How to alternate colors in Excel 2016

Step 1: Highlight the cells that need to be colored alternately.

Instructions for coloring alternating rows and columns in Excel Picture 1

Step 2: Next, we need to do is on the Home interface of Excel, select Conditional Formatting > select New Rule.

Instructions for coloring alternating rows and columns in Excel Picture 2

Step 3: Next, select the last line Use a formula to determine which cells to format , below we fill in =MOD(ROW(),2)>0 then click Format .

Instructions for coloring alternating rows and columns in Excel Picture 3

Step 4: Next, select Fill and choose the color you want.

Instructions for coloring alternating rows and columns in Excel Picture 4

Then you just need to click OK and the result will appear as below.

Instructions for coloring alternating rows and columns in Excel Picture 5

Step 5: Click Manage Rules .

Instructions for coloring alternating rows and columns in Excel Picture 6

Step 6: Here you will see the list of Rules just created and we just need to click to change the color.

Instructions for coloring alternating rows and columns in Excel Picture 7

Step 7: Continue to apply steps 3 and 4 to change the color and choose the color you want.

2. How to alternate colors in Excel 2013 spreadsheet

Step 1: Open the Excel spreadsheet that you want to color alternately.
Step 2: Color the rows on the spreadsheet with Conditional Formatting

On the program interface, go to the  Home tab  of the Ribbon bar, select  Conditional Formatting in the Style group , then select  New Rule in the drop-down menu.

Instructions for coloring alternating rows and columns in Excel Picture 8

The New Formatting Rule dialog box appears. In the Format values ​​where this formula is true box , type the following formula: =MOD(ROW(),2)>0

Instructions for coloring alternating rows and columns in Excel Picture 9

Next, to choose the fill color for the alternating rows, click the  Format button, the Format Cells dialog box   appears, click the  Fill tab  and choose the color you want to fill (here I choose light blue). Finally, press O K to finish.

Instructions for coloring alternating rows and columns in Excel Picture 10

Step 3: Change the fill color on alternating lines

If you want to choose a different color (other than the blue above) click Conditional Formatting then click Manage Rule .

Instructions for coloring alternating rows and columns in Excel Picture 11

The Conditional Formatting Rule Manager dialog box  appears, select Edit Rule .

Instructions for coloring alternating rows and columns in Excel Picture 12

Click on the  Format box and select the color you want to change (here taimienphi chooses yellow). Then click OK to finish.

Instructions for coloring alternating rows and columns in Excel Picture 13

The results have changed:

Instructions for coloring alternating rows and columns in Excel Picture 14

Alternating colors in Excel spreadsheets not only makes it easier to distinguish data, but also makes your charts more intuitive and easier to follow. This is especially useful when you work with spreadsheets containing many rows and columns of data, helping to reduce confusion and increase work efficiency.

If you want to optimize your Excel spreadsheets further, you can try other tricks such as counting the number of words in cells, rows or columns to work with data more flexibly. Refer to tutorial articles such as how to use the LEN function in combination with the SUBSTITUTE and TRIM functions to  count the number of words in Excel  easily, helping to optimize your spreadsheets.

Samuel Daniel

Update 18 February 2025