Excel Charts – Trends, targets and highlights

I’ve been reading up on excel charts recently, so I thought I would share a few tips on creating Excel charts. In this post I want to look at three main elements:

• Trends
• Targets
• Highlights

Trends

A large amount of the data that we want to look at is time phased. It is therefore useful to be able to plot the data in ways that make it easy to spot trends over time.

Since we are used to thinking of time as linear, line charts are often the best way of plotting time phased data. For example if we have total work done per day:

Then we can plot this on a chart:

This is a line chart, that shows the full six months worth of data. However, if we apply a filter to the data, by selecting the Data tab and then clicking on the Filter button, we can specify that we only want to view data from, for example, a particular month.

The chart will update to show only the selected data. If your chart continues to show all the data then follow these steps:

1. Select the chart
2. Select the Chart Tools – Design tab
3. In the Data group, click Select Data to display the Select Data Source dialogue box
4. Click the Hidden and Empty cells button to display the Hidden and Empty cells settings dialogue box
5. Ensure the Show data in hidden rows and columns check box is not selected
6. Click OK
7. Click OK

Targets

For a particular set of data we may want to set a target. For example if we look at Resource Utilisation, we may aim for 80% utilisation.

Our initial data and chart may look like this:

If we are using a pivot table as the source then the data may well already be in this nested format. If not then excel is clever enough to plot the chart based on our nested data.

To show the performance against the target of 80% we can add another data series to the chart.

In order to add this additional column to the chart:

1. Select the range E1:E25
2. Do Ctrl+C to copy this data
3. Click on the Chart area to select the chart
4. Do Ctrl+V to paste the data in to the chart

The series can now be formatted as desired.

Highlights

This time we want the chart to automatically pick out the month with the highest, or perhaps lowest, resource utilisation. We can do this by again adding another data series, but this time controlling the value of each data point using a formula.

Essentially the formula says that if the utilisation for the current month is the highest utilisation over the two year period, then show that value, other wise leave it at zero.

When this new series is added to the chart and formatted as a column then only one column is visible:

The lowest point could also be found using a similar formula.

=IF(D2=MIN($D$2:$D$25),D2,0)

This could be combined with the previous data to produce the following: