{"id":951,"date":"2015-11-19T16:03:02","date_gmt":"2015-11-19T22:03:02","guid":{"rendered":"http:\/\/www.iqaccountingsolutions.com\/blog\/?p=951"},"modified":"2015-11-19T16:03:02","modified_gmt":"2015-11-19T22:03:02","slug":"conditional-formatting-in-excel","status":"publish","type":"post","link":"https:\/\/www.iqaccountingsolutions.com\/blog\/conditional-formatting-in-excel\/","title":{"rendered":"Conditional Formatting in Excel"},"content":{"rendered":"

Everyone knows how to apply formatting such as bold, italics, or color to a cell on an Excel worksheet. But did you know you can establish rules so that if certain conditions are met, the cell will automatically receive selected formatting? You can, and it’s called conditional formatting.<\/p>\n

Let’s look at a few examples. I’ll be using Excel 2010. The exact steps may vary slightly in other versions.<\/p>\n\n\n\n\n\n\n\n\n\n
<\/td>\n\n

A<\/p>\n<\/td>\n

\n

B<\/p>\n<\/td>\n

\n

C<\/p>\n<\/td>\n<\/tr>\n

\n

1<\/p>\n<\/td>\n

CUSTOMER NAME<\/u><\/td>\n\n

BALANCE<\/u><\/p>\n<\/td>\n

\n

CR LIMIT<\/u><\/p>\n<\/td>\n<\/tr>\n

\n

2<\/p>\n<\/td>\n

Aldred Builders, Inc.<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11,612<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 15000<\/td>\n<\/tr>\n
\n

3<\/p>\n<\/td>\n

Archer Scapes and Ponds<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 8,313<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7000<\/td>\n<\/tr>\n
\n

4<\/p>\n<\/td>\n

Cannon Healthcare Center<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12,429<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10000<\/td>\n<\/tr>\n
\n

5<\/p>\n<\/td>\n

Chapple Law Offices<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9,065<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10000<\/td>\n<\/tr>\n
\n

6<\/p>\n<\/td>\n

Everly Property Management<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12,693<\/td>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 13000<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

To make all balances over $10,000 stand out, select the balances in colun B, or just select the entire column. On the Home tab of the ribbon, click the Conditional Formatting<\/em> button. From the options that drop down, choose Highlight Cell Rules<\/em>, and then Greater Than<\/em>. Under Format cells that are GREATER THAN: <\/em>enter 10000. Then choose one of the preset formatting options from the list or choose Custom Format<\/em> to specify your own formatting options. Click OK, and cells B2, B4, and B6 will all have the formatting you chose since they are over 10,000. If you change the number in any cell, its formatting will automatically be updated according to the rule you set.<\/p>\n

In that example, all of the amounts were compared to the same number. Now let’s compare each cell to another cell. To make it easy to spot anyone who has exceeded their credit limit, we’ll apply conditional formatting to column C to highlight anyone whose credit limit is lower than their balance.<\/p>\n

Again, start by selected either the entire column C, or the specific cells you want to format. Click the Conditional Formatting<\/em> button, choose Highlight Cell Rules<\/em>, and Less Than<\/em>.<\/p>\n

If you selected the entire column, enter =B1<\/em> at Format cells that are LESS THAN:<\/em>. Or if you selected just the cell that have amounts in them, enter =B2<\/em>. You could click on the cell you want instead of typing it. But if you do Excel will enter it as =$B$1 and you’ll have to remove the dollar signs. If you leave them there, every cell will be compared to B1 instead of to its corresponding cell in column B. Choose the formatting that you want and click OK. Now C3 and C4 should show the conditional format.<\/p>\n

What if you wanted to apply a conditional format to the customer name when they exceed their credit limit. In that case the cell you want to format isn’t the cell you\u00a0 want to evaluate, so you can’t use the preset rules. But that doesn’t mean you can’t do it.<\/p>\n

Start by selecting the column A or just the list of names, and go back to Conditional Formatting<\/em> button, and Highlight Cell Rules<\/em>, This time choose More Rules<\/em> from the bottom of the menu. In the window that opens, select Use a formula to determine which cells to format<\/em>. At Format values where this formula is true:<\/em> enter your formula. In this case it would be =B1><\/em>C1 if you selected the entire column, or =B2>C2 if you selected just the cells in use. Click the Format<\/em> button below that to choose your formatting options. Click OK<\/em> when you’re done. The formatting will be applied to any cell in column A when the balance on that row (column B) is greater than the credit limit on that row (column C). In this case that would be Archer and Cannon.<\/p>\n

To remove conditional formatting, selected the desired cells, click the Conditional Formatting<\/em> button, choose Clear Rules<\/em>, and then Clear Rules from Selected Cells<\/em>.<\/p>\n

So take a few minutes and explore the options that are available with conditional formatting. You can easily format cells based on numbers, dates, text, or duplicate content, as well as highlighting high and low numbers in a list or top\/bottom percent of list entries. And the Data Bars option lets you build a bar chart into the sames cells as your data.<\/p>\n