Just a heads up that if you are on my list, there is a mistake at the bottom of today’s email (10.19.16). In the spreadsheet tip of the week, I told you to enclose any text values in your formulas in ‘ ‘. Actually, you must enclose text values in  ” “. Sorry for the error!


Now, onto today’s post.

Do you ever wish the cells in your spreadsheets would somehow alert you if certain criteria were met?

For example, say you are tracking your budget and want to be alerted if your expenses exceed a certain threshold.

And, it’s not just for financials.

Recently, my 9-5 wanted me to review our client ROI delivery schedule for 2017. It looked something like this:

  1. Client’s measurement period ends.
  2. Client has a runout period of (ideally) three months for final claims to be processed.
  3. The IT smarty pants guys take about a month to ingest a bazillion sources of data for said client.
  4. My team then gets three weeks to complete the ROI.
  5. The reporting team then gets five weeks to package the ROI results up for the client.
  6. The client is presented with the ROI report and presentation.

Now repeat for a bazillion clients, and make sure that they are staggered such that my team never has more than X ROIs happening at a given time, and the reporting team never has more than Y ROIs at a given time. Oh, and there are financial penalties if we do not meet client deadlines.

Can you see a migraine coming on?

My friends, conditional formatting is your game changer.

Conditional formatting is available in both Google Sheets & Excel, and it’s an exceptionally wicked tool to have in your toolbox.

With conditional formatting, you can make certain cells do any of the following when a certain condition is or isn’t met:

  • Change color
  • Make the text effectively “disappear” (by changing it to white)
  • Display icons like checkmarks and stoplights

And, a non-exhaustive list of the types of conditions you can require can include:

  • Dates & values
  • No values
  • Text (e.g. yes, no, I hate cotton candy, etc.)
  • Relative dates & values (e.g. the bottom 10%, > or <, etc.)
  • Duplicate values

To use conditional formatting, you simply tell your spreadsheet program what cells you want to apply the formatting to, the condition these cells must meet for the formatting to be applied, and the type of formatting to be applied.

Learn how to make your spreadsheet results really *pop* using conditional formatting.Click To Tweet

The process is slightly different for Google Sheets than it is for Excel. Here goes.

Here’s how to apply conditional formatting in Excel:

To change the color of a cell that contains a negative value:

Select/highlight the cells you to which you want to apply conditional formatting => click the ‘Conditional Formatting’ icon in the ‘Home’ tab => select ‘Highlight Cells Rules’ => select ‘Less Than’ => enter in the field => from the ‘Format with’ drop-down list, choose ‘light red fill with dark red text’ => ‘OK’.

Adjusting these instructions for positive values should be self-explanatory, but if you run into issues, please let me know in the comment section below. I’m happy to help!

To change the color of a cell that contains the word ‘No’:

Select/highlight the cells you to which you want to apply conditional formatting => click the ‘Conditional Formatting’ icon in the ‘Home’ tab => select ‘Highlight Cells Rules’ => select ‘Text that contains…’ => enter ‘No’ in the field => from the ‘Format with’ drop-down list, choose ‘green fill with dark green text’ => ‘OK’.

And here’s a short gif screencast on how to apply conditional formatting to both cells that contain text and currency values in Excel:

How to change a cell's color based on its value with conditional formatting. Make it easy to identify only those values you care about!

Here’s how to apply conditional formatting in Google Sheets:

To change the color of a cell that contains a negative value:

Select/highlight the cells you to which you want to apply conditional formatting => right-click => select ‘Conditional formatting…’ => in the ‘Format cells if…’ drop-down list, select ‘Less than’ => enter in the field below => from the ‘Formatting style’ drop-down list, choose red box => ‘Done’.

Adjusting these instructions for positive values should be self-explanatory, but if you run into issues, please let me know in the comment section below. I’m happy to help!

To change the color of a cell that contains the word ‘No’:

Click the ‘Add new rule’ text below your newly created rule => change the range in the ‘Apply to range’ field to reflect those cells to which you want the rule applied => from the ‘Format cells if…’ drop down list, select ‘Text contains’ => enter ‘No’ in the field => from the ‘Format style’ drop-down list, choose the green box => ‘Done’.

And here’s a short gif screencast on how to apply conditional formatting to both cells that contain text and currency values in Google Sheets:

How to change a cell's color based on its value with conditional formatting. Make it easy to identify only those values you care about!

There are plenty of other conditional formatting options.

There are so many ways that you can apply conditional formatting to your cells!

And, you don’t need to limit yourself to the less-than-pretty shades of red and green used in the above examples because, as with everything, color customization options exist 🙂

Also, you can apply multiple rules to a single cell. Just repeat the process for each rule.

In the ROI scenario I mentioned, I ended up applying about 15 different conditional formatting rules to my spreadsheet. It might sound like a lot of work, but it probably took me three minutes in total.

My suggestion is to spend five minutes testing out the options so you have a sense of what they are capable of.

If you read this post on creating a profit & loss statement, why not try adding some conditional formatting to the profit & loss statement you created in and/or downloaded from it?

Oh, and in case you were wondering how I managed to get an automatic ‘yes’ or ‘no’ value in a cell, stay tuned for a future blog post on the subject, or sign up below to get exclusive, subscriber-only content. Then, reply to your confirmation email and request to receive ‘In the Sheets’ for 10.19.16 (*Psst – it’s the tip of the week).

Learn how to make your spreadsheet results really *pop* using conditional formatting.Click To Tweet

So, what do you think of this slick feature? Do you have any favorite conditional formatting tricks or tips? Do you have any questions about how to use it to accomplish your specific task? Start a conversation in the comments below! Till next time…