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]

Why Create Project Timeline Chart?

There are 2 key elements in all the successful projects I have been part of.

• They had exceptional individuals who are also exceptional team players
• The communication and collaboration is really good.

While there is little that project management software can do when it comes to first point, the second point can be addressed by using right tools and visualizations. In this installment and the part 5 and 6 of this series, we will learn some excel based visualizations / charts that can help you to communicate about the project status and progress to your team and stake holders.

Project milestones can be shown in a simple time line chart in excel. While the chart doesn’t look complicated, it can provide good amount of information on project progress in a simple and understandable chart.

We will learn to create a project milestone chart like this:

Steps to create a project milestone chart in excel

1. In order to create a project milestone chart, we need to have the milestone data. The simplest format for milestone data is Date and the milestone. But since our chart requires the milestone to be displayed at a certain height on the chart, we will add the third column – height.
   
   PS: the height column can be easily calculated using formulas. I leave it to your imagination.
2. Once you have the data in the above format, we will add 2 more helper columns – named DUMMY and Milestone. The Dummy column is used to create the timeline (where Y axis value is zero). The milestone column is a more cleaned up version of milestones (see how it is showing #NA where the milestone is blank.)
   
3. Now, select the date and dummy columns and insert a line chart.
   
4. To this chart, we will add one more data series – Height column.
   
5. Now select the height data series and change the chart type to a bar chart. Also set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in excel.
   
6. We will also set the horizontal / axis labels for the height series as the “milestones”. We need to do this so that when we set the data labels for the height series, we will see the milestone instead of month.
   
7. At this point our chart should look like this:
   
8. Now, we will add data labels to the height series. Set the label type as “category”
9. We will also add error bars to the height series (the bar chart). We will configure the error bar in such a way that they are shown 100% on the negative side only.
10. After doing this, the chart should look like this:
    
11. Finally we will do some formatting like,
    1. Removing fill color / line from height series by setting them to None / transparent.
    2. Changing the error bar color to a dull shade of gray.
    3. Adding chart title and aligning it.
    4. Removing vertical axes and gridlines.
    5. Formatting horizontal axis – changing label orientation, removing tick marks.

    After all this is done, our project milestone time line chart should look like this:
    

12. That is all, we now have a cool looking project milestone chart ready. Now go and achieve a milestone.

Download the Project Milestones Time Line chart template:

I am sure you are overwhelmed reading the above tutorial. You are probably thinking if it is easier to work towards the project milestones than creating this chart. Well, don’t worry. You can download the time line chart template [download .zip version] and play with it to suit your needs.

What next?

Project timelines are a great way to tell the story of project to strangers and new people joining your project. They are a good addition to project status meetings and reports.

In the next installment of this series, we will learn how to use excel to keep track of time sheets and manage resources.

If you are new, please read the first 2 parts of this series: Project planning using gantt charts, Tracking day to day project progress with team todo lists.

Your thoughts and suggestions?

What are your ideas on communicating project progress to stakeholders and new comers? What do you think about this tutorial? Please share through comments.

I was wondering if you could help me figure out how to combine the round formula with the rank formula? I need to first round all the numbers and then rank them. I have tried various ways to do this but they aren’t working properly. I really don’t want to add a column to round and then rank because I need to do this round/rank many times for this particular spreadsheet. There are also merged columns which makes it even more challenging. Before I go through the painstaking efforts to insert columns to round, I thought I’d check with you.

Of course, just as any other excel formula problem, we can solve this with … strong coffee.

Well, I am kidding, but coffee along with array formulas seems to help.

How to write an excel array formula to round and sort a list?

Assuming the data is in range C3:C12, and B3:B12 has running numbers from 1 to 10, the below array formula,

=SMALL(ROUNDDOWN(C3:C12,0),B3:B12)

when entered in the range D3:D12 will round and sort the range of values in C3:C12.

Remember, first you should select the entire range D3:D12 and then type the formula (in first cell) and press ctrl + shift + enter to make it an array formula.

Update: Even better alternatives from commenters

Here are few interesting alternatives, thanks to our commeneters.

• =SMALL(ROUNDDOWN($B$1:$B$9,0),ROW(B1)), array enter in cell C1 and drag in the entire range. Thanks to Chrisham.
• Instead of using ROW() as Chrisham did above, we can also use ROWS() function. This will let you add rows on top without having to change formulas. Elias recommends using this formula: =SMALL(INDEX(INT($C$3:$C$12),0),ROWS($D$3:D3)). Thank you.
• Since we are only sorting numbers, it shouldn’t matter whether we sort and round or round and sort, as Jeff pointed. So, we can use the normal formula =ROUNDDOWN(SMALL($B$3:$B$12,ROWS(B$3:B3)),0) in cell C3 and copy in the entire range. Thanks Jeff

More resources: Learn excel array formulas | Online Help on Excel Formulas

• How to combine two chart types in one chart, like a line chart combined with bar chart?
• How to add a secondary axis to the chart so that one or more data series are plotted on the secondary axis?

Excel combination charts are a great way to combine two chart types in to one.

Using data series options you can plot a particular series on secondary axis.

In this quick video tutorial you can learn how to make a combination chart and add secondary axis in less than 15 seconds.

Watch it on youtube: Creating combination charts and adding secondary axis - video tutorial

More quick tips and video tutorials:

• How to remove duplicates
• Hide cell contents using formatting codes
• Hide formula errors when printing
• Editing formula references quickly using mouse
• More quick excel and charting tips

• We have received 239,000+ page views from 95,000+ visitors
• 4,300+ of you have signed up for our RSS feeds and e-mail news letters. That is 800 new members in our community this month.
• There are 345 comments in this month. This is a true testimonial of how buzzing our community is.
• The blog has been featured on Lifehacker, delicious home page, MSN Japan and BNet and many more sites in this month.
• We have revised the excel formulas e-book this month and it has been selling like hot cakes ever since.
• We have started 2 new series of posts - Project Management using Excel and slightly controversial Chart Busters

I am very thankful to you. I really enjoy sharing whatever little I am learning with you all. Your support is truly amazing. Thank You.

The best posts in the last month

• Convert Excel to PDF - Review of tools and techniques
• How to hide a cell’s contents in Excel
• Double click tricks in Excel - Must read
• Project Management in Excel [New Series] - Gantt Charts | To Do Lists
• First installment of Chart Busters

Please support PHD

Our community is all about becoming successful and productive using spreadsheet software. We are going there with one little step at a time. You can help in spreading the PHD movement. Please take a minute and do one of the following,

• Tell a colleague or classmate about this site. You never know which tip can save their job. Send them to this URL: http://chandoo.org/wp/
• Tweet and tell everyone that you are becoming cooler and smarter, click here
• Save PHD to delicious or stumble upon and let the world know.
• Send me your suggestions or just say hello - drop a comment or email me at chandoo.d @ gmail.com

Thank You.

…column chart that shows daily, weekly or monthly data depending on the user’s choice. In daily the columns are displayed properly, but in weekly & monthly mode the columns are a fraction of the width they should be - why, and how can this be avoided? Bonus points if you can describe how to use an INDIRECT formula on the x-axis labels which is another problem I ran into whilst creating this mockup!

You can download the workbook from here.

Here is how it is looking:

Go!

Thanks Gordon for asking this question.

What is in it for you?

Eternal glory and a 10% discount on the Learn excel formulas - Lightning fast e-book.

Lear more about making better charts using these chart busters examples:

1. Asset Allocation Charts - Done the right way
2. Calorie chart - How much you should exercise for what you eat - fixed properly

Learn how to make dynamic charts:

1. Excel dynamic charts using data filters
2. Dynamic charts using INDEX() formula and Camera tool
3. Using scroll bar form control to make dynamic charts

Assuming the sequence of values are in column C from C3,

• you can write the following formula in B4 onwards (B3 will be 1, wake up…)
  =IF(C4=C3,B3,B3+1)
• Now just copy paste the formula over the entire range B2:Bxxx to generate sequence numbers.

How it works?

• That is for your home work for the day

Browse more excel quick tips and learn something cool.

Preparing & tracking a project plan using Gantt Charts
Team To Do Lists - Project Tracking Tools
Show Project Milestones in a Time Line Chart
Part 4: Time sheets and Resource management [upcoming]
Part 5: Tracking issues and risks [upcoming]
Part 6: Project Status Reporting - Dashboard [upcoming]

Why Team To Do Lists as a Project Tracking Tool?

Projects are nothing but a group of people getting together and achieving an objective – like building system or constructing a bridge. While it is important to have a overall project plan and vision, it is equally important to understand how various day to day project activities are going on. This is where to do lists can help you a lot.

How to create a team to-do list to track project progress using Microsoft Excel

Microsoft Excel has a very good way to share a workbook with a team of people. We can use this feature to create a team to-do list. Here is a step by step tutorial to create a team todo list:

1. First we will create a to-do list in excel in the following format:
   

   Note, depending on the type of project and the kind of activities involved, your team to do list can look differently.

2. In order to facilitate tracking, we have the following features:
   1. A column where the team member can specify his / her name. This should be done when the activity is done. A simple alternative could be to automatically load user’s name based on windows login ID. For more on this, see this article on DDoE.
   2. Another column where we generate a time stamp when the user enters the name. Please read this article to generate time stamps in excel
   3. The formula for time stamp is like this: =IF(AND(D6<>"Not Done",D6<>""),IF(E6="",NOW(),E6),""). As you can guess, it is a circular formula. So we should enable iterative calculations from calculations options in Excel. Learn more about circular references here.
   4. Using above 2 columns, we can track and measure how team members are working various activities and who has done what.
   5. When we are done, the todo list for project tracking looks like this:
      
3. Once the list is created, first we should save it a network location where the list can be accessed by everyone.
4. If your team is spread across the globe and cannot access one network, try the following options,
   1. Use Office Live Workspace. This is an online workspace that can be accessed anyone (once you share the files with them)
   2. Use google docs spreadsheets. Google docs spreadsheets is a free alternative to MS Excel with several collaboration and team features. It is very intuitive and simple to use.
   3. If your company runs Sharepoint Server, you can export the excel to do list as a list in Sharepoint site and have it accessed by everyone in the team.
   4. You can create multiple copies of the to do list and share it with your team members and consolidate all the spreadsheets on frequent basis. This is a painful process as any format changes can create problems to your consolidation process.
5. Once you place the file on network, we should enable sharing of the workbook. See the below screenshots to understand how to share a workbook.
   
6. Now go get some work done.
7. When you finish the task, just open the shared workbook and mark the task as done by entering your name. Excel will automatically fill in the time stamp when you marked the activity as done.

Download the To Do List Template and Use it to track your projects

Go ahead and download the excel team to-do list template [.xls version here] and use it as a project tracking tool.

Next Steps

You can use VBA macros to automatically remove the finished to do items. I have written an article on simple to do list app using excel sometime back. Check it out to get some ideas.

In the next installment, we will learn how to prepare a project time line that can display various key project milestones. If you haven’t already, read the previous part of the project management using excel series - Project Planning using Gantt Charts.

Your thoughts and suggestions?

I am not a project management expert. In fact, I know very little about project management, that is why I started this series, so that I can share the little I have picked up in the last few years and learn more from you. Please tell me your feed back using comments. I would love to hear from you.

Let us talk about the excel links I have come across recently.

Using XY Charts to draw state maps

Dick Kusleika provides a neat technique for creating US State Maps using XY Charts. Using XY Charts for maps is somewhat cumbersome. I have stopped trying that route after crashing excel fewtimes. Instead, I use Tushar’s method of shapes to construct maps. You can learn more about that here

Generating TinyURLs using Excel

Jimmy provides a simple VBA macro that can generate tinyurls from any given URL. This can be handy if you are planning use run a twitter campaign from excel sheet.

The sad state of Infographics

This is a classic Stephen Few article where he disapproves 2 inforgraphic visualizations and highlights all the mistakes in them. He asks “Is this the best infographics has to offer?” I echo with his opinions. That is one reason why I have stopped posting the cool infographics of the week series.

Help Jorge finish his data visualization e-book

If you have sometime, head over to Jorge’s Junk Charts blog and help him write his e-book on 101 Data Visualization Questions You Were Afraid to Ask. You can win a free copy if your question is selected.

How to design a filter for your dashboards / reports

Juice analytics highlights five features of effective filters. This is a very good lesson if you design dashboards and complex TPS reports where filtering is necessary. I thin the aspects of Selections and Impact are very relevant when designing spreadsheet based filters.

Do you want to share a useful excel or charting related link?

Send me an e-mail or drop a comment. I like to learn and share new stuff about excel, spreadsheets and charting. More so, I like to read emails from *you*. So go ahead…

• Just select the list of values from which you want to remove duplicates
• Go to Data ribbon
• Hit the Remove Duplicates button
• Bingo…

Here is the 15 second video tutorial I have prepared on understanding this feature.

Click here to watch on youtube.

PS: 15 second tutorials idea inspired from google tutorials.

PPS: Learn more quick tips, they make you blazing fast.

PPPS: There are 4 more ways to remove duplicates: 1 2 3 4

Chart busters is a new series of posts on PHD and Jon Peltier’s blog. We take turns to exterminate bad charts and associated evils. Although our proton packs are still not perfect, together we are confident of tackling most ghosts trapped in bad charts.

In this installment we take a look at Asset Allocation Chart that looks like it is hexed.

The bad chart

Our reader DMurphy submitted a poorly made asset allocation chart,

If you are looking for an early contender, here’s one which came in to my wife from her Pension company this week showing (or at least attempting to show) the make-up of her investments.

http://www.box.net/shared/goynhfo7zb

The above image is an excel reconstruction of even sadder looking chart.

What is wrong with it?

• Poor chart selection: Pie charts are good for 3-4 data elements. When we need to present 10 or so items, it is better to use a bar chart or a line chart.
• Not grouping and sorting the information: In the first chart which is displaying Asset Allocation is made from data that has 3 different series - Bonds, Equities and Other. But the chart shows everything in the same way, thus making it difficult to understand how assets are allocated to various classes of investment. Also, the data is not sorted in any meaningful order.
• Poor use of labels: The labels A,B,C … are non descriptive. They are also repeated on the other chart although they mean different things.

The Chart Busters’ Fix

Thanks to guest parachartanalyst Joe Mako, who contributed this fix:

I have taken Joe’s ideas and slightly modified them to create the below chart

Click here to download the above fix in excel and see it yourself.

Added Later: Readers Submitted Fixes

Submitted by Paulo Cesar Semblano da Costa:

• I think Paulo’s version manages to reduce chart clutter a bit more. Very good effort.
• You can download this version from here.

Submitted by Jeff Wier

• Jeff’s version is very good. Again, like Paulo, he managed to reduce the chart clutter bit more and made it look very slick.
• You can download this version from here.

What we have learned?

• Zombies are scary, even when they are looking like donuts.
• Always try to sort the data in some meaningful order before pushing it to charts
• Use a variation of panel chart or color the series sensibly to bring out key differences
• Try to avoid generic labels like 1,2,3 or A,B,C and instead use the actual values and category names

How would you have tackled this?

We dont know how open source the ghost busters were. But Chart Busters are 100% open source. Share your ideas and suggestions for improving this scary little chart to something that makes sense.

When ya gonna call…?

Consult chartbusters today. Send us your bad charts. All you have to do is fill out this google form.

Arent ya gonna read these… ?

What to do no when no one likes your pie | Non sucky excel chart templates