Vix Blog
No Result
View All Result
  • Economia
  • Educação
  • Segurança
  • Mundo
  • Negócios
  • Notícias
  • Tecnologia
  • DMCA
NEWSLETTER
  • Economia
  • Educação
  • Segurança
  • Mundo
  • Negócios
  • Notícias
  • Tecnologia
  • DMCA
No Result
View All Result
Vix Blog
No Result
View All Result

How to Format Values in Google Sheets

Carolina by Carolina
25 de março de 2022
Reading Time: 11 mins read
0

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:


Dataset Consisting of Profits of Seven Weeks in Google Sheets

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:

  1. Select the range of cells in which you want to format negative entries.
  2. Navigate to the Format option in the menu and click on Conditional formatting in the list.
    Conditional Formatting Option in Google Sheets
  3. Doing so will open the Conditional format rules window, where you need to apply the Format rules.
  4. In this window, locate Format cells if… dropdown and select the Less than option from the list.
  5. Enter “0” in the Value or Formula box.
    Apply Conditional Format Rule on Given Dataset in Google Sheets
  6. 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.
  7. 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)
    Clearing Color Fill From Data Set in Google Sheet
  8. Now, click on the Text color option right before Fill color, and choose the red color from the list.
  9. Hit Done.
    Changing Text Color of Dataset With Defined Rule in Google Sheet

MAKEUSEOF VIDEO OF THE DAY

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:

  1. Select the same dataset you want to format with another rule.
  2. Navigate to Format > Conditional Formatting.
  3. Click on Add another rule.
    Adding Second Conditional Rule for Dataset in Google Sheets
  4. Select greater than in Format cells if… and enter “1” in the Value or formula box.
  5. Clear the Fill color format as you’ve done above.
  6. Change the Text color to blue.
  7. After previewing the formatting, hit Done.
    Applying Second Conditional Rule For Dataset in Google Sheets

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.

RELATED: How to Use COUNTIF and COUNTIFS Functions in Google Sheets

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:

  1. Consider the same dataset as above.
  2. Choose the range of cells you want to format.
  3. Navigate to Format > Number > Custom number format.
    Custom Number Format Option in Google Sheets
  4. 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.


Applying Custom Number Formats Formula in Google Sheets

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).

RELATED: How to Share and Protect Your Google Sheets

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.

  1. Select the dataset you want to clear formatting for.
  2. Navigate to Format > Clear formatting.
    Clearing Formatting From Dataset in Google Sheets

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.

RELATED POSTS

France Pulls iPhone 12, USB Cables Explained, and the Best Linux Distros for Old PCs [Podcast]

How to Design a YouTube End Card in Canva

What is Rivian Camp Mode and How Does it Work?

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.


Illustration of beautiful reports using Sheets
The 9 Best Google Sheets Formatting Tips for Creating Professional-Looking Spreadsheets

Do you want to make your Google Sheets look professional and stand out? Here are some tips you should follow.

Read Next


About The Author

Shan Abdul
(127 Articles Published)

Shan Abdul is an engineering graduate. After completing his graduation and MS, he has started his career as a freelance writer. He writes about using different tools and software to help people to be more productive as a student or professional. In his spare time, he loves to watch Youtube videos on productivity.

More
From Shan Abdul

Subscribe to our newsletter

Join our newsletter for tech tips, reviews, free ebooks, and exclusive deals!

Click here to subscribe

Carolina

Carolina

Related Posts

France Pulls iPhone 12, USB Cables Explained, and the Best Linux Distros for Old PCs [Podcast]
Entretenimento

France Pulls iPhone 12, USB Cables Explained, and the Best Linux Distros for Old PCs [Podcast]

21 de setembro de 2023
How to Design a YouTube End Card in Canva
Entretenimento

How to Design a YouTube End Card in Canva

20 de setembro de 2023
What is Rivian Camp Mode and How Does it Work?
Entretenimento

What is Rivian Camp Mode and How Does it Work?

20 de setembro de 2023
How to Transfer Files From Android to PC: 7 Methods
Entretenimento

How to Transfer Files From Android to PC: 7 Methods

20 de setembro de 2023
The 7 Best Firefox-Based Web Browsers
Entretenimento

The 7 Best Firefox-Based Web Browsers

20 de setembro de 2023
As 6 melhores extensões do Mac Safari para estudantes
Entretenimento

Safari Downloads Not Working? 10 Troubleshooting Tips and Fixes to Try

20 de setembro de 2023
Next Post
Porquê fabricar uma maquete de tela no Photoshop

Porquê fabricar uma maquete de tela no Photoshop

AirDrop não está funcionando?  Conserte-o rapidamente com estas 15 dicas

AirDrop não está funcionando? Conserte-o rapidamente com estas 15 dicas

Deixe um comentário Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

No Result
View All Result

Últimas Notícias

France Pulls iPhone 12, USB Cables Explained, and the Best Linux Distros for Old PCs [Podcast]

France Pulls iPhone 12, USB Cables Explained, and the Best Linux Distros for Old PCs [Podcast]

21 de setembro de 2023
What Is ISR Programming? How Interrupts Help You Write Better Code

Getting Started With Sveltestrap

21 de setembro de 2023
What Is Fog Computing? Fog vs. Edge Computing Explained

What Is Fog Computing? Fog vs. Edge Computing Explained

20 de setembro de 2023
How to Design a YouTube End Card in Canva

How to Design a YouTube End Card in Canva

20 de setembro de 2023
What Is a Decentralized Website? How Does the Decentralized Web Work?

What Is a Decentralized Website? How Does the Decentralized Web Work?

20 de setembro de 2023

Categorias

  • Entretenimento
  • Mundo
  • Notícias
  • Segurança
Vix Blog

Somos entusiastas da tecnologia com a missão de ensinar ao mundo como usar e compreender a tecnologia em suas vidas.
SAIBA MAIS »

Entre em contato conosco enviando um e-mail para contato@vixblog.com

Posts recentes

  • France Pulls iPhone 12, USB Cables Explained, and the Best Linux Distros for Old PCs [Podcast]
  • Getting Started With Sveltestrap
  • What Is Fog Computing? Fog vs. Edge Computing Explained

Categorias

  • Entretenimento
  • Mundo
  • Notícias
  • Segurança

Links Importantes

  • Quem Somos
  • Blog
  • Fale Conosco
  • Política de Privacidade
  • DMCA

© 2021 VixBlog - Notícias e dicas para o seu dia dia.

No Result
View All Result
  • Economia
  • Educação
  • Segurança
  • Mundo
  • Negócios
  • Notícias
  • Tecnologia
  • DMCA

© 2021 VixBlog - Notícias e dicas para o seu dia dia.