Microsoft Excel for Project Management - Earn 5 PDUs

In this section I’ll introduce the concept of reports, dashboards, and why you should use Microsoft Excel to help you build these items. Reports are documents that inform readers of how your project is doing. Dashboards are consoles that help you drive the project.

In this section we’ll also discuss some good rules to follow when it comes to design your dashboards and reports. You’ll want to take some time and really plan out what you want the reports and dashboards to accomplish for your project – and for your stakeholders.

What’s the difference between a report and a dashboard? Is there a difference? Of course – but why should you care. In this lecture we’ll dive into the basics and build a good foundation of the primary goal of this course: to create reports and dashboards.

Reports are packaged and formatted information you share with stakeholders: management, the project sponsors, the project team, customers, and other stakeholders that need the information on a regular basis. Reports can be automated and that’s one of our goals in this course.

Dashboards are consoles of information. As you enter data into Microsoft Excel you can create a custom, single-worksheet that displays the results of your project performance. We’ll create a dashboard later in this course.

Great job finishing this section on using Microsoft Excel for project management. Microsoft Excel is just a tool that you’ll use to reach your objectives. Throughout this section, and the remainder of the course, keep in mind that there is only one rule for designing reports and dashboards: it must meet the stakeholder requirements.

This means you’ll need to speak with your project sponsor, and your key stakeholders to determine exactly what they want you to capture in the project. By establishing your communication and reporting requirements as early as possible you can more effectively design a report and dashboard that works for you – and the stakeholders.

It’s important that you and I have the same understanding of what Microsoft Excel is – and is not. This quick section will help you tour through Microsoft Excel and discuss the concept of tables. We’ll also take a moment to talk about formatting numbers in Excel.

This section provides a good foundation of the concept of rows, columns, data ranges, and columns. We’ll be working with these concepts throughout the course, so it’s important to define this stuff upfront in our course.

What’s a table and what’s on the table that’s any good? Microsoft Excel is really just one big table. Tables are made up of rows and columns. Rows go side-to-side and Columns go up and down. I like to say rows are like rows of corn, and columns are like the Greek temple – if that helps you remember.

In Microsoft Excel we’ll work with rows and columns of data to build reports and dashboards. The intersection of a row and a column is a cell. Cells are the individual “squares” you see in Excel and it’s where you’ll be entering your data.

A cell is the intersection of a row and a column in Microsoft Excel. Cells are numbered by the column letter and the row number; for example cell B23 would be the cell in column B and the 23^rd row. Each cell in your report or dashboard can be formatted differently, though we generally want a uniform and clean look to the cells.

In this exercise you will experiment with formatting the cells, the cell numbering, and comparing different formatting options. Let’s open Microsoft Excel to complete the exercise.

You want your data to look clean, concise, and easy-to-read. Busy, spreadsheets are distracting to your readers and hard to work in as the project manager. In addition, and to be frank, spreadsheets that don’t follow a consistent formatting look amateurish and that’s not something you want in any communications.

In this lecture we’ll discuss how to properly format numbers for dashboards and reports. It’s easy to do – and whatever approach you land on you should, and must, be consistent throughout your reports. You don’t want to change the look and feel of the report from week-to-week. You want to set expectations for what the stakeholders will receive and how that information is presented. Let’s get it right from the start.

In this exercise we’ll take a look at how to format numbers in Microsoft Excel for single line accounting underlining. This formatting looks clean and neat – and it’s a snap to apply.

This exercise is also a good place to start with the exercise files you’ll download from the resources. ready to knock out this course on Microsoft Excel for Project Management? Good! Let's get going in this exercise.

Great job finishing this section on Microsoft Excel for Project Management.

We discussed what tables are, the difference between columns and rows, and how you can navigate in Excel. We also took a quick look at formatting numbers in Microsoft Excel – something you’ll want to do in your reports and dashboards.

Throughout this course if you have a comment or question – don’t be shy! Add your comment to the course and I’ll help you out. I check out the comments and questions pretty often. Thanks!

Visualizing the data is important for you the project manager and for your stakeholders. You want to format the data so it’s easy to read, makes sense for the reader, and can be updated easily. Heck, we want to update the reports and dashboards automatically too!

