Joseph Phillips – Microsoft Excel for Project Management – Earn 5 PDUs

Joseph Phillips – Microsoft Excel for Project Management – Earn 5 PDUsMicrosoft Excel for Project Management – Earn 5 PDUsMastering Microsoft Excel for Project Management: Controlling, Tracking, and ReportingCe que vous apprendrezDesign reports for your stakeholdersCreate a dashboard with some common project management requirementsImprove project communications with chartsFormat, change, and edit chartsApply conditional formatting to save time and automate reportingImplement the Microsoft Excel Camera ToolCreate a speedometer chart for reportingClaim five (5) Professional Development Units with PMILes grandes entreprises proposent ce cours à leurs employésCe cours a été ajouté à notre sélection de cours les mieux notés, approuvée par les entreprises à l’échelle internationale.PrérequisBasics of project managementBasics of Microsoft ExcelIt’s been said that project management is 90 percent communication – and it can certainly feel that way when you consider all of the people you have to communicate with everyday. Well, did you know that you can use Microsoft Excel to help you communicate more effectively, define your project charter, build out the project scope, and even the work breakdown structure? You sure can!It’s paramount that project managers work with the project team to plan and define the project scope. Then, when the team is executing the project work, the project manager needs to monitor and control the work. Part of monitoring and controlling is to record what’s happening in the project. Based on this work performance data, you’ll process the project’s successes (and possibly failures) and report to the project stakeholders accordingly.Microsoft Excel can help you with so much of that! In this course we’re going to explore lots of things that Microsoft Excel can do for project management. We’ll dive into formatting, charting, conditional formatting, and building a custom dashboard and report. If you don’t have a more advanced project management software, that’s okay, you can do much of that business right in Microsoft Excel.This course is worth five (5) Professional Development Units with the Project Management Institute. Take this course at your leisure, learn more about Microsoft Excel and project management, keep your PMI certification going, and most importantly, manage your projects with better control and communication.À qui ce cours s’adresse-t-il ?Project managersPMPsPeople learning Microsoft Excel for reportingNew and experienced project managersProject team members responsible for reportingDon’t take this course if you’re new to ExcelContenu du cours6 sections • 59 sessions • Durée totale: 4 h 34 minReports and Dashboards 4 sessions • 22 minCourse OverviewWelcome to this course on how to utilize Microsoft Excel for project management. Project management can be cumbersome, full of data, and requires good communication skills. This course aims to streamline both the data management and to help you become a better communicator with your stakeholders.This course has plenty of hands-on exercises. Be prepared to watch a lecture and then follow along with a hands-on exercise. The best way to learn, especially in Microsoft Excel, is in the doing. Practice the exercises and experiment with the concepts. Your goal, and my goal for you, is to use Microsoft Excel to help you be a better project manager.02:54Section OverviewIn 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.02:27Comparing Reports and DashboardsWhat’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.15:29Section CloseGreat 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.01:23 Fundamentals of Microsoft Excel for Project Management 6 sessions • 21 minSection OverviewIt’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.03:37Set the Excel Table for Project ManagementWhat’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.02:46Exercise: Format Cell NumberingA 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 23rd 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.05:42Create a Clean and Concise Report by Formatting NumbersYou 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.04:16Single-Line Accounting UnderlineIn 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.03:05Section CloseGreat 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!02:03 Formatting in Microsoft Excel to show Project Performance 17 sessions • 1 h 21 minSection OverviewVisualizing 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!02:18Sparklines! Add Sparkle to your Reports and DashboardsWhee! 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.02:12Adding Sparklines to a Range of CellsWe’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.09:41Using Conditional Formatting in Microsoft ExcelConditional 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!02:13Experimenting with Conditional Formatting in Microsoft ExcelRecall 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.10:18Applying Top/Bottom Rules in Microsoft ExcelImagine 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.02:08Exercise: Applying Top/Bottom RulesIn 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 itemsBottom 10 itemsTop percentage of itemsBottom percentage of itemsAbove/below averageThis 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.03:49Customize Project Reports by Utilizing Data BarsData 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.02:25Exercise: Experimenting with Conditional Formatting Data BarsData 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.06:42Exercise: Color Scales and Conditional Formatting in Microsoft ExcelColor 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.06:27Quickly Format Reports by Adding Icon SetsYou 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!02:36Exercise: Conditional Format and Icon Sets in Microsoft ExcelHow 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.05:37Manipulating Icon SetsYou 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.02:26Exercise: Customizing the Icon SetThrough 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.05:03Take Control in Microsoft Excel by Manually Applying Conditional FormattingThis 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.02:20Exercise: Manually Create Conditional FormattingRather 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.13:10Section CloseWay 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:SparklinesConditional formatting basicsApply top/bottom rulesCustomizing Reports with data barsAdding icon sets to your dataManipulating icon setsManually applying conditional formatting rulesWorking with the camera feature in Microsoft Excel01:24 Creating Charts in Microsoft Excel for Performance Reporting 9 sessions • 46 minSection OverviewCharts, 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.02:45Build Your Reports and Dashboards by Building ChartsCharts 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.03:27Working with Excel ChartsCharts 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.09:40Exercise: Experiments with Microsoft Excel ChartsAs 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.05:43Working With Chart SeriesImagine 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.04:00Exercise: Working with Series in Microsoft ExcelSeries 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.05:51Format and Customize ChartsCongratulations! 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.02:12Exercise: Formatting Charts in Microsoft ExcelIn 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.11:16Section CloseBy 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 chartsWorking with Gantt chart in Microsoft ExcelExploring and editing chart series00:46 Customizing Charts in Microsoft Excel for Project ManagementSection OverviewSo 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 chartsAdding and editing chart fills and bordersFormatting the chart backgroundUsing pictures as parts of our chartsAdding names to our charts02:23Working with Chart Fills and BordersIn 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 it02:46Exercise: Managing Fills and Borders in Microsoft ExcelIn 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.05:07Get Fancy! Format the Chart BackgroundJust 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.02:33Using Pictures as ChartsHere’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!02:39Exercise: Using Pictures in ChartsHow 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.04:43Charts Need Names – SometimesWhen 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.03:53Working With Gantt ChartsWhat’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.04:55Exercise: Creating a Microsoft Excel Gantt ChartGantt 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.12:04Configure Earned Value Management for Microsoft ExcelEarned 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 completionPercent of work completedActual costs to dateHow complete the project is to beThese 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.09:51Building an Earned Value Management Worksheet in Microsoft ExcelEarned 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 completionActual costsEarned valuePlanned valueCost variancesSchedule variancesCost performance indexSchedule performance indexEstimate at completionEstimate to completeTo-complete performance indexVariance at completion10:11Section WrapGreat 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 chartsAdding and editing chart fills and bordersFormatting the chart backgroundUsing pictures as parts of our chartsAdding names to our charts01:44 Build a Custom Dashboard and Reports in Microsoft Excel 11 sessions • 41 minSection OverviewNow 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