Excel Dashboards and Reports: Apply Creative Label Management
As trivial as it may sound, labeling can be one of the sticking points to creating effective trending components for your Excel dashboards and reports. Trending charts tend to hold lots of data points, whose category axis labels take up lots of room. Inundating users with a gaggle of data labels can definitely distract from the main message of the chart.
Here are a few tips to help manage the labels in your trending components.
Abbreviate instead of changing alignment
Month names look and feel very long when you place them in a chart — especially when that chart has to fit on a dashboard. However, the solution isn’t to change their alignment, as shown in this figure.
Words placed on their sides inherently cause a reader to stop for a moment and read the labels. This isn’t ideal when you want them to think about your data and not spend time reading with their heads tilted.
Although it’s not always possible, the first option is always to keep your labels normally aligned. So instead of jumping right to the alignment option to squeeze them in, try abbreviating the month names. As you can see in the figure, even using the first letter of the month name is appropriate.
Imply labels to reduce clutter
When you’re listing the same months over the course of multiple years, you may be able to imply the labels for months instead of labeling each and every one of them.
Take this figure, for example. The chart in this figure shows trending through two years. There are so many data points that the labels are forced to be vertically aligned. To reduce clutter, as you can see, only certain months are explicitly labeled. The others are implied by a dot.
To achieve this effect, you can simply replace the label in the original source data with a dot (or whatever character you like).
Go vertical when you have too many data points for horizontal
Trending data by day is common, but it does prove to be painful if the trending extends to 30 days or more. In these scenarios, it becomes difficult to keep the chart to a reasonable size and even more difficult to effectively label it.
One solution is to show the trending vertically using a bar chart. As you can see in this figure, with a bar chart, you have room to label the data points and keep the chart to a reasonable size. This isn’t something to aspire to, however. Trending vertically isn’t as intuitive and may not convey your information in a very readable form.
Nevertheless, this solution can be just the work-around you need when the horizontal view is impractical.
Nest labels for clarity
Often, the data you’re trying to chart has multiple time dimensions. In these cases, you can call out these dimensions by nesting your labels. This figure demonstrates how including a year column next to the month labels clearly partitions each year’s data. You would simply include the year column when identifying the data source for your chart.