Apply Color To Alternate Rows Or Columns - Microsoft Support

Skip to main content Sign in with Microsoft Sign in or create an account. Hello, Select a different account. You have multiple accounts Choose the account you want to sign in with. Applies ToExcel 2013

Adding a color to alternate rows or columns (often called color banding) can make the data in your worksheet easier to scan. To format alternate rows or columns, you can quickly apply a preset table format. By using this method, alternate row or column shading is automatically applied when you add rows and columns.

Here's how:

1. Select the range of cells that you want to format.

2. Go to Home > Format as Table.

3. Pick a table style that has alternate row shading.

4. To change the shading from rows to columns, select the table, under Table Design, and then uncheck the Banded Rows box and check the Banded Columns box.

   

Tip: If you want to keep a banded table style without the table functionality, you can convert the table to a data range. Color banding won't automatically continue as you add more rows or columns but you can copy alternate color formats to new rows by using Format Painter.

Use conditional formatting to apply banded rows or columns

You can also use a conditional formatting rule to apply different formatting to specific rows or columns.

Here's how:

1. On the worksheet, do one of the following:

   • To apply the shading to a specific range of cells, select the cells you want to format.

   • To apply the shading to the whole worksheet, select the Select All button.

     

2. Go to Home > Conditional Formatting > New Rule.

3. In the Select a Rule Type box, select Use a formula to determine which cells to format.

   

4. To apply color to alternate rows, in the Format values where this formula is true box, type the formula =MOD(ROW(),2)=0.

   To apply color to alternate columns, type this formula: =MOD(COLUMN(),2)=0.

   These formulas determine whether a row or column is even or odd numbered, and then applies the color accordingly.

5. Select Format.

6. In the Format Cells box, select Fill.

7. Pick a color and select OK.

8. You can preview your choice under Sample and select OK or pick another color.

   Tips: 

   • To edit the conditional formatting rule, click one of the cells that has the rule applied, go to Home > Conditional Formatting > Manage Rules > Edit Rule, and then make your changes.

   • To clear conditional formatting from cells, select them, go to Home > Clear, and pick Clear Formats.

     

   • To copy conditional formatting to other cells, click one of the cells that has the rule applied, go to Home > Format Painter , and then select the cells you want to format.

Need more help?

Want more options?

DiscoverCommunity

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Yes No Thank you! Any more feedback for Microsoft? Can you help us improve? (Send feedback to Microsoft so we can help.) What affected your experience? Resolved my issue Clear instructions Easy to follow No jargon Pictures helped Other Didn't match my screen Incorrect instructions Too technical Not enough information Not enough pictures Other Any additional feedback? (Optional) Submit feedback By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×