How to Mark Positive Numbers as Green and Negative Numbers as Red in Google Sheets
Learn how to turn positive numbers green and negative numbers red in Google Sheets, an important technique for showing gains and losses.
In financial markets, the values and points of financial products such as stocks and currencies are often shown either in green or red, where green indicates a gain in its value while red indicates a drop in value.
In this tutorial, let us learn how to mark positive numbers as green and negative numbers as red in Google Sheets.
How to color positive numbers with green color
We will use conditional formatting to color positive numbers with green color. We will use this example for our entire tutorial:
Step 1: Select the range of cells that you want to shade with color green if the values are positive.
Step 2: Go to Format > Conditional formatting.
A sidebar will appear on the right side of Google Sheets.
Step 3: Go to Format rules. Click the drop-down list below Format cells if… then scroll down to find Greater than option.
A small textbox will appear below, where you can input a value. Input 0 as the value.
Step 4: By default, the formatting style will shade the cell green if the condition is fulfilled. If you are satisfied with the color, click Done.
It is also possible to turn the text green for positive values rather than shading the whole cell. On the sidebar, look for Formatting style and click the green shaded box with Default text. Preformatted options will appear:
Choose this option:
Then click Done.
You can make the numbers boldface by either selecting the range then pressing Ctrl+B on your keyboard or by clicking the bold option in the toolbar:
How to color negative numbers with red color
We will follow the same steps for negative numbers with only slight deviation.
Step 1: Select the same range that we have in the previous section. This is because these values change frequently; you want to ensure consistency on all the involved cells.
Step 2: Click Format > Conditional formatting.
Step 3: The Conditional format rules sidebar will appear again on the right side. Click Add another rule.
Step 4: Click again the drop-down list below Format cells if… and select Less than.
A textbox will appear again below the box. Type 0 on it.
Step 5: We will change the formatting style. Look for Formatting style and click the green shaded box with Default text. Preformatted options will appear:
We will select this option so that we have consistency with the green text:
Click Done.
The range now looks like this:
How to apply conditional formatting to entire row
This tutorial would not be complete if we didn’t apply the conditional formatting rules we set to the entire row. We will use the same example, but for now we will only focus on the first row. Here are the steps:
Step 1: Select the cells.
Step 2: Go to Format > Conditional formatting, then click Add another rule on the sidebar on the right side of Google Sheets.
Step 3: Click again the drop-down list below Format cells if… and select Custom formula is. Type C2>0 on the text box. C2 is the cell where the value of change (which can be positive or negative) is located.
Step 4: Look for Formatting style and click the green shaded box with Default text. Preformatted options will appear. Choose this option:
Click Done.
As you can see, the percentage change is not highlighted in green. There is an easy fix; simply apply the same steps in the first section. Selecting D2 alone, we apply the Greater than rule with 0 as the number:
Do it again to other rows, also adding the negative values rule as well, and you can get the following result:
