Zebra striping based on cell contents in Google Spreadsheets

In its latest version, Google Docs spreadsheets now support using formulas for conditionally formatting cell font and background colors. What this means is that you can make your sheets much easier to read. Here are some useful formulas for sprucing up your sheets.

If you're like me and love the power of spreadsheets but don't miss Excel one bit, this new feature has been a welcome addition. Truth to be told, it should have been added years ago. But it's here now so let's see what we can use it for.

What conditional formatting is

With conditional formatting, a cell's appearance can reflect its contents. In the past, Google spreadsheets could be used to format cells based on whether a cell's value was higher or lower than what was specified, equal, empty et c. With formulas, this feature becomes considerably more powerful.

Formulas for conditional formatting, which are now available, makes it possible for the formatting of one cell to depend on the value of another cell. Not just itself. Furthermore, it allows you to use the same formulas you normally use for calculations. With custom functions and scripting, the uses are almost limitless.

How to use them

The conditional formatting can be found by following the instructions provided by Google here.

Formulas

To use these formulas, highlight all cells you want the formatting to apply to, then use these formulas. By default, formatting formulas are relative to the cell they apply to (just like all spreadsheet formulas), why it's important to keep those dollar signs there as they indicate the formula cell reference is absolute, not relative.

Zebra striping a numbered list or a list of dates

If you have a list of dates you can easily give each month a distinctive color to make it easier to read it.

The following formula expects a date in the format YYYY-MM-DD, it extracts (MID) the month (MM) and uses the modulo (MOD) function to divide it by 2. Modulo (the % operand) returns the remainder after a division. So 3%2 = 1 and 4%2 = 0. Even month numbers will return 0, i.e. the cell won't have the formatting apply. Odd will return 1, applying the rule. As a result, every second month, starting with the first one, will have the formatting.

=MOD(MID($A1; 6; 2); 2)

TODO list

This formula will apply to cells in rows where the first cell has the value "done" by doing an equivalency check (EQ()), if true, the formatting will apply:

=EQ($A1; "done")

I will add more examples over time. Please post your conditional formatting formulas in the comments section below to share.

Photo by eschu1952

Tuesday, October 28, 2014 - 08:41

Comments

You're welcome!

Leave a comment

Welcome to my blog. My name is Jakob Persson and I'm an entrepreneur and consultant. I work with companies to create measurable change by focusing on users, customers, market strategy and pricing. I'm a deep and broad specialist-generalist with skills in business strategy, digital marketing, pricing, interaction and service design as well as project management and software development.

This is my personal blog where I write on topics that interest me such as design, value-based pricing, marketing and technology but also on personal interests.

I am the founder of Leancept which makes companies more competitive through value-based pricing and customer intimacy.

Follow me on Twitter to get updates and links that I share.

If RSS is your preferred way of reading blogs, check out the RSS feed.

See the contact page for how to get in touch with me.

Best,

Jakob