How often have you seen a manager struggle to use Microsoft Office Excel to manage project information such as scheduling project dates or recording task assignments? Often, it’s a matter of sticking with what they know — and every manager in the world seems to know Excel. In this article, I’ll supply you with the arguments you need to help them understand when it’s time to shift to using Microsoft Project instead.
When it comes to managing project information such as scope (phases, tasks, milestones and deliverables), timelines (schedules and deadlines) and resources (assignments, effort, availability and costs), Project provides better built-in capabilities than Excel. Once the user is over the hump of learning Project, using Excel to manage project information doesn’t make sense since Project is set up to increase productivity and effectiveness.
Key challenges in using Excel in project management work include:
- Project and summary level data calculation is largely done manually.
- Creating and maintaining phases, tasks and milestones is more difficult.
- Creating and maintaining project timelines and deadlines isn’t dynamic or easy.
- Assigning resources and costs to tasks isn’t automated nor project-specific.
- There are no automatic updates for scope, time or resource assignments.
- There are no specific tools to manage change effectively.
- It’s difficult to see the ripple effect of delays.
- There’s no feature that calculates the impact of resource availability.
- There are no pre-defined project-specific reports for different stakeholders.
Building a Project Schedule
Project enables managers to not only create a schedule but to see a project through the lifecycle of planning, tracking and reporting. Also, if a user has a plan in Excel, importing it to Project is simplified by using the Project step-by-step guides to map Excel columns to project columns. It’s then possible to convert the task list from Excel into phases, tasks and milestones which defines the project scope known as the work breakdown structure (WBS). Project has the tools the user needs to quickly set-up and maintain the WBS. The next step is to establish a timeline by defining task durations and dependencies or links. Completing the schedule involves identifying and allocating resources. Once complete, the task relationships can be represented in the form of graphs or reports. Information can then be easily shared. Project allows the user to:
- Quickly set up project phases, tasks and milestones.
- Define task durations and relationships.
- Clearly identify and allocate resources and coordinate workload.
- Quickly access and share relevant project information.
- Effortlessly create custom reports to keep the project team informed and aligned.
With Project it’s also possible to:
- Import existing data from Excel or Outlook.
- Initiate a plan using the built-in project templates.
- Collapse or expand plans to show the necessary level of detail.
- Click and drag to change task duration.
- Keep notes on important task information.
- Do “what if” analysis with multiple undo’s.
- Focus on tasks that are driving project completion and critical path.
- Determine the factors that are affecting task dates and easily trace the sourcef of issues.
In addition to importing Excel files, Project offers users two options to create or start a new project. First, project schedules can be created from templates. In addition to Project’s built-in templates and those available at Office Online, custom templates can be built. Using templates saves time and ensures consistency and standardization across an organization.
The second approach is to build a plan from scratch using the powerful planning tools Project has to offer. Building dynamic and effective plans in Project involves these five steps:
- Enter WBS (phases, tasks and milestones) into task name column.
- Outline (indent/outdent) tasks to reflect the WBS hierarchy (major, minor and task pieces).
- Estimate duration (or work-for-work based schedules).
- Set dependencies or task links to establish the flow of activities.
- Assign resources (and costs if applicable).
Unlike in Excel, any task with zero duration in Project appears as a milestone automatically. A milestone is a reference point marking a major event in a project, used to monitor the project’s progress. By default, milestones appear as diamonds on the Gantt chart, as shown in Figure 2 above, at the end of each phase (summary task). There are many different models and methods for estimating duration, but none is entirely accurate. In Excel, it would be necessary to build formulas to see a project’s total duration or the total duration per phase or major pieces. Using Project, a user can establish a base calendar and estimate each task by entering its duration in the task column. In turn, Project will total the project and phase duration without having to build in formulas.
In Project it’s possible to easily create task dependencies, which are also referred to as activity relationships or task connections. Types of dependencies, as shown in Figure 3, include sequential (finish to start), concurrent and overlapping. Once the task relationships are established in conjunction with duration estimates, Project automatically calculates your schedule. Furthermore it establishes a dynamic schedule that can be recalculated automatically as you modify your plan.
One extraordinary new tool in Project is “Show Change Highlighting,” which will highlight the impact of making a change throughout the entire plan so you can decide whether to keep or undo the change. It will also calculate the critical path, which can be easily viewed through filters, highlights, groups or formatting. In Excel this would involve many separate and complicated calculations to determine the overall schedule of a critical path.
What is a critical path? It’s the set of tasks that affects the overall end date. In any single-project network, one sequence of tasks fixes the duration of the project. Any slippage or failure to begin a task on time that is on the critical path will cause the project to finish later than planned. The critical path is illustrated in Figure 4 below using the red highlights and the red Gantt bars.
Project allows users to manage cost and resources by defining local project resources and the respective related information in a separate view or window. In turn, the Project Resource Assignment Tool is used to add resources to tasks, including multiple resources to a single task or multiple resources to multiple tasks. This is a cumbersome process in Excel involving creating and linking worksheets.
Communicating from the Project Schedule
Project has capabilities and features beyond those available in Excel. Those features can be used to present project data to different stakeholders according to their requirements. It’s easy to keep stakeholders informed using these Project capabilities:
- Using appropriate views to keep different stakeholders informed.
- Controlling the information to present with predefined tables.
- Focusing on the specific information using filters.
- Grouping information for effective communication and decision making.
- Highlighting cells to convey a different meaning.
- Analyzing and reporting project data in professional reports and charts.
Using Excel, it would take numerous steps to generate the views that are a click away in Project. There are many built-in views in Project and it’s possible to create custom views as well. Some of the views include:
Gantt Chart. Shows activity/task start and end dates as well as expected durations. The Gantt chart is the default display and principle report format in Project.
The Network View. Shows the task boxes. Where nested, they indicate the hierarchy of tasks to clarify dependencies between tasks.
The Calendar View. Displays project tasks in a calendar format. A task bar representing a task spans the days or weeks on which the task is scheduled.
Project also includes many built-in resource views. The resource usage view shows the tasks and their timing per resource. The resource histograms show resource utilization data. It would be possible but laborious to create these views in Excel.
The Visual Reports feature leverages charts and diagrams by using Excel and Microsoft Office Visio Professional to produce charts, graphs and diagrams based on Project data. A user can define custom report templates or choose from a list of customizable, ready-to-use report templates. Reports can be shared with other Project users. Tables and filters can be applied to further control the data that is communicated to different stakeholders. Filters provide an easy way to zero in and focus on a set of tasks for more effective communication. Instead of filtering tasks or in conjunction with filtering tasks, grouping tasks makes it easy to reorganize information to enhance your ability to analyze or communicate information. Project has a set of predefined views, tables, filters, groups and reports specifically designed for project managers to become more productive immediately without having to spend extra time formatting or customizing — which is exactly what a user would need to do using Excel.
Tracking and Analyzing the Project Schedule
Project can also be used to track and analyze progress of projects. Within Project, once an optimized schedule has been approved, it’s possible to save a copy of the original plan by setting a baseline. In Excel, this would require manually populating additional baseline columns. With Project, once the baseline is set, the project constraints are managed by comparing the evolving plan with the saved baseline. Managers can then track progress, identify problems and take corrective action using the built-in tools already in Project.
For example, a project manager can easily update a task using the Update Tasks dialog box. The results can then be analyzed in the tracking Gantt view which is already customized for tracking purposes. This view shows both baseline Gantt bars in gray and the current plan bars in red for critical path task and in blue for non-critical. These tools are presented in Project so new users can take advantage of them quickly and easily while providing advanced users the ability to go deeper when necessary. In comparison to Excel, no such tools exist to manage project information in this way, and it would be quite laborious to customize and format it with no guarantee of success.
Gaining a Work Edge
Understanding when to shift to Microsoft Office Project and using the right tool gives those put in charge of managing a project a distinct advantage. Managing project information such as scope (phases, tasks and milestones), time (schedules, deadlines and critical path), resources (assignments, effort, costs) and the need to communicate that information effectively are all indicators of when to use Project. This advantage continues throughout a project life cycle as Project makes it easy not just to plan, but to communicate, track and analyze project data. Project is the best tool to help manage projects because it’s specifically designed for this purpose with tools that offer immediate gains in productivity.