Fleetwood Posted June 4, 2013 Posted June 4, 2013 Hi all, If anybody has a spare Gantt Chart template for Excel I'd greatly appreciate it! (I'm looking for one that's a daily timeline, hopefully split by weeks and displaying the day 'Week 1' 'Week 2' 'M T W T F' 'M T W T F' etc. Or if anybody has any other suggestions that'd be fab. Ta.
LosOjos Posted June 4, 2013 Posted June 4, 2013 Don't have a template, but there are some details on the Office website on how to make a basic Gantt chart from a stacked bar chart which might get you started: Create a Gantt chart in Excel - Excel - Office.com Or there's a free template here (can't vouch for it I'm afraid. never used it): Free Gantt Chart Template for Excel Does it have to be done in Excel?
Fleetwood Posted June 4, 2013 Author Posted June 4, 2013 Don't have a template, but there are some details on the Office website on how to make a basic Gantt chart from a stacked bar chart which might get you started: Create a Gantt chart in Excel - Excel - Office.com Or there's a free template here (can't vouch for it I'm afraid. never used it): Free Gantt Chart Template for Excel Does it have to be done in Excel? Thanks and no it doesn't have to be in Excel. He (HT) said Excel (he has a printed Gantt Chart from a year ago that was apparently done in Excel) but I'm sure he won't refuse something alternative, as long as it's easy/ier to use
pcstru Posted June 4, 2013 Posted June 4, 2013 I'd find yourself some open source project planning software such as taskjuggler (just an example - not particularly a recommendation). Gannt charts are not things in themselves (as such) - they are representations of operational networks (so called precedence or arrow methods, although I doubt many people have even heard of ADM these days). If you use them in anger, you need the network to make life easy when things (dates or even whole activities) change. I guess you could do a simple Gannt chart in excel with conditional formatting. So Col A would be description, B & C would be start and end dates. row 1 would have the dates and then columns D-ZZ could be conditionally formatted to 'light up' if their column date in row 1 is between the dates in the appropriate row in column B & C. Should take 10 mins to knock up ... in theory.
pcstru Posted June 5, 2013 Posted June 5, 2013 (edited) Should take 10 mins to knock up ... in theory. Seemed like a challenge. GanntSmlB.xlsx Edited June 5, 2013 by pcstru 1
plexer Posted June 5, 2013 Posted June 5, 2013 I've used the one that was linked too and it worked fine. Ben
Fleetwood Posted June 5, 2013 Author Posted June 5, 2013 (edited) @pcstru that works great, thanks if I wanted to colour/'key' certain tasks/bars I assume that's also conditional formatting? I.E.: The A column has 'areas' (Finance, Marketing, Admin etc.) - could I somehow formulate so a bar will be red if any of the cells in A column contain 'Finance', blue for 'Marketing' etc. Edited June 5, 2013 by Fleetwood
pcstru Posted June 5, 2013 Posted June 5, 2013 @pcstru that works great, thanks if I wanted to colour/'key' certain tasks/bars I assume that's also conditional formatting? I.E.: The A column has 'areas' (Finance, Marketing, Admin etc.) - could I somehow formulate so a bar will be red if any of the cells in A column contain 'Finance', blue for 'Marketing' etc. Sure. You can do it directly by adding a formula for each colour (as in the attached). Or if it will get much more complicated, I'd probably look at doing the analysis on the GLog tab and generating different numbers which then drives the conditional formatting. I think the latter is just a bit more ... manageable.GanntSmlC.xlsx 1
Fleetwood Posted June 5, 2013 Author Posted June 5, 2013 (edited) That's great @pcstru, thanks a bunch! Just a final question (I'll stop after this haha,) the HT wants to have 'categories' so what I've done is just replaced some cells with a merged+centred subheading. Unfortunately this is going to be worked on as a 'may add more later' under each section, but I've noticed that if I try and insert a new row it does not follow through to the GLog sheet (I.E. it skips it, then moves down... if that makes sense.) See below, you'll get what I mean. (clicky) I've tried doing the CTRL+click on the Glog tab so it's also selected, then copying a 'blank' row within the formula to the category - but what happens is the row ABOVE the one I copy ends up duplicating across both of them. Edited June 5, 2013 by Fleetwood
pcstru Posted June 5, 2013 Posted June 5, 2013 You can get rid of the GLog sheet entirely and change the formula(s) in the conditional formatting to read like : =AND(AND(G$2>=$D4,G$2<=$E4),$B4="Marketing") Just do that in the G4 cell then copy that cell, select the block of the chart area and use paste special and select "formats" from the list. Now when you insert a row, the formats should copy from the row where you did the insert. GLog simply acts as an intermediate scratchpad. It would make it easier to do clever(er) things like different colours for progress or better formatting of the bars. I find editing conditional formats a bit of a pain (as soon as you move the cursor it starts putting in cell references), so I like to keep the calculations away from that. 1
Fleetwood Posted June 5, 2013 Author Posted June 5, 2013 (edited) Oh I see! I'll give that a go and see what happens. Thanks for all your help on this, life saver lol! EDIT: Works perfectly! Cheers again. Edited June 5, 2013 by Fleetwood
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now