Question

Conditional Formatting in Google Sheets: How to Apply It Easily

Answer 1

What is conditional formatting for in Google Sheets?
Conditional formatting in Google Sheets based on values
Google Sheets: rule-based conditional formatting
Formula-based conditional formatting

---

Conditional Formatting in Google Sheets: How to Apply It Easily

When a table includes many records, it is common for the content to become confusing: sometimes, due to the large amount of information, it takes a long time for the reader to find the data that interests him. In these cases, custom highlighting some values can help you work with them. With conditional formatting rules in Excel or Google Sheets, values ​​that meet the conditions that have been set are automatically highlighted..

Should we highlight in red all the values ​​of a table greater than 500, or cells with a date after the current one, for example? In these cases, the user of the Spreadsheets can apply certain formatting rules, defining the conditions in detail and establishing the format of the cells that meet these conditions.

Index

1. What is conditional formatting for in Google Sheets?
2. Conditional formatting in Google Sheets based on values
3. Google Sheets: rule-based conditional formatting
4. Formula-based conditional formatting

What is conditional formatting for in Google Sheets?

The possible applications of conditional formatting are as varied as the conditions that can be defined to format cells. In accounting , you can use conditional formatting to highlight monthly gains and losses, highlighting all negative values ​​in red, for example. On the other hand, in project management , it is possible to format the overview of current budgets , establishing that the latest expenses in certain concepts are immediately highlighted in different colors in case they exceed the budget..

In general, applying conditional formatting helps to more clearly organize all content in long tables. The highlighting of certain elements in the table, usually in color, can be based on pure values ​​or on some specific rules or formulas.

Conditional formatting in Google Sheets based on values

The easiest way to highlight certain data is to select a range in the table and then compare and relate the values ​​it contains. With the "Color Scale" format option, which includes a series of default settings, higher values ​​are displayed in a more intense color than lower ones. This gradation appears in green tones by default, although you can also configure the colors to your liking. To give this simple conditional formatting to a Google Sheets table, follow these steps:

1. Select the desired range from the table.
2. Right-click on the selected range, and in the drop-down menu, press? Conditional formatting ?.
3. Click on the item? Color scale ? in the menu on the right. You can leave the green tones to highlight the corresponding values ​​in the table or you can define the fill colors for the cells yourself. Finally, press? Ready ?

If you wish, you can customize the colors for the minimum and maximum values ​​and, optionally, for the average value.

Google Sheets: rule-based conditional formatting

If you have a table showing your company's profit and loss, and you want to be able to distinguish between months with particularly good or bad performance at a glance, you can define a conditional format based on rules and exact values. In the following example, we use a table that shows the twelve months of the year and their respective income or expenses. We want to highlight the following conditions in color: winnings above 1000 euros should be automatically colored green , while losses should automatically be shown in red ..

1. Select the range of the table for which you want to define the conditions, right-click anywhere in the area marked in blue and choose the option? Conditional formatting ?.

By right clicking on the selected range, a menu appears with the option? Conditional Formatting ?.

2. In the window of? Conditional formatting rules ?, you can see in which range the conditions and the corresponding format will be applied. Click on the field arrow? Format cells if ... ? and, in the drop-down menu, select? Greater than ? Write the value? 1000 ? in the field below. In this way, all values ​​greater than 1000 in the selected range from the table will automatically be highlighted in green.

Google Sheets gives the chosen format to the cells that contain the values ​​that meet the condition you have defined.

3. Then by clicking? Add another rule ?, you can also set values ​​below 0 (i.e. losses) to be highlighted in red. Click on this option and select? Less than ? Then write the value? 0 ? and set the color to red. To finish, click? Ready ? and the conditional formatting will be confirmed in Google Sheets.

Since negative values ​​and values ​​over 1000 are highlighted in color, they are immediately distinguishable. When you enter new figures in that range, they will automatically adopt the format that matches the rule you have established.

Formula-based conditional formatting

In addition to the predefined rules that the program includes to format certain values, texts or data, you can also create your own formulas as a condition for formatting . With a custom formula , only cells and records that comply will be formatted. This can be useful if you want the data to be entered later to be highlighted in the table, as in the following example:

1. Select the range in the table that you want to apply conditional formatting with a formula. Right-click anywhere in the range marked in blue and press? Conditional formatting ?.

The option? Conditional Formatting? It can be applied to both a single cell and an entire range - just right-click to select it.

2. In the configuration window? Conditional Formatting Rules ?, opens dropdown menu? Format cells if ... ? and select? The custom formula is ?. Enter the formula you want (in our example,? = B4> TODAY () ?) For the corresponding range and choose a suitable format. In this example, all data that is entered on a date after today will be highlighted in red:

The formula? = B4> TODAY ()? will cause, starting from cell B4, all the data that is entered on a date after today will be highlighted in red.

3. Confirm the conditional formatting by clicking? Ready ? If you right click on any of the cells with conditional formatting and select the option? Conditional formatting ?, you will see all the formatting enabled for that cell and, if necessary, you can add more rules.

With this option, you can see all the conditional formatting rules that have been enabled: in this case, it shows the formula of the condition, the range in which it is applied and a preview of the format, shown in the numbers? 123 ?

In the following YouTube tutorial , you will find more information about conditional formatting in Google Sheets and other interesting examples:

To protect your privacy, the video will be uploaded after clicking.

advice

The Google spreadsheet program is also included in the handy IONOS G Suite package, which includes a Gmail company account, additional storage space and Google's extended technical support service.

---