How-to Add Resource Names to Excel Gantt Chart Tasks

How-to Add Resource Names to Excel Gantt Chart Tasks
How-to Add Resource Names to Excel Gantt Chart Tasks

How-to Add Resource Names to Excel Gantt Chart Tasks

I recently received a request from a fan that asked how he could add resource names to Excel Gantt Chart that he had created.  The technique I describe below is a technique that you can use to add more task information to your Excel Gantt Charts.

One caveat, this technique will work for any Excel version past 2003.  It is a great workaround to add labels in Excel 2007 and Excel 2010.  However, for Excel 2013 and Excel 2016 there is a much easier technique that I will demonstrate in the next post.

How-to Add Resource Names to Excel Gantt Chart Tasks
How-to Add Resource Names to Excel Gantt Chart Tasks

The Breakdown

The crux of this technique is to add another data series to the chart that will be put on the secondary axis so that the labels can display alternate categories.

1) Create Chart Data and Stacked Bar Chart

2) Modify Duration Filler Series Fill





3) Modify Primary Axis

4) Move Resource Filler Series to Secondary Axis

5) Modify Secondary Axes

6) Modify Secondary Axis Categories

7) Change Secondary Axis Chart Type



8) Add Chart Labels

9) Modify Secondary Series Fill

10) Chart Clean Up

Step-by-Step

1) Create Chart Data and Stacked Bar Chart

Assuming we start out with our data in this format:

A B C D
1 Phase Task
2 Duration Filler Duration (Days)
3 Plan Requirements 2/5/2018 7
4 Design 2/12/2018 14
5 Develop Development 2/26/2018 63
6 Unit Test 4/30/2018 7
7 Deploy to QA 5/7/2018 7
8 Test UAT Test 5/14/2018 21
9 Bug Fix 6/4/2018 7
10 Deploy Deployment 6/11/2018 7
11 Training 6/18/2018 14

 





The first step is to add 2 columns of data to your Excel Gantt Chart data range.

A) One, in column “E” add a filler series called “Resource Filler” to the right of the duration data.  This is a calculated column.  In Cell E3 enter =C3+D3.

B) Two, in column “F” add the resource names that you want to have for your labels by task line.

Your chart data will now look like this:

A B C D E F
1 Phase Task
2 Duration Filler Duration (Days) Resource Filler Resource Name
3 Plan Requirements 2/5/2018 7 2/12/2018 Miguel, Luis, Maria, Steve
4 Design 2/12/2018 14 2/26/2018 Miguel, Luis
5 Develop Development 2/26/2018 63 4/30/2018 Miguel, Luis
6 Unit Test 4/30/2018 7 5/7/2018 Miguel, Luis
7 Deploy to QA 5/7/2018 7 5/14/2018 Luis
8 Test UAT Test 5/14/2018 21 6/4/2018 Maria, Steve
9 Bug Fix 6/4/2018 7 6/11/2018 Miguel, Luis
10 Deploy Deployment 6/11/2018 7 6/18/2018 Luis
11 Training 6/18/2018 14 7/2/2018 Steve
Worksheet Formulas

Cell Formula
C4 =C3+D3
C5 =C4+D4
C6 =C5+D5
C7 =C6+D6
C8 =C7+D7
C9 =C8+D8
C10 =C9+D9
C11 =C10+D10
E3 =C3+D3
E4 =C4+D4
E5 =C5+D5
E6 =C6+D6
E7 =C7+D7
E8 =C8+D8
E9 =C9+D9
E10 =C10+D10
E11 =C11+D11

Next, we will want to create a Stacked Column Chart.

To do this, first highlight the chart data range of A2:E11 as you see here:



Highlight Excel Gantt Chart Data Range
Highlight Excel Gantt Chart Data Range

Next, click on the Insert Ribbon and choose the Bar button in the Chart group and finally select Stacked Bar Chart as you see here:

Insert Stacked Bar Chart for Excel Gantt Chart
Insert Stacked Bar Chart for Excel Gantt Chart

Your chart should now look like this:

Add Resource Names to Excel Gantt Chart Tasks 1
Add Resource Names to Excel Gantt Chart Tasks 1

2) Modify Duration Filler Series Fill

The next step is to modify the “Duration Filler” series fill color.  In order for the Excel Gantt Chart to have a Gantt Chart look, we need to make this filler series disappear on screen.

To complete this step, select the chart, then double click on the “Duration Filler” series.  Or select the series and press CTRL+1 to bring up the Format Series dialog box.  Then click on the Fill options and choose “No Fill” so that the series is now not visible but still there.

