{"id":7561,"date":"2021-03-26T11:39:00","date_gmt":"2021-03-26T10:39:00","guid":{"rendered":"https:\/\/www.sheetgo.com\/?p=6202"},"modified":"2026-10-16T16:22:28","modified_gmt":"2026-10-16T14:22:28","slug":"formato-condicional-basado-en-otra-celda-en-google-sheets","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/google-sheets-features\/conditional-formatting-based-on-another-cell-in-google-sheets\/","title":{"rendered":"C\u00f3mo usar el formato condicional basado en otra celda en Google Sheets"},"content":{"rendered":"

[et_pb_section fb_built=”1″ module_class=”sheetgo-post” _builder_version=”4.16″ custom_padding=”||1px|||” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_row _builder_version=”4.16″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ custom_padding=”|||” global_colors_info=”{}” custom_padding__hover=”|||” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” theme_builder_area=”post_content” hover_enabled=”0″ sticky_enabled=”0″]<\/p>\n

There are times when you need to look through mounds of data on a spreadsheet, often searching for certain values. However, without any design elements or visual cues, scouring the data can be time consuming and hard on the eyes. Fortunately, with Google Sheets<\/a> you can use conditional formatting to change the color of the cells you\u2019re looking for based on the cell value. This functionality is called conditional formatting. This can be done based on the individual cell, or based on another cell. I\u2019ll show you how it works with the help of a few examples.<\/p>\n

[\/et_pb_text][et_pb_text admin_label=”Connections B – Merge, split, filter – Vertical” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” text_font=”||||||||” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”50477″ theme_builder_area=”post_content”]<\/p>\n

\"\"<\/p>\n

\"39\"<\/p>\n

Merge, split, and filter spreadsheets<\/span>
Regardless of the file format, Sheetgo can handle it.<\/span><\/p>\n

Start free trial<\/a><\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n

Different types of conditional formatting<\/h3>\n

Example 1: Identify the cells below a threshold<\/h4>\n

I\u2019ll start with a basic example using conditional formatting to change a cell\u2019s color. Consider the following spreadsheet. The warehouse supervisor, at a local automobile service station, uses it to track an inventory of spare parts.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1-Conditional-formatting.png” alt=”Conditional formatting based on another cell \u2014 spare parts inventory with Product ID, In Stock, Lead Time and Demand columns” title_text=”Spare parts inventory with Product ID, In Stock, Lead Time and Demand columns” align=”center” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

He needs to place a new order for a product if the quantity in stock (column B) falls below the threshold of 20. There are a large number of products and the values in column B change over time. So, he finds it difficult to identify the products that are going out of stock. Even if he were to color the cells manually to easily fish out the cells later, that would double his work \u2013 not an ideal solution!<\/p>\n

The conditional formatting functionality comes to the rescue, with which you can change the cell colors based on the cell value in Google Sheets. To apply this formatting, first select all the cells in column B.<\/p>\n

Now navigate to Format > Conditional formatting<\/b>. A sidebar opens up on the right side of the screen. You\u2019ll notice that in the Apply to range<\/b> field, the value is already set to B2:B27. This is because that range is already selected. In any case, if you wish to change the range, you can do that.<\/p>\n

I\u2019ll choose Less than or equal to<\/b> in the Format cells if<\/b> drop-down field. Then key in the value 20 in the immediate next field. Essentially you are configuring Google Sheets to format cells<\/a> in a certain way if it satisfies this condition. I\u2019ll choose a Formatting style that\u2019s red in order to suit the situation. To finish, just click on the Done<\/b> button<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2-Conditional-formatting.png” alt=”Conditional formatting based on another cell \u2014 In Stock values of 20 or less highlighted red by a single color rule” title_text=”In Stock values of 20 or less highlighted red by a single color rule” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

This is just a one-time configuration and automatically updates the formatting even if there are future changes to the data.<\/p>\n

I used a direct condition for this particular example. You can also change the cell colors by making use of a formula. For that, you just have to ensure the value in the Format cells if<\/b> drop-down field is Custom formula is<\/b>, and accordingly key in the formula in the immediate next field.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Example 2: Conditional formatting based on another cell<\/h4>\n

Now that you know how to use conditional formatting, let\u2019s look at how to change the color of a cell based on the value in a different cell. I\u2019ll use the same example as before. This time, however, the warehouse supervisor wants the Product ID to be highlighted instead of the Quantity.<\/p>\n

To do this, follow all the same steps as before, but select Custom formula is<\/b> from the Format cells if<\/b> drop-down field. Then, type in the formula based on the cell in column B. In this example, I\u2019ve typed =B2<=20.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/3-Conditional-formatting.png” alt=”Conditional formatting based on another cell \u2014 Product IDs highlighted red by a custom formula that checks column B” title_text=”Product IDs highlighted red by a custom formula that checks column B” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Now I\u2019ve achieved the same highlighting of cells with low inventory, but the Product ID is highlighted instead of the Quantity!<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Example 3: Formatting with multiple rules<\/h4>\n

You don\u2019t need to limit yourself to just one rule when using conditional formatting. Adding multiple conditional formatting rules can help visually dissect your data even further.<\/p>\n

In this example, a fifth-grade teacher wants to highlight students whose grades are either below or above a threshold. Specifically, the teacher wants students with an average below 70 in red, and students in the 90\u2019s in green.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/4-Conditional-formatting.png” alt=”Conditional formatting based on another cell \u2014 student grades sheet with Math, Science, English, Social Studies and Average” title_text=”Student grades sheet with Math, Science, English, Social Studies and Average” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

To do this, you need to add two separate rules. The process for each rule is very similar to the previous example.<\/p>\n

Because you want the names highlighted based on the average scores, highlight the names as the Apply to range<\/b>, and add the scores by typing the rule into the Custom formula is<\/b> section from the Format cells if<\/b> drop-down field<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/5-Conditional-formatting.png” alt=”Conditional formatting based on another cell \u2014 two custom formula rules on the names, red for averages under 70 and green from 90″ title_text=”Two custom formula rules on the names, red for averages under 70 and green from 90″ align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Once you\u2019ve written the first rule, press Done<\/b> and then Add another rule<\/b>. The formulas I used in the rules here are =F2<70 and =F2>=90.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Example 4: Formatting based on multiple cells<\/h4>\n

You can also use conditional formatting based on multiple cells, all within one formatting rule. To do this, you can use the OR or AND functions.<\/p>\n

In this example, the teacher wants to consider each individual subject instead of the average. If a student has a grade below 60 in any subject, their name should be highlighted in red so the teacher can quickly see who\u2019s struggling.<\/p>\n

The method here is similar to the other examples \u2013 hopefully you\u2019re getting used to it by now! The difference is, in the Custom formula is<\/b> section from the Format cells if<\/b> drop-down field, type =OR(B2<60,C2<60,D2<60,E2<60).<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/6-Conditional-formatting.png” alt=”Conditional formatting based on another cell \u2014 names in red text from an OR rule across four subjects” title_text=”Names in red text from an OR rule across four subjects” align=”center” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”change-cell-color-based-on-the-value-of” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Change cell color based on the value of another cell<\/h2>\n

To change a cell’s color based on the value of another cell, you use a custom formula that points at that other cell. This may be helpful when dealing with data that rely on each other a lot, or where you have a lot of dynamic data. Here is a second example, with a list of sales reps. Let’s say I want to highlight the names of the sales team who have less than 10 clients in red. In this example, the formatting of the sales team cells relies on the value of the number of clients.<\/p>\n

1.<\/strong> Highlight the range of cells you wish to format (here, the names in A2:B45), then head to Format > Conditional formatting.<\/p>\n

2.<\/strong> Under \u201cFormat rules\u201d, select “Custom formula is” from the drop-down menu. Add the formula that will create the correct condition, remembering to reference the other cell. In this example, my formula will be =C2<10, written for the first row of the range. As you can see, I\u2019m referencing the \u201cNumber of clients\u201d column in the formula.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/conditional-formatting-based-on-another-cell-formula.webp” alt=”Conditional formatting based on another cell \u2014 custom formula checking column C for the range A2:B45″ title_text=”Custom formula checking column C for the range A2:B45″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

3.<\/strong> Change your \u201cFormatting style\u201d preferences and click \u201cDone\u201d.<\/p>\n

4.<\/strong> As you can see, the names of my sales team who have less than 10 clients are now highlighted in red.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/conditional-formatting-based-on-another-cell-names.webp” alt=”Conditional formatting based on another cell \u2014 names highlighted red where the client count is under 10″ title_text=”Names highlighted red where the client count is under 10″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Highlight an entire row based on one cell<\/h3>\n

To color the whole row instead of just the name, select every column of the table (for example, A2:D45) as the Apply to range<\/b> and add a dollar sign before the column letter: =$C2<10. The $ keeps every cell in the row looking at column C, so each row turns red when its own number of clients is under 10.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”how-to-use-a-custom-formula-in-condition” _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

How to use a custom formula in conditional formatting<\/h2>\n

Using a custom formula as part of your conditional formatting is an extremely useful thing indeed. Not only does it give you the opportunity to create more personalized rules that can provide the exact outcome you want, but they can also be used to achieve other important objectives, as you will see further on in this blog post. You can use different functions such as the IF formula for conditional formatting that may help you solve more complex objectives. First, let\u2019s explore how to use the custom formula to create formatting rules.<\/p>\n

In this example, I want to highlight all the total sales that are between $4,000-$8,000 in orange. I can add a simple AND formula to achieve this quickly and easily.<\/p>\n

1.<\/strong> Highlight the data range you want to format and head to Format > Conditional formatting.<\/p>\n

2.<\/strong> In the \u201cFormat rules\u201d drop-down menu, select “Custom formula is”. In the field below, enter the formula that you need to fulfill your goal. In this case, to highlight the total sales between $4,000-$8,000, I need to add the following AND formula: =AND(D1>4000,D1<8000).<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/conditional-formatting-custom-formula-and-rule.webp” alt=”Conditional formatting custom formula \u2014 Custom formula is field holding an AND formula for D1:D45″ title_text=”Custom formula is field holding an AND formula for D1:D45″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

3.<\/strong> Under \u201cFormatting style\u201d, specify the formatting for the cells that fulfill your rule. In this example, I will set it to orange. Once finished, click \u201cDone\u201d.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/conditional-formatting-custom-formula-orange-style.webp” alt=”Conditional formatting custom formula \u2014 orange formatting style set for the AND rule” title_text=”Orange formatting style set for the AND rule” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

As you can see below, all of the total sales between $4,000 and $8,000 have an orange cell background.<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2026\/10\/conditional-formatting-custom-formula-applied.webp” alt=”Conditional formatting custom formula \u2014 total sales between 4,000 and 8,000 highlighted orange” title_text=”Total sales between 4,000 and 8,000 highlighted orange” align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.22.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Relative and absolute references in a custom formula<\/h3>\n

Google Sheets reads a custom formula as if it were written for the first cell of the Apply to range<\/b>, then adjusts it for every other cell, the same way a formula changes when you copy it down a column. That’s why the examples on this page use =B2 or =C2 and not a whole column.<\/p>\n

A dollar sign locks the part of a reference that shouldn’t move:<\/p>\n