Microsoft Office Tip: Conditional Formatting in Excel

Although Windows 8 and Office 2013 recently hit the marketplace, many of us are still using Windows 7 and Office 2010. But be honest, even with Office 2010, don’t you still feel like there’s a ton of great features that are being kept secret from you? Well you’re not alone.

This is the first in a series of blogs to unmask these hidden gems. Many of the tools that I’m going to review are features that are easy to use and right at your fingertips. During this series we will focus on three of the most widely used applications in Microsoft Office 2010: Excel, Outlook and Word.

We’ll begin with Microsoft Excel.

Conditional formatting is a powerful feature and is a useful way to easily identify cells with a value that meets some benchmark. However, we often want to create conditional formatting rules that go beyond matching a cell’s contents to a single value or highlighting a single cell reference.

Excel’s conditional formatting is located in the Home tab of the menu ribbon. One of the options within the conditional formatting style allows us to create highlighted rules for cells that contain specific types of data. This feature let you control how a cell is formatted – such as changing colors – depending on the data the cell contains. If the cell data meets the conditions you set, then the formatting is applied to the cell. This is data that you might want to have standout for quick analysis or reporting purposes.

In the following example we’ll take a look at how to flag duplicates using conditional formatting.

Imagine having a list of contacts that you want to invite to a special event. Contact lists are notorious for having duplicate records.

Here are the three simple steps needed to quickly find your duplicated contacts:

1. Select your list or the cells you want to format.
2. On the ribbon under the “Home” tab > “Styles” > “Conditional Formatting” select “Highlight Cells Rules” then pick “Duplicate Values” from the list.
3. You should already see your dups highlighted for you. Simply click OK to keep the default formatting or select an alternative formatting style from the drop-down menu.

I hope you found this little gem useful and easy to do. Remember there’s many more to come.