4. Adding Charts to Your Dashboard
Charts make it easier to spot trends and insights from our data. As a result they are a crucial component of all dashboards.
4 steps to add charts to your dashboard
1 Decide the type of chart you wish to create
2 Calculate the data for that chart in the backup sheet
3 Create the chart in the backup sheet and complete any required formatting
4 Cut / Paste chart into dashboard
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
ALT + N , C: Create column chart
CTRL + X: Cut selected item(s)
CTRL + V: Paste selected items(s)
So far on our dashboard, we have five headline values across the top of the page. Target Revenue, which is a user input. Total Revenue for 2013, and then three metrics that relate to customer retention and growth. To the analyst building the dashboard, the connection between these three metrics is quite obvious. However, to the executive or manager who will actually be using the dashboard, this may not be the case. To make sure the dashboard user understands the connection between New Revenue Rate, Lapsed Revenue Rate, and the Overall Annual Growth, I'm going to include a waterfall chart to my dashboard that will look something like this. As you can see, charts are intuitively much easier to understand than plain numbers and serve to bring those numbers to life. When building a chart for a dashboard, it's best to leave the chart input data on another sheet, let's say our dashboard input sheet and then cut and paste the chart into the dashboard once it's been created. The revenue numbers for our waterfall chart are the same for revenue numbers we have in row three, however, I'm going to divide each of these numbers by a million because it'll make the labels a lot easier to read on a waterfall chart. I'll simply copy this formula across for the remaining three cells. Now let's plot our chart. So I'll select these eight cells, Alt + N C for a column chart and I'll select the Stacked Column Chart. In our chart the 2012 revenue number and the 2013 revenue number are actually in the correct position. We just need to change the new revenue and the last revenue, to create our waterfall chart. We simply need to move the new revenue and the last revenue upward to create the waterfall. To boost the new revenue number, we simply create another bar underneath the new revenue figure that's invisible. The height of this invisible bar is the same height as the 2012 revenue. So back in our data set I'll move this figure underneath and then let cell B8 equal to the 2012 revenue. If I plot my chart again including an additional row with Alt + N C, you can see that we now have the new revenue bar in the correct position. For lapsed revenue, the invisible bar underneath is the height of the 2013 revenue. So again returning to my data set and deleting these two charts, I'll move my lapsed revenue down onto a new line and underneath create an invisible bar with the height of 50. I'll again create my chart to Alt + N C and then go to stacked column chart and now I have the bars in the correct position. How to remove the lines, the Y-axis? I'll add data labels to my chart. So simply right-click and Add Data Labels and then we'll change the color of our bars to finish the waterfall. So for the invisible new revenue bar, I'll select, go to Home, and color it white. I'll do the same for the bar underneath the Lapsed Revenue and I'll also color the labels white. I'll color the new revenue bar green as well as the lapsed revenue bar red. And I'll remove the legend. Off camera, I'll make some small edits to the labels. And now we're ready to add this chart to our dashboard. So I cut with Ctrl + X then move to the dashboard and paste with Ctrl + V. I'll then adjust the size of the waterfall chart until I'm happy how it looks on the dashboard. As you can see from this chart, the relationship between new revenue, lapsed revenue and overall growth is now much easier to understand than from our simple headline metrics.