Format Data Series No Fill
Format Data Series No Fill

Your chart should now look like this:





Add Resource Names to Excel Gantt Chart Tasks 2 No Fill
Add Resource Names to Excel Gantt Chart Tasks 2 No Fill

3) Modify Primary Axes

Now you may have noticed that both the horizontal and vertical axes have issues that need to be corrected.  One, the vertical axis needs to be reversed so that Plan is on top and Deploy on the bottom.  Also, the horizontal axis starts at the year 1900.  We need to fix that as well as that is too far in the past and as our Excel Gantt Chart won’t span years, we can remove the year from the number format without issue.

A) Reverse Vertical Axis Direction

To move Plan to the top of the vertical axis, first select the chart, then double click on the vertical axis or select it and press CTRL+1 to bring up the Format Axis dialog box. Next, click on the “Categories in reverse order” checkbox and press the close button.

Format Axis Categories in reverse order
Format Axis Categories in reverse order

Your chart will now look like this:

Add Resource Names to Excel Gantt Chart Tasks 3 Categories in Reverse Order
Add Resource Names to Excel Gantt Chart Tasks 3 Categories in Reverse Order

B) Set Horizontal Axis Minimum Value

In order to show a small task duration on the chart, you will need to set the minimum value on the chart’s horizontal axis to a more recent date.  By default, Excel sets a date of January 1st, 1900 if you have a value of zero (0) but display it as a date.  All dates are actually numbers, so our chart is showing the base of zero for the chart as the minimum even though we would want to show a later date.  April 5, 2012, which is our minimum date on the Duration Filler column is equal to 43,136, so it is best if we start are chart at that date.

To set the horizontal axis minimum, first, select the chart, then double click on the horizontal axis to show the Format Axis dialog box.  Alternately, you can select the horizontal axis and press CTRL+1.  Next, change the minimum to Fixed with a value of 43,136 as you see here:



Format Axis Fixed Minimum Value
Format Axis Fixed Minimum Value

Your chart should now look like this:

Add Resource Names to Excel Gantt Chart Tasks 4 Primary Horizontal Fixed Minimum
Add Resource Names to Excel Gantt Chart Tasks 4 Primary Horizontal Fixed Minimum

C) Change Horizontal Axis Number Format

As you can see when we change the minimum value for the Horizontal Axis our dates overlap and it is unreadable.  The solution is to change the number format in the chart axis.

To change the number format on the horizontal axis, first, select the chart, then double click on the horizontal axis or select it and press CTRL+1 to bring up the Format Axis dialog box. Next, click on the “Number” options and select a short date like 03/14 to just show the day and month as you see here:

Format Axis Number Format
Format Axis Number Format

Your chart should now look like this:

Add Resource Names to Excel Gantt Chart Tasks 5 Primary Horizontal Number Format Change
Add Resource Names to Excel Gantt Chart Tasks 5 Primary Horizontal Number Format Change

 





4) Move Resource Filler Series to Secondary Axis

Now we are going to start really messing with the chart.  In order for this technique to work, we need to move the “Resource Filler” chart series to the Secondary Axis.

To move the series, first select the chart, then double click on the “Resource Filler” series.  Or select the series and press CTRL+1 to bring up the Format Series dialog box.  Finally, in the “Series Options”, select the “Secondary Axis” radio button from the “Plot Series On” group as you see here:

Format Series Dialog Box Plot Series On Secondary Axis
Format Series Dialog Box Plot Series On Secondary Axis

Your chart should now look like this:

Add Resource Names to Excel Gantt Chart Tasks 6 Move Resource Filler to 2nd Axis
Add Resource Names to Excel Gantt Chart Tasks 6 Move Resource Filler to 2nd Axis

5) Modify Secondary Axes

Now you may have noticed that we now see the secondary horizontal axis but not the vertical axis in the chart. We will need to show the secondary vertical axis as well as change the order to match the reversed order of the primary axis.  Finally, we will need to modify the secondary horizontal axis to start at 2/5 to match the primary axis minimum fixed value.  As we won’t be displaying the secondary horizontal axis, we will leave the number format unchanged.

A) Show the Secondary Vertical Axis

So that we can reverse the order of the secondary vertical axis, we first need to show it so that we can complete the action and also verify that it now matches the primary axis.  To do this, first select the chart, then select the “Layout” Ribbon, then select the “Axis” button and then the “Secondary Vertical Axis” menu option and finally the “Show Default Axis” choice as you see here:



