Project Management with Excel

Getting Started

Let’s Say Your Project is a Relatively Straightforward One Such As Training Your Staff to Use a New Software Application. You’ve Got to Select and Schedule Instructors, Create or Purchase Course Materials, Schedule Live Classes and Virtual Training Sessions (and Advertise the Schedule so People Can Sign Up), Perform the Training, Design and Utilize Skills Assessment Tools to Determine the Value of the Training, and Gather the Students’ Impressions to Inform Future Training and Provide Effective Post-training Support.

Keeping Track of All That Will Require an Excel Workbook That Stores an Overview of the Project and Tables for All Those (hopefully) Flowing Parts. on Separate Worksheets, You’ll Keep Track of Your Instructors, Your Materials, Your Hardware and Other Technical Resources, Student Enrollments, Student Responses to Your Skills Assessment and Training Evaluations, and the Training Schedule—all of Which You’ll Store in Tables on Those Sheets. Each Table Can Be Accompanied by Charts and Diagrams to Help You Get a Quick Visual Status Whenever You Need One.

Of Course, Your Project Might Be Entirely Different. Maybe You’re Developing a New Product or Planning an Event. Just Break It Down into Its Components and Keep Each of Them in Its Own Table, Each Table on a Worksheet Unto Itself. See All the Distinct Moving Parts of Your Project—those Balls You Have to Keep in the Air—is Something That Gets Easier the More Often You Do It, and You’ll Learn a Lot from the Process of Simply Breaking It Down into Separate Tables, Building the Project Workbook. It’s a Valuable Exercise!

Begin with a Template?

I’m Going to Assume You Know How to Start a New Workbook—but Let’s Stop at the Point Where You Might Normally Click That Blank Workbook Button to Start from Scratch. There Are Two Project Templates You Can Use, to Which Sheets for Those Individual Tables Can Be Added:

• Project Tracker
• Milestone & Project Status Timeline

You might not need them both, but check them out. Just type “Project Management” into the search box, and your options appear. Try them on. Download them, and whichever one seems friendliest or most useful to you, stick with it. Save it with your project name, and then start adding those worksheets for the components of your project. That process—building the individual sheets—will help you fill in the parts of the template workbook that track your progress. It all works together.

The Project Tracker works well for a project to which you assign the tasks involved to individuals—either on your staff, external contractors, or both, and you can also set some of the Assigned To column items to vendors and suppliers who are contributing essential components for the project. Of course, you can also add columns and change the column headings to meet your needs. Remember, as is the case with all templates, the Project Tracker is just a start, a jumping-off point.

Or Start from Scratch

Of course, you don’t need to start with a template—in fact, that’s rarely my instinct. I tend to want to make my own mark on things, to design my worksheets for my specific needs. If you know what your project is, what all the parts that you have to juggle are, you can build the worksheet piece by piece, to parallel the project’s components. From the overall goal to the resources (in all their forms) to the expenses, you can track and meet your deadlines, monitoring the process along the way.

Using the training project as an example, given that I know what has to be accomplished and what has to happen to reach that goal, I can create my timeline—the series of milestones which must occur in order for the final goal to happen—and I can track all of the moving parts, the balls (and sometimes, flaming sticks) that I have to keep in the air.

Adding Bells & Whistles

There Are Two Things You May Want to Add to Your Workbook to Enhance Usability and Accuracy:

• Create Navigation Links Between the Worksheets, Making Moving Around Quick and Easy (and Ending the Need to Go Find the Tab You Want at the Bottom of the Screen)
  
  
• Through the use of Data Validation, streamline your entries into columns such as Status, wherein you designate a given step or phase as Starting, In Progress, Under Review, or Completed. Your options will vary, obviously, but using the same words to indicate progress made will make the worksheet more meaningful and reliable.

Make Navigation Easier

To create buttons that link to the other sheets in your workbook, simply draw a shape—I recommend a rectangle, using the Shapes tools on the Insert tab, in the Illustrations drop list—and type in it. The name of the sheet it will link to is a good choice, or type the name of the information that will be visible once the button’s clicked.

Then, right-click the shape and choose Link from the pop-up menu. In the resulting dialog box, shown here, choose Place in This Document, and select the sheet you wish to link to.

TIP! Note that there’s also an option to link to Defined Names. This means if you’ve named a cell or range of cells in one of your worksheets, you can choose that name and that cell or range is where the link will take you or anyone else using your workbook.

Use Consistent Terminology

If you wanted to filter a list of tasks down to just those that are Complete, how annoying would it be to have the one into which you typed “Finished” in the Status column not appear? The chance of this is greatly increased by sharing your workbook and giving others editing capabilities, but sometimes that’s not our choice. And, of course, if you’re a human, you’ll want to protect your workbook from yourself, too—and Data Validation can help you do that.

Using Data Validation is a matter of selecting the data in a column and then using the Data Validation tool on the Data tab. Choose the List option for the type of validation you’re creating, and type the list, separated by commas, into the Source box. That’s all there really is to it, as the process creates a drop list in every cell in that column, from which you will choose from the prescribed list of values. This works well for the aforementioned Status column, or to select Resources from a list of approved vendors or people to work on the project from a list of available staff. Whenever there’s a fixed list of values you want to stick to in a given column (or row, depending on your worksheet’s structure), Data Validation will help you.

A Picture’s Worth a Thousand… Cells

In Addition to Lists of Milestones with Dates and the Worksheets That Track All the Parts of the Project, You Can Create Useful Visuals. These Visuals Help You Keep Track of Your Progress Right There in the Worksheet and Can Also Be Added to a Document or Presentation When You’re Called upon to Share Your Progress with Others.

To Build Those Visuals, SmartArt Diagrams Depict the Process—from the First Steps in the Project Through All the Milestones That Must Be Met on the Way to the Project’s Successful Completion. Building the Diagrams is Also Useful in That It Forces You to Break the Project Down into Images. It’s Happened to Me More Than Once—in Building the Visual to Share the Project with Someone Who Doesn’t Need or Want to See the Details, I’ve Realized I Forgot a Step or a Resource. I’ve Even Noticed I Had a Couple of Milestones Set to the Wrong Order, and Was Able to Fix It in the Worksheet. There’s No Underestimating the Value of a Fresh Set of Eyes, Even If They’re Your Own, on a Project.

Sharing, with Caution

It’s Nice to Share. We Hear That from the Time We’re Children, and It’s True. but Sharing Can Be Tricky, Especially with a Workbook You’ve Carefully Crafted to Help You Manage Your Project. the Exact Process by Which You Share Your Workbook Will Vary, Depending on the Systems Your Organization Has in Place. It Might Be Accomplished by Linking to a Folder on Your Company’s OneDrive, or Maybe You’re Pasting a Link into a Teams Chat. Maybe You’re Storing the Workbook in a Folder on Sharepoint or to a Google Drive. However You’re Making the Workbook Accessible, Applying Protection to Keep People You Don’t Want Meddling from Doing so Can Make That Sharing Much Less Stressful.

Using the Protect Sheet And/or Protect Workbook Buttons on the Review Tab, You Can Set a Password That People with Whom You’ve Shared the Workbook Must Know to Edit Your Worksheets. Without the Password, the Workbook is Opened As Read Only, and That’s All They Can Do—read.

But you? You can happily store and track your project—building its milestones and editing them over time, juggling all those moving parts on tidy, linear worksheets. Enjoy! And good luck with your project!