Light at the End of the Line
When I use a multi-series line chart in my Xcelsius dashboards, there is more than one way to identify each series:
1) display legends
2) display data labels with “Series Name” enabled
3) mouse-over each data point to see the series name.
But often I like to display the series name at the end of each line and in many occasions I get the same request from users as well. But Xcelsius can’t do it – yes, that’s right. If I enable the Data Label I can select either/all of Value, Series, and Category labels to display – but then it adds the series name for every data point and shows up like the image below. This is not an efficient use of space and it is not only quite redundant information, but it also makes it difficult for users to concentrate on the actual data. It looks pretty messy as well.
The way to clean it up is to add a legend. This works and looks fine, except you have to keep going back to it to match the color of the line. The legend also takes up space for itself as well as the white space around it that could be used otherwise and some of the lines end at different x-axis data values and it helps to have the series name right next to it.
In this post, I am going to share an idea to get around this restriction and show the series name only once at the end of the line.
So, here is my cute little data set for the chart where 3 modes of payments (check, online and phone) will make individual series and the dates will go on the horizontal x-axis. The lines show the count of payments made via each mode by payment date.
Here are the steps that I followed:
- I added 3 additional columns to the dataset with exact same series name as the original. B2-D2 is the series names for the data to be plotted. I added E2-G2. I found the last value in each series and repeated that in the new columns for their respective date.
- I remapped my line chart to the entire data set now – Sheet1!$A$2:$G$10.
- I maintained the same color and shape for each pair of series – original and additional. So, both of the series named check should have the same line, fill color, and shape.
- The next thing I did was enable the Data Label for the additional series and under the “Label Contains” property I checked “Series Name”. You need to make sure that the same property is disabled for the original set of series.
- I got rid of the legends at this point and this is exactly how I wanted my chart to look.
That’s it! Now the chart should display the series like one below!
Easy, isn’t it?
Not just yet. It’s that simple when you know the end of your data for each series while creating the three additional columns. What about the real situation when you have dynamic data? Well, you can use the following set of formula I used to create the additional series.
The green area is the original data set. Pink is the three new series and blue is more formula.
I took the following approach – one blue column for each series. The blue set of formulas checks if there is data present for the corresponding row and increments the value if there is. The formula in J3 sets 1 if there is a data value in N2 and 0 if there isn’t. =IF(N3=””,0,1)
J4:J10 checks and assigns the same, but by adding 1 to the previous row if there is a value and 0 otherwise. =IF(N4=””,J3,J3+1)
J2 calculates the maximum of J3:J10. =MAX(J3:J10)
The first row that matches with the maximum value is the row with last data point in column N. The formula in Column Q assigns null if column J does not match the maximum value and assigns the actual data value when it hits the maximum. =IF(AND(J3=J$2,N3 <> “”),N3,””)
Repeat the formula for each series.
And there you have the same additional data set as what I had manually. I can see a lot of use for this in many different dashboards. I hope you can use it too and get creative to improvise the solution to fit your dashboard!
I can think of a few limitations though:
- Need to hide the legend
- Adding double the number of columns as the original data set with too many columns can cause maintenance issues.
- It may look clumsy with lines too close to each other or when other data labels like ‘Value’ is enabled. You may also need to play with the offset to make it work for your data.
I would love to hear any other twists to achieve the same outcome!