Color Formatting in Spreadsheets

# 4 on our list of the "Top 10 Dysfunctions of #Spreadsheets” … drumroll please … #ColorFormatting. 🖍️ Why can something so simple become a dysfunction for your team?!  Read on:👇

Color formatting your spreadsheets may be helpful to those reading the output, or doing a quick review of data, but it may not scale or change well over time. And it may actually lead to conflicting sources of truth. 📈

For example, say that you use #colorCoding to review a large set of data, flagging rows for review by another teammate. Ask yourself, can that teammate quickly filter or search for your color tags after the fact? Might they miss a row or two if they are quickly scrolling and eyeballing? 👀

And do you have a key of rules that explains the meaning of yellow vs. red? This #metaData — the data about your data — brings a ton of clarity, and as #BreneBrown says, #clarityIsKind. ✅

A common example might be that you conduct a one-off review of inventory levels across a set of products. In this case, you would likely want to include some sort of numeric rule, to signify reorder levels per product, rather than simply a color code.

That’ll make it much easier next time you do a stock take! 📦 Those one-offs have a way of becoming a process over time.

------------

Two ideas for you:

1. If you are stuck in spreadsheets, then consider adding a new column or two with the attributes broken out as 0’s and 1’s, or a checkbox, or text. And use #ConditionalFormatting instead of cell formatting to bring some visual pop dynamically. Conditional formatting rocks, in that it actually improves your data entry quality, rather than creating a trap door for your teammates. You make the rules.

2. If you can migrate your spreadsheet into a system, then model that status or reorder level as a new field, and add controls to dictate how data should be entered going forward.

Bonus points: break out the status changes into a historical log, so you know who entered them, where, when, and add any comments. 📝


Enjoy!