Show Secondary Vertical Axs for Excel Gantt Chart
Show Secondary Vertical Axis for Excel Gantt Chart

Your chart will now look like this:

Add Resource Names to Excel Gantt Chart Tasks 7 Show 2nd Vertical Axis
Add Resource Names to Excel Gantt Chart Tasks 7 Show 2nd Vertical Axis

B) Reverse Secondary Vertical Axis Direction

As you can see above, the secondary vertical axis does not match the primary.  To move Plan to the top of the secondary vertical axis, first select the chart, then double click on the secondary vertical axis or select it and press CTRL+1 to bring up the Format Axis dialog box. Next, click on the “Categories in reverse order” checkbox and press the close button.

Format Axis Categories in reverse order
Format Axis Categories in reverse order

Your chart will now look like this:

Add Resource Names to Excel Gantt Chart Tasks 8 2nd Axis Categories in Reverse Order
Add Resource Names to Excel Gantt Chart Tasks 8 2nd Axis Categories in Reverse Order

C) Set Horizontal Axis Minimum Value

Just as we did for the primary horizontal axis, we need to fix the minimum value on the secondary vertical axis.

To set the secondary horizontal axis minimum, first, select the chart, then double click on the secondary horizontal axis to show the Format Axis dialog box.  Alternately, you can select the horizontal axis and press CTRL+1.  Next, change the minimum to Fixed with a value of 43,136 as you see here:





Format Axis Fixed Minimum Value
Format Axis Fixed Minimum Value

Your chart should now look like this:

Add Resource Names to Excel Gantt Chart Tasks 9 2nd Horizontal Axis Fixed Minimum
Add Resource Names to Excel Gantt Chart Tasks 9 2nd Horizontal Axis Fixed Minimum

6) Modify Secondary Axis Categories

Here is the little-known technique that we will use to create our resource name labels in the Excel Gantt Chart.  What we want to do is to modify the secondary axis categories from Plan, Develop, Test and Deploy to the resource names by task.  In order to do this, follow these steps:

A) Select the Chart, Click on the “Design” Ribbon, then click on the “Select Data” button in the “Data” group to open the “Select Data Source” dialog box:

Select Data Button on the Design Ribbon
Select Data Button on the Design Ribbon

B) Select the “Resource Filler” series in the “Legend Entries (Series) area, then click the “Edit” button in the “Horizontal (Category) Axis Labels” area:

Select Data Source Dialog Box to Change 2nd Axis Categories
Select Data Source Dialog Box to Change 2nd Axis Categories

C) Finally, select the range that you have for your resource names by task.  In our case, it is F3:F11 as you see here.  Then press OK on all dialog boxes to finalize your updates to the secondary axis labels.



Change 2nd Axis Labels for Gantt Chart Resource Names
Change 2nd Axis Labels for Gantt Chart Resource Names

Your chart should now look like this with the resources by task appearing on the secondary axis:

Add Resource Names to Excel Gantt Chart Tasks 10 New 2nd Horizontal Axis Labels
Add Resource Names to Excel Gantt Chart Tasks 10 New 2nd Horizontal Axis Labels

7) Change Secondary Axis Chart Type

In order to make our labels appear at the end of each Gantt task, we need to change the chart type of the “Resource Filler” series on the secondary axis to a Clustered Bar Chart.  This will allow us to place a label on the end of the bar as opposed to only on the inside of a stacked bar chart.

To do this, first select the chart, then select the “Resource Filler” series.  Next, select the “Design” Ribbon and then choose the “Change Chart Type” button in the “Type” group as you see here:

Change Chart Type Menu
Change Chart Type Menu

Finally, select the Clustered Bar Chart type as you see here:

Change Chart Type to Clustered Bar Chart
Change Chart Type to Clustered Bar Chart

Your chart will now look like this.  It may look strange as the series is now being plotted along with the resource names, but don’t worry, when we delete the Secondary Axes, it will align to the Phase and Task labels on the left.





Add Resource Names to Excel Gantt Chart Tasks 11 Change Chart Type of Resource Filler
Add Resource Names to Excel Gantt Chart Tasks 11 Change Chart Type of Resource Filler

8) Add Chart Labels

Finally, we are at a point that we can Add Resource Names to Excel Gantt Chart Tasks by adding labels to the “Resource Filler” chart series and then modify them to display the category labels instead of the value.

A) Add Outside End Chart Labels

To do this, first select the chart, then select the “Resource Filler” Chart Series.  Next, select the “Layout” Ribbon, then choose “Data Labels” Button from the “Labels” Group and finally, click on the “Outside End” Option as you see here

