Single Point Annotation
Often folks want to add a label to a single point or annotate a handful of events in a graph. This article lays out how to do that with both numerics and strings.
It’s very easy to add free text to a data set (note “Label” here is a new column below):
But when adding direct text, the chart will also plot labels as ‘null’ for the blank points:
Fortunately we have some alternatives. By using formulas, we can explicit set the blanks to ensure our labels are built properly.
In this case we’ll create a formula to find the MAX() of the data series and only label that point =IF(B154 = MAX(B:B), "Big Day!", ""):
This will leave only the label on our high-point day:
Working with numeric points requires one addition step. When writing a similar formula for numbers, the database will revert to return nulls for the blank points. This results in labels of 0 on our graph:
We can use the TEXT() function to adjust our formula to match the behavior with strings. TEXT() allows us to format the resulting value, and retain the empty other cells. In this case, we’ll use ==IF(B1 = MAX(B:B), TEXT(B1, "#"), ""):
Note that the if statement can be adjusted to highlight whatever cells make the most sense (or even use an additional column to “X” the cells that require value labels.
Thinning Out Data Labels / Sparse Labels
This same technique can be used to thin out data labels on charts (we’d also like to get vis controls down the line).
This is a common problem:
Again using a thoughtful calc and the label controls we can choose the density. Here, we’ll show every fourth label with =IF(MOD(ROW(), 4) = 0, TEXT(B1, "#,###"), ""):
