Microsoft_Excel_2013_logo.svg

Automatically Set Conditional Formatting Based on Another Cell

The ability to automatically adjust conditional formatting based on another cell is a valuable skill to learn because it will allow you to take advantage of the full power of conditional formatting. So, if you have a situation where you want to highlight an entire row (or column) of data based on the value of a cell (or cells) within that row, you can use conditional formatting to accomplish this. This makes it easy to quickly identify rows that contain blank cells or whatever value you specify. Once you know how to adjust the conditional formatting based on another cell, you can make your formatting more dynamic, more flexible, and more powerful.

Automatically Set Conditional Formatting Based on Another Cell

To start, simply highlight the rows you would like to apply the conditional formatting to.

Conditional Formatting Based on Another Cell Step 1

Then go into the Home Tab, select “Conditional Formatting”, and then select “New Rule”. You’ll get a screen that looks like this:

Conditional Formatting Based on Another Cell Step 2

In the box enter the following forumla:
=$D2="Yes"

Note that you need to include the dollar sign before the cell reference. This ensures that as Excel evaluates each row, it only evaluates the values contained in Column D. If you wanted to set the conditional formatting based on another cell (the cells in Column C, for example), you would change the cell reference in the formula to $C2. Once you’ve written your formula, set the cell background color to green. This formula will color the entire row green if the employee is bonus eligible. Your entries should look like this:

Conditional Formatting Based on Another Cell Step 3

When you click “OK”, Excel automatically colors the entire first two rows green because they are deemed bonus eligible.

Conditional Formatting Based on Another Cell Step 4

This is a very simple example of how to automatically set conditional formatting based on another cell. You can make the reference formula much more complicated if you want to evaluate several different columns or even set the color based on whether each employee’s score is above or below a certain level. The variations on this technique are almost endless and limited only by your imagination.

Don’t Stop Here

Take your education to the next level! Sign up for our free email newsletter, and you’ll receive free Excel tips and tricks directly in your inbox. You’ll also be the first to receive exclusive discounts on our training classes.

Microsoft_Excel_2013_logo.svg

How to Find Duplicates in Excel (And What to Do With Them)

Being able to find duplicates in Excel is a very valuable skill to learn, as it enables you to quickly clean up data and troubleshoot problems. Sometimes you just need to locate the duplicates, other times you need to find and remove them. Regardless of your end-goal, this tutorial will teach you two different ways to find duplicates in Excel and how to quickly remove them (if you so desire).

1. Remove Duplicates Button

The most obvious way to find and remove duplicate values in Excel is to simply use the Remove Duplicates button (in the Data Tools group on the Data Tab). The functionality is actually pretty flexible, allowing you to select either a single column or a whole series of columns and then removing duplicates based on the criteria that you input. So, you can either remove duplicates from a single column (while leaving the rest of the table intact) or delete an entire row if it contains a duplicate value in one or more columns that you specify. So, if you just need to quickly clean up your data and completely remove any duplicate values the dataset, the Remove Duplicates button is the way to go.

2. Conditional Formatting

If you simply want to find duplicates in Excel (without necessarily deleting them), the best way to accomplish that is with Conditional Formatting. Simply use the Highlight Duplicate Values option. Once you’ve set the formatting, you can then apply a filter to the column with duplicate values and filter down to the duplicates. At that point, you can either edit, delete, or just ignore the duplicate values. The best part about using the Conditional Formatting method to find duplicates in Excel is that it’s dynamic! So, if you deciede to delete a few of your duplicates (or add in some more duplicates later), the Conditional Formatting will automatically update. Simply refresh the Filter to see the most current duplicates.

Learn to Find Duplicates in Excel and Much More!

Sign up now for our free email newsletter: Not only will you receive Excel tips and tricks directly in your inbox, you’ll also receive a free Gantt Chart template, which you can use to plan your next project.

Microsoft_Excel_2013_logo.svg

4 Ways to Use Conditional Formatting to Power Your Analysis

Conditional formatting is a powerful way to incorporate formatting into your data analysis. When you use conditional formatting in Excel, you make your formatting dynamic, which enables your formatting to complement and add to your data analysis. If you use the four tips described in the video, you can quickly and easily enhance your data analysis and make it more useful for your end-users.

1. Pick Your Favorites With Highlight Cells

The Highlight Cells conditional formatting option allows you to easily identify subsets of data within your data set. If you want to quickly find all of the values in your data that are greater than 10, simply use Highlight Cells and use conditional formatting to make all cells with a value greater than 10 red. Highlight Cells also offers several text options, so you can use this function with either text strings or numbers.

2. Find the Outliers With Top/Bottom Rules

If you just need to identify the outliers in your data, simply pick the Top/Bottom Rules conditional formatting option. You can use this option to quickly identify the values at the top and/or bottom of your range. You can also use it to simply identify values that are above/below your range’s average.

3. Quickly Rank Your Data With Data Bars

If you are more graphically inclined, Data Bars are an invaluable conditional formatting option. With Data Bars, you can quickly and easily create mini “graphs” inside each cell in your range that allow you to see how individual values rank within your range (cells with larger values will have larger Data Bars and vice-versa). Data Bars are a great way to add a functional graphic to your analysis.

4. Add a Splash of Color With Color Scales

Color Scales work much like Data Bars, except they use different shades of color to indicate ranking rather than a bar graph. You can use the Color Scales conditional formatting option to quickly add a splash of color to your analysis while allowing your end-users to quickly see where individual items rank within the range.

Become a Master of Conditional Formatting

If you follow these four tips, you will be well on your way to learning how to master the conditional formatting feature in Excel. However, there is much more to conditional formatting than what is presented here.

To learn more, sign up for our free email newsletter, where you’ll get Excel tips and tricks delivered directly into your inbox. For a limited time, you’ll also receive a free Gantt Chart template when you sign up!

Microsoft_Excel_2013_logo.svg

Finding Your Top Values with Conditional Formatting

Have you ever faced a large data set like this and wanted to quickly pick out the values in the top 10% (or 20%, 30%, etc.)?

largelist

The easiest way to accomplish this is to use Conditional Formatting. With Conditional Formatting, you can quickly pick out the top range of values (however you wish to define it). The advantage to using conditional formatting (especially in a case like this) is that it allows you to identify the values you need quickly without resorting your list. For example, if you wanted to sort the above list alphabetically by name but still wanted to identify the top scores on the two tests, you would use Conditional Formatting.

To start, simply select the data that you want to apply the formatting to and navigate to the menu shown below:

condform10

Then in the next pop-up, simply tell Excel which values you would like to highlight (and what color you would like to highlight them with):

condform10pop

In this example, I’ve set the range at 10%, but you can set it to any number you’d like (between 0%-100%) depending on what you need highlighted.

As you can see below, I’ve successfully highlighted the values in the top 10% in light red.

condform10final

Conditional Formatting can be an extremely useful and powerful tool if you know how to use it. To learn how you can unlock and use the full range of Conditional Formatting options, sign up for Excel 341, part of our Becoming an Excel Power-User Course Track.