In this section we’ll discuss how to add Sparklines to your worksheets and we’ll dig around in conditional formatting. We’ll also take a look at how data bars and color scales can help your reports visualize and communicate what’s happening with big chunks of data. Let’s go check it out!

Whee! Sparklines! Sparklines are like quick charts you can add to data to show performance. You’ll need a row or data, called a series, that has significance in your project. For example, you might have a row of hours worked by each team member on the project over several weeks of a project phase. A Sparkline added to the end of row would shows the highs and lows each work of the project or phase.

While Sparklines are cool and fun to add to your projects, it can be distracting to readers of the report. Use these sparingly and only for information that is going to get a cursory look. For more import information we’ll create a standalone chart that represents all the data. With that in mind, let’s hop in and explore with Microsoft Excel’s Sparklines.

We’ve talked about Sparklines and how these “mini-charts” can quickly show trends in a series. In this exercise we’ll dive into the action by adding Sparklines to a series.

Be prepared to work in Microsoft Excel in this exercise to add Sparklines to your data. Of course, you wouldn’t do this in every report, but it’s a nice addition for a quick visual summary of what’s happening in the project.

Conditional formatting allows you to create rules and conditions that trigger automatic formatting in your reports. Based on the conditions of the project information the cells and data within can be formatted in different colors, backgrounds, and even with pictures (if you really wanted to).

In this lecture we’ll create some rules to experiment with the data in Microsoft Excel to format the cells. For example, you could have a row that has a record of all project team member hours worked. With conditional formatting, you could create a condition where anyone who’s worked more than 40 hours in one week is highlighted in yellow.

If you can imagine the conditions you can create a rule to apply a format to the data. Let’s go!

Recall that conditional formatting are rules that are built into Microsoft Excel to automatically format data based on the conditions you define. Conditional formatting are simply formulas where the outcome of the formula causes a format, such as color, to be applied to the data.

Microsoft Excel has many pre-built conditional formatting rules we’ll investigate, but know that you can customize these pre-defined rules to best suit your reports and dashboards. In this exercise we’ll experiment with conditional formatting options in Microsoft Excel.

Imagine a project with twenty-five project team members. That’s a lot of data to keep track of for each person: hours worked, sick days, cumulative costs, and activities in motion versus activities completed. It would take much effort to manually find the top, or bottom, performers in that collection of data.

With Microsoft Excel’s Top/Bottom rules, a type of conditional formatting you can quickly identify top or bottom performers in a given range of data. You can also use these rules to identify things like a percentage of top/bottom performers, costs, or even an average. It’s pretty nifty, so let’s take a look.

In this exercise we will explore the top/bottom rules within conditional formatting. This formatting approach allows you to automatically format the data based on:

• Top 10 items
• Bottom 10 items
• Top percentage of items
• Bottom percentage of items
• Above/below average

This formatting approach is a fast way to format a range of numbers based on top/bottom performers. In this exercise we’ll experiment with these conditional formatting rules.

Data bars, another type of conditional formatting, is a method to quickly identify data that meets given criteria. Data bars, however, can use a heat mapping approach to your data information. For example, you might have a list of cumulative costs for each month of your project. With data bars you could identify which months spent more than others – and create a fill of each cell that’s representative of how much was spent with each month.

With this tool you can show the months that are on track, above your cost baseline, or trending downwards. No more creating weird charts just to show the months and their costs. Those old bar charts take time to create and a pain to read. With data bars you can quickly create and show which month, employee, or deliverable is doing well or bleeding the project’s budget.

Data bars are a way of showing how each cell compares to the other cells in a range. They are a form of conditional formatting, but allow you to see bigger numbers represented with bigger bars – and smaller numbers represented with smaller bars.

These pre-defined conditional formatting allows you to apply solid bars or gradient bars. The bars won’t replace the data in the cell, but allows the data to sit atop of the bars in the spreadsheet. The column width can also affect the size of the bar as you’ll see in this exercise.

