This is a guest post by Daniel Ferry of Excelhero.com.
Have you ever wanted to fetch live stock quotes from excel? In this post we will learn about how to get stock quotes for specified symbols using macros.
One method that has worked well for my clients can be implemented with just a few lines of VBA code. I call it the ActiveRange.
An ActiveRange is an area on a worksheet that you define by simply entering the range address in a configuration sheet. Once enabled, that range becomes live in the sense that if you add or change a stock symbol in the first column of the range, the range will automatically (and almost instantly) update. You can specify any of 84 information attributes to include as columns in the ActiveRange. This includes things such as Last Trade Price, EBITDA, Ask, Bid, P/E Ratio, etc. Whenever you add or change one of these attributes in the first row of the ActiveRange, the range will automatically update as well.
Sound interesting, useful?
In this post, you can learn how to use excel macros to fetch live stock quotes from Yahoo! Finance website. It is also going to be a crash course in VBA for the express purpose of learning how the ActiveRange method works so that you can use it yourself.
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Background – Understanding The Stock Quotes Problem:
The stock information for the ActiveRange will come from Yahoo Finance. A number of years ago, Yahoo created a useful interface to their stock data that allows anyone at anytime to enter a URL into a web browser and receive a CSV file containing current data on the stocks specified in the URL. That’s neat and simple.
But it gets a little more complicated when you get down to specifying which attributes you want to retrieve [information here]. Remember there are 84 discreet attributes available. Under the Yahoo system, each attribute has a short string Tag Code. All we need to do is to concatenate the string codes for each attribute we want and add the resulting string to the URL. We then need to figure out what to do with the CSV file that comes back.
Our VBA will take care of that and manage the ActiveRange. Excel includes the QueryTable as one of its core objects, and it is fully addressable from VBA. We will utilize it to retrieve the data we want and to write those data to the ActiveRange.
Before we start the coding we need to include two support sheets for the ActiveRange. The first is called “YF_Attribs”, and as the name implies is a list of the 84 attributes available on Yahoo Finance along with their Yahoo Finance Tag Codes. The second sheet is called, “arConfig_xxxx” where xxxx is the name of our sheet where the ActiveRange will reside. It contains some configurable information about the ActiveRange which our VBA will use.
All of the VBA code for this project will reside inside of the worksheet module for the sheet where we want our ActiveRange to be. For this tutorial, I called the sheet, “DEMO”.
Writing the Macros to Fetch Stock Quotes:
Press ALT-F11 on your keyboard, which will open the VBE. Double click on the DEMO sheet in the left pane. We will enter out code on the right. To begin with, enter these lines:
Private rnAR_Dest As Range
Private rnAR_Table As Range
Private stAR_ConfigSheetName As String
Always start a module with Option Explicit. It forces you to define your variable types, and will save you untold grief at debugging time. In VBA each variable can be one of a number of variable types, such as a Long or a String or a Double or a Range, etc. For right now, don’t worry too much about this – just follow along.
Sidebar on Variable Naming Conventions
Variable names must begin with a letter. Everyone and their brother seems to have a different method for naming variables. I like to prefix mine with context. The first couple of letters are in lower case and represent the type of the variable. This allows me to look at the variable anywhere it’s used and immediately know its type. In this project I’ve also prefaced the variables with “AR_” so that I know the variable is related to the ActiveRange implementation. In larger projects this would be useful. After the underscore, I include a description of what the variable is used for. That’s my method.
In the above code we have defined three variables and their types. Since these are defined at the top of a worksheet module, they will be available to each procedure that we define in this module. This is known as scope. In VBA, variables can have scope restricted to a procedure, to a module (as we have done above), or they can be global in scope and hence available to the entire program, regardless of module. Again we are putting all of the code for this project in the code module of the DEMO worksheet. Every worksheet has a code module. Code modules can also be added to a workbook that are not associated with any worksheet. UserForms can be added and they have code modules as well. Finally, a special type of code module, called a class module, can also be added. Any global variables would be available to procedures in all of these. However, it is good practice to always limit the scope of your variables to the level where you need them.
In that vein, notice that the three variables above are defined with the word Private. This specifically restricts their scope to this module.
Every worksheet module has the built-in capability of firing off a bit of code in response to a change in any of the sheet’s cell values. This is called the Worksheet_Change event. If we select Worksheet from the combo box at the top and Change in the other combo box, the VBE will kindly define for us a new procedure in this module. It will look like this:
End Sub
Notice that by default this procedure is defined as Private. This is good and as a result the procedure will not show up as a macro. Notice the word Target near the end of the first line. This represents the range that has been changed. Place code between these two lines so that the entire procedure now looks like this:
The Heart of our Excel Stock Quotes Code – Worksheet_Change()
ActivateRange
If Worksheets(stAR_ConfigSheetName).[ar_enabled] Then
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
If Target.Column <> rnAR_Dest.Column And Target.Row <> rnAR_Dest.Row Then
PostProcessActiveRange
Exit Sub
End If
ActiveRangeResponse
End If
End Sub
That may look like a handful but it’s really rather simple. Let’s step through it. The first line is ActivateRange. This is the name of another sub-procedure that will be defined in a moment. This line just directs the program to run that sub, which provides values to the three variables we defined at the top. Again, since those variables were defined at the top of the module, their values will be available to all procedures in the module. The ActivateRange procedure gives them values.
Next we see this odd looking fellow:
If Intersect(Target, rnAR_Dest) Is Nothing Then Exit Sub
All this does is check to see if the Target (the cell that was changed on the worksheet) is part of our ActiveRange. If it is the procedure continues. If it’s not, the procedure is exited.
The next line checks to see if the cell that was changed is in the first column or first row of the ActiveRange. If it is, the post processing is skipped. If the change is any other part of the ActiveRange, another sub-procedure (defined below) is run to do some post processing of the retrieved data, and then exits this procedure.
If the cell that changed was in the first column or the first row, the program runs another sub-procedure, called ActiveRangeResponse, which is also defined below. ActiveRangeResponse builds the URL for YF, deletes any previous QueryTables related to the ActiveRange, and creates a new QueryTable as specified in our configuration sheet.
That’s it. The heart of the whole program resides here in the Worksheet_Change event procedure. It relies on a number of other subprocedures, but this is the whole program. When a change is made in the ActiveRange’s first column (stock symbols) or its first row (stock attributes), ActiveRangeResponse runs and our ActiveRange is updated.
Understanding other sub-procedures that help us get the stock quotes:
So let’s look at those supporting subprocedures. The first is ActivateRange:
stAR_ConfigSheetName = “arConfig_” & Me.Name
Set rnAR_Dest = Me.Range(Worksheets(stAR_ConfigSheetName).[ar_range].Value)
Set rnAR_Table = rnAR_Dest.Resize(1, 1).Offset(1, 1)
Worksheets(stAR_ConfigSheetName).[ar_YFAttributes] = GetCurrentYahooFinancialAttributeTags
End Sub
Again, all this does is give values to our three module level variables. In addition it builds the concatenated string of YF Tag Codes required for the URL. It does this by calling a function that I’ve defined at the very bottom of the module, called GetCurrentYahooFinancialAttributeTags.
The next subprocedure is ActiveRangeResponse:
Dim vArr As Variant
Dim stCnx As String
Const YAHOO_FINANCE_URL = “http://finance.yahoo.com/d/quotes.csv?s=[SYMBOLS]&f=[ATTRIBUTES]”
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count – 1, 1).Offset(1))
stCnx = Replace(YAHOO_FINANCE_URL, “[SYMBOLS]”, Replace(WorksheetFunction.Trim(Join(vArr)), ” “, “+”))
stCnx = Replace(stCnx, “[ATTRIBUTES]”, Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
AddQueryTable rnAR_Table.Resize(UBound(vArr)), “URL;” & stCnx
End Sub
Notice that here we have variables defined at the top of this procedure and consequently their scope is limited to this procedure only. This means that we could have the same variable names defined in other procedures but those variables would not be related to these and would have completely different values.
Next notice that we have defined a constant. This is good practice, as it forces us to specify what the constant value is by naming the constant. I could have just used the value where I later use the constant, but then the question arises as to what is this value and where did it come from. Here I have named the value, YAHOO_FINANCE_URL, removing all doubt as to its purpose.
The next line is this:
vArr = Application.Transpose(rnAR_Dest.Resize(rnAR_Dest.Rows.Count - 1, 1).Offset(1))
and it deserves some explanation. Let me back up by saying that whenever we write or read multiple cells from a worksheet we should always try to do it in one go, rather than one cell at a time. The more cells involved the more important this is. Otherwise we pay a massive penalty in processing time. One of the best optimization techniques available is to replace code that loops through cell reads/writes and replace it with code that reads/writes all the cells at once. It can literally be hundreds to thousands of times faster.
Here we are interested in getting the list of all of the stock symbols in the first column of the ActiveRange. So how do we get them in one shot? We use something called a variant array. Notice that we defined vArr at the top of this procedure. A variant array is a special kind of variable that holds a list of values and it DOES NOT CARE what variable types those values are. This is important when retrieving data from a sheet because the data could be numbers, text, Boolean (True or False), etc. Variants are powerful, but they are much slower than other variable types, such as a Long for numeric data for example. However, in the case of retrieving or writing large chunks of data from/to a sheet the slight penalty of the variant is dwarfed by the massive increase in the speed of data transfer.
It’s very simple to retrieve range data (regardless of the size) into a variant array. All you do is:
v = range
where v is defined as a variant and range is any VBA reference to a worksheet range. And magically all of the values in that range are now in v. Note that v is not connected to the range. A change in any of v’s values does not propogate back to the range, and likewise a change to the range does not make it’s way to v all by itself. v will ALWAYS be a two-demensional array. The first dimension is the index of the rows, the second dimension is the index of the columns. So v(1,1) will refer to the value that came from the top left cell in the range. v(6,9) will hold the value that came from the cell in the range at row 6 and column 9.
For most circumstances this two-dimensional format is fine. But we are only retrieving one column of stock symbols. The procedure will still give us a two-dimensional array, with the column dimension being only 1 element wide. This is a shame because VBA has a wonderful function called Join that allows you in one step (no loop) to concatenate every element of an array into a string. You can even specify a custom string to delimit (go in-between) each element in the output string. The problem is that Join only works on single dimensioned arrays 🙁
But there’s always a way, right? We can use the Application.Transpose method on the 2-D array and presto we get a 1-D array. The rest of the line just specifies what range (the stock symbols) to grab.
The next two lines are:
stCnx = Replace(YAHOO_FINANCE_URL, "[SYMBOLS]", Replace(WorksheetFunction.Trim(Join(vArr)), " ", "+"))
stCnx = Replace(stCnx, "[ATTRIBUTES]", Worksheets(stAR_ConfigSheetName).[ar_YFAttributes])
Again a handful, but all we are doing here is replacing the monikers, [SYMBOLS] and [ATTRIBUTES] in the YAHOO_FINANCE_URL constant with the list of stock symbols (delimited by a plus sign) and the string of attributes.
In the final line of the procedure:
AddQueryTable rnAR_Table.Resize(UBound(vArr)), "URL;" & stCnx
we are running another subprocedure called, AddQueryTable and we are telling it where to place the new QueryTable and providing the connection string for the QueryTable, which in this case is the YF URL that we just built.
Nothing unusual happens in the AddQueryTable sub. It just deletes any existing AR related QueryTables and adds the new one according to the options in the configuration sheet.
The PostProcessActiveRange sub is interesting:
If rnAR_Dest.Columns.Count > 2 Then
Application.DisplayAlerts = False
rnAR_Table.Resize(rnAR_Dest.Rows.Count).TextToColumns Destination:=rnAR_Table, DataType:=xlDelimited, Comma:=True
Application.DisplayAlerts = True
Worksheets(stAR_ConfigSheetName).[ar_LocalTimeLastUpdate] = Now
End If
End Sub
Processing Yahoo Finance Output using Query Table & Text-Import Utility:
As mentioned before the data from YF comes back as a CSV file. The QueryTable dumps this into one column. If you were only retrieving one attribute for each stock this would be fine as is. However, two or more attributes is going to result in unwanted commas and multiple attribute values squished into the first column of the QueryTable output. Unfortunately this is poor design by Microsoft, especially when you consider that the QueryTable does not behave like this when it is retrieving SQL data or opening a Text file from disk. You can actually specify this operation to be a text file and it will properly spread the output over all of the columns. To do so, you specify the disk location as being the URL of the YF CSV file, but as Murphy would have it, it’s unbelievably slow and pops up a status dialog as it slowly retrieving the CSV. Using the URL instruction instead of the TEXT instruction at the beginning of the connection string is incredibly fast in comparison, but dumps all of the data into the first column.
So what to do? We’ll just employ Excel’s built-in TextToColumns capability and bam, our data is where we want it.
Our finalized stock quotes fetcher worksheet should look like this:
Download Excel Stock Quotes Macro:
Click here to download the excel stock quotes macro workbook. It will be much easier to follow this tutorial if you refer to the workbook.
Final Thoughts on Excel Stock Quotes
The ActiveRange technique is quite versatile. It can be implemented with other data sources such as SQL, or even lookups to other Excel files, or websites.
In this example it provides a nice way to easily track whatever stocks you may have interest in and up to 84 different attributes of those stocks. You can enable and disable the activeness of the ActiveRange on the fly. You can set the AR to AutoRefresh the data at periods that you set or to not refresh at all.
This is a basic implementation. For example, changing the AutoRefresh setting will have no effect until a new QueryTable is built. That won’t happen until you also add or change a stock symbol or add or change an attribute. An easy enhancement would be to add a little code to the arConfig_DEMO code module to respond to changes to the ar_AutoRefresh named range cell.
Another enhancement would be to eliminate the slight flicker of the update by moving the QueryTable destination to the arConfig_DEMO and then doing the TextToColumns with the destination set to the DEMO sheet. In an effort to simplify this tutorial I have left these easy enhancements as an exercise for you to implement.
Have a question or doubt? Please Ask
Do you have any questions or doubts on the above technique? Have you used ActiveRange or similar implementations earlier? What is your experience? Please share your thoughts / questions using comments.
I read Chandoo.org regularly and will be monitoring the post for questions. But you can also reach me at my blog:
Further References & Help on Excel Stock Quotes [Added by Chandoo]
- Fetching Stock Quotes using Research Pane
- Stock Portfolio Tracker using Google Docs
- QueryTable Object Model & Properties
- Using QueryTable to Generate Dynamic Reports
- Yahoo Finance API Documentation & Example
Excel Hero is dedicated to expanding your notion of what is possible in MS Excel and to inspiring you to become an Excel Hero at your workplace. It has many articles and sample workbooks on advanced Excel development and advanced Excel charting.
164 Responses to “Gantt Charts – Project Management Using Excel [Part 1 of 6]”
ninja =)
Superb!
Chandoo,
One concern with this approach is that it is often very important to display the Planned and Actual schedules superimposed on each other (not available here). Also, it is not uncommon to save multiple benchmarked versions of the plan along with current re-plan, and to display multiple versions of the plan simultaneously.
This leads me to the thought that the Gantt chart you present will be very effective for a certain class of project, but not for others (ie. where Primavera or MS Project are more appropriate for their power). Can we identify those cases?
Also, it is very clear that Excel is a critically important tool for schedule analysis even when the high-power tools are used. Some of the best uses are for data export/import and graph analysis that far surpass the big boys in flexibility.
@Alex... I like your point. I have thought about it when writing this post, but I didnt mention the exact method to achieve it here. One reason is I wanted to keep the series and this tutorial simple and yet share powerful ideas.
I think excel based gantt charts are less powerful when you compare with MS project or some other project management tool. But excel has one great advantage that almost everyone in the workplace has it. This is particularly true for large and complex projects where there are several tasks and plans always change.
For eg. in MS Project, you can specify dependencies of a particular task and the gantt and schedules will be arranged automatically. I am sure we can do this in excel too, but it would be too many functions and scaling it would be an issue.
Thankyou Sir for great works done by you, I have a point .. I used the following formula to shade the weekly cell and then used conditional formating.. I found the INDEX formula bit complicated.. though i didnt make provision for % done..
=IF($M$7="Planned",(IF(AND(H$8>=$C9,H$8=$E9,H$8<=($E9+$F9-1)),"2","")))
The value 1 & 2, I have assigned with shades of my choice..
(to which email I can send you my file)..your comments please...
Regards
Rohit Chadha
[...] link Leave a Reply [...]
Chandoo,
Could you please share link to an excel file for download instead of zip file.
The Zip file hits the firewall roadblock.
@Rohit: Thanks for sharing your formula. Can you upload the file on skydrive and leave the url here... that way everyone can see your work.. 🙂
@Pankaj: You can download the .xls file from here
http://cid-b663e096d6c08c74.skydrive.live.com/self.aspx/Public/gantt-chart-project-management-template.xls
nice...
[...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists - Project Tracking Tools Part 3: Preparing a project time line [upcoming] Part 4: Time sheets and Resource management [upcoming] Part 5: Tracking issues and risks [upcoming] Part 6: Project Status Reporting - Dashboard [upcoming] [...]
I'm sure it wouldn't be difficult to finagle the conditional formatting to outline the target, and fill in the actual (or vice versa), or some other form of formatting that will superimpose the two.
The alternative, of course, is creating a graph that does the same, and shows actual versus projected...
@Sal: I agree. Excel 2003 has a limitation on the number of 3 conditional formats applied on a range of cells. Also the formats stop if true. Excel 2007 none of these limitations. We can use that to show planned and actual both on the grid along with % completion.
[...] have started 2 new series of posts - Project Management using Excel and slightly controversial Chart [...]
Ive been playing (learning) with this and took cognisance of the need to show planned and actual on the same graph.
I used the following formula in the cell
=IF(AND(J$8>=$E9,J$8<=($E9+$F9-1),$E9=$C9,J$8<=($C9+$D9))
This got the same effect without the need for a 3rd conditional format.
however id like to use that 3rd conditional format to colour the text "?" based of the %complete column. Could Index be used ?
ill try upload the file later but im at work now. Thanks
hmm timed out and didnt upload properly ill update later when i get time
@Jon: Cool, thanks for sharing your formula. However the formula is not working. I think it missed the other two parameters and only specifies the parameters for AND(). If possible, please upload your file somewhere or email it to me at chandoo.d @ gmail.com so that I will be able to upgrade the version in the post.
thanks for getting back. i tried to update again last night but it didnt work( and i didnt want to look like i was spamming 🙂 )
http://cid-8d9d5762da62b814.skydrive.live.com/self.aspx/.Public/Copy%20of%20gantt-chart-project-management-template.xls
I looked at the comments from the other users and also noticed that the %complete line shows 100% even if its only 90% in column G.
Please note i have only changed Rows 9 and 10.
After having a play i came up with the following formulas.
in the cell formula i have (note addition of the -1)
=IF(AND(H$8>=$E9,H$8<=($E9+$F9-1),$E9=$C9,H$8<=($C9+$D9))
-and-
=H$8=$AL$6
This frees up the 3rd conditional format. I was hoping to reflect the % complete by changing the colour of the "?" based on the %'s and colours in columns AO and AP and was hoping Index could do this.
your advice would be greatly appreciated 🙂
(really doesnt like the way im typing this so ill try without the paragraphs. It should read -)
Please note i have only changed Rows 9 and 10.
After having a play i came up with the following formulas.
in the cell formula i have (note addition of the -1)
=IF(AND(H$8>=$E9,H$8<=($E9+$F9-1),$E9=$C9,H$8<=($C9+$D9))
-and-
=H$8=$AL$6
grr
in the conditional format formulas i have
=AND(H$8>=$C9,H$8<=($C9+$D9))
@Jon... Thanks for such an elegant solution. I am trying to update the template and provide another download. I will do it this weekend.
[...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists - Project Tracking Tools Project Status Reporting - Create a Timeline to display milestones Part 4: Time sheets and Resource management [upcoming] Part 5: Tracking issues and risks [upcoming] Part 6: Project Status Reporting - Dashboard [upcoming] [...]
I'm feeling very inadequate after reading through these posts (and the tips above). I am a project manager that is trying to get an 'old fashioned' (defunked) program under control by making everything uniform. I thought I was doing well with my chart, until it came to learning actual/planned, conditional formatting, and the formulas needed. I've been able to figure out a lot of what I'm doing on my own, but I've hit a wall with this last part. Can you please explain what the two different colors on the chart represent (light blue, dark blue) and the difference between the proposed and actual bars? I've probably taken on more than I should have with a gantt chart, but I'm too far along to give up now. Besides, it'll drive me crazy until I figure it out. THANK YOU in advance for any help you can lend.
@Kelly: Welcome to PHD and thanks for your comments.
The spreadsheet uses slightly intricate formulas to show planned or actual gantt chart. The dark blue bars are that. The light blue ones are the portion of work that is finished. This is based on the "% complete" values you enter. I am not sure if my answer is helping you. Let me know if you want more help.
Hi Chandoo
Fantastic stuff! How can I change the colors of the cells? I want to change the light blue one.
@Espri... Welcome to PHD and thanks.
You can change the light blue color (actually it is white, due to the blue background it looks like light blue) by selecting the entire grid and changing the font color. 🙂
[...] Gantt charts are very good to understand a project progress and status. But they are heavy on planning side. They give little insight in to what is happening. A burn down chart on the other hand is good for understanding the project progress and how deliverables are coming along. According to Wikipedia, A burn down chart is graphical representation of work left to do versus time. The outstanding work (or backlog) is often on the vertical axis, with time along the horizontal. That is, it is a run chart of outstanding work. It is useful for predicting when all of the work will be completed. [...]
Hi, everyone, I just found the very interesting web that open my eye on excel. Can I learn how to create the box with world 'plan' and 'actual' inside. Also I notice there is a legend sheet, what is that? Thanl You.
Hi,
I'm an Excel noob so apologies in advance if the following statements seem foolish. I'm wondering if the following could become part of the tutorial.
I'm currently planning a home extension & will be the de facto project manager. Is it possible to include budgets as a % of total build cost, contractor's quotes against actual payments to them, etc.
Won't go into too much detail yet in case this sort of enquiry is inappropriate to the thread.
Thanks,
Clive
This is really great stuff - easy tool to use. One quick question - How do i change the grid to display 52 weeks or say 35 to 52 weeks. I am no excel guru so any help is welcome.
@Ta: welcome to PHD. you can learn about adding validation drop down list to a cell by reading this article: http://chandoo.org/wp/2008/08/07/excel-add-drop-down-list/
@Clive: Very good idea. I havent really dedicated a whole post to cost aspect. Since cost is directly proportional to effort I left it out. I will be discussing about effort estimation as part of the 4th installment of this series (will be posting it this week). Meanwhile, you can edit the downloadable workbook on this post and add budget details as well. You might want to take a look at the burn-down charts post here: http://chandoo.org/wp/2009/07/21/burn-down-charts/ and may be use it to indicate budget burn down too.
@Sundeep: Welcome to PHD. You can easily add more columns.If you are familiar with how excel formulas (and conditional formatting) work then it should be fairly easy. Otherwise spend a few minutes reading articles on conditional formatting (here: http://chandoo.org/wp/tag/conditional-formatting/ ) and formulas (here: http://chandoo.org/wp/tag/formulas/ ) and then try it. Good luck.
Hi,
Thanks for the site.
Is there a way to add milestones to the chart with no duration (similar to MS Project) like start, or meeting, or other no duration events?
Great post! I am trying the project management on excel.
Thanks for the tips.
[...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Part 4: Time sheets and Resource management Part 5: Tracking issues and risks [upcoming] Part 6: Project Status Reporting – Dashboard [upcoming] Bonus Post: Using Burn Down Charts to Understand Project Progress [...]
Great stuff. This info post helps me lot.... thanks so much for sharing
Not to put a damper into this series of post but using excel as a project management tool (other than unique one/few time use needs) is really a poor idea. Excel is a poor mans database and should only be used with cost reporting for regularly used reports. Even then I do not think it is a great idea. Don't get me wrong I use it all the time for project management. Not because I want to use excel to create the reports let alone collect the data. It is because my company is to cheap and ignorant (of the processes) to buy the software to do project management functions more effectively in less time. Excel is used in way to many cases instead of a properly designed database by us Finance/Accounting professionals.
I needed a quick & inexpensive way to build a decent-looking Gantt chart and this fit the bill perfectly. Thanks for posting it.
@Brian: I disagree with you on "using excel as a project management tool ... is really a poor idea." part. While excel cannot fulfill a large scale project's management needs, it works very well for day to day project management stuff be it maintaining issue logs, change logs, listing and tracking activities, reporting, analyzing. It is simple to learn and easy to work with. I have used MS project and a handful of agile software project management tools, but I find excel quite simple and intuitive compared to these complicated tools.
However, excel has a ton of limitations too. To start with, there is no straight forward way to do even the simplest project management activities using excel. You have to make templates, created models and charts before starting to use it for a real project. That is where this series comes in to picture.
My experience is very small and limited. From what I have seen, excel seems like a decent fit when the other choice is learning a complex tool or paying money through nose. But, I am sure you have different experiences and thus different opinion. 🙂
@Sree, Study and Steve.. thank you 🙂
Chandoo - I love your posts - I drift back and forward periodically, as a source of expertise and skill. I rate myself as a reasonably serious user of Excel (VBA programming - not just recording....), and whilst I understand it has limitations, it is often my fallback tool for exactly those reasons you state. In addition, it is much more pliable than other products, insofar as its ability to present and manipulate data. I also accept that it is not purpose built for gantt charting, dependency mapping and WBS development - but let me tell you, it sure can represent the outputs much more effectively! (and more attractively!). Andin my line of business (consulting) I aasure you - not all companies understand, invest, or even want to use the more complex tools.
So - thank you for your posts - and I hope all those that stumble on your site, like I did years ago, find it as informative, educational, and value adding as I did (and continue to do so)!
@Chandoo: I partly agree with your statement that is why I said "(other than unique one/few time use needs)" in my message. I should of added as I did in another post "except for small and simple projects."
The problem with excel is scalability and multiple users of the same info. Excel is great for making personal logs and list that very few people will use (and have limited amounts of records and fields). I will also say excel is fine for creating very simple schedules. This I agree with, which my post probably did not convey.
However, when a lot of people start using the same sheet, then you run into a whole host of problems. Excel is only there to help one do your job better. My point is you need to look at the alternatives which includes total life cycle cost , creation time, and who will use the information. Spending a little time looking at better ways to do things (which includes taking into consideration everyone’s fully burdened hourly rate) instead of defaulting to excel can save you a lot of money in the long run. There is no point in recreating the wheel if there is software that cost a firm what they pay for 2 or 3 hours of your time.
This is an excellent series - and perfect for this situations where (even though you have access to some fancy and more complicated software) - once this is set up - it can be used as a template for those smaller projects (like the ones I always seem to get)...
Thank you for the time you put into this presentation!
Certainly we can all agree that there can be changes made to suite each of our needs - but that's the neat thing about it...add/remove away - alter as you like...make it your own...
Having said that - Chandoo - I sent you and email to inquire about Pivot Table usage with regards to some portion of this...
Thanks again for the awesome presentation!
Sincerely,
Jae
Totally agree!
[...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Part 5: Issue Trackers & Risk Management Part 6: Project Status Reporting – Dashboard [upcoming] Bonus Post: Using Burn Down Charts to Understand Project Progress [...]
[...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Issue Trackers & Risk Management Part 6: Project Status Reporting – Dashboard Bonus Post: Using Burn Down Charts to Understand Project Progress [...]
..Follow-up to question regarding the colours and the legend sheet
Could you explain exactly how the legen sheet is used and what (and where) the link is between the two sheets
And about the colours, I managed to change the colors related to the 'actual' element of the grid by selecting the entire grid and changing the font color, but how do I change the color linked to the 'planned' part of the grid - thanks for a great series...!
Hi,
Really liking this, however the %'s seem to have very little impact aside from 0 and 100. is their a way to make them reflect the %?
Hope that makes sense. Basically if i put 25% or 75% in the amount of area covered is exactly the same.
Thanks
Ben
@CS: legend sheet is used to define the symbols used in filling the gantt chart completion bars and labels for Planned and actual words. The color is same for both planned and actual. You can edit these colors from conditional formatting dialog. If you are not familiar with conditional formatting, read up this article: http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/
@Ben: I am using a variety of charts called as in-cell charts. That is why may be you are seeing bars in jumps. You can replace the in-cell charts with traditional bar charts so that you can get more fine grained bars...
Also, try with more duration than 2 days (say 30 days) and you should see bars of varying lengths.
How easy would it be to also incorporate a 'Budget' section showing a vertical bar chart presenting the target budget and actual costs incurred in implementing the project? It would be a nice addition to this or the dashboard template.
@Joe.. this is very easy to do. Infact you get a gantt chart template (template #1) in the project management bundle that has a vertical bar chart showing activity completions. You can easily configure this to show budget vs. actual spend per activity. See more here: http://chandoo.org/wp/project-management-templates/
[...] http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/ [...]
[...] have stared the Project Management using Excel series in this month with Project Management Gantt Charts. The 6+1 posts soon became legendary and helped me launch the project management templates. In [...]
[...] Part1: Preparing & tracking a project plan using Gantt Charts Part2: Team To Do Lists – Project Tracking Tools Part3: Project Status Reporting – Create a Timeline to display milestones Part4: Time sheets and Resource management Part5: Issue Trackers & Risk Management Part6: Project Status Reporting – Project Management Dashboard Part7: Using Burn Down Charts to Understand Project Progress [...]
I realize this may be very basic but I cannot seem to figure out how to highlight the week column. Some assistance please.
Nicole as far as i can remember its in Format - Conditional Formatting
[...] Excel Gantt Charts part of our project management series, we have discussed about how using Conditional Formatting and [...]
Dear Chandoo,
Today I started this Project Management Series but realized that even after reading Chandoo's blog I can't independently prepare Gantt chart as I say this because Chandoo is our hero and champion of novices and non-professionals like me and his blogs are not meant only for MVPs. So my request is please explain the above steps like, how we arrive at % done, how you created the graphs, how you created the combo boxes & lists etc., in laymen terms that is, the hallmark of Chanoo's blogs, as you explained in an excellent way the conditional formatting and formulas so that even a layman like myself can prepare a gantt chart after reading your blog and say " long live Chandoo".
Thanks and kind regards
Fakhar
I don't understand the difference between actual and % Complete.
In any plans I have, I have a Plan and a % complete. If the Plan changes, as long as you've followed a process to justify that change, I don't see the need to keep the original plan and then have a 'new plan', which I assume is what the Actual is being used for here.
@Jamie... Very good point. I have used Actual vs. Planned view as some times in projects even though plans change, managers (and sponsors) stick to original view and want to ask for justifications for schedule slippages. I have seen this happen in several of the projects where I worked. If you follow agile methodologies or similar ideas in project management, this should not be a problem, otherwise you may want to have actual and planned view of gantt to find differences.
hi chandoo,
this series of yours is very cool. i saw the template which is quite flawless.
however, when i tried to make my own, i couldn't. i am simply very poor at using excel.
please help me out. it is quite urgent for me..
thanks a ton.. 🙂
[...] useful as it lets us change the starting month very easily. We can use such a set up in, for eg. Gantt Charts to change the project start dates with ease. Today we are going to learn how to set up automatic [...]
hi chandoo,
i like ur work but i want to know the tracking system how to done in ASP.NET...
if u knw about ASP.NET den plz tell me on my e--mail and u have any example of this den also send me....
and i request all my friend who read this comment if u know den u also tell me...
Thank You...
[...] to and use in both my educational and career endeavors. Viola! A learning goldmine from the pointy haired dilbert. If you’re a fellow learner, I’d love to hear from you and what you think about the [...]
hi chandoo, just a line to say thanks a lot dude! that's very helpful.
[...] gantt chart based project plan assumes that there is only one possible end date for each [...]
Love the chart, one thing; I'd like to change the "planned" and "actual" start days from weeks to days. So for example "Activity 1" has a planned start date of "1" and actual of "1"
I would like to change to: "Activity 1" has a planned start date of "7/9/10" and actual of "7/9/10".
How can this be done without breaking the formulas??
Thanks,
Chris
@Chris... Thanks for the comments 🙂 You can easily change everything from numbers to dates. Make sure you have changed the numbers in top row to dates of successive weeks as well. It might take sometime to figure out how this works, but you should be able to do it easily without writing drastically new formulas.
[...] Most read post ever: It has to be the Gantt Charts – Project Management using Excel post. Written on June 16th, last year, the post attracted 150k page views so far, with 63 comments. [...]
Awesome........................
beep beep 😉
Dear all;
see i too want to change the start date format to 1/10/'10, I had followed what "Chandoo" is suggesting, but not successful...
pls I thin the "Dur" is the problem..
Pls help...
tks
Anil
This is an excellent chart and am very impressed witht eh feaures, on question, I am a maintenance planner and use MSProject i see this as a bit of a step forward to me if it could schedule in hrs. We have a lot of daily shutdowns that i would like to schedule in a minimun on 30Minute units. Do you think it would be poss to do ??..
Regards
James
Fantastic chart! Thank you! But when I try to include this into another Excel book, the chart doesn't work propertly. Chandoo, is there any way to integrate this in a different Excel book?
thanks again!
[...] Gantt Charts in Excel – Free Template & Tutorial 3.07% [...]
Thank your for your great chart.
Is it possible to introduce your chart in our blog at: http://xprojectmanagement.com/ ?
This would be helpful for our readers.
Best regards,
Hong
[...] Gantt Chart – Excel Tutorial & Free Template [...]
@Hong: you can link to this page with an excerpt and a small image of the chart. I hope that is ok.
Thank you! It's a pleasure to visit this Web site.
Hi,
Excel is an excellent tool for creating and managing projects when used correctly. Thanks...
Great series Chandoo...I've bought the set and looking for some help to understand an issue I have run into...
In Project Management Dashboard 2 (created 10/13/2009):
Can you explain the Ongoing Activities 5 calculation you are using?
If you put in the number of weeks in the Plan Gantt chart as being 4 you only list 4 activities when I see 6 are highlighted in the Plan Gantt Chart with 1 being 100% completed. My expectations are that 5 activities should be ongoing. (Activities 2-6 with Activity 1 not being shown)
I've tried changing the Index calculation with no luck and tried understanding whether the Named lists are off, but to no avail...
Cheers
Cliff
@Cliff... Thanks for your comments. I have answered your question via email.
Hi, may I know how do I apply the circular reference to have the highlighting of current week or day? Thanks! and your spreadsheet have been of great use to me. Thanks again!
Hi chandoo! thanks for this. What does your legends indicate? How do I show actual vs. planned timeline?THanks!
Hi, Great job! I always knew that I knew only 1% of the power of excel. Do you have a version of the worksheet which displays in days rather than in weeks? Will we be able to display weekends & holidays too?
Not much use for me. For me It is important to have dates showing, but I could not get that to work. I could not view the planned and actual at the same time, and I needed something that allowed me to compare how things were doing at a glance, and this does not allow it.
I think if you go by weeks, then it will be OK, but for those of us that need to work to set dates and don't have the time to compare the week number to a calendar, then sorry, I don't see this being much use, sorry
Never mind, got the date thing to work.
Is it possible to view the planned and the actual at the same time?
I loved the approach but have hit a wall. ..
I am helping a friend implementing a gantt chart I have just downloaded your templates but she needs a cell per week, and in each cell the numbers of days worked in that week. The project is for a few years!. So the bars would go from one bar to 5 bars.
I want to align them all so that it looks like a continuous bar with the bars in the first cell aligned to the right if there are less than 5, and the one at the end of the line aligned to the left if less than 5. This cannot be done by conditional formatting as the alignment is not part of the conditional formatting tools ( I am using 2003 for that project, but checked 2007, the alignment is also not a conditional formatting option). I then thought of adding spaces for the days that are not worked, either to the left or to the right using if in the formula. The spaces do not have the same width as the font I tried. I'm sure there is an easy way but cannot see it!
Thanks!
It is looking great what you are achieving with Excel but this one is limited at a certain point regarding project management. You can not handle working calendar and all king of scheduling mechanisms if you have these needs. Here is a comparison between spreadsheets and dedicated project management tools:
http://www.rationalplan.com/projectmanagementblog/upgrade-from-spreadsheets-to-project-management-software/
Hi Chandoo,
Thanks for all these great stuff. I made my own chart with planned and actual dates and colored it with conditional formatting. Now I am thinking how to highlight a date which crosses over two months example planned start date is from 28 April to 4 May. It does not work if I repeat dates after 31st for the next month. Please help.
I use EasyProjectPlan
http://www.EasyProjectPlan.com
EasyProjectPlan is an Excel Gantt Chart that syncs with Outlook and Microsoft Project
[...] that I am coming across more and more amongst designers. The options vary from the low-tech free MS-Excel solutions to the high-tech and expensive professional packages like primavera (Which I wouldn’t recommend [...]
[...] Chandoo understands that there are dozens of excellent office management programs out there, and thousands of offices where you’re not allowed to use them. The “approved software” list is the bane of the efficient employee, but so is whining about that, so this guy has gotten on with an excellent mission. Making Excel work for everyone who has to work with it. Chandoo Gantt [...]
thx for sharing this template., very usefull for developing my software., thx... 😀 i already download it..
can anybody teach me how i can prepare a progress measurement table
Hi, just going back to Jon solution I'm struggling to get a third CF that will show the border/lines associated with the current week No on the 'Planned' part of the Gantt. Does that make any sense... like: |colour|
Thanks, fantastic blog
Hi, I have quite some experience of project management, and would make the following comments. For a large complex project you use Excel at your peril because you should first establish dependancies after you have identified the tasks, then assign resources and evaluate durations in real world calendars, however you can share your MS-Project with Excel users using Paste Link to distribute the calculating engine of MSP. On one project I managed, we took 9 months out of a 3 year project by evaluating the critical path, and doing some re-structuring. Also, I always plan using what % needs completing. Whilst reporting what has been done (actual) is easy you can do nothing about it, it is what still needs to be done that can be managed (nothing to do with Excel or MSP 🙂 )HOWEVER for relatively simple projects, without complex dependancies everyone seems to use Gantt charts, and Excel is excellent at that, so if you can plan using a Gantt, you can use Excel just keep questioning what is really happening and don't mistake the precision of Excel for the accuracy of the real world. Excellent that the capabilities of Excel are being shared, so keep it up.
[...] … Download [...]
I apologize for the novice quesiton. How do I change the Project Status from Amber to Yellow? I changed the drop down from Red,Amber,Green to Red,Yellow,Green via Data Validation on the Excel's Data Tab, but I am not sure how to change the Project Status color on the Project Status Dashboard tab in the spreadsheet. Thanks!
When I download this gnatt chart it has only 32 columns for scheduling (i.e project length can at max be 32 days / 32 weeks ) . How can I increase that frame . I tried a lot but failed . please help .
[...] excel 2007 *.xlsx fileDynamic Gantt Chart.xlsxRecommended links:How to create a dynamic chart Gantt Charts – Project Management Using Excel [Part 1 of 6] (Chandoo.org) Gantt charts in excel (PDF by John Walkenbach)Related blog postsExcel charts: Use [...]
Gantt charts are an excellent project management tool and the best thing about integrating them into Excel is that you don't have to learn or install any new software!
Hi Chandoo,
Can you explain to me the meaning of the 3 Gantt Chart Symbols?
Thank you so much, you just saved my life 😀 great chart!
Hello Chandoo. Thank you for your Gantt Chart Project Template. I also like http://www.excel-easy.com/excel-examples/gantt-chart.html
what is the formula to indicate the percentage completions on the chart?
this is a very good stuff.
I have try this, and do some modifications, but I have a problems:
1. how to change the colors of graphic?
2. where is the formula to move a red line according to "Current week"?
please, give me answers...
Thanks a lot bro...
Chandoo,
Excellent stuff mate! You are the Superman of Excel. Keep up the good work. Brilliant!
Sincerely,
Ashwin
Excellent chart, many thanks.
Anyone managed to alter it so that when there is a value in the Actual Start column but the Actual Duration value is blank then this is still shown with blue between the actual start value and this weeks value? At the moment nothing is indicated.
Hi Chandoo,
I can never thank you enough on the gantt chart help and examples !
Yaamuna Aldragen
[...] Free Excel Gantt Chart Template and Tutorial – Project Management … Link to this post!Related posts: [...]
Great work - I've created something similar - albeit a bit less sophisticated... http://www.mlynn.org/2012/05/excel-project-planning-spreadsheet/
Just stopping by to let you all know that I've updated my similar project... freely available example project planning template with color coded progress indicators.
http://www.mlynn.org/2012/05/excel-project-planning-spreadsheet-version-2
Hi Chandoo,
Thanks a lot for this great tutorial. I am trying to create my own version using these principles, and I was wondering how you made that red moving vertical line marker? When you change the week number, the line moves accordingly.
Would love to know how you made that.
Thanks,
Patrick
am trying to do this sample,,,hoping i will succeed or my task would excelence,,,thank you.
Greetings,
I am project template with dashboard. My projects are not long projects but more like projects performed during maintenance window ( 6 hours) Can the templates be configured to use time base formulas as opposed to days?
I am not an Excel expert but I have read great things about this site and the templates provided here. Any information you can provide will be greatly appreciated.
Here is an example of a maintenance project:
11:00 message sent to users to log off system
11:15 users disabled from system
11:20 maintenance window begins
11:30 service packs installation starts
12:30 Status check to see if we are on schedule
01:30 Service packs installed
01:35 IT notified to run scan
02:30 Status check to see if we are on schedule
03:30 scans complete
03:35 Copy to clients start
04:00 status on copy to client 1
04:00 status on copy to client 2
04:30 copy to client 1 complete
04:30 client 1 updates local pc's
04:45 copy to client 2 complete
04:45 Client 2 updates local pc's
05:30 all system updated
05:45 all systems enabled
06:00 maintenance window completed
I hope this helps to paint a clear understanding ..
Thanks for all you assistance and look forward to your response
Thanks man...helped me a lot....
Hey there,
I love your template. It has really helped me. The only thing I cant figure out is how to delete the red boarder around the number 3 and downwards. Could you please help me with this?
Thanks
Hi Nick, Welcome to chandoo.org and thanks for your comments.
To remove the red color line, select the entire chart cells, go to conditional formatting and remove the rule corresponding to red line.
I've got a project here at work that I would like to put in to a gantt chart. Currently they are just using Word to list out the "schedule" of tasks with their corresponding due dates. But there is nothing in this schedule that refers to which of these tasks are dependent upon the completion of other tasks. Start dates and duration times are irrelevant to us. So is there a way for me to easily add task dependency to this gantt chart?
Hi miffany
The simplest way to illustrate the dependancy is to add a column in which you list the task number of any dependancies, eg 4,5,8
If you want more information, there as of course 4 types of dependancy, Finish-Start, Start-Start, Finish-Finish and Start-Finish, all of which can have lag or lead times added.
An Excel wizard could make the graphic like visible, but that is the most basic way that would solve your issue now.
tpmbrian
First off, this Gantt Chart is amazing! Thank you for putting it together.
With that said, I am looking to recurring meetings to chart. For example there is a meeting on the 5th of every month that I would like to put on the chart. Does anyone know a way to do with without hard coding it into the chart?
[...] http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/ [...]
Really cannot wait to download this wonderful and functional chart to be used in my production timeline.TQ
[...] Preparing & tracking a project plan using Gantt Charts Team To Do Lists – Project Tracking Tools Project Status Reporting – Create a Timeline to display milestones Time sheets and Resource management Issue Trackers & Risk Management Project Status Reporting – Create a Timeline to display milestones More on using Excel for Project Management [...]
You are a champ!!! I really like trcaking tool of your's and that % signage.. You are simply outstanding..
Hi Chandoo, Thank you for offering the free tool! Unfortunately, when I download it, I cannot open it: I get a warning that the document's damaged and cannot be opened. Can you maybe email it to me? Thanks again!
Fixed it! Turned out to be a technical problem with the network @work.
Looking forward to working with your sheet, thanks again!
Any idea how to exclude weekends from this great tool?
I am not sure how to incorporate NetWorkDays into the formula.
Thanks
Hi Trying to add a priority column by inserting a column at C and selecting 1 for hi 2 for medium etc
in the conditional formatting box to change from blue to Green Amber etc I've added a new rule and tried
=AND(C8=1,IF($O$5=INDEX(ganttTypes,1),AND(I$7>=$D8,I$7<$D8+$E8,I$7=$AM$5),AND(I$7>=$F8,I$7<$F8+$G8,I$7=$AM$5)))
and
=IF(C8=1,IF($O$5=INDEX(ganttTypes,1),AND(I$7>=$D8,I$7<$D8+$E8,I$7=$AM$5),AND(I$7>=$F8,I$7<$F8+$G8,I$7=$AM$5)),"")
Neither work and I can't see why can anyone help?
Thanks for sharing your thoughts. I truly appreciate your efforts and I will be
waiting for your further write ups thanks once again.
Link exchange is nothing else but it is just placing the
other person's web site link on your page at suitable place and other person will also do same in favor of you.
Is there a possiblity to add scrollbar to this - if I want to display the data for say 3 months
I am honestly very glad that I came to this site. I learned a lot of great things about gantt charts project management and excel. Thank you very much for sharing this information.
The gantt chart is excellent. I was able to create it with all the formatting and got the same results! Thanks for posting it.
Hi Chandoo,
I need to chart, say, 30 tasks that are performed each month between business day (BD) 1 and BD8; and that are spread over a few departments (say, dept 1 to 5). Some tasks involve intra-department or inter-department dependencies. Other tasks are carried out without much dependencies.
I need to arrange the 30 tasks into a timeline (BD1 to BD8), and am hoping to do it without spending too much time with technical set ups and maintenance -- preferably in Excel. There are no specific dates (like June 10, 11 etc.) since the tasks are carried out each month during the BD range mentioned above.
Can you suggest some solutions that will meet my need?
Thank you
Srikanth
hi, I need some more experimental example using user form in excel 2003 by VBA..like want to make a program where i can browse other file in a work book an past them ryt there in the active workbook...hope u can help me out! Regards....
THANK YOU SIR FOR THIS REPORT
Excellent work.
Great thanks!!!
I love it when folks come together and share views.
Great blog, stick with it!
I am managing a garment factory and my boss is very demanding!!
I want the help of Chandoo/Excel to solve some of my day to day/chronic/repeaed work place problems so that I do not have to go back and forth on these issues every day
Pls suggest me a suitable solution and if there are charges I don’t mind remitting the money to your account once we have an agreement on the cost.
My issue is that of Tracking WIP-we have 24 steps wherein the WIP moves through until the final packaging and despatch;i have formats in place to track the daily progress-STYLE WISE and PROCESS WISE-which as of now work ok but I feel the need for improvement.
The problem is that though I am able to monitor the progress on a day to day basis,I am unable to manage the history of a particular style code
For instance for an average style code would have the order qty of 2000pcs;process would register variance due to the fact that recording of data is manual and at the time of despatch I am unable to locate the complete qty of say 2000pcs in this case in terms of despatch qty and rejection;I have to create a third category by the name of LOST GOODS as I am unable to locate these goods at the time of despatch due to heavy flow of WIP;only after few weeks/or after the seasonal cycle gets to be on the leaner side
Pls suggest something which is excel based to solve this problem
@Raghav
Your problem is your using Excel for you database. Seriously Excel is brillant for custom jobs, working out complex models before you program them into a database, and data analysis that is never quite the same twice. However, a MRP/MPS system that has WO and is set-up to track information like you are requesting is a must have. I am sure there are some fairly cheap manufactoring suites designed for the Garmet industry (under 100k if not less than 80k U.S. Dollars to purchase plus some addtional cost for implimentation and training.) If you stick to it out of the box I don't and you are in Asia the training implimenation should be a lot cheaper than the purchase value. Excel is a poor man's database and you will get what you pay for. Pay some money for a real system, you will save on labor and vastly imporve quality of information (by a few decades) if you use the system correctly.
@Raghav
Your problem is your using Excel for your database. Seriously Excel is brillant for custom jobs, working out complex models before you program them into a database, and data analysis that is never quite the same twice. However, a MRP/MPS system that has WO modual for Garmet industry, and is set-up to track information like you are requesting is a must have. I am sure there are some fairly cheap manufactoring suites designed for the Garmet industry (under 100k if not less than 80k U.S. Dollars to purchase plus some addtional cost for implimentation and training.) If you stick to the system out of the box and you are in Asia, the training and implimenation portion should be a lot cheaper than the purchase value. Excel is a poor man's database and you will get what you pay for. Pay some money for a real system, you will save on labor and vastly imporve quality of information (by a few decades) if you use the system correctly.
[…] Gantt charts are a very popular way to visually depict project plans. Today, let us learn how to use Excel to make quick & easy Project Plan Gantt Chart. […]
[…] Gantt charts are a very popular way to visually depict project plans. Today, let us learn how to use Excel to make quick & easy Project Plan Gantt Chart. […]
Looks great
Is there a way to add a resource column and to create a sheet that is grouped by the resources to show bars for each resource?
Hi Chadoon, Excel master.
I came again in the website.
I downloaded the gantt chart you made and trying to simulate(?) it. currently I lost somewhere how I can make this progress bar (red rectangular).
could you give a some comment where I can start?
because I am thinking this progress bar automatically follow the current computer time then I can see the daily progress and check work load.
Thanks.
but
Chandoo,
There is a heading like this - Steps for preparing an Gantt Chart. There is a small error. May I correct it? It should be 'Steps for preparing a Gantt Chart'.
Regards,
Nagarajan
Every weekend i used to visit this website,
as i wish for enjoyment, since this this site conations really pleasant
funny information too.
An impressive share! I have just forwarded this onto a coworker who has
been doing a little homework on this. And he actually ordered me breakfast because I discovered it for him...
lol. So allow me to reword this.... Thank YOU for the meal!!
But yeah, thanks for spending time to discuss this matter here on your internet site.
Can the row (row 7 in your example) above the chart that is in weeks be changed to months? My projects go from fiscal year to fiscal year and are tracked by months instead of weeks. I tried changing the numbers through formatting to months, but chart goes away.
thanks in advance.
Dear Chandoo,
I like various format availble on your website. Need one help.I monitor the project on week number basis, Like for 2014 we call week no 1401 to 1452. First two digit repersent year 2014 and last two digit number of week. same way for 1501 to 1552 for year 2015.
Appriciate if you can provide gantt chart template that way. because inputting week data give problem. after 1452 next week is 1501 and activity shown at this transision peroids will not refelected correctly.
Need your help.
Regards
Lokesh
[…] [3] Many Gantt Chart Tutorials are available on line. For example, see: http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/ […]
[…] may have pulled some Some of the original work from this from a post at […]
I have purchased your project templates and i am trying to work out how to change in the gantt chart the dates across the top with the =valStart formula.
Good article. I'm facing many of these issues as well..
Good explanation. I use lot of org charts for managing HR of my projects and the tool I use or the org chart software I use is creately.
Quick question, might be me being stupid and missing something...
Our team are given projects individually in which we have 50+ projects with 8/9 we are currently working on...
What method could i use to get the % completed? any ideas??
In need of this being delivered by COB 4th Sept so qucik response would be nice
HK
@HK... you can calculate % complete as = Total hours worked so far / Total hours needed for projects that began
This way, you can ignore any projects that are yet to begin. If you don't have hours, you can use money / days etc.
Chandoo
Hi
Thanks for the template of the gantt chart, it is very nice and useful. I want to modify it because I can not show to my boss with the links. could you explain me how unprotect the sheets to cancel the links
thanks
Kind Regards
Caminos
Hi,
How do i add in multiple months?
Regards,
Francesca
[…] Free Excel Gantt Chart Template and … – Gantt Chart is a great way to prepare and manage a project plan. It shows project activities and what is their start and end dates. In this tutorial, learn how […]
Hi,
I would like to ask if someone can explain how to create the "red rectangle" every time we select the current week.
Thanks!
[…] Gantt Charts – Project Management Using Excel [Part 1 of 6] […]
Hello - I'm using your gantt chart template as I'm not quite skilled enough to create my own. My project has about 43 weeks and 61 activities. If/when I add columns and rows to make the template bigger, is there an easy way to copy in the needed formulas? On more simple spreadsheets I've created, copy/paste of formula automatically updates the rows/columns for where it's pasted, but I'm not certain if that works here too. Any advice is appreciated! Love your templates BTW.
Hi Nicole,
Like you I find it hard to manage a gantt in excel. I tried to find an easier way to create gantts, but did not find it.
I'm trying to build something but want to understand what people need before building. Are you up for a 20 min chat?
No selling, just trying to learn.
Thank you!
Hello Chandoo, I am new to using your templates and have down loaded a Gantt chart. I would like to know how to widen the columns so that the dates that I have entered are visible. I have tried to expand the column by clicking at the top, however this does not work and wonder is the document secured and how do I unlock it? Look forward to hearing back from you. Thank you Maria Rowland