How To Change Gantt Chart Time Scale In Excel

Monday, April 2, 2018
Peltier Technical Services, Inc., Copyright © 2021, All rights reserved.

Gantt charts are a special kind of bar chart used in scheduling and program management. A set of tasks or activities is listed along the left hand axis, and the bottom axis shows dates. Horizontal bars indicate when each task begins and ends, and which tasks are in progress at any given time. The simplest kind of Excel Gantt chart involves a worksheet range, with the tasks listed in the first column, and dates (for example, week beginning or ending dates) in the top row. If a date along the top falls between the start and end dates for that task, the cell in the same row as the task is shaded a different color.

It is not too difficult to create a regular bar chart showing this same amount of detail.

And once you've decided to make a chart, you can add more advanced embellishments to show more details.

This tutorial will show you how to make all types of Gantt chart in Microsoft Excel.

Simple Gantt Charts in the Excel Worksheet

Let's start with this simple Gantt chart data. Listed are tasks, beginning and ending dates, and duration. For this simple chart, we don't even need the duration column.

We can expand the range to the right, with weekly dates in the top row of the added columns.

We can fill the cells in manually, of course, but it's nice to set up conditional formatting so the colors update whenever the beginning or ending dates change. For each cell, the formula checks whether the date at the top of the column is greater than or equal to (on or after) the starting date and less than (before) the ending date for that row. If so, then the cell is formatted as shown.

The result is a quick little graphic, easily done right in the worksheet, so the Gantt chart timelines line up with the task rows.

It's not fancy, but sometimes all you need is something quick.

Simple Excel Gantt Charts

To make a Gantt chart from an Excel bar chart, we can start with the following simple data: Task, Start Date, End Date, and Duration.

Since dates and duration are interrelated, usually Start is entered, and either End or Duration. If Start and Duration are entered, End is calculated as =Start+Duration. If Start and End are entered, Duration is calculated as =End-Start.

The small range to the right will come in handy  when fixing our date axis limits in the bar chart. The upper blue-shaded cell contains a formula that returns the minimum of the Start Dates, and the cell above this is seven days earlier. The lower blue-shaded cell has a formula that returns the maximum of the End Dates, and the cell below that is seven days later. The second column shows these dates in General number format, so we see just the serial number of the date (the number of days since 1/1/1900); we will use the yellow shaded cells as the limits of the chart's date axis.

Before we make the chart, we should apply a special number format to the Start Dates so they fit closer along the axis. Select the dates, click Ctrl+1 to format them, and on the Number tab, choose Custom, and in the Type box, enter d (for day), type Ctrl+J to enter a line feed right in the format, then enter mmm (for three letter month abbreviation) and press Enter. This produces two-line dates with the day number on the first line and the month abbreviation on the second. We can change this back later, but it's easier to create this format in the worksheet than to try to achieve it in the chart. Also, clear the top left cell, so Excel plots the data the way we need it plotted.

To create the simple Gantt chart, we start by selecting the columns with Task, Start Date, and Duration, and inserting a stacked bar chart. Notice the date format which was taken from the worksheet range.

The tasks are listed in the opposite order, so we need to format the vertical axis (select it and press Ctrl+1 or simply double click on it). Check 'Categories in Reverse Order' and 'Horizontal Axis Crosses at Maximum Category' to correct this. See Why Are My Excel Bar Chart Categories Backwards? and Excel Plotted My Bar Chart Upside-Down to read all about it.

Now we need to format the date axis, so select the axis and press Ctrl+1. Change the minimum and maximum to 43184 and 43289 (from the special range we set up besides the main data range), and set the major unit to 7. Under Number, uncheck 'Linked to Source', and then you can reset the date formats in the data range.

Finally, a little clean up. Format the Start bars to have no fill color, and give the Duration bars an appropriate fill. I like to use 25 or 50% transparency, so the fill isn't overpoweringly dark and so the gridlines show through. Also delete the legend.

That wasn't too hard now, was it?

Advanced Excel Gantt Charts

Now that we know how to make a Gantt chart with a regular Excel chart, we can think of ways to enhance it. How about duration bars that show the fraction complete? How about milestone markers coded to show finished and unfinished tasks? And maybe a vertical line to indicate a date of interest?

The data range is a bit more complicated. The user enters Start Date, % Complete, and either Duration or End Date. % Complete could also be computed from the Start Date, End Date, and the date of interest (indicated in the block labeled "Vertical"). If the user enters Duration, the End Date formula in G3 is =C3+F3. If the user enters End Date, the Duration formula in F3 is =G3-C3. Duration is split into Done and Not Done components with two simple formulas. The formula in D3 is =K3*F3 The formula in E3 is =(1-K3)*F3 The Milestones formula in J3 is =(ROW()-ROW(J$2)-0.5)/ROWS(J$3:J$11), which provides the milestone series with a vertical position to align with the bars. The Finished milestones formula in H3 is =IF(K3=1,J3,NA()) The Unfinished milestones formula in I3 is =IF(K3<1,J3,NA()) All of these formulas are copied down to row 11.