Color scales, like data bars, format the selected cells with different hues and tints of colors that you define. Color scales show the values from smallest to greatest on a scale of color. For example, a series of numbers from 1 to 100 would be colored as red for the lowest number and then gradually up to green for 100.

You can designate the colors you’d like to be applied to your range of numbers to show which cells are low performers up to high performers. Like many of the items in Microsoft Excel, it’s easier to see the results than talk about the results. So, let’s hop into Excel and try out conditional formatting and color scales.

You want your reports to provide valuable information to your readers – that’s the first goal of the report, to be valuable. Good information in Microsoft Excel can help you create good information for the readers. But how you package that data can tell a story of project performance.

Icons sets are a way of using some conditional formatting rules to assign particular icons to given data. With this trick you can add an up or down symbol, for example, so the reader can skim the report, have a quick idea of how things are going in the project, and get on with their day.

Icons sets are easy to add – and that’s what we’re going to do in this lecture. Let’s go!

How would you like to apply a “stoplight” to your reports to show high, medium, and low values? You can apply a stoplight – a simply circle that is red, yellow, or green – to all of your numbers in your reports.

You can also choose other icons to use: check marks, stars, exclamation points, and many other icons. These are all built into Microsoft Excel and are a nifty way to quickly add some flair to your number ranges.

You can create some rules and plop in some icons sets – not a big deal. But you can also take things one step farther by manipulating the icon sets. In this lecture we’ll dig a little deeper and create some rules to show only one icon based on the rules you’re assigned.

Why? Why would you ever want to do this? Imagine a report where you only wanted to add an icon set to the data that represents an issue or problem. By manipulating the icon sets rules we can say only show data below, or above, a given value and now are reports are even easier to read – and provide even more telling information. Instead of sea of icons bobbing around, you can drop an icon on select information for your readers.

Through Microsoft’s Excel conditional formatting you can apply icon sets to a range of numbers. This conditional formatting feature lets you apply icons, like stoplights and checkmarks, to numbers in your reports. Ideally, you’ll write formulas as part of your reports and apply the conditional formatting as needed to display the icon sets.

You can also create rules to show only one icon, such as a red exclamation for late-running activities, cost overages, or any other value you add. That’s what we’re going to do in this exercise – we only want to see one icon in our reports.

This is the good stuff. Sure, there are lots of pre-built conditional formatting rules, but you can do better in Microsoft Excel. By manually applying conditional formatting you can create your own rules and effects for your reports and dashboards.

You can write formulas and functions and then apply conditional formatting for the outcomes. This is fancy stuff and can make your reports sharper and allow you to create them faster every week. Let’s take a look at this business in Microsoft Excel.

Rather than using the built-in rules of conditional formatting in Microsoft Excel, you can totally customize conditional formatting. This approach gives you more control over the types of conditional formatting that is applied.

You can also create multiple rules to apply to a range of numbers – and the order of precedence of the rules you’ll apply. For example, you might have three rules for data based on given criteria: above average, below average, and even negative values. Each of these values could then be given a conditional formatting rule.

Way to go! You’re making some good progress in this course – and I want you to keep making progress. There’s lots of great information still to come in this course.

In this section we talked all about formatting our worksheets. Here’s a quick recap of what we discussed:

• Sparklines
• Conditional formatting basics
• Apply top/bottom rules
• Customizing Reports with data bars
• Adding icon sets to your data
• Manipulating icon sets
• Manually applying conditional formatting rules
• Working with the camera feature in Microsoft Excel

Charts, charts, charts! Do you love charts? If so, this is the section for you. But don’t worry, if you’re not in love with charts there is still some great information on how you can quickly create charts for your reports and dashboards.

Charts help you communicate the status of your project and can be customized to display exactly what’s needed in your project reports and dashboards.

Charts are pictures that represent data – of course you knew that already! In project management charts can help us communicate what’s happening the project. We can create charts for labor, activity counts, cumulative costs, earned value management, and just about anything else you’d want to chart.

In this lecture we’ll dive in Microsoft Excel charting and experiment with the parts and configuration of charts. Just remember, when in doubt, right-click! All most everything you’d want to configure with a chart can be accessed through the good ol’ right-click button.

