Sometimes it is useful to be able to annotate a chart with the latest value, for instance comparing planned progress with actual progress.
It is possible to do this manually or to select a data point to display however there is an easier way which allows the process to be automated. Basically, you need to add a new series of data for each single data point you want to show full of null data (#NA) except for the point you want to display. Excel will not attempt to plot the “errors” and so the series will show only as one point. This data series has data labels and so the single point will display the data you want to show.
Taking a simple set of data as an example:
Add a new series
First add a new column alongside your data (column C) which I have called “Latest” which will hold the single data point we want to show. Then make some dummy data which will make adding the series to the chart much easier. In this instance we can add a value of 11 into C5:C7.
Now simply add the new series to the chart. One of the simplest ways to do this is to copy the data (including the column heading) - C1:13 and paste it in the chart. If this doesn’t work or your data is more complex than add the series as you normally would by selecting the Chart and choosing “select data” and adding the series.
Display data points and labels
Now set up the data points as you want to see them. In this case we will have an amber circle showing the data point with a label in the same colour as the line.
Select the line and formal the data series to display markers, select circle with a line colour = amber and fill = none.
Then reselect the line and right mouse and chose to display data labels, then chose to format data labels. Note that it is important to check that all data lables are selected and you are formatting data labels rather than label. Now chose the font colour/size in the normal way and also chose if you want to display the label above, below, left or right of the data point (in this example we are below).
You can choose to set the chart line to show no line however as there will only ever be one point this isn’t necessary unless you want to display this in the legend.
Display only one data point
We need to generate errors for every point except the latest one, the easiest way to do this is to compare the date column with either a set status date or TODAY(). In this example I have added a Status Date in cell F1.
C2 =IF(A2=$F$1,B2,NA())
Then copy this formula down across all the rows (in this example C1:C13).
The formula compares the date in A1 against the contents of F1 and if it matches is displays the value in column B but if not it displays and error code #NA. Note that the use of $ against both the row and column elements of F1 prevents the act of copying and pasting from changing F1.
If you wanted to have the data for today (which may be different to the “status date” you would use the formula
C2 = IF(A2=TODAY(),B2,NA())
Now the “Latest” series has only one data point to display which is set at the status date.
A more complex example
To generate the example shown at the top of this entry we have two columns of data and so need two additional “latest” columns however this uses exactly the same method as above.
Note that for ease of updating it makes sense to have the labels left and right of the data point so they can’t overlap should the values be the same.
Note that if you have a legend for the chart these new series will display – to remove them simply select the legend and then click again to select the individual entry for the series and delete it.