In this article:

How to Mark Positive Numbers as Green and Negative Numbers as Red in Google Sheets

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. 

NASDAQ data showing changes in the values in red and green colors. Taken from the official NASDAQ website.


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:

Original data.


Step 1: Select the range of cells that you want to shade with color green if the values are positive.

The range of cells highlighted.


Step 2: Go to Format > Conditional formatting

Click Format then click Conditional formatting from the drop-down menu.


A sidebar will appear on the right side of Google Sheets.

Conditional format rules sidebar with default options.


Step 3: Go to Format rules. Click the drop-down list below Format cells if… then scroll down to find Greater than option. 

Format rule options. Greater than highlighted.


A small textbox will appear below, where you can input a value. Input 0 as the value. 

The greater than rule selected. Value to compare to is 0.


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

The rule applied to the selected range. The positive values are highlighted by green shade in the cell. 


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:

Preformatted rules shown: green, yellow, and red. Can be shaded or the text color set. 


Choose this option:

Preformatted rules shown. The green text highlighted.



Then click Done.

The greater than rule applied to the third row. 


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:

The same numbers but in boldface.


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. 

Same range of cells selected but with existing conditional formatting. 


Step 2: Click Format > Conditional formatting.

Click Format then click Conditional formatting from the drop-down menu.


Step 3: The Conditional format rules sidebar will appear again on the right side. Click Add another rule.

Conditional format rules sidebar. Add another rule below the existing rule.


Step 4: Click again the drop-down list below Format cells if… and select Less than

Format rules options. The less than rule selected. 


A textbox will appear again below the box. Type 0 on it. 

The less than rule selected. 0 set as the value to compare to.


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:

Preformatted rules shown: green, yellow, and red. Can be shaded or the text color set. 


We will select this option so that we have consistency with the green text:

Preformatted rules shown: red text color highlighted. 


Click Done. 


 The range now looks like this:

Same range but with both positive and negative number rules.


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.

The same data, but with the whole row selected.


Step 2: Go to Format > Conditional formatting, then click Add another rule on the sidebar on the right side of Google Sheets. 

Conditional formatting rules sidebar. Depending on the cells, the existing rules can appear on the list.

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.


Custom formula selected, with the formula added in the textbox.


Step 4: Look for Formatting style and click the green shaded box with Default text. Preformatted options will appear. Choose this option:

Preformatted rules shown, green text highlighted. 


Click Done

The rule applied to almost all cells in the selected row.


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:

Another rule added to work for all rows. 


Do it again to other rows, also adding the negative values rule as well, and you can get the following result:

The rules now applied to all rows. 


Schedule a free automation consult
Learn more

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started