How Conditional Formatting in Spreadsheets Improves Collaboration

aNewDomain.net—In addition to being a technology writer and consultant, I’m also a serial entrepreneur. In other words, I’m a business owner with too many projects at various stages of completion. I have investors, employees, and bankers all who need to know what’s going on with different aspects of the projects I’m part of. I can share copious amounts of information with my staff and partners, but experience has taught me they prefer not to know the whole scope of a project. Most people in these positions want to know the bare minimum—and relevant information—to do their jobs. Over the years, I learned my best communication tool is a properly-formatted spreadsheet with conditional formatting that conveys information in a digestible and concise manner.

Everyone knows how useful spreadsheets can be. Whether you love them or hate them (I love them) they’re a very effective way to present numbers. The downside is when someone needs to interpret those numbers. A plain spreadsheet just doesn’t present the narrative of the numbers. You could embed the numbers into a document, explaining them, but I consider that ineffective communication. If you need to tell someone what you’ve just shown them, in this case explaining a spreadsheet, you haven’t done your job well.

Conditional formatting adds context to a spreadsheet. If you’re not familiar with conditional formatting, it simply means a format that allows a spreadsheet cell to appear differently depending on its contents. You could have a cell turn bold when a number reaches a certain threshold or apply a background color to the cell. These variations in formatting show users who are less experienced with the document the elements that are important. In other words, by using conditional formatting, your spreadsheets have context to a wide group of collaborators.

I use conditional formatting in a few different ways. I maintain a Google Drive spreadsheet for each Vermont Computing, Inc. client. The spreadsheet tracks maintenance on servers and workstations. By entering date parameters in the conditional formatting, the longer a maintenance item has gone, the more “red” the cell background becomes. We also maintain a spreadsheet for client contracts that gets updated frequently, indicating those clients who need something more in order to meet their contract obligations.

In both of these examples, I use shades of green, yellow, and red. These colors based on common traffic lights are familiar to everyone and give a quick view of the context of the documents. If a cell is green, it’s good. If it’s red, it’s bad. I’ve found that these documents are effective for both internal and external communication. By understanding the narrative that the conditional formatting brings to the document, clients can make better decisions and employees can prioritize and better do their jobs. Can you tell that I’m a fan?

Every spreadsheet program handles conditional formatting differently, but I’ll show you how to set it up in Microsoft Excel and Google Drive.

For Google Drive, select the cells that you want to format and then right click on one of them.

Image credits: Jeremy Lesniak for aNewDomain.net

Select Conditional Formatting at the bottom of the list. From here, add the rules you want to apply to those cells. Note the different options–they let you get creative with your documents.

For Microsoft Excel, select the cells that you want to format, and then press the Conditional Formatting button under the Styles section of the Home ribbon.

Select New Rule from the list and then choose the formatting rule that best matches your needs. If you don’t see a rule that works for you, you can create a formula from scratch.

Be sure to test your formulas with realistic values to make sure the formatting works the way you want it to.

There you have it. You now know how to apply conditional formatting to your spreadsheets so that your users can easily decipher what the numbers mean to them.

Enjoy!

Based in Vermont, Jeremy Lesniak is managing editor at aNewDomain.net and founder of Vermont Computing, Inc. and whistlekick.com Follow him @jlesniak or email him [email protected]

 

Jeremy Lesniak
Based in Vermont, Jeremy Lesniak is managing editor at aNewDomain.net and founder of Vermont Computing, Inc. and whistlekick.com. Email him [email protected]
Jeremy Lesniak
Jeremy Lesniak
Tags: Business,Productivity,Software,Technology
  • http://www.collatebox.com/ Sean kris

    Completely agree, though all these features are great but in built validation is something which is not focused by many. My sales team enters wrong email id in the column & Excel, Google docs just accepts it blindly. For which I spend my time to correct all cells.
    But to my relief I came across this tool called bit.ly/collatebox seems a handy one. Neat formatting, inbuilt validation criteria etc.