Showing posts with label Conditional Formatting. Show all posts
Showing posts with label Conditional Formatting. Show all posts

Conditional Formatting bar chart

I’ve a table of data with target values and actual values, by month, like this:

Conditional_Formatting_Chart1

I want to create a bar chart with this data, something like this:

Conditional_Formatting_Chart2

But I would like, for better understand the data, to see if the actual values, for each month, are above or below the target value. If it’s on target, or over the target, I would like the month bar to show up in green color and, if it’s below target, to show up in red color. This can be done by adding two columns to our table, one for the results “under target” and one for the results “over target”. On this two columns, what we are doing, is to check if the actual sales value is less that the target, then show the actual value; if not, leave the cell blank. This for the “under target” column. For the “over target” column, we are going to check if the actual sales value is equal, or greater than the target value. If true, then, show the actual sales value; if not, leave the cell blank. The result will be like this:

Conditional_Formatting_Chart3

For the “under target” column, put this formula on cell D2 and copy down:

=IF(C2<B2,C2,””)

For the “over target” column, put this formula on cell D2 and copy down:

=IF(C2>=B2,C2,"")

Now we need to change our chart data source. If you right click on your chart and select “Select Data”, it will open the “Select Data Source” dialog box, like this:

Conditional_Formatting_Chart4

As you can see, your chart has only one data series. We are going to work with two data series: one for the “under target” values and one for the “over target” values. To start, we are going to change the series values for the existing series. For that, click on the Edit button on the Series box, on the left of the dialog box. This is pointing to column C, as you can see on the image below. We need to change the “Series name” to “=Sheet1!$D$1” and the “Series values” to “=Sheet1!$D$2:D$4” and click the OK button.

Conditional_Formatting_Chart5

Our chart series in now point it out to the “under target” column. We need to add the second series for the “over target” values. Click on the Add button, on the Series box:

Conditional_Formatting_Chart6

Fill out the “Edit Series” dialog box, like this, and click on the OK button:

Conditional_Formatting_Chart7

As you can see, you need to specify the “Horizontal Axis Labels” for this new series because it’s not showing up the months names. Just click on the Edit button and select the “=Sheet1!$A$2:$A$4” range.

Conditional_Formatting_Chart8

Close the “Select Data Source” dialog button, by clicking the OK button, and your chart should now look like this:

Conditional_Formatting_Chart9

We need to change the color of our series bars to display the “under target” values as red bars and the “over target” values as green bars. Just right click on the “over target” bars and select “Format Data Series”. Go to the Fill group and select “Solid fill” with a green Fill Color. Click the Close button when finished.

Conditional_Formatting_Chart10

Do the same for the “under target” series but for this select a red Fill Color. Before clicking on the Close button, select the “Series Options” group and on the “Series Overlap”, put 100% so that the chart bars appear center between each month.

Conditional_Formatting_Chart11

When you click the Close button, the result should be like this:

Conditional_Formatting_Chart12

Try changing any value on the B2:C4 range to see the conditional formatting of the chart adjust the colors.

Highlight duplicates

One of the most common tasks that an Excel user needs to do is to find duplicates on a list of data. There are several ways to do this, depending on the needs. One of the ways is to highlight the values that are duplicated on a column. This can be done using the Format-Conditional Formatting option on the menu bar. Here's an example:

Highlight-Duplicates
On cell A2, I created a Conditional Formatting with a condition "Formula is" and putted the function =COUNTIF(A:A,A2)>1. The COUNTIF function has the following syntax:

COUNTIF(range,criteria)

range is the area where you you want to check if it finds the value specified by criteria.

This will return TRUE if the value from cell A2 is found more than 1 time in column A. If this is TRUE it will apply the Format that I defined. I then Copy and Paste Special-Format to the rest of the cells in column A.

In Excel 2007 and 2010, you can use the a new option on the Conditional Formatting menu that is the Duplicate Values.

Highlight-Duplicates2
Just select the range where you want to highlight the duplicate and go to the menu bar and choose Highlight Cells Rules-Duplicate Values. A Duplicate Values dialog box will open. You can choose to highlight the Duplicate or the Unique values and you can set the color that you want to use to highlight the values.
Highlight-Duplicates3
The result will be the same as using the COUNTIF solution. On Excel 2003 you don’t have this Duplicate Values option on the Conditional Formatting menu.