You might need to format all negative entries in Google Sheet to make them more visible when navigating through extensive spreadsheets. This makes them stand out from other entries, and it gets easier to locate them.
In Google Sheets, there are two ways to format numbers, including conditional formatting and custom number formatting. Let’s see how these methods work on formatting a dataset in Google Sheets uniquely.
Changing Format of Negative Numbers Using Conditional Formatting
By allowing you to apply different rules to the dataset, conditional formatting simplifies the formatting process. However, despite giving you great flexibility to format cells with different formulas and conditions, you may need to add rules multiple times to achieve the desired result.
Let’s apply the conditional formatting to a dataset to make understanding clear. Consider the following list of sales profit for seven weeks:
Consider the case where you want the color of all negative entries to be changed to red. Follow the below steps to see how you can achieve that in the given dataset:
- Select the range of cells in which you want to format negative entries.
- Navigate to the Format option in the menu and click on Conditional formatting in the list.
- Doing so will open the Conditional format rules window, where you need to apply the Format rules.
- In this window, locate Format cells if… dropdown and select the Less than option from the list.
- Enter “0” in the Value or Formula box.
- In the Formatting style, Fill color will be selected by default that will change the color of the cells (having values less than 0) with the one chosen there.
- Navigate to Fill Color and click on Theme color white. (This will make the Fill color white, making it appear as if the formatting has been cleared)
- Now, click on the Text color option right before Fill color, and choose the red color from the list.
- Hit Done.
Formatting Dataset With Multiple Rules
By adding another rule, you can conditionally format other values in the dataset, for example, changing positive values to blue. Here is how you do it:
- Select the same dataset you want to format with another rule.
- Navigate to Format > Conditional Formatting.
- Click on Add another rule.
- Select greater than in Format cells if… and enter “1” in the Value or formula box.
- Clear the Fill color format as you’ve done above.
- Change the Text color to blue.
- After previewing the formatting, hit Done.
Here is how you can use conditional formatting in Google Sheets to format specific values in the dataset to make them stand out. Now, let’s explore how to use custom number formatting to achieve the same goal.
Changing Format of Negative Numbers Using Custom Number Formatting
Custom number formatting produces the same results as conditional formatting, but it involves formatting values in datasets using readily-available different format styles or custom formulas.
The following steps will show you how to format negative numbers using custom number formatting:
- Consider the same dataset as above.
- Choose the range of cells you want to format.
- Navigate to Format > Number > Custom number format.
- Type this formula in the box: “General;[Red]General;General;@”
The above code will format the numbers in your selected range, as shown in the preview below. Click Apply when you are satisfied with the format.
How Does Custom Number Formatting Works?
Let’s look at how the above code in custom number formatting works:
General;[Red]General;General;@
The four types of data (values) exist by default in Google Sheet, namely “Positive,” “Negative,” “Zero,” and “Text.” The sequence remains the same when changing formats by applying formulas.
You can change the color of any value by placing [Color] before General (which means default value) in the code. The “;” is used to separate all types of formats, while the “@” is used to keep the text values intact. Though, you can also change the text color in the same way.
In this example, we only intended to change the color of the negative number, which lies second in the code. That’s why we’ve placed [Red] before it. Thus, if you wish to change the color of positive values, you can do so in one step. Likewise, you can change the color of both zero and text values.
One limitation of custom number formatting is that you can only change the color of the text rather than the whole cell, which could be easily done with conditional formatting.
Custom number formatting has one advantage over conditional formatting in that you can change the formatting for all four types of values in one go. In contrast, you have to apply each rule separately with conditional formatting.
Are These Formatting Types Dynamic in Nature?
Yes, both types of formatting make your dataset dynamic. Therefore, the format will be applied automatically whenever you change any value in the entire dataset (where the formatting has been used).
Even if you remove a value from a cell where formatting has been applied, that cell will retain its formatting. It formats new values based on the rules you specify the next time you add them.
Moreover, copying the data from cells to other cells in the same or different sheets will carry the formatting rules with them. Thus, once you apply the format, you can continue altering the data in your dataset.
How to Clear Formatting in Google Sheets
Follow the steps below to clear formatting in Google Sheets.
- Select the dataset you want to clear formatting for.
- Navigate to Format > Clear formatting.
In clearing the format of a specific dataset, only the conditional formats are removed, along with the alignment and other applied formats. To remove custom number formats, you’ll have to clear the applied formula.
Easily Format Values in Google Sheets
Having the values formatted differently in the dataset simplifies data navigation. Changing negative numbers to red or any other color, for instance, makes it easier to spot them in the dataset.
Although both types of formatting work well, they have their downsides. Conditional formatting requires applying rules multiple times, while custom number formatting may be more error-prone if you don’t know the formula or code. You can choose the one that best suits your needs.
Is it difficult for you to make your spreadsheets look more professional? It is possible to make them stand out by following simple tips. This includes using the right font and spacing in the sheet to using headers and grid lines to make information easier to read.
Read Next
About The Author
