2

I want to create a stack chart that shows the timeline of 3 phases on the x-axis (intervals separated by month) and total budget on the y-axis. The budgets for all projects will stack up against each other. How do I go about creating this graph, and how do I display the project name in each bar? I would like to have 3 colors on the graph to represent the 3 different phrases. Below is a sample data for two projects. In this sample data, some of phase 1 of Project B stacks up on phase 2 of project A.

Project Phase Start       End         Budget
A       1     07/01/2014  01/16/2015  $150,000
A       2     01/19/2015  08/28/2015  $790,000
A       3     08/28/2015  09/29/2015  $650,000
B       1     07/01/2014  07/30/2015  $ 68,000
B       2     07/31/2015  12/16/2016  $335,000
B       3     12/16/2016  01/17/2017  $543,000

gantt_bar_sample

  • Do you have a sample of what you'd like it to look like (to help your description)? – dav Jun 30 '14 at 18:45
  • I'm unable to upload images here, but I also asked this question on the excel help forum: excelforum.com/excel-charting-and-pivots/… You can see the image of what I want it to look like there after logging in – user1539217 Jun 30 '14 at 18:59
3

You can create that type of chart using Excel's stacked column chart. To make things easier, you should reformat your data. Here's one method:

  1. Create a table of your data, with the following columns:

    • Date
    • Project A, Phase 1
    • Project A, Phase 2
    • Project A, Phase 3
    • Project B, Phase 1
    • Project B, Phase 2
    • Project B, Phase 3

    Each date period (month or day) will have its own row of data in the table.

  2. Enter your monthly budget values on the appropriate line, in the appropriate column. Leave blanks (or insert =NA()) into the cells that have no value.

    data sample

  3. Create a stacked column chart, using the above data.

  4. Format the chart series to have 0 gap (this creates the gantt effect) and no borders. Then, format the rest to taste.

Here's what the chart could look like: gantt_column_excel

I used two date columns (Month & Year) to get the stacked label effect on the horizontal axis. Also, I only used month level, but you could use daily level for more granularity.

For the labels, I simply chose a single data point for each series and added a label to that point. That works for a small number of labels. If you have many more, you'll want to consider something more automated (consider an XY chart overlay, with the datapoints to place labels).

To create data labels using an XY chart overlay, you'll need to add some data to your table and your chart (you can shortcut some of this by installing and using the excellent FREE add-in XY Chart Labeler)

  1. Add these additional columns to your data table (again, you can shortcut some of this, once you learn the basic principle):
    • Number (from 1 to whatever your row count is)
    • A column for each data series (e.g. A1, A2, A3, B1, B2, B3). Name the column what you want your label to say. (Excel, by default, can only use a Series name, X value or Y value as a data label).
  2. In each label column, on the row of the middle value in your series, enter the series value divided by 2. That will place a data point half-way through your data series on the X Axis, and half-way up your data series on the Y Axis.
  3. Add another data series to your chart (it doesn't matter what, we'll change it in the next step).
  4. Select the new data series and change that series chart type to XY.
  5. Use Select Data to update your new data series with the XY data values. You'll want"
    • Series Name = your new column header, which will be your new data label.
    • X Values = Your new counter column (from step 1).
    • Y Values = Your new data label column (from step 2).
  6. Once your data point is added, format it to no symbol (we're only using it as a placeholder for your label).
  7. Add a data label for this new point, selecting Series Name and Y Value, separated by a new line.
  8. Repeat for all points.
  • Thank you for your suggestion! I'm still struggling on figuring out how to add labels using an XY chart overlay. How do you go about doing this? – user1539217 Jul 1 '14 at 14:42
  • Added some directions at the bottom of the answer. – dav Jul 1 '14 at 17:27
  • Did that work for you? – dav Jul 3 '14 at 13:14
  • Yes it did, thanks! Is there a way to add a border for each project-phase combination instead of having borders shown for each month? I tried using the stacked area chart, but the border lines are shown diagonally instead of vertically. – user1539217 Jul 3 '14 at 15:04
  • I don't think there's an easy way to make the borders that way, since Excel is looking at each stacked column as a discrete object. One possible way (but I'm not sure its worth the work) is to use an XY Chart to draw your borders-I just don't think it would be easy to do automatically-you'd probably have to create a custom data set (based on your primary set) with each of the corner coordinates for the XY points, then format them with a visible connecting line. – dav Jul 9 '14 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.