{"id":172839,"date":"2023-11-04T19:12:51","date_gmt":"2023-11-04T18:12:51","guid":{"rendered":"https:\/\/liora.io\/en\/?p=172839"},"modified":"2026-08-08T12:55:18","modified_gmt":"2026-08-08T11:55:18","slug":"efficient-excel-data-formatting-based-on-cell-values","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/efficient-excel-data-formatting-based-on-cell-values","title":{"rendered":"Efficient Excel Data Formatting Based on Cell Values"},"content":{"rendered":"\n<p><strong>Conditional formatting can be used to highlight certain values or make cells easy to identify. It modifies the appearance of a range of cells according to a condition or several criteria. \nIn this article, you&#8217;ll learn the tricks of conditional formatting and understand how to highlight the data you wish to advance in your analysis.  <\/strong><\/p>\n\n\n<p>We&#8217;ll be using the table below throughout the article to illustrate the various <a href=\"https:\/\/liora.io\/en\/how-do-i-insert-columns-and-rows-in-excel\">features of excel conditional formatting.<\/a> The table shows the price of different plant varieties according to the date on which they are sold.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:464px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"354\" loading=\"lazy\" sizes=\"(max-width: 464px) 100vw, 464px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple1.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple1.jpg 464w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple1-300x229.jpg 300w\" style=\"width:464px;max-width:100%;height:auto\" width=\"464\"\/><\/figure>\n\n\n<h2 class=\"wp-block-heading\" id=\"1-conditional-formatting-using-the-excel-toolbar\">1\/ Conditional formatting using the Excel toolbar <\/h2>\n\n\n<ul class=\"wp-block-list\"><li>First, select the range of amounts, then click on Conditional formatting in the Home tab.<\/li><\/ul>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:284px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"379\" loading=\"lazy\" sizes=\"(max-width: 284px) 100vw, 284px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple2.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple2.jpg 284w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple2-225x300.jpg 225w\" style=\"width:284px;max-width:100%;height:auto\" width=\"284\"\/><\/figure>\n\n\n<ul class=\"wp-block-list\"><li>If you choose Cell highlighting rules, you have several options. You can highlight amounts that are greater than, less than, between or equal to a certain amount&#8230; and even create your own rules using the &#8220;Other rules&#8230;&#8221; menu.<\/li><\/ul>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:422px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"372\" loading=\"lazy\" sizes=\"(max-width: 422px) 100vw, 422px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple3-1.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple3-1.jpg 422w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple3-1-300x264.jpg 300w\" style=\"width:422px;max-width:100%;height:auto\" width=\"422\"\/><\/figure>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center wp-container-core-buttons-is-layout-5ee10de4\" style=\"margin-top:32px;margin-bottom:32px\"><div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"https:\/\/liora.io\/en\/courses\/data-ai\/data-analyst\">Discover our Excel training<\/a><\/div><\/div>\n\n\n<p>Example 1<\/p>\n\n\n<p>When you select &#8220;less than&#8221;, you can enter a maximum amount (e.g. 10) below which the values will be highlighted, as shown below: all plants priced under \u20ac10 will be highlighted using the custom format you&#8217;ve chosen. Here, cells meeting this criterion are highlighted in blue with red characters.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:560px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"147\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple4.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple4.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple4-300x73.jpg 300w\" style=\"width:560px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:526px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"446\" loading=\"lazy\" sizes=\"(max-width: 526px) 100vw, 526px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple5.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple5.jpg 526w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple5-300x254.jpg 300w\" style=\"width:526px;max-width:100%;height:auto\" width=\"526\"\/><\/figure>\n\n\n<p><strong>Example 2<\/strong><\/p>\n\n\n<p>You can also highlight all cells containing a certain text, or dates occurring yesterday, today, tomorrow, in the last 7 days, last week, this week, next week, last month, this month or next month.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:564px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"268\" loading=\"lazy\" sizes=\"(max-width: 564px) 100vw, 564px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple6.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple6.jpg 564w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple6-300x143.jpg 300w\" style=\"width:564px;max-width:100%;height:auto\" width=\"564\"\/><\/figure>\n\n\n<ul class=\"wp-block-list\"><li>You can also choose Set high\/low range values. If you want to highlight the 5 highest values in red, select &#8220;10 highest values&#8221; and replace 10 with 5 in the dialog box. You can then change the format in which you want your values to appear (e.g. Light red fill with dark red text).<\/li><\/ul>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:584px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"454\" loading=\"lazy\" sizes=\"(max-width: 584px) 100vw, 584px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple7.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple7.jpg 584w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple7-300x233.jpg 300w\" style=\"width:584px;max-width:100%;height:auto\" width=\"584\"\/><\/figure>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:572px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"200\" loading=\"lazy\" sizes=\"(max-width: 572px) 100vw, 572px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple8.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple8.jpg 572w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple8-300x105.jpg 300w\" style=\"width:572px;max-width:100%;height:auto\" width=\"572\"\/><\/figure>\n\n\n<p>This is what you get:<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:379px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"446\" loading=\"lazy\" sizes=\"(max-width: 379px) 100vw, 379px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple9.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple9.jpg 379w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple9-255x300.jpg 255w\" style=\"width:379px;max-width:100%;height:auto\" width=\"379\"\/><\/figure>\n\n\n<ul class=\"wp-block-list\"><li>You can also select Data Bars, Color Shades or Icon Sets.<\/li><\/ul>\n\n\n<p>Example with icon set<\/p>\n\n\n<p>Choose 3, 4 or 5 icons, depending on the number of slices you require.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:233px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"205\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple10.jpg\" style=\"width:233px;max-width:100%;height:auto\" width=\"233\"\/><\/figure>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:263px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"223\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple11.jpg\" style=\"width:263px;max-width:100%;height:auto\" width=\"263\"\/><\/figure>\n\n\n<ul class=\"wp-block-list\"><li>To modify these rules as you wish, click on Home \/ Conditional formatting \/ Manage rules.<\/li><\/ul>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:602px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"216\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple12.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple12.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple12-300x108.jpg 300w\" style=\"width:602px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<p>And double-clicking on the Icon set rule produces :<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:602px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"517\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple13.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple13.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple13-300x258.jpg 300w\" style=\"width:602px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<p>(To be modified as desired)<\/p>\n\n\n<p>If you have defined more than one rule for a cell range, and you have ticked Interrupt if true, the subsequent rules will no longer be applied if the first is true.<\/p>\n\n\n<p>Please note: when you copy\/paste cells, the conditional formatting is also copied.<\/p>\n\n\n<p>Don&#8217;t hesitate to go to Manage rules in Home \/ Conditional formatting to create, modify or delete a rule. Look carefully at the cell ranges to which these rules apply.<\/p>\n\n\n<p>As you need to select the cell range before modifying the rules, it would be a good idea to name this range in the Formulas \/ Define name ribbon, so that you can find your way around if your <a href=\"https:\/\/liora.io\/en\/excel-to-power-bi-how-to-transform-a-pivot-table-in-excel-into-a-dataset-that-can-be-used-by-power-bi\">Excel sheet contains a lot of data.<\/a><\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:454px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"348\" loading=\"lazy\" sizes=\"(max-width: 454px) 100vw, 454px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple14.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple14.jpg 454w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple14-300x230.jpg 300w\" style=\"width:454px;max-width:100%;height:auto\" width=\"454\"\/><\/figure>\n\n\n<p>Then simply select the name from the drop-down list on the left of the formula bar, before modifying the conditional formatting rules.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:440px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 1\/ Conditional formatting using the Excel toolbar\" decoding=\"async\" height=\"69\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple15.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple15.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple15-300x34.jpg 300w\" style=\"width:440px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<p>You can also change the priority of rules using the arrow keys.<\/p>\n\n\n<h2 class=\"wp-block-heading\" id=\"2-formatting-an-entire-line\">2\/ Formatting an entire line<\/h2>\n\n\n<p>Even more interesting! Excel also lets you color an entire row, not just a single column, according to the value of a column:<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:266px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 2\/ Formatting an entire line\" decoding=\"async\" height=\"296\" loading=\"lazy\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple16.jpg\" style=\"width:266px;max-width:100%;height:auto\" width=\"266\"\/><\/figure>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:263px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 2\/ Formatting an entire line\" decoding=\"async\" height=\"317\" loading=\"lazy\" sizes=\"(max-width: 263px) 100vw, 263px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple17.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple17.jpg 263w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple17-249x300.jpg 249w\" style=\"width:263px;max-width:100%;height:auto\" width=\"263\"\/><\/figure>\n\n\n<p>To obtain the table on the left, select the range of cells corresponding to the Plant column, then click on the Home \/ Conditional formatting tab and select the type of rule: Apply formatting only to cells containing the plant.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:602px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 2\/ Formatting an entire line\" decoding=\"async\" height=\"347\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple18.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple18.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple18-300x173.jpg 300w\" style=\"width:602px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<p>To obtain the table on the right, select the entire table and use a formula with a mixed reference: freeze the column but not the row, i.e. put a dollar in front of the column and nothing in front of the row, or press the F4 or Fn F4 key 3 times:<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:602px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 2\/ Formatting an entire line\" decoding=\"async\" height=\"347\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple19.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple19.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple19-300x173.jpg 300w\" style=\"width:602px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center wp-container-core-buttons-is-layout-5ee10de4\" style=\"margin-top:32px;margin-bottom:32px\"><div class=\"wp-block-button\"><a class=\"wp-block-button__link wp-element-button\" href=\"https:\/\/liora.io\/en\/courses\/data-ai\/data-analyst\">Discover our courses<\/a><\/div><\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"3-inserting-functions-into-conditional-formatting\">3\/ Inserting functions into conditional formatting<\/h2>\n\n\n<p>You can also insert all Excel&#8217;s functions into conditional formatting if you choose to use a formula to determine for which cells the format will be applied.<\/p>\n\n\n<p>Case study 1<\/p>\n\n\n<p>Show the start and end dates of plant sales on a schedule as above. Freeze the column for the start and end dates and the row for the month dates with mixed references and use the AND function.<\/p>\n\n\n<p>Select the grid cell range in the table and select Home \/ Conditional Formatting \/ Use a formula to determine for which cells the format will be applied:<\/p>\n\n\n<p>Write the formula: =ET(F$3&gt;=$A4;F$3&lt;=$B4) then click on the Format&#8230; button and the Fill tab.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:560px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 3\/ Inserting functions into conditional formatting\" decoding=\"async\" height=\"109\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple20.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple20.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple20-300x54.jpg 300w\" style=\"width:560px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:602px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 3\/ Inserting functions into conditional formatting\" decoding=\"async\" height=\"347\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple21.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple21.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple21-300x173.jpg 300w\" style=\"width:602px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<p>Case study 2<\/p>\n\n\n<p>Highlighting weekends and the current date in a schedule<\/p>\n\n\n<p>Let&#8217;s create two rulers by clicking on New Ruler then Use formula to determine for which cells the format will be applied and in the Apply formatting to values for which this formula is true, enter your formula. You can&#8217;t automatically insert the functions, you need to know them.<\/p>\n\n\n<p>Click again on New Ruler or in Manage Rulers to see all the rulers in your selection.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:602px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 3\/ Inserting functions into conditional formatting\" decoding=\"async\" height=\"347\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple22.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple22.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple22-300x173.jpg 300w\" style=\"width:602px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<p>Here are the two formulas to write:<\/p>\n\n\n<p>=F$10=TODAY()<\/p>\n\n\n<p>=OR(JOURSEM(F$10)=7;JOURSEM(F$10)=1)<\/p>\n\n\n<p>Set a mixed reference by freezing the line containing all the dates of the month and use the Joursem function, which returns a number from 1 to 7 from Sunday to Saturday.<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:602px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 3\/ Inserting functions into conditional formatting\" decoding=\"async\" height=\"216\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple23.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple23.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple23-300x108.jpg 300w\" style=\"width:602px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n<p>You can return to the formulas by clicking on Manage rules and double-clicking on the rule you wish to modify.<\/p>\n\n\n<p>The result is as follows:<\/p>\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\" style=\"width:560px;max-width:100%;margin-top:32px;margin-right:auto;margin-bottom:32px;margin-left:auto\"><img alt=\"Illustration for 3\/ Inserting functions into conditional formatting\" decoding=\"async\" height=\"121\" loading=\"lazy\" sizes=\"(max-width: 602px) 100vw, 602px\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple24.jpg\" srcset=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple24.jpg 602w, https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple24-300x60.jpg 300w\" style=\"width:560px;max-width:100%;height:auto\" width=\"602\"\/><\/figure>\n\n\n","protected":false},"excerpt":{"rendered":"<p>Conditional formatting can be used to highlight certain values or make cells easy to identify. It modifies the appearance of a range of cells according to a condition or several criteria. In this article, you&#8217;ll learn the tricks of conditional formatting and understand how to highlight the data you wish to advance in your analysis. [&hellip;]<\/p>\n","protected":false},"author":76,"featured_media":172840,"comment_status":"open","ping_status":"open","sticky":false,"template":"elementor_theme","format":"standard","meta":{"_acf_changed":false,"editor_notices":[],"footnotes":""},"categories":[2433],"class_list":["post-172839","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-ai"],"acf":[],"_links":{"self":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172839","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/users\/76"}],"replies":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/comments?post=172839"}],"version-history":[{"count":3,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172839\/revisions"}],"predecessor-version":[{"id":209987,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172839\/revisions\/209987"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media\/172840"}],"wp:attachment":[{"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/media?parent=172839"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/categories?post=172839"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}