In a recent Wall Street Journal article I saw the following chart regarding Dropbox, YouSendIt and Box.com. The graph describes how many people use Dropbox vs YouSendIt vs Box. As you can see by the chart, Dropbox has almost double both of the other two services. The thing that intrigued me was how can you easily create this in an Excel Chart.
(Sorry for the poor quality , I took the picture with my IPhone.
Here is my rendition of the chart using an Excel Chart. This was all done by using the Excel chart functionality and there are no drawing or other shapes overlaid in the chart. It is just a chart.
Here is what the chart has going for it and things you may wish to include in your Executive Dashboard Charts:
1) I have used another series to create what I call a Horizontal Panel Chart.
2) Also, I have 2 different label groupings/categories on the Top and Bottom of the chart that describes each one of the panel charts.
3) I have used special number formats to create the Vertical Axis.
4) I have modified the title with custom formatting.
5) I have changed the major gridline values and format.
And here is the final chart. Looks just like the original and all with the standard Microsoft Excel program.
2) Create the Chart: Highlight cell range D3:G13
3) Fix the Primary Horizontal Axis:
then click on the Edit button in the “Horizontal (Category) Axis Labels” from the Excel Select Data Source dialog box:
You will now see the labels only show 2010, ‘11 and ‘12 on the horizontal axis only for the first data series.
4) Fix the Primary Horizontal Axis:
b) Then change the Axis Options as follows:
Minimum = Fixed & 0
Maximum = Fixed & 1.05E8 (this equals 100 Million)
Major Unit = Fixed & 2.5E7 (this equals 25 Million)
d) Then click on the Number options and then click on Custom Category and input this in the Format Code: box and press Add button.
Here is another posting on making custom Vertical Axis number formats:
5) Change the major gridlines to a Dashed Line
6) Fix the Column sizes, Colors and Axis Placement (this is how we make the panel chart look)
a) Right click on the left most data series in the chart and click on the “Format Data Series…” item from the Excel Chart pop up menu.
c) We will now create the horizontal panel chart look and feel. Select Series 4 by first Selecting the chart and then select “Series 4” from the Chart Elements Picklist in the Current Selection group.
You can check out a video on selecting data series in charts on this posting:
From the Series Options, change the “Plot Series On” to the Secondary Axis:
Now it won’t look like you have done anything, but you have and we will show you in a minute after we change the colors of the other series.
d) Finally, lets change the color of each data point that you can see on the chart. Start with the data point on the far left by first selecting the chart series on the left and then selecting a second time the far left data point. It will look like this:
7) Create upper horizontal categories and horizontal panel.
We are almost done. Don’t worry
a) You need to click on the chart and then go to the Layout Ribbon. From there click on the Axes button from the Axes group. From there you need to select the Secondary Horizontal Axis and then choose “Show Left to Right Axis”
Wow, it now looks like an Excel Horizontal Chart Panel. Notice the gridline breaks in between each series?
b) Now we need to replace the secondary horizontal categories (the upper one) with the words DropBox, YouSendIt and Box.com. To do this, select the chart and then open up the Select Data dialog box from the Chart Design Ribbon.
From there, you need to select “Series 4” from the Legend Entries (Series) area and then click on the Horizontal (Category) Axis Labels “Edit” button on the right. You MUST click on the “Series 4” before you click on the Edit on the right. If you do not do this, you will change the wrong horizontal axis.
c) Almost there. So lets get rid of the secondary horizontal axis line on the top. To do this, right click on the Secondary Horizontal Axis (the one on the top) and choose “Format Axis”:
8) Hide the Secondary Vertical Axis (right one)
So lets get rid of the secondary vertical axis line and values on the right and on the secondary vertical axis on the right. To do this, right click on the Secondary Horizontal Axis (the one on the right) and choose “Format Axis”:
9) Clean up the chart by removing the Legend and then add/customize a Chart Title.
a) For the Legend, do this by selecting the Legend and pressing the delete key.
b) For the title, I won’t show you how to do the Excel Chart Title because you should have learned that in my last post. Here is a link so that you can find it easily:
ALL DONE!! Lets look at the 2 charts side by side. What do you think? Looks the same to me.
Here is a free Excel video tutorial show you how quick and easy to do this Horizontal Panel Chart tip and trick.
Let me know what you think in the comments on if you will use this technique.