Use Colors and Symbols to Indicate Trends in Google Sheets

Using colors and symbols in spreadsheets makes data more accessible to read and understand, leading to better decision-making and improved productivity. Colors and symbols do this by visually differentiating data that would otherwise appear similar. Let’s show this with an example.

Unformatted Data

Budget-to-actual reports are a common type of financial analysis. These reports often look bland and uninteresting, but they don’t need to. We’ll use conditional formatting for the colors and an IFS formula to insert symbols.

This spreadsheet is a typical report without any accentuation of variances.

Now let’s apply insert symbols based on the variances in column F.

Visual Differentiation

Symbols

We’ll use the IFS formula to insert the symbols based on multiple conditions.

The formula used in cell G3: =IFS(F3>0,"▲",F3<0,"▼",TRUE,"-")

We use the IFS function to insert a triangle pointing in the direction of the change. Our first formula is in cell G3, which looks at the variance in F3. We’ll use an upward-pointing triangle for positive (F3>0) and downward for negative (F3<0). In the case of no variance, as in row 11, the third argument TRUE is a fallback if the first two conditions aren’t met, which places a dash (-) to indicate no change.

These triangles are a significant first step. Now let’s add some color.

Conditional Formatting

We will use conditional formatting with a green background fill and font color for positive numbers and red for negative. This conditional formatting rule is formatting column G if the data in column F meets certain conditions. Therefore, we must use the Custom formula option. First, go to the menus and choose Conditional formatting, then pick Custom formula is.

Next, we’ll define the custom formatting rules.

Formulas: =F3>0 and =F3<0.

The formulas are pretty simple, but there are a few things to notice.

• You write custom formatting rules for the top left of the formatting range, so you write these rules as if they are in cell G3, even though they apply to G3:G14.
• We are using a light background and dark font color to increase contrast.
• Although Google Sheets applies the rules to column G, they look at the data in column F.

Formatted Data

And here’s the finished table.

The table tells a much clearer story now that you’re formatted it. Unfortunately, it’s showing a lot of red. However, you can tell your boss that much of the red is lower expenses!

Live Template

Grab a copy of the spreadsheet used in this tutorial as a guide.

Related Posts