Few details about the project management bundle for excel:

• The bundle contains 24 highly reusable excel templates for project planning, task management, timesheets, issue tracking, risk logging, status reporting and more.
• The templates come in 2 versions – Excel 2003 and Excel 2007.
• The bundle costs $30 for Excel 2007 or Excel 2003. You can buy both the versions for $45.

What will you get when you buy these templates?

When you purchase the project management bundle for excel, you get a zip file with all the excel files. The zip file contains the following templates in 19 different files:

• 7 Gantt Chart Templates for Project Planning & Tracking [know more]
• 3 Timesheet Templates for resource management [know more]
• 5 Templates for issue tracking, risk management & todo lists [know more]
• 3 Dashboard templates for project status reporting [know more]
• 2 Chart templates for making project milestone charts [know more]
• 4* Templates for burn-down charts, issue tracking charts [know more]

* If you are purchasing Excel-2003 version of the templates, you will get only 3 chart templates.

Who can purchase these templates?

The “Project Management Templates for Excel” toolkit is aimed at project managers and analysts who use excel to manage their projects. The templates are designed keeping the project managers in mind. You need just the basic knowledge of excel to start using these templates and make a difference in your projects.

Supported Versions of Excel

The “Project Management Templates for Excel” toolkit comes in two versions. One for Excel 2007 and above, another for Excel 2003. The templates use excel features like Data tables (or lists). So I don’t recommend you to buy this toolkit if you are running Excel 2002 or earlier versions.

Get your copy of Project Management Templates for Excel

How the purchase process works?

30 Days Money Back Guarantee

If you do not like the templates or have any other reason for returning them, please write to me at chandoo.d @ gmail.com and I will refund your payment. No questions asked. That simple!

Customer Support

We have opened a separate discussion forum area for the members who purchase the project management templates. Please log in to PHD Discussion Forums and ask your questions there. I will reply to you and resolve any questions you may have in implementing these templates.

Also, limited amount of support is available through e-mail. Feel free to write to me at chandoo.d @ gmail.com and I will get back to you at the earliest.

So what are you waiting for?

Click here and download your copy of project management templates for excel today.

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 – Project Management Dashboard
Bonus Post: Using Burn Down Charts to Understand Project Progress

Communication is a very important aspect of project management. Communicating with stakeholders, sponsors, team members and other interested parties takes up quite a bit of project manager’s time.

In almost all the projects I have been part of, the first and foremost question anyone used to ask us is, “how is the project going?”. There is no one line answer to this. A project status dashboard or project status report can help us express the project status in a crisp yet effective manner.

In today’s installment of project management using excel series, we will learn how to make a project management dashboard using Microsoft excel. [related: Making Dashboards using Excel]

To make the project management dashboard, you must answer the following questions,

• Who is the audience of this dashboard?
  • Top management or project sponsors or team members or other departments?
• What are they interested to know?
  • Day to day issues or High level stuff or Plans or Budgets?
• What is the frequency for updating the dashboard?
  • Weekly, Bi-weekly or Monthly or Once in a blue moon?

The answers to these questions will determine what goes in to the dashboard and how it should be constructed.

For our example, I have assumed the following scenario, but you can easily change the dashboard constituents based on your situation.

• Audience of the report: Project Sponsorship Team
• Interested to know: Project Progress wrt Plan, Blocking issues, Overall timeline and Delivery Progress
• Frequency: irrelevant (could be weekly or bi-weekly)

Step 1: Make an outline sketch of the dashboard

Based on the above answers, we vaguely know what should go in to the dashboard. Based on this, we should make an outline sketch of the dashboard. This will help you structure the dashboard on an excel spreadsheet. For our example, this is the outline I have prepared.

the finalized dashboard will look like this: (click here for a bigger version)

Step 2: Get the data to be placed on dashboard

Making a dashboard in excel is a complex and intricate process. Knowing the outline of the dashboard is only the 10% of work. Getting your data to calculate the dashboard metrics (or KPIs) is the most vital part of any dashboard construction.

In our outline, the sections 1,2 and 3 are purely data and 4,5 and 6 are charts prepared from data.

To facilitate this, first, let us create a worksheet named “data” where we can capture user inputs. These inputs can be further manipulated to make the dashboard.

For our dashboard, we need the following inputs,

• Overall project status and progress
• List of ongoing activities and issues

