IQ Accounting Solutions

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.

Let’s look at a few examples. I’ll be using Excel 2010. The exact steps may vary slightly in other versions.

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 button. From the options that drop down, choose Highlight Cell Rules, and then Greater Than. Under Format cells that are GREATER THAN: enter 10000. Then choose one of the preset formatting options from the list or choose Custom Format 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.

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.

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

If you selected the entire column, enter =B1 at Format cells that are LESS THAN:. Or if you selected just the cell that have amounts in them, enter =B2. 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.

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  want to evaluate, so you can’t use the preset rules. But that doesn’t mean you can’t do it.

Start by selecting the column A or just the list of names, and go back to Conditional Formatting button, and Highlight Cell Rules, This time choose More Rules from the bottom of the menu. In the window that opens, select Use a formula to determine which cells to format. At Format values where this formula is true: enter your formula. In this case it would be =B1>C1 if you selected the entire column, or =B2>C2 if you selected just the cells in use. Click the Format button below that to choose your formatting options. Click OK 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.

To remove conditional formatting, selected the desired cells, click the Conditional Formatting button, choose Clear Rules, and then Clear Rules from Selected Cells.

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.