Conditional Formatting 1

Download

HTML can be useful for transferring content to other online publishing platforms, typically by copy/pasting to the new platform.

<p id="p_PnDt" name="p_PnDt">In Excel, we can use <span class="glossary-link" role="button" data-anchor-name="conditional">conditional</span> formatting to change the format of a cell-based on its value. We’ll use conditional formatting in this chapter to highlight important cells in a list of 25 or so different parts. (See <strong>Figure 21.1</strong>)</p>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_DUmS" name="h3_DUmS">Practice Spreadsheet</h3>
<p id="p_ojnq" name="p_ojnq">Use this <a href="https://drive.google.com/uc?export=download&id=1aBJrkNGJj2e5cXhrJ7K5j8piP48yDfqX" data-shortened-url="https://books.byui.edu/-XQvQj" data-unshortened-url="https://drive.google.com/uc?export=download&id=1aBJrkNGJj2e5cXhrJ7K5j8piP48yDfqX">workbook</a> for the chapter.</p>
</div>
<p id="p_aRgT" name="p_aRgT">In the spreadsheet, we have price, lead time, order quantity, and part classification. Let’s suppose that these parts are being ordered from one of our vendors, and we want to keep our prices low, so we want to flag all of our parts that are over $80. We can customize cell formatting if their value exceeds $80 by creating conditional rules.</p>
<figure class="image text-center"><img class="centered col-12" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.1.png" alt="BUS115_image_21.1.png" id="img_wttz" name="img_wttz">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.1</em></span></figcaption>
</figure>
<h2 id="h2_BkNb" name="h2_BkNb">The Greater Than Rule</h2>
<p id="p_kCEK" name="p_kCEK">First, we’ll set up an input cell as a reference. Select two empty cells. Type <strong>Max price</strong> into the first, and <strong>$80</strong> into the second. Now we’ll use the cell with the price as a reference for formatting other cells with a value greater than its own.</p>
<ol id="ol_YakI" name="ol_YakI">
<li id="li_ruSZ" aria-level="1" name="li_ruSZ">Select the cell(s) to be formatted.</li>
<li id="li_WCAW" aria-level="1" name="li_WCAW">Press the <strong>Conditional Formatting</strong> button on the ribbon toolbar.</li>
<li id="li_Tzim" aria-level="1" name="li_Tzim">Select <strong>Highlight Cells Rules</strong> and then <strong>Greater Than…</strong> to open an options window. (See <strong>Figure 21.2</strong>)
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.2.png" alt="BUS115_image_21.2.png" id="img_DHea" name="img_DHea">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.2</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_qzfK" aria-level="1" name="li_qzfK">Type or select the input cell with the max price value into the reference field.</li>
<li id="li_LSbe" aria-level="1" name="li_LSbe">Select the desired format to be applied.</li>
<ol id="ol_JWIn" style="list-style-type: lower-alpha;" name="ol_JWIn">
<li id="li_ITAv" aria-level="2" name="li_ITAv">For this example, we’ll leave it on the default <strong>Light Red Fill with Dark Red Text</strong>.</li>
</ol>
<li id="li_XUXA" aria-level="1" name="li_XUXA">Press <strong>OK</strong> to finish formatting.</li>
</ol>
<p id="p_rSsN" name="p_rSsN"><em>Note:</em> If the wrong cell or group of cells is formatted, the formatting rule can be modified using the Conditional Formatting Rule Manager.</p>
<ol id="ol_MZfK" name="ol_MZfK">
<li id="li_bEfZ" aria-level="1" name="li_bEfZ">Press the <strong>Conditional Formatting </strong>button on the ribbon toolbar.</li>
<li id="li_Mtpa" aria-level="1" name="li_Mtpa">Select <strong>Manage Rules…</strong> (See <strong>Figure 21.3</strong>)<br>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.3.png" alt="BUS115_image_21.3.png" id="img_XkyH" name="img_XkyH">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.3</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
<li id="li_xQHR" aria-level="1" name="li_xQHR">Use the <strong>Applies to</strong> field to modify the cells to be formatted. (See <strong>Figure 21.4</strong>)</li>
<ol id="ol_nAUf" style="list-style-type: lower-alpha;" name="ol_nAUf">
<li id="li_prpv" aria-level="2" name="li_prpv">Additionally, the rule can be edited by selecting the rule and pressing <strong>Edit Rule…</strong>.</li>
</ol>
</ol>
<figure class="image text-center"><img class="centered col-12" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.4.png" alt="BUS115_image_21.4.png" id="img_auHy" name="img_auHy">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.4</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_pAwA" name="h2_pAwA">The Less Than Rule</h2>
<p id="p_iIvB" name="p_iIvB">We’ll add another rule to format cells below a specific value. We’ll set up another input cell as a reference. Select two empty cells. Type <strong>Min price</strong> (for minimum price) into the first, and <strong>$40</strong> into the second. Then we’ll select the desired cells to modify the formatting if their values are lower than the minimum price.</p>
<ol id="ol_jfMi" name="ol_jfMi">
<li id="li_diwg" aria-level="1" name="li_diwg">Select the cell(s) to be formatted.</li>
<li id="li_EcUk" aria-level="1" name="li_EcUk">Press the <strong>Conditional Formatting</strong> button on the ribbon toolbar.</li>
<li id="li_fPyo" aria-level="1" name="li_fPyo">Select <strong>Highlight Cells Rules</strong> and then <strong>Less Than…</strong> to open an options window. (See <strong>Figure 21.5</strong>)</li>
<li id="li_rmkL" aria-level="1" name="li_rmkL">Type or select the input cell with the minimum value into the reference field.</li>
<li id="li_EkPV" aria-level="1" name="li_EkPV">Select the desired format to be applied.
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.5.png" alt="BUS115_image_21.5.png" id="img_UAWi" name="img_UAWi">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.5</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
</li>
</ol>
<p id="p_aGch" name="p_aGch">If we want to customize the formatting used for the rule instead of the default options, we would select the <strong>Custom Format…</strong> option in the drop-down list. A menu window will open with four tabs: Number, Font, Border, and Fill. For this example, we will choose to apply a green fill and a partial border format.</p>
<ol id="ol_CJiM" name="ol_CJiM">
<li id="li_qGIU" aria-level="1" name="li_qGIU">Select the <strong>Fill</strong> tab.</li>
<li id="li_VAJz" aria-level="1" name="li_VAJz">Select a preset light green.</li>
<li id="li_mKwh" aria-level="1" name="li_mKwh">Select the <strong>Border</strong> tab.</li>
<li id="li_hduq" aria-level="1" name="li_hduq">Select a preset dark green.</li>
<ol id="ol_IyRt" style="list-style-type: lower-alpha;" name="ol_IyRt">
<li id="li_Ihqo" aria-level="2" name="li_Ihqo">The border style may also be modified as desired.</li>
</ol>
<li id="li_aSGj" aria-level="1" name="li_aSGj">Press the left edge button to apply the partial border. (See <strong>Figure 21.6</strong>)</li>
<li id="li_tWmD" aria-level="1" name="li_tWmD">Press <strong>OK</strong> to finish the custom format.</li>
<li id="li_eNYQ" aria-level="1" name="li_eNYQ">Press <strong>OK</strong> again to complete the conditional formatting rule.</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.6.png" alt="BUS115_image_21.6.png" id="img_AxiQ" name="img_AxiQ">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.6</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_QLnU" name="h2_QLnU">The Between Rule</h2>
<p id="p_WBNN" name="p_WBNN">Another rule we can add is to format cells with values <em>between</em> a specific range. For this example, we’ll need to set up two input cells for the lowest and highest values in the range, and we’ll label them <strong>LT Bottom</strong> and <strong>LT Top</strong> respectively. The LT Bottom will be 6, and LT Top will be 12.</p>
<p id="p_CKvi" name="p_CKvi"><em>Note:</em> The LT abbreviation represents “Lead Time.”)</p>
<ol id="ol_fjWD" name="ol_fjWD">
<li id="li_bngq" aria-level="1" name="li_bngq">Select the cell(s) to be formatted.</li>
<li id="li_UNkG" aria-level="1" name="li_UNkG">Press the <strong>Conditional Formatting</strong> button on the ribbon toolbar.</li>
<li id="li_RVGW" aria-level="1" name="li_RVGW">Select <strong>Highlight Cells Rules</strong> and then <strong>Between…</strong> to open an options window. (See <strong>Figure 21.7</strong>)</li>
<li id="li_TXKr" aria-level="1" name="li_TXKr">Type or select the input cell for the lowest value into the first reference field.</li>
<li id="li_xhKj" aria-level="1" name="li_xhKj">Type or select the input cell for the highest value into the second reference field.</li>
<li id="li_EVIo" aria-level="1" name="li_EVIo">Select the desired format to be applied.</li>
<li id="li_Ddwp" aria-level="1" name="li_Ddwp">Press <strong>OK</strong> to finish formatting.</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.7.png" alt="BUS115_image_21.7.png" id="img_nPrX" name="img_nPrX">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.7</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_tvGp" name="h2_tvGp">Conditional Formatting Rules Manager</h2>
<p id="p_LSRa" name="p_LSRa">The Conditional Formatting Rules Manager lists all the rules applied to the selected cell(s). It can also show all rules in the worksheet by selecting the <strong>Show formatting rules for</strong> drop-down list on the top of the window. Any conditional formatting rule can be modified or deleted from the manager.</p>
<ol id="ol_eLJC" name="ol_eLJC">
<li id="li_ZIEk" aria-level="1" name="li_ZIEk">Press the <strong>Conditional Formatting </strong>button on the ribbon toolbar.</li>
<li id="li_nAyu" aria-level="1" name="li_nAyu">Select <strong>Manage Rules…</strong>.</li>
<li id="li_YXXh" aria-level="1" name="li_YXXh">Press the <strong>Edit Rule…</strong> button to open an options window. (See <strong>Figure 21.8</strong>)</li>
<ol id="ol_qJJD" style="list-style-type: lower-alpha;" name="ol_qJJD">
<li id="li_ZAtn" aria-level="2" name="li_ZAtn">The new window will include four tabs: <strong>Number</strong>, <strong>Font</strong>, <strong>Border</strong>, and <strong>Fill</strong>. We can customize the format with various options from these tabs. Multiple format options can be selected and combined as well.</li>
</ol>
</ol>
<p id="p_RWtE" name="p_RWtE">If more than one conditional formatting rule can apply to the same cell(s), the manager displays the rules that are applied in order from top to bottom; rules listed at the bottom are applied last and will override the previous rule formatting. To prevent overriding previous formatting rules, the <strong>Stop if True</strong> checkbox on the right for the rule to be last.</p>
<figure class="image text-center"><img class="none" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.8.png" alt="BUS115_image_21.8.png" width="665" height="468" id="img_QSNn" name="img_QSNn">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.8</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_iUBI" name="p_iUBI"></p>
<h2 id="h2_gKko" name="h2_gKko">Data Bars and Icon Sets</h2>
<p id="p_Kyuf" name="p_Kyuf">The Data Bars and Icon Sets are also available to format cells based on their values. Data Bars may be used to view in-cell charts for quick visualization of data comparison. (See <strong>Figure 21.9</strong>)</p>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.9.png" alt="BUS115_image_21.9.png" id="img_AcEx" name="img_AcEx">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.9</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_oJZp" name="p_oJZp"></p>
<p id="p_Mdyc" name="p_Mdyc">Icon Sets can be useful to represent values at specific levels. For example, if we format an Order Quantity column to see which items have sufficient quantities, items that are reaching low inventory, and items that fall under the desired amount, we can select a basic icon set with green, yellow, and red icons. (See <strong>Figure 21.10</strong>)</p>
<p id="p_RWRr" name="p_RWRr"><em>Note:</em><em> </em>The rule may need to be modified to appropriately represent the desired effect.</p>
<figure class="image text-center"><img class="none" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_21.10.png" alt="BUS115_image_21.10.png" width="526" height="545" id="img_DqVt" name="img_DqVt">
<figcaption class="text-center"><span style="font-size: 10pt;"><em>Figure 21.10</em></span></figcaption>
</figure>
<div class="clearfix"> </div>
<p id="p_SsWs" name="p_SsWs"></p>
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_RkbA" name="h3_RkbA">Supplemental Resource</h3>
<p id="p_RgLf" name="p_RgLf"><iframe id="kaltura_player_1636661561" src="https://cdnapisec.kaltura.com/p/1157612/sp/115761200/embedIframeJs/uiconf_id/33020032/partner_id/1157612?iframeembed=true&playerId=kaltura_player_1636661561&entry_id=1_rsvan0ex" width="480" height="270" frameborder="0" allowfullscreen="allowfullscreen"></iframe></p>
</div>