We will derive other inputs from the following,

• Project Plan Gantt Chart discussed in Part 1 will provide us the project plan
• Project Timeline Chart in Part 2 will give us the timeline chart
• Burn down chart will give us the project deliverable status
• Issue Tracker discussed in Part 5 will give us the metrics related to issues

Step 3: Put everything together and make a dashboard

[PS: I have greatly simplified the process of dashboard construction to keep the article readable. Please note that this step usually takes a few of hours and has lot more detail]

Now that we have all the bits of our data ready, we just need to bring them together to make a dashboard.

We will use the following excel concepts,

• Excel Camera Tool to get a live snapshot of the project gantt chart
• Conditional Formatting to show Red, Green or Amber traffic light to depict the project status
• Thermo-meter chart to show the project progress against 100% total
• We will create a stacked bar chart of outstanding issues by using sumproduct() formula [examples] to summarize using multiple criteria [counts for issue status="open" and issue priority="high", issue status="open" and issue priority="medium", issue status="open" and issue priority="low"]

Let us place the remaining pieces of dashboard from already constructed charts and available data,

• Burn-down chart to show the project deliverable status
• Project Time line to show the project milestones over a period of time
• We will create references to the “issue” and “activity” data and show only the first 5 items.

See the below illustration to understand how each part of the dashboard is constructed.

That is all, our dashboard is ready now.

Download the project management dashboard excel file

Unlike other downloads on PHD, this file is locked. Don’t panic, there is a reason for it. Next week I am going to release project management bundle for excel. By purchasing this bundle, you can get an unlocked version of this dashboard along with a handful of useful project management templates. Stay tuned and get a flavor of things to come from this locked file. The bundle is already released. Click here to buy it.

• To download the locked version of project management dashboard excel file click these links:  excel 2003, excel 2007
• To get an unlocked version of the dashboard along with 23 other templates, click here.

Tell us about your Project Management Dashboard / Status Report

Tell me about your project management dashboard, project status report formats and how it is constructed. Do you use excel or some other tool (like powerpoint, word) to prepare the report? How the report / dashboard generated? Is the process automated or manual? What have you learned from using / making such status reports?

What next?

This is the last installment of project management using excel series. I am looking for ideas to extend this series in useful manner. Please use comments to tell me what other activities of project management can be made easy using Microsoft Excel. I will try to write follow up posts if the topics are interesting.

Thanks a lot for reading the series and suggesting valuable inputs to make it better. I have learned a lot about project management and excel writing this series. I hope you have picked up few concepts too.

Tell me your feedback using comments.

As more and more people are migrating to excel 2007 (and eventually to excel 2010) it is very important to master the ribbon UI to be  productive with spreadsheets.

So to make you an excel guru, I am releasing a free learning guide to excel 2007 ribbon interface.

The learning guide has 10 pages. It explains 7 ribbons and has 3 more pages of ribbon tips. The ribbon tabs explained are,

1. Home ribbon tab
2. Insert ribbon tab
3. Page Layout ribbon tab
4. Formulas ribbon tab
5. Data ribbon tab
6. View ribbon tab
7. Review ribbon tab

See the sample page for insert tab (click on it to see at higher resolution)

Download the free Excel 2007 learning guide now

Click here to download the Using Excel 2007 Ribbon – Learning Guide.

What is the catch?

There is no catch, except that, I am in a generous and becoming-a-daddy mood.

But if you must catch, just go ahead and sign-up for our e-mail news letter. It is free, awesome and packed with super-cool tips. And as if there is not enough free, you will also get a 25 page free e-book on using excel when you sign-up. It has 95 really fun and productive excel & charting tips.

Go…

Learn everyday excel formulas using this e-book

What is an excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

When you make a table (more on this in a sec) you can easily add more rows to it without worrying about updating formula references, formatting options, filter settings etc. Excel will take care of everything thus making you a Data God.

How to create table from a bunch of data?

To create an excel table, all you have to do is select a range of cells and press the table button from Insert ribbon in Excel 2007.

See this simple tutorial:

Today we will learn 10 excel data table tricks that will make you a data god, no, lets make it data GOD.

1. Change table formatting without lifting a finger

Excel 2007 has some great pre-defined table formatting options. Just select any cell in your table and change the table formatting by going to “format as table” button in the home ribbon.

If you are bored with the predefined formats, you can easily define your own table formatting color schemes and apply them.

