Excel Gantt Chart

x-menIT super hero
CERTIFIED EXPERT
Published:
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 ;))

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

ee2.PNG
  • With the new chart object active, one needs to select the Data:
ee3.PNG
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...

ee4.PNG
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"

ee10.PNG
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á:

ee11.PNG 
5
1,598 Views
x-menIT super hero
CERTIFIED EXPERT

Comments (2)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Nice job illustrating.  Voted Yes.
Pretty decent.  I am sure it could come in handy some day.  Thanks!

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community