Charts are a great way to visualize the data in your reports. No one likes to receive a report with rows and rows of numbers and no explanation of what the numbers mean. Be good to your stakeholders! Create charts that represent what’s happening in the project – something that Excel can do so easily for us.

Charts can be inserted right into a worksheet, on their own worksheet, or most likely, you’ll create a report and include the chart on that report in Microsoft Excel. By creating a report on its own worksheet in Excel you can configure the worksheet as a report that you’ll share each week. You’ll update the data each week as the project manager and then the chart – and report – can be updated automatically.

As you know charts can visualize your data for stakeholders. Charts can help tell a story of what’s happening in your project.

In this exercise, we’ll experiment with different types of charts and how the charts can help stakeholders understand what the data with data means. Let’s get into Excel and look at bar charts, hierarchy charts, line charts, tree graphs, and other charts.

Imagine you have a project with ten project team members. You might create a table with all of their names, total hours worked on the project, their cumulative costs, and number of activities they’ve completed in ratio to the number of activities they have assigned to them.

A series describes the order of project team members, the order of costs, hours, assignments, and anything else that happens in a specific ordering. Good examples of series could be days of the week, months of the years, phases of the project, and the ordering of activities. Let’s hop in and see how the series affect what’s charted in Microsoft Excel.

Series are things that happen in a particular order: days of the week, months of the year, or even phases of your project. Often, in project management, series include the team members’ data that has assignments within the series.

Line charts are a great way to map the series that happen within a project. You can make a series for each week of the project. As the project progresses you’d add data based on actual events in your project. Then you’ll need to update the chart because you’ve updated the data series with the new information. That’s what we’ll explore with in this exercise.

Congratulations! You’ve created a chart in Microsoft Excel… but it’s not quite the way you want it to look. You’d like to change the colors to match your company’s color scheme, edit the fonts, and drop the borders on the chart. You can do all of this – and more – by formatting and customize your chart parts.

Basically, if you can see it in a chart you can format it. Through the ribbon and through contextual menus you can edit everything within a chart. You can even change the type of chart you’ve created altogether. In this lecture we’ll explore how to format the parts of your charts.

In Microsoft Excel you can quickly create charts with just a few clicks. Once you’ve created a chart you’ll probably want to format it to match your organization’s colors and standards. From the Design ribbon you can change the standard colors, lines, and other elements.

In Excel, you can format the chart parts, such as the Chart Title, data labels, legends, and more. In this exercise we’ll experiment with adding and removing parts of the chart and we’ll add some colors and borders to our chart parts.

By creating charts you can create automated reports and dashboards in Excel. By creating good charts in your reports and dashboards you can visualize data for your project team and other stakeholders. Reports can become somewhat automated, and stakeholders will appreciate the clean, professional look of the charts you’re creating.

• We discussed three things in this section:
• Building reports and dashboards by creating useful charts
• Working with Gantt chart in Microsoft Excel
• Exploring and editing chart series

So you’ve made some charts and you think they look pretty okay… but now you’d like to add some elements to really make your charts pop! In this section that’s what we’re going to do. We’ll cover:

• Formatting and customizing your charts
• Adding and editing chart fills and borders
• Formatting the chart background
• Using pictures as parts of our charts
• Adding names to our charts

In Microsoft Excel you can customize the fills and borders of your charts. Borders are the lines that surround the chart, the components of the charted data, and even the legend. Fills describe the content of the bars and boxes within your charts – the good stuff that represents the data you have charted.

In this lecture we’ll examine the plot area options and other components of your charts and how you can format these options to make your chart exactly as you want it.

In this exercise we will create a chart based on a sample project’s Cost Performance Index and Schedule Performance Index – though this exercise is applicable for just about any data. We’ll look at formatting a line chart and how we can add borders and fills.

Borders and fills can dress up the chart, but you do want to take caution to not make the charts too colorful and busy – or the changes will distract from the message of the chart and what your stakeholders expect from your reports. Let’s take a look at these fills and borders in a chart.

Just as you can format the fill of the components of your charts, you can also format the background of the chart. Some people prefer a solid white background, especially for printing, but you can choose whatever color you’d like.