2. Add Zebra Lines to Tables without doing Donkey Work

When you create a table, zebra lines come as a bonus. And when you add new rows to the table, excel takes care of zebra lining or banding automatically. You can turn on / off the banded rows feature from “design ribbon tab” as well.

That means you don’t need to use conditional formatting or manually format alternative rows in different color.

3. Tables come with Data Filters and Sort Options by default

Each data table comes with filters and sorting options so that you can filter and sort the data in that table independently. That also means, if a worksheet has 2 tables, they each get their own data filters (usually excel wont allow you to add more than one set of filters per sheet, but when it comes to tables, all exceptions are made, just for you)

4. Bye, bye cell references, welcome structured references

The most important advantage of tables is that, you can write meaningful looking formulas instead of using cell references. When you create and name the table (you can name the table from design tab), you can write formulas that look like this:

The beauty of structured references is that, when you add or remove rows, you dont need to worry about updating the references.

5. Make Calculated Columns with ease

Any tabular data will have its share of calculated columns. Excel tables make having calculated columns very easy. With structured references, all you need to know is English to make a calculated column. The beauty of calculated columns in table is that, when you write formula in one cell, excel automatically fills the formula in the rest of cells in that column. Aint that good for a God?

6. Total your Tables without writing one formula

The ability to summarize data with pivot tables is extended to excel tables as well. You can add total row to your table with just a click.

What more, you can easily change the summary type from “sum” to say “average”.

7. Convert table back to a range, if you ever need to

Tables is a new feature in Excel 2007. So when you need to send that excel file to a colleague running excel 2003, you can easily convert the tables back to named ranges.

Excel will take care of the formulas and change the references to cell references.

8. Export Tables to Pivot Tables, Woohoo

What good is a bunch of data when you can analyze it? That is where Pivot tables come in to picture [pivot table tutorial]. Thankfully, you dont need to do much. Just click a button and your table goes to pivot table.

9. Push the table data to Sharepoint Intranet Site

If you have a corporate intranet Sharepoint portal, you can easily publish the excel tables as share-point lists. This can be handy if you want to publish, say the top 10 sales persons of the quarter on the intranet.

10. Print Tables Alone, with out all the other stuff around

Select the table, hit CTRL+P and select “Table” for “print what?” and you will be able to print the tables alone. This is far more easier and cooler than trying to adjust print settings when you are printing tabular data.

So, What do you think about data tables?

I say, give them a try. You can find some cool uses for tables in your data to day work. They are intuitive, easy to use and provide great power without added complexity.

Related Material

Excel 2007 Review – 10 things better and cooler in excel 2007
Excel 2007 Productivity Tips & Tricks
Excel Pivot Tables – Tutorial & Tricks
Excel Conditional Formatting – 5 Must Know Tricks

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
Project Status Reporting – Dashboard
Bonus Post: Using Burn Down Charts to Understand Project Progress

Tracking issues and risks is where most of the project management time goes. Once the project planning and organizing activities are in good shape, most of the project management activities are around risk management and issue tracking. In this installment of project management using excel, we will learn how to create a simple issue tracker template using excel and how to analyze issues using excel.

Issue Tracker Template

Excel is perfect for making an issue tracker template. Its grid structure and easy interface makes it totally easy to create and maintain an issue log. Here is a simple issue tracker template you can create in less than a minute.

The above template becomes very easy to manage with excel features like data validation, filters and tables (lists in 2003 and earlier).

More Robust Issue Log Template

While the above issue tracker template is good for most project needs, often you might need something little more robust. Of course, doing this is just a matter of adding few columns. For eg. it is common for project managers to keep track of the various types of issues and who is logging them, who is closing the issues. Here is an issue log template that is more robust.

Analyzing and Reporting Issue Status

Issues are part of everyday project management. It is important to keep track of various issues in the project and understand their progress. There are various ways to monitor the progress of issues using excel charts and pivot tables. In this tutorial, we will learn how to make the open vs. closed issues chart (see below).

1. To make the chart, we will use the issue tracker data from the template shown above.
2. We need to generate issue counts for the last 30 days from a chosen date like this:
   
3. The counts can be easily generated by using the COUNTIF Excel formula [tutorial] like this: =COUNTIF(issueOpenDates,Date)
4. We can easily make the counts cumulative.
5. Finally select the 3 columns above and make a  line chart with 2 series. Adjust the chart formatting you have a simple “open vs. closed issues in the last 30 days chart”
6. The above chart can be a great way to start discussion about issue run rate.

