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.

Add new data to an existing chart

This is a common task on Excel. We have a chart and we need to update the data displayed by adding more data. For instance, we have a chart that displays data from January to March and we need to add data for a new month. Let’s see how we can do this. We have this existing chart:

Add_new_data_chart1

Now lets add a new column to our table to add the April sales data, like this:

Add_new_data_chart2

There are several ways of adding and additional column of data to our chart. Let’s see some:

Method 1: This is the easiest way of doing this but it’s now always possible to to. Instead of putting the new data on the right side of the last column, insert a new column between columns C and D, copy the March data into this new column and put the April values on column E. Because you are adding a new column inside the source data range of the chart, Excel will adjust to accommodate this new column. As I said, this is not always possible if you have more data below this table. If you insert a new column, it will mess the data below.

Method 2: Put the April data on column E. Select cells E1:E4 and press Ctrl+C (Copy). Then click on the chart to select it and press Ctrl+V (Paste). You will have your new data displayed on the chart, like this:

Add_new_data_chart3

Method 3: This is the usually method that most people use. Right click on the chart and select the Select Data option, of go to the Design tab and on the Data group, click on the Select Data button.

Add_new_data_chart4

A Select Data Source dialog box opens and the chart source range get’s highlighted with the “marching ants”.

Add_new_data_chart5

With the cursor on the “Chart data range” field, just select the new data range, in this case select A1:E4. Click the OK button and your chart will be updated with the new data.

Method 4: Click on the chart. If you look at your table now, you can see blue, green and violet lines around the cells.

Add_new_data_chart6

If you pass the mouse on top of the blue line, it will get thicker showing you the actual source data of your chart, like this:

Add_new_data_chart7

Just drag the line, pushing from one of the blue squares on the corners, so that you get the new range of data selected, in this case range B2:E4 and release the mouse button so that your chart gets updated.

As you can see, there are many ways of doing the same thing in Excel. You just need to choose the one that you like it more.

Add automatic subtotals

This is a very handy trick. We have a table, like this:

SubTotals1

We now want to get the subtotals of the Value column, for each Store. First thing that we need to do, is to sort the table by the Store column. The easiest way is to click on any cell on the column (in this case, C2:C12), go to your Data tab and click on the “Sort A to Z” button, like this:

SubTotals2

We now have our table sorted by the Store column. To add the subtotals, on your Data tab, you’ll find a Subtotal button, on your Outline group. Click it and a Subtotal dialog box will open, like this:

SubTotals3

Now you can choose what is the column that you want to group (“At each change in”), in this case is the Store column, what is the function you want to use, we want to Sum, and what is the column that you want to sum, that is the Value column. Just click OK and your sheet will now look like this:

SubTotals4

As you can see, you now have an Outline column to the left of your sheet and a subtotal for each Store and a Grand Total in the end. Let’s see what you can do with this new Outline columns. Click on the “1” button, on the top of the Outline column and you will hide all of the details on your table, leaving you only with the Grand Total, like this:

SubTotals5

If you click on the “2” button, you will get only the subtotals by store and the Grand Total, like this:

SubTotals6

If you click on the “3” button, you will get your complete table again. You can always click the “+” and “-“ buttons to expand or collapse some of your groups. If you want to remove your subtotals, and go back to your original table, just select a cell on your table, go to the Data tab, click the Subtotal button again and on the dialog box that opens, click on the “Remove All” button.

Copy visible cells only

You have a table of data and you hide some rows and columns and you want to copy only the visible cells into another sheet? I’m going to show you some techniques that you can use to do that. Let’s see how our original table looks like:

Copy_Visible_only1

Now, let’s hide some rows and columns so that our table looks like this:

Copy_Visible_only2

If you select the entire table (cells A1:E12) and just copy and paste (Ctrl+C and Ctrl+V) it into another sheet, it would copy all rows and columns and would look like the original table. That’s not what we want to do, we just want to make a copy and show only the 7 rows and 4 columns that we see on the image above. For that we need to select the visible cells only so that we can copy only those. Let’s select again cells A1:E12 but this time we’re going to select first only the visible cells, before copying. We can do this by just using the shortcut Alt+; (semicolon) or you can go to the Home menu bar, on the Editing group, and click on the Find & Select button and choose Go To Special option.

Copy_Visible_only3

Instead of going to the menu bar, you can just press F5 or Ctrl+G to open the “Go To” dialog box and then just press the Special button on the bottom. On the Go To Special dialog box, select the “Visible cells only” option:

Copy_Visible_only4

This will select only the visible cells, as you can see on this image:

Copy_Visible_only5

Now you can just Ctrl+C (Copy) and Ctrl+V (Paste) it into another location. This will only copy the visible cells, like you wanted.
If you are working with Filters, you don’t need to use this techniques because, when you copy filtered data, it only copies the visible cells already.