In this lecture we’ll examine the difference between the chart area and the plot area and how you can format the contents of both. You could, if you really wanted to, format the chart area one color and the plot area another color. It’s busy, but you could do this if you really, really wanted to.

The goal is to make the chart useful, not pretty.

Here’s something that’s almost fun in Microsoft Excel: you can use pictures to fill your chart parts. Imagine your project is to configure 1,890 laptops. Instead of creating a simple bar chart to show the laptops completed in each area of the company, you could use a tiny picture of laptop to show the highs and lows of the data gathered.

By using a picture for charting you can fill the bars in a bar chart, for example, with pictures of laptops, flowers, or even hearts to show progress. This is fun to do, but it can make your charts hard to read – so use caution with this little nugget!

How would you like to dress up your charts just a little bit? In this exercise you will create a simple bar chart for your project team performance. Based on a series you can change a bar chart to include a picture.

For example, in this lecture we’ll create a bar chart that includes the month of February. We’ll change the fill of the bar representing February to be little hearts. Yes, it’s a little goofy, but it’s a fun thing to do occasionally. Sure, it’s not something that we want to do every time we create a chart, but it’s a nice way to occasionally dress up your charts.

When creating a report you might add several charts to show project performance information. Sometimes the charts will be obvious, but other times, not so much. If you have a chart for your project’s cost performance index, schedule performance index, and cost variances it may be difficult for a reader to understand what each chart represents without chart titles.

This lecture shows you how to add, edit, and even remove chart titles. It’s really easy-to-do, but chart titles need to be concise, accurate, and steadfast. We don’t want to change the ordering or title of charts in each weekly report.

What’s a Gantt chart? It’s a bar chart that shows the duration of activities across a calendar. It was made popular by Henry Gantt in the early 1900s – and we still use it in project management today.

Gantt charts illustrate not only the duration of activities, but also the relationship of activities in the project. You could say that a Gantt chart is a special type of histogram, a bar chart, that is used to create a project schedule.

In this lecture we’ll experiment with the Gantt chart template in Microsoft Excel to see how it works with planning project schedules. It’s a pretty cool tool, but be forewarned, this is just a bar chart, not a complete Gantt chart like you would find in Microsoft Project.

Gantt charts are a way of showing activities across a calendar. Gantt charts are the default view in Microsoft Project – but you can create one in Microsoft Excel.

Gantt charts can show not only the progress of the project work, but also the relationship of activities across the timeline. Each bar in the Gantt chart represents an activity and the length of the bar represents the duration of the activity. You’ll also track the start date, the actual duration, and planned start and finish dates. The formulas in Excel will update the Gantt chart based on what’s happening in the project and what enter in the spreadsheet.

Earned Value Management (EVM) is a suite of formulas that show project performance. Through these project factors you can use a set of formulas to show project performance:

• Project budget – called the budget at completion
• Percent of work completed
• Actual costs to date
• How complete the project is to be

These four values are then feed into a series of formulas, which Microsoft Excel can calculate so quickly for us, to show performance on time, cost, and overall project health. In this lecture we’ll explore a special spreadsheet made just for EVM.

Earned Value Management (EVM) is a suite of formulas that you can use to show and track project performance. In this lecture we will build a worksheet of all the EVM formulas. You will learn how to track:

• Budget at completion
• Actual costs
• Earned value
• Planned value
• Cost variances
• Schedule variances
• Cost performance index
• Schedule performance index
• Estimate at completion
• Estimate to complete
• To-complete performance index
• Variance at completion

Great job finishing this section on formatting your charts! Charts can help us better communicate as project managers, but charts need to be formatted to best display the information and not confuse stakeholders.

In this section we discussed:

• Formatting and customizing your charts
• Adding and editing chart fills and borders
• Formatting the chart background
• Using pictures as parts of our charts
• Adding names to our charts

Now that you’ve completed the bulk of this course, let’s take some time and talk about putting it all together. In project management there are lots and lots of different forms, templates, charts, reports, and other communications you need to create, disperse, track, update, and monitor.

