“Yes, tell me more about spreadsheets!”

I know, with such an exciting topic it’s hard to contain the enthusiasm – all those cells, and formulas, and the pleasant white and grey colortone…

Okay, spreadsheets aren’t the most excitings to work with, but these two tips for Google Sheets should not only help break up the monotony of those white and grey bars, but also help with organization and save you time when digging through data.

Conditional Formatting with Colors

conditional formatting options Google SheetsGoogle Sheets allows you to set various rules for the format of cells. For example, if you have to frequently update the status of projects with current completion status, you can easily automate the color coding of cells based on the percentage complete.

This feature can be found under Format > Conditional Formatting within Sheets.

This allows anyone looking at the sheet to quickly identify progress by the color.  There are many built in options to choose from, as well as using your own custom functions.

conditional formatting rules Google Sheets

 

 

 

 

 

 

 

Sort by Color in Google Sheets

When data is color-coded and the only constant is the color of the cells or the fonts, sorting your data can be difficult. If you’ve ever had to dig through a data set looking for “only the ones highlighted in red,” you’ve felt this pain.

Fortunately, thanks to a convenient add-on to Google Sheets, Sort by Color allows you to sort your color-coded spreadsheet based on the cell or font color. By reading the color of the cell or font, Sort By Color uses the hex code as sortable text.

This really helped me when I had a list of hundreds of users, with 100 or so listed in red that needed to be removed. Rather than delete a line, search for the next red entry, erase it and repeat, this moved all the red ones to the top of the list!

Sort by Color screenshot