Use Colors and Symbols to Indicate Trends in Google Sheets

Using colors and symbols in spreadsheets makes data easier 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.

An unstyled table of data showing variances with no symbols or colors
Unstyled Data

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

Visual Differentiation


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

An IFS statement brining symbols into the spreadsheet to indicate directional change
Symbols as Indicators

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 fifth argument TRUE is a fallback if the first two conditions aren’t met, which places a dash (-) to indicate no change.

You can use the Insert Special Characters add-on to place the special characters (,↗,◩,⇡) in your sheet without leaving the Google Sheets window.

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.

First, choose Conditional formatting.

The conditional formatting menu option
Conditional Formatting Menu Option

Then pick Custom formula is.

The custom formula menu option
Custom Formula

Next, we’ll define the custom formatting rules.

The two custom formulas defined
Both Custom Formulas

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.

Table with formatted variances
Formatted Data

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

Leave a Comment