The block to the left below the main table will help with setting the date axis scale later. Cell C14 has the formula =MIN(C3:C11) Cell C15 has the formula =MAX(G3:G11) Cell C13 has the formula =C14-7 Cell C16 has the formula =C15+7 Cells D13 through D16 link to C13 through C16, but are formatted as General to show the serial number of the date.

The date listed next to 'Vertical' is where we will draw a vertical line indicating our interest in that date. Before we insert our chart, let's apply a temporary special number format to the Start Dates in C3:C11. Select the range, click Ctrl+1 to format the cells, and on the Number tab, choose Custom, and in the Type box, enter d (for day), type Ctrl+J to enter a line feed right in the format, then enter mmm (for three letter month abbreviation), and press Enter. We will change this back later, but it's easier to create this in the worksheet than to try to achieve it in the chart. Note also that cell B2 has been cleared, to help Excel understand how to plot the data.

The chart starts out pretty much like the simple Gantt chart above. Select the first four columns containing data for Tasks, Start Date, Done, and Not Done, and insert a stacked bar chart.

Format the vertical axis (select it and press Ctrl+1 or simply double click on it). Check 'Categories in Reverse Order' and 'Horizontal Axis Crosses at Maximum Category' to put the tasks in the order we want.

Now change the time scale on the horizontal axis. Change the minimum and maximum to 43184 and 43289 (from C13 and C16 in the special range we set up below the main data range), and set the major unit to 7. Under Number, uncheck 'Linked to Source', and then you can reset the date formats in the data range.

Now we can format the bars. I've made the Start series invisible by using no fill color; I've colored the Done bars blue and the Not Done bars orange, with 25% transparency on each so the gridlines appear through them.

At this point, we depart from the procedure for a simple Excel Gantt chart, by adding the Milestone data. Select and copy G2:I11 (End Date, Finished, and Unfinished), select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, and add the data as new series, with series in columns, category labels in first column, and series names in first row. The data is added as new bars stacked on the Done and Not Done bars.

Right click on any series in the chart, and choose Change Series Chart Type from the pop-up menu. Change Finished and Unfinished to XY Scatter. Excel automatically assigns them to the secondary axis group and adds a secondary vertical axis.

We need to switch the new axis: select it and press Ctrl+1, and check 'Values in Reverse Order'. We also need to lock the axis min and max to 0 and 1. Under Axis Options, the Minimum box shows 0.0, and the Maximum box shows 1.0. Change these to 0 and 1, which does not change the chart, but the Auto label next to each box changes to Reset.

Now we can hide the secondary vertical axis (the one we just reversed). While formatting it, under Labels, change Label Position to None.

Now let's format those milestones. I've used black-bordered diamonds for both, with black fill for Finished and white fill for Unfinished.

Now let's add the data for the vertical reference date line. Select and copy G13:H14 (which includes the "Vertical" label), select the chart, and use Paste Special from the Paste dropdown on the Home tab of the ribbon, to add the data as a new series, with series in columns, category labels in first column, and series names in first row. The data is added as a new single point along the top of the chart.

Select this new point, and choose Error Bars from the menu you get when you click the plus icon floating beside the chart. Microsoft calls these little icons "Skittles".

You might not even see them, but the point now has vertical and horizontal error bars. Selecting them is a bit of a challenge: you can right click on the chart, and click the Chart Element dropdown to see all of the chart elements.

Or you can click the Current Selection dropdown at the top left corner of the Chart Tools > Format tab of the ribbon. Select the 'Series "Vertical" X Error Bars', and press the Delete key. Then select the 'Series "Vertical" Y Error Bars', and press Ctrl+1 to format them. Select Plus (the bar will point downward, but we just reversed the order of the axis, remember?), No Cap, and assign it a Fixed Value of 1. Then format the line color (I used a green lin). Select the Vertical data point again, and using the plus skittle, check Data Labels. Format the data label to show X value, not Y value, and assign a number format of "d mmm". The space between day and month is fine, no need to try to insert that pesky linefeed.

And now a little more clean-up. I made the error bar thicker, gave the "Vertical" data label ("1 Jun") a white fill with 25% transparency, and formatted the Vertical to use no marker at all. I deleted the legend, because it's no longer needed.

There are a few additional labeling embellishments you might use, shown below. I've hidden the task labels on the vertical axis (change Label Position to None), and added data labels to the invisible Start Date bar chart series. I am using the Category Name option instead of Value, and the Inside End Label Position. This puts the task names to the left of the visible floating bars. Then I added data labels to the Finished and Unfinished markers. In both cases, I changed to the Value from Cells option, using the % Complete values in K3:K11.

I've given all of these labels a white fill background with 25% transparency.

Articles Related to Gantt Charts on this Blog

• Gantt Charts in Microsoft Excel
• Gantt Chart with Nice Date Axis
• Error Bar Gantt Chart
• Repeated Gantt Chart to Track Players' Ice Time
• Gantt Chart for Repeated Tasks
• Easier Gantt Chart for Repeated Tasks
• Gantt Chart with Repeated Tasks via Excel XY Chart

How To Change Gantt Chart Time Scale In Excel

Source: https://peltiertech.com/excel-gantt-charts/

Posted by: costadereddeedly.blogspot.com

Share this post