Advanced Gantt Chart Template
This Gantt chart uses a stacked bar chart to display the tasks and their corresponding date ranges. Completed days are green and remaining days are red, see image above.
The chart utilizes an Excel defined Table in a very efficient way, you don't need to adjust chart data source ranges when you add or delete records. The Excel defined Table takes care of that for you.
How to build worksheet
First you need to convert your data into an Excel defined Table.
Excel defined Table
- Select a cell in your data set.
- Press CTRL + T.
- Enable the checkbox if the data set contains column headers.
- Press with left mouse button on OK.
Now you need to create a stacked bar chart.
Insert a stacked bar chart
- Select one of the cells in the Excel defined Table.
- Go to tab "Insert" on the ribbon.
- Press with mouse on "Insert Column or Bar Chart" button.
- Press with mouse on "Stacked Bar chart" button.
- A chart appears, however, we need to customize it quite a lot before we can use it.
Customize stacked bar chart
The data source ranges are not what we want but we can easily change those.
- Press with right mouse button on on chart
- Select "Data source..."
- Change the first Legend entry "Days" so i points to column "Start Date" in the Excel defined Table".
- Press with left mouse button on "Edit" button (Horizontal (Category) Axis Labels and change the cell reference so it points to the "Activity" column on the Excel defined Table.
- Press with left mouse button on OK button.
The chart now looks like this:
We are now going to remove the blue bar.
- Double-press with left mouse button on one of the blue bars to open the format pane.
- Press with mouse on the Fill & Line button.
- Select "No fill".
If you want the tasks to be arranged in reverse order then follow these steps.
- Double press with left mouse button on the y-axis categories to open the Format Pane, see image above.
- Press with mouse on "Axis Options" button.
- Press with left mouse button on the checkbox "Categories in reverse order.
The image now looks like this, the dates are however in a mess.
- Double-press with left mouse button on x-axis values to open the Format Pane.
- Press with mouse on "Axis Options" button.
- The "minimum" value is the number equivalent to an Excel date, in this case, 10/24/2011. The first date range starts at 20/11/2011 which is number 40868. I will use 40867.
- Feel free to change the maximum value as well.
I recommend that you apply minor gridline to make the chart easier to read.
- Press with right mouse button on on x-axis values.
- Press with left mouse button on "Add minor gridlines".
- Change the "Minor" value to 1 in the Format Pane.
- Select and delete the major gridlines.
Repeat above steps but this time with y-axis values.
If you prefer the x-axis values below the chart then double-press with left mouse button on the y-axis values to open the Format Pane.
Press with left mouse button on "Horizontal axis crosses" At maximum category.
Recommended blog posts
Gantt Chart with Repeated Tasks via Excel XY Chart | Peltier Tech BlogÂ
Charts category
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Custom charts category
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
This chart is an arrow chart that has horizontal and vertical lines, positive arrows are green and negative arrows are […]
I found a chart that I wanted to show you how to build. It contains values both horizontally and vertically, […]
The picture above shows the following equation x^3+3*x^2-3 plotted on an x y scatter chart. Here are the instructions on how […]
The chart above is built using the NORM.DIST function and is called Normal Distribution or Bell Curve chart. This curve is often […]
Gantt category
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
Templates category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article demonstrates a basic invoice template I created. It lets you use dropdown lists to quickly select products on […]
This article demonstrates how to highlight given date ranges in a yearly calendar, this calendar allows you to change the […]
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]
I would like to share this simple weekly schedule I created. How to use weekly schedule Type any date in cell […]
This template makes it easy for you to create a weekly school schedule, simply enter the time ranges and the […]
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
Rattan asks: In my workbook I have three worksheets; "Customer", "Vendor" and "Payment". In the Customer sheet I have a […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
I have created another monthly calendar template for you to get. Select a month and year in cells A1 and […]
Excel formula categories
Excel categories
2 Responses to “Advanced Gantt Chart Template”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
I need to chart task planned v's actuals and i'm stuck. i've tried gantt & x/y but can't get it to work for multiple calendar periods.
Example data
Deliverables Period11 Actuals11 Period 12 Actuals 12
Task 1 17/11/2014 12/11/2014 15/12/2014 17/12/2014
Task 2 19/11/2014 20/11/2014 17/12/2014
Task 3 20/11/2014 20/11/2014 18/12/2014
Task 4 20/11/2014 24/11/2014 18/12/2014
Any help appreciated.
Tammyw,
What are the calendar periods?
Task 1 17/11/2014 - 12/11/2014 and 15/12/2014 - 17/12/2014?