{"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-02-06T08:50:59","modified_gmt":"2026-02-06T07:50:59","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":"<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>\t\t\n\t\t<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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"464\" height=\"354\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple1.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 464px) 100vw, 464px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t<h3>1\/ Conditional formatting using the Excel toolbar <\/h3>\t\t\n\t\t<ul><li>First, select the range of amounts, then click on Conditional formatting in the Home tab.<\/li><\/ul>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"284\" height=\"379\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple2.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 284px) 100vw, 284px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<ul><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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"422\" height=\"372\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple3-1.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 422px) 100vw, 422px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><div class=\"wp-block-button \"><a class=\"wp-block-button__link wp-element-button \" href=\"\/en\/courses\/data-ai\/\">Discover our Excel training<\/a><\/div><\/div>\n\n\t\t<p>Example 1<\/p><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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"147\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple4.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"526\" height=\"446\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple5.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 526px) 100vw, 526px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p><strong>Example 2<\/strong><\/p><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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"564\" height=\"268\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple6.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 564px) 100vw, 564px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<ul><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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"584\" height=\"454\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple7.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 584px) 100vw, 584px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"572\" height=\"200\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple8.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 572px) 100vw, 572px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p>This is what you get:<\/p>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"379\" height=\"446\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple9.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 379px) 100vw, 379px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<ul><li>You can also select Data Bars, Color Shades or Icon Sets.<\/li><\/ul><p>Example with icon set<\/p><p>Choose 3, 4 or 5 icons, depending on the number of slices you require.<\/p>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"233\" height=\"205\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple10.jpg\" alt=\"\" loading=\"lazy\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"263\" height=\"223\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple11.jpg\" alt=\"\" loading=\"lazy\">\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t<ul><li>To modify these rules as you wish, click on Home \/ Conditional formatting \/ Manage rules.<\/li><\/ul>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"216\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple12.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p>And double-clicking on the Icon set rule produces :<\/p>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"517\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple13.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p>(To be modified as desired)<\/p><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><p>Please note: when you copy\/paste cells, the conditional formatting is also copied.<\/p><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><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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"454\" height=\"348\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple14.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 454px) 100vw, 454px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"69\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple15.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p>You can also change the priority of rules using the arrow keys.<\/p>\t\t\n\t\t\t<h3>2\/ Formatting an entire line<\/h3>\t\t\n\t\t<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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"266\" height=\"296\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple16.jpg\" alt=\"\" loading=\"lazy\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"263\" height=\"317\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple17.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 263px) 100vw, 263px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"347\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple18.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"347\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple19.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><div class=\"wp-block-button \"><a class=\"wp-block-button__link wp-element-button \" href=\"\/en\/courses\/data-ai\/\">Discover our courses<\/a><\/div><\/div>\n\n\t\t\t<h3>3\/ Inserting functions into conditional formatting<\/h3>\t\t\n\t\t<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><p>Case study 1<\/p><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><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><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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"109\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple20.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"347\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple21.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p>Case study 2<\/p><p>Highlighting weekends and the current date in a schedule<\/p><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><p>Click again on New Ruler or in Manage Rulers to see all the rulers in your selection.<\/p>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"347\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple22.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p>Here are the two formulas to write:<\/p><p>=F$10=TODAY()<\/p><p>=OR(JOURSEM(F$10)=7;JOURSEM(F$10)=1)<\/p><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>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"216\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple23.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t<p>You can return to the formulas by clicking on Manage rules and double-clicking on the rule you wish to modify.<\/p><p>The result is as follows:<\/p>\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t<figure>\n\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" width=\"602\" height=\"121\" src=\"https:\/\/liora.io\/app\/uploads\/sites\/9\/2023\/11\/excel-mise-en-forme-conditionnelle-exemple24.jpg\" alt=\"\" loading=\"lazy\" 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\" sizes=\"(max-width: 602px) 100vw, 602px\">\t\t\t\t\t\t\t\t\t\t\t<figcaption><\/figcaption>\n\t\t\t\t\t\t\t\t\t\t<\/figure>\n\t\t\t\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex is-content-justification-center\"><div class=\"wp-block-button \"><a class=\"wp-block-button__link wp-element-button \" href=\"\/en\/courses\/data-ai\/\">Learn more about Excel functions<\/a><\/div><\/div>\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":1,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172839\/revisions"}],"predecessor-version":[{"id":206277,"href":"https:\/\/liora.io\/en\/wp-json\/wp\/v2\/posts\/172839\/revisions\/206277"}],"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}]}}