<

Excel Gantt Chart

Published on
7,697 Points
1,197 Views
5 Endorsements
Last Modified:
Approved
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
Comment
Author:x-men
2 Comments
 
LVL 66

Expert Comment

by:Jim Horn
Nice job illustrating.  Voted Yes.
0
 

Expert Comment

by:kev_hinds
Pretty decent.  I am sure it could come in handy some day.  Thanks!
0

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