Exploiting Advanced Conditional Formatting Concepts – Part I

Posted: May 2, 2013 by MaxFraudy in Excel
Tags: , ,

Conditional Formatting is an extremely powerful feature in Excel 2007 and above that can be used not only to beautify spreadsheets but also to highlight things based on some pre-defined rules. Excel provides a lot of built-in rules that mostly work on cell values and are easy to apply. What I’ll be covering in this series is less common applications of conditional formatting. Since it’s a very vast topic (like custom charts), it’s going to be another series. 

Today I’m going to cover some examples which use formulas to format the ranges in the worksheet based on the values stored in them. You can download the file that I have used here.

Example 1

Consider a case where you have sales data in a spreadsheet. You have regionwise Targets and Sales against for each product.

Regionwise Sales Data

Regionwise Sales Data

It can be time-consuming to go through each and every line to see where targets are not being met. Why don’t we just use conditional formatting to highlight the same? This can be done as below:

1. Go to Home tab -> Styles section -> Conditional Formatting -> Manage Rules -> New Rule -> Use a formula to determine which cells to format

2. In the formula bar that appears, write the following formula:

=$C1<$D1
3. Click the format tab and select how you want the highlighted values to lookNew Rule
4. Select the Applies to range
5. Click OK

After Applying Conditional Format

After Applying Conditional Format

Example 2

Suppose you have a database of names and you want to look for a name in the whole list.

Names Database

Names Database

You can do so by using one cell on the sheet as a ‘Search’ option. Write the name that you want to search for (it should be an exact match with the name in the list. For partial matches, we’ll share another post).
Again follow similar steps as above until you arrive at the formula bar. Use this formula:
=$B3 = $B$1

Again, choose how you want the row to highlight.

After Applying Conditional Formatting

After Applying Conditional Formatting

To understand how the ‘$’ sign works in cell references, read Absolute and Relative cell references here.

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s