{"id":89,"date":"2009-11-21T19:00:12","date_gmt":"2009-11-22T01:00:12","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=89"},"modified":"2012-09-24T14:08:49","modified_gmt":"2012-09-24T19:08:49","slug":"excel-conditional-formatting","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/excel-conditional-formatting\/","title":{"rendered":"EXCEL &#8211; Conditional Formatting"},"content":{"rendered":"<p>Conditional formatting in Excel allows you to change the color, border, and other properties of a cell based on rules that you specify.\u00a0 All you have to do is select the cell or range of cells that you want, then open the <em>Format <\/em>menu and choose <em>Conditional Formatting<\/em>.\u00a0 (In Excel 2007, click the <em>Conditional Formatting<\/em> button on the Home ribbon, then choose <em>Highlight Cells Rules<\/em>.)\u00a0 You can enter your criteria, and then click the <em>Format<\/em> button to tell Excel how you want cells formatted when they meet your conditions.<\/p>\n<p>As a simple example, let&#8217;s say you have a spreadsheet with customer names in column A and their current balance in column B.\u00a0 If you want balances over $10,000 to stand out:<\/p>\n<ul>\n<li>Select column B<\/li>\n<li>Go to <em>Format &gt; Conditional Formatting<\/em>. \u00a0<\/li>\n<li>Leave the first option set at &#8220;Cell Value Is&#8221;.<\/li>\n<li>Change the second option to &#8220;greater than or equal to&#8221;.<\/li>\n<li>Enter 10000 in the third box.<\/li>\n<li>Click the <em>Format <\/em>button and make whatever formatting changes you want &#8211; bold, italic, or color, add a border, or set a background color (cell shading).<\/li>\n<li>Click <em>OK <\/em>to save your changes and the conditional formatting will be applied immediately to any cell with a balance of 10,000 or more.<\/li>\n<\/ul>\n<p>Now let&#8217;s say that column C holds each customer&#8217;s credit limit.\u00a0 And you want to know who is over their limit.\u00a0 This time you would select just cell B2 (the balance for the first customer in the list) instead of the entire column.\u00a0<\/p>\n<ul>\n<li>Go back to Format &gt; Conditional Formatting.<\/li>\n<li>Leave the first option set at &#8220;Cell Value Is&#8221;.<\/li>\n<li>Change the second option to &#8220;greater than&#8221;.<\/li>\n<li>Enter &#8220;=C2&#8221; (without quotes) in the third box.\u00a0 You could click on cell C2 with your mouse, but you would need to remove the $ that Excel automatically adds.<\/li>\n<li>Click the <em>Format <\/em>button to make the formatting changes that you want.<\/li>\n<li>Click OK to save your changes.<\/li>\n<\/ul>\n<p>Since you don&#8217;t want to have to repeat that process for every line, today&#8217;s tip within a tip is the format painter.\u00a0 With cell B2 still selected, click on the paintbrush button on your toolbar.\u00a0 Now use your mouse to select the cell or range of cells that you want to have B2&#8217;s formatting copied to.\u00a0 Excel will automatically adjust which cell you use for comparison as it applies the conditional formatting to the new cells.\u00a0 That is, the conditional formatting for cell B3 will automatically be adjusted to &#8220;greater than&#8221; C3<\/p>\n<p>Feel free to explore the various options in Conditional Formatting.\u00a0 If you don&#8217;t like the results, the <em>Delete <\/em>button at the bottom of the Conditional Formatting window will delete the formatting but leave the cell contents in place.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Conditional formatting in Excel saves you time by making important information stand out.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5],"tags":[],"_links":{"self":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/89"}],"collection":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/comments?post=89"}],"version-history":[{"count":4,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/89\/revisions"}],"predecessor-version":[{"id":92,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/posts\/89\/revisions\/92"}],"wp:attachment":[{"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/media?parent=89"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/categories?post=89"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iqaccountingsolutions.com\/blog\/wp-json\/wp\/v2\/tags?post=89"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}