Excel-User.com

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

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

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:

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:

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.

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:

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

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.

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

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.

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.

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

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

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:

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

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:

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.

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

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.

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:

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.

This is a simple trick. If you have a series of data on your Excel sheet and want to make a quick chart with it, the fastest way is to select your range of cells that have the data and just press F11. This will create a new Chart sheet on your workbook. All you have to do next is to change the type of chart that is displayed and format it to suit your needs.

This is a tip on how you can delete all pictures and/or charts on a worksheet without having to manually select each one and delete them.
So, you can use the F5 shortcut key on Excel to open the Go To dialog box.

Select the Objects option and click the OK button to close the dialog box. Now you have all of the objects on your worksheet selected. Check if you don’t have any more objects on your sheet that you don’t want to delete, like a company logo or something, before you press Delete key to delete all of them at the same time.

This happens very often with most of the Excel users. You have a set of data that you use to create a chart. If you latter want to add more data to the chart, you need to redefine your chart data series, unless if you didn’t add rows or columns in the middle of your original data where in this case, Excel will assume them on your chart.
To avoid this, just create your chart as usual. Then select any cell inside the range of data that you used to create the chart. Go to Insert-Tables to convert the range of data to a table. This is all you need to do for Excel to recognize the data as a table and the data added to the table can be reflected on the chart without the need to redefine your source.

As you can see, the chart will display the zero values. If you want to hide the zeros, you have two ways of doing:

Method 1:
1) Select the range of cells where you have the values for your chart
2) Right click and select Format Cells
3) On the Number tab, on Category, select Custom
4) In the "Type" box you will see the current format for the range you selected
5) Put the cursor on the end of the content of the Type box and enter 0,0;;; it it will look something like this picture:

Method 2:
Go to Tools, Options and on the View tab, under Window option, uncheck the Zero values options, like this: