13
Jan
11

Adding multiple graphs (stacked bar and line) to an excel chart

I don’t normally blog on Excel, but since I was working with Excel Services and asked to create some KPIs to display using Excel Services, I thought it had some relationship to SharePoint.  So, let me be the first to say I’m not an Excel expert, but I can usually get it to do what I want it to do…after spending the time.

I really like the business part of SharePoint.  I like “SharePoint-tizing” business processes and figuring out the business side of things.  Let’s face it…SharePoint is a tool just like Excel, Word, PowerPoint, SAP, PeopleSoft, Oracle…and it’s really about the skill you apply to using the tools to provide the maximum business benefit.  While “I love technology” (one of my favorite songs from Napoleon Dynamite), I much prefer to figure out how to apply it in real business scenarios.

Publishing KPIs is a great way to get people on the same track and thinking about the same important goals in your business.  With Excel Services you can easily do this and, if you have a good back-end data source, you can connect your workbook to the live data.  In order to get started and prove out your KPIs, you may want to simply publish some items out of an Excel workbook that someone is manually updating on a regular basis.  That’s where we’re coming from in this example.

I have some data (employee headcount) and I want to create a combination graph that shows the prior year’s (2009) ending headcount by group and continue with showing the monthly headcount for each of the groups for the new year (2010).  This is what I was shooting for (and finally obtained).

image

The stacked column is showing the ending headcount from 2009 and the lines are showing the monthly headcount for 2010.  Here’s my original data.

image

Creating the original line graphs is easy.  Insert a chart and select the line chart and select the entire table of data and Excel will perform its magic.

image

image

That was easy.  Total time, 60 seconds.  Now we need to use the trick of adding another series to the chart and formatting it as a different graph (chart) type.  But first, we need to add a column to hold our data…or create another small table with our year-end stats.

image

  • Select the data and press Ctrl-C to copy it to the clipboard.
  • Click on the graph to select it (very important, so the paste option has the correct context).
  • Use the “Paste Special…” clipboard feature to paste the new series into the existing graph.

image

  • Use the default settings:  New series, Columns and click OK.

image

Notice that the series is added as another line.

image

Now you can change this to a column graph.

  • Right-click on the series and select “Change Series Chart Type…”

image

  • Select the stacked column chart type and click OK.

image

Notice you end up with a chart that doesn’t look like what we want it to look like.  There is no space in the X axis for the “YE 2009” label and the data from the year end totals is not stacked.  WRONG ANSWER!  Start over.

image

  • Right-click on the series you added and click Delete.

image

What we need to do is add each group’s year end total as a separate series using the same technique as we did previously.

  • Select the data for the first group and copy it to the clipboard.
  • Select the chart by clicking on it.
  • Select “Paste Special…”

image

  • Click OK using the defaults from the Paste Special action.

image

You’ll notice very little change in the chart.  What you should notice, however, is the addition of another “Group A” label in the legend.  The reason you don’t see anything on the graph is that the graph is simply a single point and it is being covered up by the other lines.

image

To select the new “point series” you can click around on the chart where you know it should be located, or you can use the Current Selection tools that are located on the far left of the Chart Tools | Layout and Chart Tools | Format tool ribbons. 

image

You should be able to identify the new “Group A” series and select it from the drop-down.

image

When you select it from the drop-down, the series will be selected on the chart.

image

Now you can change the chart type by using “Change Chart Type” in the Chart Tools | Design tool ribbon.

  • Click on “Change Chart Type” or carefully right-click on the selected series (takes a steady hand).

image

  • Select the stacked column chart and click OK.

image

You should see the first part of your stacked column graph appear.

image

Now let’s add the next series.  It’s basically repeating the above process.

  • Select the data and copy to the clipboard.
  • Select the chart.
  • Select “Paste Special.”
  • Click OK to add the series.
  • Select the “point series” using the drop-down in the Current Selection area of the Chart Tools | Layout ribbon.
  • Change the chart type by using “Change Chart Type” in the Chart Tools | Design tool ribbon.

image

You should see another bar get “stacked.”

image

Repeat the process for the final series.  Now that the stacked column graph as been added to the chart, it’s time to do a little clean up.

image

First, let’s make a space for the “YE 2009” label on the X axis.  We’ll do this by adding a new column to our original data and leaving the data blank.

image

Next, we’ll expand our selection for the original data.

  • Right-click on the graph and click Select Data…

image

  • Click the Edit button to edit the horizontal axis labels.

image

Notice that the range that is selected only includes the original 12 months. 

image

  • Re-select the range to include “YE 2009” and click OK.

Notice that the “YE 2009” label is added to the chart.

image

  • Select one of the original series and click the Edit button. 

image

  • Select the series values to include the blank “YE 2009” column and click OK.
  • Repeat this process for each series (Group A, Group B, Group C and Total).
  • Click OK to close the dialog.

image

Your graph should resemble this.

image

You can delete the redundant legend entries, if you like, by clicking on the legend entry until a single one is selected and pressing the delete key.

image

Now, if you want, you can select each of the series and format them manually to change their colors to be consistent between the line and column graphs, if they are not already.

image

The final product.

image

That was rather long-winded, but I hope it has helped you as much as it has helped me to document what I did, because it might be months or years before I do it again!

Advertisements

6 Responses to “Adding multiple graphs (stacked bar and line) to an excel chart”


  1. November 1, 2011 at 2:30 am

    Thank you very much for showing this fantastic guide on how to make two different chart types in one Excel chart. Is this possibly in all the never excel versions?

  2. 3 TNT
    November 16, 2011 at 5:37 pm

    this was VERY helpful and I really appreciate the time you took to include step by step graphics. THANK You!

  3. 4 Craig
    March 12, 2012 at 10:34 pm

    Hi,

    Do you know if you can do this with 3D charts? I know in Excel 2007 it wasn’t possible how about 2010??

    (I’m trying without success :< )

  4. 5 Alan
    November 16, 2012 at 5:23 am

    Hi – this is almost exactly what I need- the only thing is that in my case I need the stacked bar chart to appear at the right of the lines not the left. Is there a way to do this

  5. 6 Thanh Thúy
    April 16, 2014 at 1:45 am

    Thank you very much, I will try it, I got the instruction from my boss ealier but I did it by hard way. Thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Asif Rehmani’s SharePoint Videos

SharePoint-Videos

Click to access a wealth of SharePoint videos

SharePoint Rx

SharePoint Rx Home

Categories

Posts by Date

January 2011
M T W T F S S
« Dec   Feb »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Support Wikipedia

%d bloggers like this: