Practical Uses of Microsoft Excel’s Conditional Formatting

At some point, you may have found yourself asking, “What is conditional formatting?” Conditional formatting means that you can change or add visual cues based on a specified set of rules. It provides a way to improve the appearance and increase the functionality of your spreadsheets as you’re working based on the conditions you have created.

So let’s take a look at a few examples of conditional formatting available within Microsoft Excel.

Rules with Formulas

One example of conditional formatting might include wanting to keep track of customers’ birthdays so that you can send them a card. You could build a conditional formatting rule in your customer spreadsheet to shade the customer’s birthday cell when the date is ten days from the current date.

Then when you send out their card you can place a Y in the birthday card sent column and that cell will turn green letting you know at a glance that you have sent their card. The condition being that you have placed Y in the cell which cues the box to turn green automatically. It does some of your work for you automatically and lets you know at a glance that you have completed a task.

Another example includes setting a rule to let you know when a cell contains a value greater than a preset amount. When you enter a number greater than the amount set in your formula, you can set a condition that will cause the cell to change colors automatically. You could also set a condition to change the font weight or color to denote the amount is negative or has fallen below a preset threshold.

Comparisons

You can also build rules or conditions to compare values. For example, you could set a condition to highlight cells in an inventory spreadsheet if the on-hand inventory numbers fall below a certain threshold. This may help you compare the current inventory column to the reorder level column allowing you to easily see if it’s time to reorder a product.

Determining if a condition is true or false is another way of comparing items. You might want to compare a start time to an end time to learn if the total time elapsed is less than or greater than a preset number. An example of this comparison would be if someone worked more or less than 8 hours. You could insert a small icon into the cell that contains the elapsed time calculation to let you know instantly if someone’s time is over 8 hours.

You can also find duplicate values in a column, determine the minimum and maximum duplicates within cells and even compare lists or columns. The formula would work by comparing every value in one column to its counterpart in another column. If the two values are different, you can set a condition to highlight the entire row so that it stands; denoting that one of these things is not quite like the other.

Formatting

Your level of creativity is the only limitation to the things you can do to build visual cues in your spreadsheet. You can:

• change color cells
• highlight entire rows based on cell values in the row
• insert icons
• change fonts
• format alternating row bands
• and more!

You can also set rules to reorder the columns from the greatest to the lowest based on a cell value and change their colors, fonts, and maybe add formatting to display them with dollar signs and decimal places. Thanks to these features, you can creatively present your spreadsheet and make it informative at the same time.

Excel could possibly be the most influential business tool ever invented. It’s very intuitive and easy to use, but most people only scratch the surface of Excel’s advanced treasure trove of functionality. If you want to increase your Excel expertise exponentially, there is no better way than to take a class from a professional training provider such as ONLC. Contact us today to see how you can be on your way to becoming an Excel master.