Within Microsoft Excel you can create a dashboard of these topics – and this will save you time as you move from project to project: all of your data is in one place and reusable and adaptable. In this section we’ll look at some items that can help get to work building your project dashboard. This section also includes my favorite tool in Microsoft Excel: the camera.

I love the camera in Excel – and so many people don’t even know it’s there. Top secret! You have to turn on the camera feature in Excel to access it, something we'll do in this lecture.

The camera feature in Microsoft Excel is a tool that allows you to take a picture of some data, or charts, or anything you want and then display that information somewhere else in Excel. The “picture” is dynamic – meaning if the original data changes the picture will update to reflect the changes.

The camera feature is your secret weapon for building reports and dashboards that can print on one sheet of paper. Great stuff!

A speedometer in your car tells you how fast you’re going. A speedometer for project management in Microsoft Excel can tell you the same thing, but it can also tell you if your project team is slipping on goals. Or if you have cost overruns, or trends on time, or a myriad of other topics.

In this lecture I’ll share with you my favorite charting trick in Microsoft Excel: creating a speedometer for your projects. This special chart uses a donut chart, takes a little time, but once you’ve got it down it works like a champ, can be very useful, and it very useful for stakeholders. Let’s hop in and check out this unique trick for Microsoft Excel.

One of the best secrets of Microsoft Excel is the camera feature. The camera feature allows you to take dynamic pictures from one part of your worksheet and display it somewhere else. This is one of the best ways to quickly build a report or a dashboard in Microsoft Excel.

In this exercise, you will turn on the camera feature in Microsoft Excel (it’s off by default!) and then we’ll experiment with taking some pictures in Excel.

Now it’s time to put all of this fine information to good use and build your own dashboard in Microsoft Excel. Let’s discuss all of the things you could put in a dashboard: everything! There are lots and lots of forms and templates you can use in project management and most of them, I’d wager, can be configured in Microsoft Excel.

In this lecture we’ll discuss the project charter and the project scope and how you can add these necessary project documents into Microsoft Excel. These are two documents you will reference throughout the project (especially the project scope) so it makes sense to keep them handy as part of your Microsoft Excel dashboard.

In this lecture we’ll examine the project charter and the project scope. The project charter authorizes the project to exist within the organization. This is a template that includes:

• The project name
• Project manager
• Project sponsor
• Contact information
• Approval status and date of approval
• Project goals and deliverables
• High-level risks, constraints, and assumptions
• Business case
• Project purpose
• Let’s hop into this lecture and exercise

Another requirement in good project management is a risk log. A risk log is just a table that records all of the identified project risks, tracks their status, responses, costs, outcomes, and a few other factors. You’ll need a risk log to effectively plan in the project and Microsoft Excel is a fine place to record this information.

You can also use Microsoft Excel to create a probability-impact matrix. A probability impact matrix is a table that defines the probability of a risk event happening, the financial impact of the event, and then calculates a risk score. The higher the risk score the more serious the risk event.

Risks are uncertain events or conditions that can have a positive or negative effect on the project. This lecture explores the project risks, a risk log, and how conditional formatting can help automate the tracking of the project risks.

You will create a risk log that identifies:

• Risk identification
• Risk probability
• Risk impact
• Risk score
• Risk status
• Risk response

By updating this log throughout the project you’ll have a good grasp on what risks are pending, which risks have passed, and the status of each risk event in the project.

Great job finishing this section on building a project dashboard. We discussed my favorite type of chart, the project speedometer, in this section. I highly encourage you to take some time and experiment with this chart and how you may implement it into your reports and dashboards.

We also discussed some project management forms, the project charter and the project scope, and how you need these forms for every project. Finally, we looked at building a risk log and how you can use it to document and track risk events throughout your project.

CONGRATS! You’ve completed this entire course on using Microsoft Excel for Project Management. We’ve covered lots of information in this course and I know you’re eager to go apply this information to your projects.

In this final lecture I have some important information to share with you first. I’ll do a quick summation of this course and what we’ve covered. I’ll also address how you can claim your Professional Development Units with PMI. Let’s wrap this all up with this final lecture.

Thank you for choosing my course! All the best in all your projects,

Joe