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.
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.
We’ll use the IFS formula to insert the symbols based on multiple conditions.
The formula used in cell G3:
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.
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.
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
- 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
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!
Grab a copy of the spreadsheet used in this tutorial as a guide.
IFS Function – Google Sheets
The IFS function conditionally returns a value based on multiple conditions.