#41: Shading Alternate Rows in Excel

Have you ever had trouble reading across a large spreadsheet? One row blurs in with another when you’re trying to match what’s in Column A with something clear over in Column Q.

Here’s a good trick that can help improve the readability of your spreadsheets. Shading every other row is a quick and easy way to help keep your eyes on track and not mix up data from different rows.

Conditional formatting is your friend – Conditional formatting is one way to make your data more legible by automatically applying cell shading to every other row in a range of cells.

Here’s how to do it:

  1. Select the range of cells that you want to format
  2. From the Format menu, select Conditional Formatting
  3. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula:
    =MOD(ROW(),2)=0
  4. Click the Format button, select the Patterns tab, and pick a color for the shaded rows.
  5. Click OK twice to return to your worksheet.

The good thing about doing it this way is that this approach is dynamic. If you update, filter, or sort the data, the formula preserves the alternating format you chose for that range.

Learn more about Conditional Formatting

Advertisements

7 thoughts on “#41: Shading Alternate Rows in Excel

  1. We’re a group of volunteers and starting a new scheme in our
    community. Your web site provided us with valuable info to work on. You’ve done a formidable job and our entire community
    will be thankful to you.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s