Using colors and symbols in spreadsheets makes data easier to read and understand, leading to better decisionmaking and improved productivity. Colors and symbols do this by visually differentiating data that would otherwise appear similar. Let’s show this with an example.
Contents
Unformatted Data
Budgettoactual 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 upwardpointing 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 addon 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.
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 toG3: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 columnF
.
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

IFS Function – Google Sheets
The IFS function conditionally returns a value based on multiple conditions.