Risk Management using Excel

We can use similar ideas to prepare a risk management plan using excel. The risk log is similar to issue log. But when it comes to risk analysis, the usual practice is to make a risk matrix to highlight key risks. This can be easily done in excel with the help of heat maps. This is your home work to figure out.

Download the Issue Tracker Templates

You can download the excel issue log template from here. Click the below link based on your excel version and the file type you prefer.

• Download Issue Tracker Template [Excel 2003 and earlier]
• Download Issue Tracker Template [Excel 2007 and above]
• Download Issue Tracker Template [Both files in a zip]
• Download 24 Project Management Templates for Excel

What next?

The ideas presented here can be extended to do more complex analysis of issues and risks in your project. However the issues tracker systems can only go so far if we don’t ask right questions. Often when the project is going through a rough patch, it might be better to keep the issue trackers simple and focus on the work.

In the next installment of project management using excel, we will combine all the five parts to build a project status reporting dashboard.

If you are new to the series, please read the first 4 parts as well.

• 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 Sheeet Templates and Resource Management using Excel
• While at it, also check out the bonus post about Burn Down Charts.

What is your experience with issue tracker systems

Share your ideas and opinions on using issue trackers. What is the best and worst you have seen? In one project, we have used a very complicated issue log (actually a defect log) that took almost 5 minutes to create an issue. The system would produce nice looking 3d bar and 3d pie charts depicting the issue distribution, ownership and status. Our morning scrums were a disaster when someone choose to present these. What about you?

The principle behind pareto charts is called as pareto principle or more commonly the 80-20 rule. According to wikipedia,

The Pareto principle (also known as the 80-20 rule,[1] the law of the vital few, and the principle of factor sparsity) states that, for many events, roughly 80% of the effects come from 20% of the causes.

The pareto chart is a great way to do the pareto analysis. Today, we will learn how to use excel to make a pareto chart.

See an example pareto chart of visits to this website:

(Please note that in this example, the 80/20 rule does not hold as I have chosen very small sample of data. In reality, the 80/20 principle applies to my website as well)

Making a Pareto Chart in Excel

In order to make the pareto chart in excel, first you must have the data ready. Once we have the values for each cause, we can easily calculate cumulative percentages using excel formulas. We will also require a dummy series to display the “cutoff %” in the Pareto chart.

I have arranged the data in this format. You can choose any format that works for you.

Once you have the data ready, making the pareto chart is a simple 5 step process.

1. Make a column chart using cause importance data

In our case, we select the first 2 columns in the above table and then make a new column chart.

2. Add the cumulative %s to the Pareto Chart as a line

Select the third column, press ctrl+c (copy). Now select the chart and press ctrl+v (paste). Excel will add another column series to the chart. Just select it and change the series chart type to “line chart”. Learn more about combining 2 different chart types in excel combo charts.

3. Move the cumulative %s line to secondary axis

Select the line chart, go to “format data series” (you can also press ctrl+1) and change the axis for this chart series from “primary” to “secondary”.

4. Add the cut-off % to the pareto chart

Select the fourth column in our data table, copy and paste it in the chart. This should ideally be pasted as a new line chart. If not, follow step 2 for this as well.

5. Finally, adjust formatting to make the final pareto chart

Now, our basic pareto chart is ready. We should adjust the chart formatting to make it more presentable. Once you are done, the final output will be something like above chart.

Download the Pareto Chart Template in Excel

Click here to download the excel pareto chart template.

When to use Pareto Chart?

Pareto charts can be used,

• During quality control to analyze the causes of defects and failures
• When you want to focus your resources on few important items from a large list of possibles
• To tell the story that attacking problem A might be better than solving problem C, D and F

Pareto charts and pareto analysis has great practical uses for almost anyone in a managerial role.

Have you used Pareto analysis or Pareto charts in your job?

Pareto principle is the first real management lesson I have learned during my MBA. It is the topic for my first presentation too. During the presentation, Anoop, my jovial team mate said, “80-20 principle can be tested anywhere. For eg. in most parties 80% of the beer will be consumed by 20% of people”, and the whole class started laughing.

