Are you a user?

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).

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.

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.

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.

• 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.

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

Notice that the series is added as another line.

Now you can change this to a column graph.

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

• Select the stacked column chart type and click OK.

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.

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

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…”

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

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.

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. 

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

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

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).

• Select the stacked column chart and click OK.

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

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.

You should see another bar get “stacked.”

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.

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.

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

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

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

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

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

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

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

• 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.

Your graph should resemble this.

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.

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.

The final product.

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!