Add Outside End Data Labels to Resource Filler Series
Add Outside End Data Labels to Resource Filler Series

Your chart should now look like this:

Add Resource Names to Excel Gantt Chart Tasks 12 Add Labels to Resource Filler Series
Add Resource Names to Excel Gantt Chart Tasks 12 Add Labels to Resource Filler Series

B) Change Data Labels to Categories

Excel default for data labels in a chart will display Values.  We need to change ours to Categories.

To do that, first, select the chart, then double-click on any of the data labels we just added like the “7/2/2018” at the bottom middle of the chart or select it and press CTRL+1 to bring up the “Data Labels” dialog box.

Then “uncheck” the “Values” checkbox and check the “Categories” checkbox as you see here:

Format Data Labels Dialog Box for Categories instead of Values
Format Data Labels Dialog Box for Categories instead of Values

Your chart should now look like this with the Resource Names now appearing in the chart as a data label.  Don’t worry, the chart will sort itself out when we delete the secondary axis in 2 steps:

Add Resource Names to Excel Gantt Chart Tasks 13 Change Labels to Display Categories
Add Resource Names to Excel Gantt Chart Tasks 13 Change Labels to Display Categories

9) Modify Secondary Series Fill

We are almost done.  We have left the “Resource Filler” series as an automatic solid fill of green this whole time so that we would be able to select the series as needed.  But we don’t need to see it anymore.  So let’s hide it from the chart by changing the Fill Option to “No Fill”.

To do this, first, select the chart, then double click on the green “Resource Filler” data series or press CTRL+1 to open the “Format Data Series” dialog box.  Then change the Fill type to “No Fill” as you see here:

Format Data Series No Fill
Format Data Series No Fill

Your chart will now look like this:





Add Resource Names to Excel Gantt Chart Tasks 14 No Fill Resource Filler Series
Add Resource Names to Excel Gantt Chart Tasks 14 No Fill Resource Filler Series

10) Chart Clean Up

Last but not least is to clean the chart up of unneeded items and this will create the final look and feel you were looking for with the Gantt Chart Format in Excel.  This is pretty simple.  First select your chart, then select each of these items and press the delete key.

A) Delete Legend

B) Delete Secondary Vertical Axis

C) Delete Secondary Horizontal Axis

Your final chart should now look like this:

Add Resource Names to Excel Gantt Chart Tasks 15 Final Chart
Add Resource Names to Excel Gantt Chart Tasks 15 Final Chart

You can use this technique to show any task information in your Gantt charts that you wish, such as Percent Complete (% Complete), Assigned Resources, Start Date and Finish Date.  The possibilities are only limited by your imagination.

Check out Some Other Tutorials Related to this Article:

https://www.exceldashboardtemplates.com/FirstAndLastDates

https://www.exceldashboardtemplates.com/FixMissingMulitLevelCategoryLabelOption

https://www.exceldashboardtemplates.com/GanttChart7EasySteps

Video Demonstration

Check out this Video tutorial on the techniques presented above.





Sample File Download

Click here to Download the Free Sample Excel Template File:
How-to-Add-Resource-Names-to-Excel-Gantt-Chart-Tasks.xlsx

Your Thoughts and Comments

This is a great trick to add more detail to any line chart, column chart or bar chart within Excel.  Do you think that Excel should default to show all of the secondary axes?  Do you like this Excel Gantt Chart technique?  Let me know in the comments below.

Also, make sure you sign up for the newsletter so that you get the notification of the release of the video on how you can do this more easily in Excel 2013 and Excel 2016 in the next post.

Steve=True





2 COMMENTS

  1. Hi I am trying to have the cell highlight by colour if
    the date of birth listed is between 30/04/2014 & 30/04/2018 = Green
    & the date of birth listed is between 30/04/2015 & 30/04/2018 = Orange
    & the date of birth listed is equal to or less that 30/04/2013 Red

    • Hi Jo, it looks like your dates overlap so it may not work as you would like. i.e. both Green and Orange overlap to 2018. However, i would recommend that you use Conditional Formatting from the Home Ribbon and use the Highlight Cells Between option. You will have to do it 3 times on the same range. If you need a formula, you can use this for the between ones: =AND($A1>41759,$A1<43220) [assumes your data is in column A]. Also I have converted the dates to a number to see the value of 30/4/2014 and 4/30/2018.

LEAVE A REPLY

Please enter your comment!
Please enter your name here