Excel Gantt Chart

Published on
7,697 Points
5 Endorsements
Last Modified:
Where used to see Gantt charts for illustrating project timelines, but what if I wanted to visualize passed timed events? Here's how.
I came across the need to easily visualize the time window for my database backups, and thought that displaying them through a Gantt chart would five a nice and easy way to illustrate them.
However, being a lazy data scripting guy, I wasn't found of going through all the trouble of drawing the whole thing, there should be a way of clicking a few buttons and make magic, and after a few tips & trick out of the internet, and a lot of trial  & error, I finally made it happen...and here is how.

Let us start by assuming that we've already imported the data to excel (check this article on how to get to this point ;))

INSERT a 2-D Stacked Bar chart on your excel sheet

  • With the new chart object active, one needs to select the Data:
In the "Select Data Source" window, Add Legend Entries (Series) the backup_start_date and the Duration. Notice that the first field is the name of the series, and the second is the values. This Values filed has a default value of ={1}, make sure you delete it so you wont end up with something like ={1}+Sheet1!$B$2...

Edit the "Horizontal (Category) Axis Labels" to be the database_name
ee5.PNGEnding up with the following selected data source:

ee6.PNGAnd the corresponding stacked bar chart

ee7.PNGAre we there yet? not yet, almost.

Now for some cosmetic magic:
Select the Series "backup_start_date", either directly on the chart, or the "FORMAT" menu under "CHART TOOLS" - "Format Selection"
ee8.PNGee9.PNGAnd choose "No Fill" on the "Format Data Series"

Finally, to adjust the start of the graph, copy the lowest start date to another cell (9-11-15 3:58) and format it as Number (42258.165787037) and take note.

Select the "Horizontal (Value) Axis", again, either directly clicking it on the chart, or through the "FORMAT" menu under "CHART TOOLS"- "Format Selection".

Under "AXIS OPTIONS", change the "Bounds" - "Minimum" value to the number noted earlier, et voilá:

LVL 66

Expert Comment

by:Jim Horn
Nice job illustrating.  Voted Yes.

Expert Comment

Pretty decent.  I am sure it could come in handy some day.  Thanks!

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Join & Write a Comment

This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month