<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Excel Gantt Chart

Published on
7,776 Points
1,276 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 67

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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month