Jokes apart, I think pareto principle is a very powerful idea told in an extremely simple way. I use the pareto analysis to find best way to invest my time. What about you? Tell me about your experiences of using pareto analysis using comments.

Related Material:

• Excel Combination Charts – What are they? [video tutorial here]
• Burn Down charts – Project Management using Excel
• Pareto Principle and the Math behind it

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.

Here is the complete list of entries for the twitter formula contest.

Follow the links next to contributor’s name to see the original twitter post or comment

To return the full Path+Filename of your (saved) workbook

by Dmurphy on PHD comments

Create a Dynamic Range that Grows and Shrinks with Data

by ps62 on twitter [@ps62]

Find the last cell in a row

by govi on twitter [@govi]

Cleaning your data (Example, changing the values in a column)

by artjohnson on twitter [@artjohnson]

Extract the month from a date

by Alan on PHD comments

Clean your text before you lookup

by rushikul on twitter [@rushikul]

Find if two ranges are statistically different

by nandoaires on twitter [@nandoaires]

Lookup 3 criteria and return the match

by Alan_xls on twitter [@Alan_xls]

Offset with Match, get data from somewhere else

by Arnab Bose on PHD comments

Using SUM with multiple conditions

by ps62 on twitter [@ps62]

VLOOKUP but get values from the left

by bsamson on twitter [@bsamson]

Getting data from a dynamic range

by Arnab Bose on PHD comments

Find the difference between maximums of two ranges

by PreetAulakh on twitter [@PreetAulakh]

Find the top 3 values of a range

by JassiAulakh on twitter [@JassiAulakh]

SUMPRODUCT with multiple conditions

by Martin on PHD comments

