Excel Cheats: Part Four

In this post, Part Four of the series, Excel Cheats: What You Didn’t Know! I will venture into the world of organizing your data. Specifically, changing the background color of alternating rows in your document. This will help you identify important information quickly and easily!

Alternating Shading using Conditional Formatting
You may already know that with Excel, you can change the style of your worksheet to apply shading to alternate rows. This is often the quickest way to do so and can be accessed by highlighting the appropriate rows of content and clicking on the Format as Table option in the menu bar. See image below.

Excel MenubarHowever, in this post I will focus on applying shading to alternate rows when you use conditional formatting. This will only allow you to select one color for shading, not both colors.

  1. First, highlight/select the cells that you wish to format, or if you want to apply shading to the whole worksheet click the Select All button in the top-left corner.
  2. Select All Button in ExcelNext, click on Conditional Formatting from the menu bar, and then click New Rule. Excel Menu Bar: Conditional Formatting
  3. In the New Formatting Rule window, under Select A Rule Type, click Use a formula to determine which cells to format.
  4. In the Format values where this formula is true box, enter =MOD(ROW(),2)=0
  5. If you want to apply shading to alternate columns, instead enter =MOD(COLUMN(),2)=0
  6. Click Format. New Formatting Rule Box in Excel
  7. In the Format Cells box, click the Fill tab
  8. Select the background color that you want to use for the shaded rows, and then click OK.   Select Background Color in Excel
  9. You should now be able to see the color you selected show up in the Preview window in the New Formatting Rule box.
  10. Click OK.

Your Excel worksheet should now be formatted appropriately!
Alternate Shading in ExcelNote: If you want to make changes at some point, click one of the cells that contains the shading, click Conditional Formatting on the menu bar and then click Manage Rules.

Thanks for learning more about Excel in Part Four of the Excel series. I hope you found this cheat useful for your present and future endeavors!