The Excel Skinny

A blog about Excel and its users

Shading your rows with alternating colors is an easy way to make your spreadsheet more legible and less confusing.  There are 2 ways of doing this on Excel for Mac.  

Using the AutoFormat feature completes this task, but if you end up deleting a row your spreadsheet becomes an uneven mess. AutoFormat does not automatically correct the shading to alternate every other row so you end up with 2 rows shaded next to each other - never good.

The more accurate and preferred way of shading every other row is through Conditional Formatting.  Basically we are going to have Excel for Mac calculate if the row is even or odd, and shade them accordingly, so if you delete a cell the shading will shift for the entire worksheet.  

 

How to:

  • Select the Range of Cells you want to shade.  I usually select the entire worksheet by using the Command + A, Select All shortcut 
  • Now select Format – Conditional Formatting from the upper toolbar
    • This pops open the Conditional Formatting Dialog Box
    • Press the + Button on the bottom left hand side to open the New Formatting Rule Dialog Box.  
      • Change the options in this box to the following (In this order):
      • Style: Classic
      • In the second drop down: Use a formula to determine which cells to format 
      • Type in: =MOD(ROW(),2)=0
        •  This tells Excel for Mac to check if the row is even.
      • The Format With option lets you choose the color the shading will be.
    • Click OK and you will be brought back to the original Manage Rules dialog box with your rule now applied.
    • Hit OK again, and your spreadsheet will be striped!  

 

And that’s how to apply a color to alternating rows in Microsoft Excel for Mac!  Let us know if you have any questions or Excel for Mac tip suggestions! 

#Excel