Here’s my little contribution (previously posted Named Ranges (should be dynamic, but….) Ship $A$2:$A$8 Captain $B$2:$B$8 flights $C$2:$C$8 in F:F Summary_ship $F$2:$I$2 this 3:3 Summary_Captain $E$3:$E$6 data is in range A1:C8, and summary is in E1:I6. =SUMPRODUCT((Ship=in Summary_ship)*(Captain=this Summary_Captain)*(flights))

Get the name of the workbook

by Dmurphy on PHD comments

Excel Formula Fun - Should we fight… ?

by chrismelck on twitter [@chrismelck]

More ways to use IF and Then formula

by Olu D. on PHD comments

Using INDIRECT along with VLOOKUP to make dynamic lookups

by squash86 on twitter [@squash86]

Calculate the p-value of a t-statistic [Don’t ask me what it is ]

by David on PHD comments

What is on the right side of that string

by aniVy on twitter [@aniVy]

Find frequency distribution of a range of values

by Cody on PHD comments

In-cell bar graph

by JohnCorp on twitter [@JohnCorp]

Get the name of the current worksheet

by Dmurphy on PHD comments

Excel formula fun – Usetheforce()

by _mikii on twitter [@_mikii]

UDF to calculate to royalty, I am not getting any

by chrislbs on twitter [@chrislbs]

Find the Next Friday the 13th

by S3bast1an on twitter [@S3bast1an]

Split first name and last name

by Mahmut on PHD comments

IF with a VLOOKUP

by m4th1337 on twitter [@m4th1337]

And now for the winners

I wish I had more prizes to give. All the tips are truly marvelous. I have learned several cool uses of excel formulas. But alas, we have only 2 prizes in this contest.

Dashboard bundle from Bonavista Systems goes to Govi

The excel formulas 2007 book by John Walkenbach goes to DMurphy

Both the winners are randomly selected. I have already sent them an e-mail with the further instructions to claim the prizes.

Big thank you to Bonavista Systems, the contest sponsor

I would like to thank Andreas from Bonavista systems for sponsoring the dashboard bundle. Bonavista systems makes some really cool tools for excel dashboards, spark-lines and helps you make cleaner and better looking charts. Checkout their products and know more about them from their site.

Further Resources if you want to learn Excel Formulas

• 15 Excel formulas to transform you from beginner to pro
• Important excel formulas: IF and Then, Vlookup, Offset, Sumif, Countif, Working with date and time
• Debug excel formula errors
• 50 Everyday excel formulas explained in plain English

In plain English, it means, you can take the sales data with columns like salesman, region and product-wise revenues and use pivot tables to quickly find out how products are performing in each region.

In this tutorial, we will learn what is a pivot table and how to make a pivot table using excel.

click here to see a video tutorial of making pivot tables in excel

Example uses of Pivot Tables

As I said before pivot tables are very powerful and useful. There are numerous uses of pivot tables that we can talk about them until Christmas.

Here are some example uses of pivot tables:

• Summarizing data like finding the average sales for each region for each product from a product sales data table.
• Listing unique values in any column of a table [learn more]
• Creating a pivot report with sub-totals and custom formats
• Making a dynamic pivot chart
• Filtering, sorting, drilling-down data in the reports without writing one formula or macro.
• Transposing data – i.e. moving rows to columns or columns to rows. [learn more]
• Linking data sources outside excel and be able to make pivot reports out of such data.

Excel Pivot Table Tutorial: How to create your first pivot table

Let us make your first pivot table. We will use example data in the following format. Download the excel pivot tables tutorial workbook with the data.

Step 1:  Select the data
Select the data range from which you want to make the pivot table.

Step 2:  Go to Insert ribbon and click on new Pivot table option
To insert a new pivot table in to your spreadsheet, go to Insert ribbon and click pivot table icon and select pivot table option.

Step 3:  Select the target cell where you want to place the pivot table. For starters, select New worksheet.
Excel will display a pivot table wizard where you can specify the pivot table target location etc. Select “New worksheet” option and your pivot table will be placed in newly created worksheet.

Step 4:  Make your first pivot report

The pivot report UI is very intuitive and sandbox like. To make powerful analysis, all you have to do is drag and drop fields in to the pivot table grid area. In excel 2007, you can also control this by using the “Pivot table panel”.

The pivot report is divided in to header and body sections. You can drag and drop the fields you want in each area. The body itself contains three parts. Rows, Columns and Cells. You can use any fields in these areas too.

For the above sample data, I have set this criteria:

And the outcome is this pivot report.

It might be a bit difficult to understand how this works. But believe me, if you have seen any reports or worked with any other reporting systems, then the idea of pivot tables, pivot reports and pivot charts becomes quite simple to you.

You can use the excel pivot table features to make a more complicated pivot report like this in no time.

Some useful tips on Excel Pivot Tables

• You can apply any formatting to the pivot tables. MS Excel has some very good pivot table formats (and they are better in Excel 2007 and 2010).
• You can easily change the pivot table summary formulas. Right click on pivot table and select “summerize data by” option.
• You can also apply conditional formatting on pivot tables although you may want to be a bit careful as pivot tables scale in size depending on the data.
• Whenever the original data from which pivot tables are constructed, just right click on the pivot table and select “Refresh Data” option.
• If you want to drill down on a particular summary value, just double click on it. Excel will create a new sheet with the data corresponding to that pivot report value. (This is extremely useful)
• Making a pivot chart from a pivot table is very simple. Just click on the pivot chart icon from tool bar or Options ribbon area and follow the wizard.

Download the excel pivot tables tutorial workbook and practice yourself

Click here to download the excel pivot tables tutorial workbook. [.zip version of tutorial here]The workbook has sample data and one pivot table in it. You can play with it to learn more.

Checkout the video tutorial to make excel pivot tables

Click here to see a video tutorial of making pivot tables in excel

Share your experiences of using pivot tables

Tell me how you use pivot tables, your favorite tricks using comments.

This post is part of our spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.

Waterfall charts are great, especially for visually showing the contribution of parts to a whole. While there are several tutorials on how to make a waterfall chart online the end products of these tutorials rate low on the visually appealing scale.

The principle problem with these charts is the separation between the elements of the waterfall. They are always either pushed together (Example A) or left apart, without element connectors (Example B):
Example A

Example B

Many users of waterfall charts employ the separated (default) version (example B) opting to add in element connectors manually via Insert>Shapes>Line on the Excel tool bar. The frustration with this approach is that all too often the values of the chart elements will need to be updated or changed forcing user to manually readjust each of their connector lines in turn.

With some simple charting trickery in Excel 2007 one can easily make a waterfall chart with connectors that will update automatically as element values are changed.

A Better Waterfall Chart

Steps to Building a Better Waterfall Chart

List of data series (columns) needed for your chart:

• Horizontal Axis Labels: in the example above North, East, South and West.
• Base Values: What your element values will “sit on.” Essentially this is the white space beneath each charted element shown above.
• Element Values: the meat and potatoes of your chart – the value of your elements as you want them to appear (above these are 40, 30, 20, 10 and 100).
• Label Spaces: This is optional but it allows you to place the value of you data elements on top of their respective bars (this avoids the use of the annoying Label Position options available after one has used Add Data Labels).
• Label Connectors: This is the key item needed to create the chart as shown above. You will need one column (series) for each of the data elements (excluding one for the total). For the chart above, four label connector series are needed.

Step 1: Enter all of the required series in a worksheet:

• Horizontal Axis Labels – self explanatory
• Base Values – A running total of the subsequent Element Values (Column C). Whereas nothing proceeds North in the example above leave its base value blank. Do the same for Total.
• Element Values – These are whatever numbers you want to highlight in your chart. These are represented by the blue column segments in the above chart.
• Label Spaces – Again this is optional. These will eventually hold text labels for the Element Values (Column C). The numbers here should all be the same and be some number about 1/4 to 1/3 the value of your lowest Element Value.
• (to H. ) Connectors – Connectors 1 to 4 correspond to Axis Labels North to West in the example above. In the respective Connector column make the cell at the row corresponding to the related Axis Label equal to the sum of Column B + Column C (Base Value + Element Values). Enter the same value for the cell beneath.

Step 2: Chart data and adjust

1. Select your data, here A2:H6, and go to Insert>Charts>Column>2-D Column>Stacked Column>OK (to exit). Your chart should look like this:

2. Switch column and row data by right-clicking within the chart and going to Select Data…>Switch Row/Column>OK (to exit). Chart should now look something like this:

3. The top colored column element in each column (purple, aqua, orange and baby blue, respectively) is what will become that Element Value’s connector. To convert to these columns to connectors, in turn, right-click on the series (the first one is the purple column element) and go Change Series Chart Type…>Line>Line>OK (to exit). Repeat this process for the other connector column elements (aqua, orange and baby blue). After this step your chart should look like this:

4. Follow this up by formatting each connector in turn. Right-click on the connector and go Format Data Series…. Consider making the Line Color>Solid Line>Color black, Line Style>Width .25 pt and Line Style>Dash Type>Square Dot. Play around with these options as you see fit to get the best look. Again, do this for each connector.

5. Remove grid lines (optional), delete the Legend (necessary). Your chart should now look like this:

6. Go into you Base Values series (blue column element in the chart above) and eliminate the color fill and borders: right-click on a blue column element and go Format Data Series….>Fill>No Fill and Border Color>No Line>Close (to exit).

7. Format your Element Values series (red above, using same process in Step 6 to change the fill color and add a border.

8. Right-click on your Label Spaces series and go Add Data Labels…. Don’t worry about the value on the labels for now, they’ll be changed in the next step. Follow this up by formatting the Label Spaces series just like how the Base Values series was formatted (in Step 6). Make it so there is no fill and there are no borders. This is what you should now have:

9. All that remains is to convert your labels to the values of the Element Values. To do this for each label: click on the specific label twice (so that only the box for that label appears, as below).

Click a third time on the edge of the box that appears and then type the equals sign “=”. Now go back to your data table and click on the cell of the Element Value that you want appear in the label. Then press enter. This links the value of the label to the Element Value (if your Element Value ever changes so too will the text in this label). Repeat this for the other data labels in turn. The result is your Better Waterfall Chart:

Download the Waterfall Chart Template:

Please download the waterfall chart template from here [.zip version here]

Final Thoughts

I hope you will agree that this waterfall chart is more visually appealing that the examples at the start of this tutorial. In addition to a more professional look this waterfall will fully update (step heights, labels, connector positions) automatically whenever you change your Element Values. While the process of implementing this form of waterfall chart may at first seem cumbersome it can be quickly implemented with some practice and is a great item to have in your charting toolkit. Enjoy.

Note from PHD:

• Thank you so much Aaron. You have taught us a very valuable tutorial. I really appreciate your effort in putting this together.
• If you need to make a lot of waterfall charts, I recommend trying Jon Peltier’s Waterfall Chart Utility.

Hello there, Reader: If you like this waterfall chart tutorial, please drop a note of thank you to Aaron through comments.

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
Issue Trackers & Risk Management
Project Status Reporting – Dashboard
Bonus Post: Using Burn Down Charts to Understand Project Progress

Timesheets are like TPS reports* of any project. Team members think of them as an annoying activity. For managers, timesheets are a vital component to understand how team is working and where the effort is going. I will not get in to the merits and pitfalls of timesheets. However, I feel that by using Microsoft Excel capabilities you can create a truly remarkable timesheet tracking tool and still leave your team members un-annoyed.

In this tutorial we will learn 3 things about timesheets and resource management using Excel

1. How to setup a simple timesheet template in excel?
2. How to make a more robust timesheet tracker tool in Excel?
3. How to use the timesheet data to make a resource loading chart?

1. Make a Simple Excel Time Sheet Template

According to Wikipedia, timesheets are used for

Timesheets may record the start and end time of tasks, or just the duration. It may contain a detailed breakdown of tasks accomplished throughout the project or program. This information may be used for payroll, client billing, and increasingly for project costing, estimation, tracking and management.

By defining a simple and straight forward template in Excel and using it to track time (or efforts) in your projects, you can easily consolidate the data, compare efforts and make any necessary analysis.

At its simplest form, the timesheet is nothing but list of team members and list of activities in a matrix. Look at the below example:

You can easily create such template in excel.

2. A More Robust Excel Timesheet Tracker

While the time sheet format shown in the above section is good, it is a wrong format if you need to analyze the timesheet entries of a 100 member project. Also in large projects usually members do few activities at a time. That means the above format (in section 1) will result in a sparse matrix.

Using a tracker log format is much more convenient to both record and analyze timesheet entries. Look at the example below:

We can use excel features like data validation drop downs, shared workbooks to make the timesheet entry and management a breeze.

3. Create a Resource Loading Chart – Project Management

Resource loading charts are a good way to show how busy the team members are in a project. At the outset the resource loading chart is nothing but a heatmap.

Look at an example resource loading chart below:

You can make a resource loading chart in MS Excel by following the below steps:

1. The pre-condition for the resource loading chart is that we have clear data available to make one. This is where the robust timesheet tracker shown in section 2 of this post comes handy.
2. First create a blank table in excel with team member names in first column and week numbers in first row. (Please note, you can make other types of resource loading charts by changing the Row and Column headers. For eg. You can show resource loading by Project and Team member)
3. Assuming we have the time sheet data in the format shown in Section 2,
4. Assuming “log_member_names” refers to the member name column and log_weeknum refers to the last column in the timesheet, we can write a simple SUMPRODUCT formula like this: =SUMPRODUCT(--(log_member_names="John Galt"),--(log_weeknums="3")) to calculate the count of activities John Galt has done on week 3. Learn more about using SUM and SUMPRODUCT to calculate sumif with multiple conditions.
5. Once we calculate values for all team members using the above formula, we can apply conditional formatting to make the heat map. In Excel 2007, this is one step. In earlier versions of excel, you need to specify 3 conditions to make the heatmap look hot enough or use a macro to get over the 3 conditional formats limitation.
   
6. That is all.

Download the Excel Timesheet & Resource Loading Chart Templates

You can download the excel time sheet template, timesheet tracker log template and resource loading chart template from here. Click the below links:

• If you have Excel 2007 and above, download the .xlsx template
• If you have Excel 2003 and earlier, download the .xls template
• If you are not sure, download both in a .zip file
• Download 24 Project Management Templates for Excel

What Next?

Timesheets are a great way to understand how the effort is spent. Even though project estimation models have become more and more effective, still lots of projects are overshooting budgets and timelines. And this is where timesheets can help you as a manager. While estimation looks in to future, timesheets look at past. Timesheets give feedback to your estimation models. This can help you in making better estimates in future.

In the next installment of this series, we will learn about tracking issues and risks using excel spreadsheets.

If you are new to the series, please read the first 3 parts as well.

• Preparing & tracking a project plan using Gantt Charts
• Team To Do Lists – Project Tracking Tools
• Project Status Reporting – Create a Timeline to display milestones
• While at it, also check out the bonus post about Burn Down Charts.

Your thoughts and suggestions?

What are your ideas about timesheets using excel? Does your organization use excel as a way to manage timesheets or do you use some time tracking software? What is the granularity of detail captured in timesheets? As a project manager, what use do you find in time sheet data?

Share your ideas and experiences using comments.

*PS: If you are wondering what the heck TPS reports are, then you are spending way too much time with Excel buddy. And while at it, you missed the greatest comedy of all time. Go watch office space, now!

PPS: the TPS report image is from wikipedia.