[\/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
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n
![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
Merge, split, and filter spreadsheets<\/span> [\/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 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 1″ title_text=”1 Conditional formatting” 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 2″ title_text=”2 Conditional formatting” 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 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 3″ title_text=”3 Conditional formatting” 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 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 4″ title_text=”4 Conditional formatting” 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 5″ title_text=”5 Conditional formatting” 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 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 6″ title_text=”6 Conditional formatting” 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 Congratulations! You successfully used conditional formatting to provide visual cues that make the data identification process a lot easier!\n<\/p>\n Learn more about alternated colors in the following blog post: How to create an alternated colors list in Google Sheets?<\/a><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" 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 you can use conditional formatting to change the color of the […]<\/p>\n","protected":false},"author":40,"featured_media":33309,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"There are times when we need to look at mounds of data on our daily spreadsheets. However, without any design elements or visual cues, the scouring the data can be stressful to the eyes. Fortunately, though, we can change the cell colors based on the cell value in Google Sheets application! This functionality is called conditional formatting. Let us explore this with the help of a couple examples.\n
Regardless of the file format, Sheetgo can handle it.<\/span><\/p>\n![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/available-on-google-workspace-button-text.webp\")
<\/a><\/p>\nDifferent types of conditional formatting<\/h3>\n
Example 1: Identify the cells below a threshold<\/h4>\n
Example 2: Conditional formatting based on another cell<\/h4>\n
Example 3: Formatting with multiple rules<\/h4>\n
Example 4: Formatting based on multiple cells<\/h4>\n
Example 1: Identify the cells below a threshold<\/h3>\nPlease consider the following spreadsheet. The warehouse supervisor, at a local automobile service station, uses it to track inventory of the spares.\n\nExample<\/strong>
![\"Change](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/change1.png\")
\n\nHe 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 up his work. So, that is not an ideal solution.\n\nThe conditional formatting functionality comes to our rescue, using which we can change the cell colors based on the cell value in Google Sheets. To apply this formatting, first, we will select all the cells in column B (i.e. range B2:B900).\n\nNow navigate to Format > Conditional formatting<\/strong>. A sidebar opens up on the right side of the screen. We'll notice that in the Apply to range<\/strong> field, the value is already set to B2:B900. This is because we have already selected the data. In any case, if we wish to change the range, we can do that.\n\nWe will choose Less than or equal to<\/strong> for the Format cells if<\/strong> drop down field. And then, key in the value 20 in the immediate next field. Essentially we are configuring Google Sheets to format cells in a certain way if it satisfies this condition. We'll choose a Formatting style<\/strong> that's on the reddish side, so as to suit the situation. Finally, we click on the Done<\/strong> button.\n\nExample<\/strong> ![\"Change](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/Change2.png\")
\n\nTo test if this has worked, let us scroll a little bit and validate. We may close the Conditional formatting rules<\/strong> sidebar if we wish to.\n\nExample<\/strong> ![\"Change](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/change3.png\")
\n\nYes, it seems to have worked! Furthermore, this is just a one-time configuration and automatically updates the formatting even if there are future changes!\n\nWe used a direct condition for this particular example. We can also change the cell colors based on the cell value in Google Sheets, by making use of a formula as well. For that, we just have to ensure the value in the Format cells if<\/strong> drop down field is \"Custom formula is<\/strong>\", and accordingly key in the formula in the immediate next field.\nExample 2: Heatmaps!<\/h3>\nHere's a spreadsheet that holds the scores of fifth-grade students in a school.\n\nExample<\/strong>
![\"Change](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/change4.png\")
\n\nThe class teacher wants to quickly zero in on those students who scored on the higher side. To the naked eye, this is difficult. Color scale within the conditional formatting functionality can help us here. Just like we did in the previous example, we will select all the cells (F2:F31) and navigate to Format > Conditional formatting<\/strong>. Then we select the Color scale<\/strong> tab on the sidebar that pops on the right side of the screen.\n\nWe make sure the fields are as shown in the snapshot below, and click Done<\/strong>. Instantly we see that the conditional formatting utility did change the cell colors based on the cell value in Google Sheets. Higher values are in green, while the lower values are in red.\n\nExample<\/strong> ![\"Change](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/change5.png\")
\n\nCongratulations! We successfully used the conditional formatting to our benefit to aid our eyes with visual cues that make the data identification process a lot easier!\n\nLearn more about Alternated Colors<\/strong> in the following blog post: \"How to create an alternated colors list in Google Sheets?<\/strong><\/a>\"\n\n
\n\nFeatured image designed by ijeab \/ Freepik<\/a><\/em><\/span>","_et_gb_content_width":"","footnotes":""},"categories":[37],"tags":[39,28],"class_list":["post-7561","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-features","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/7561","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=7561"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/7561\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/33309"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=7561"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=7561"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=7561"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}