Excel Hacks for Help Writers

By Mike Hughes

Published: July 21, 2008

“Looking for a better way, I discovered Excel and the power of managing by task inventories and check-off lists.”

One of my earlier careers was in manufacturing management, and it grounded me in the principles of project planning and management. When I moved into technical communication, I brought my project management disciplines with me, and I embraced the prevailing tools of my new profession. I dutifully produced documentation plans in Microsoft Word and supported them with detailed project plans in Microsoft Project. However, the problem is that—like bad relationships—these artifacts never gave back results that were sufficient to reward the effort I put into creating them.

Excel: A Minimalist Tool

Looking for a better way, I discovered Excel and the power of managing by task inventories and check-off lists. Project management boils down to just three essential requirements:

  • scoping the size of the project
  • bundling the tasks into manageable and assignable chunks, or components
  • tracking progress

I have found that working with a simple Excel spreadsheet gives me everything I need. Knowing just a few tricks makes Excel a versatile tool that meets my needs over the life of a project. Unfortunately, Excel is not a tool most technical communicators learn in school. So, we tend to fall back into our comfort zone, producing documents—with their own overhead of creating templates and styles and writing lots of words that no one seems to read—and working with project planning tools that seem to ask us the hard questions we were hoping they would answer for us. For example, task duration is an input in Microsoft Project, not an output. It’s like having my doctor ask me what I think is wrong with me.

The Information Model

“Let’s start at the end, with an information model that shows what work we need to do, who to assign the different task components to, when the components are due, and what the current status of each component is.”

In discussing this different approach to project management, let’s start at the end, with an information model that shows what work we need to do, who to assign the different task components to, when the components are due, and what the current status of each component is. Figure 1 shows an example of an information model for a simple Help project. On this project, multiple writers are working separately on their own topics, and the manager wants all topics to go through editing before including the Help in the Quality Assurance build. To avoid creating a bottleneck at the end of the project, the manager wants the editing to keep pace with the information development.

Figure 1—A simple information model for a Help project

Information model

On real projects I’ve helped manage, the actual tables I’ve created have had more rows to accommodate more components than this example shows, but otherwise, this is a realistic example.

“The power of this information model comes from its ability to let you filter by multiple columns.”

Next, I’ll discuss how to use this information model. Then, I’ll show you how to build one.

The first column is an inventory of stuff we need to document. I have found two useful organizational structures for Help projects. The one shown in Figure 1 lists the basic components of the user interface (UI) itself and even identifies them by their navigational paths in the product. Another approach is to organize the work around use cases—a useful approach if you must do your planning before the UI design exists.

The second column is for assigning writers. Instead of free-form text entry, it uses a drop-down list that includes the names of the writers on the team. (I’ll show you how to create these drop-down lists later.) The use of lists for data entry is important if you want to be able to filter consistently, as we’ll soon see.

The third column is an estimate of how long it will take to document a component. Later, I’ll show how Excel can help you come up with that number.

The fourth column is the due date, which the writer provides, basing it on the estimated durations. The last two columns are status columns—once again with data coming from a predefined drop-down list.

The power of this information model comes from its ability to let you filter by multiple columns. And, in this day of modular writing projects, it lets you track the status of components across a broad writing team.

Scenarios for Using This Information Model

Let’s review a couple of scenarios that show different ways team members could use this information model.

Scenario 1—Mary wants to see what components she is responsible for. (Granted, in this example, that’s not too hard, but a real project would have a much longer inventory.) As shown in Figure 2, she merely has to click the arrow in the Writer column header to display a drop-down list and select her name to filter the list of components, displaying only those assigned to her. (The drop-down list lets her filter the list of components in several different ways.)

Figure 2—Filtering by writer

Filtering by writer

Once Mary has selected her name and the list is filtered to show just her assigned topics, she can further filter the list by selecting Blanks in the Dev. drop-down list under Status. She now sees just her own assignments that she has neither started nor completed, as shown in Figure 3.

Figure 3—Model filtered to show only components not yet started by a writer

Model filtered by components not started

Scenario 2—Mary’s boss wants to follow up on how the writers are doing, so she filters the list to see what topics are due on August 1st, the upcoming Friday, in the Due Date column.

Tip—Enter all due dates using a consistent day of the week such as Friday.

Mary’s manager look at the view shown in Figure 4, then walks by Mike’s and Mary’s cubes to ask them how the Updates and Status topics are coming, respectively, and whether they’ll finish them this week.

Figure 4—Model filtered to show only what’s due this week

Model filtered by week

How to Build This Information Model

“The information model does not require any formulas.”

The good news is that the information model I’ve shown so far in this article does not require any formulas. All cells require just simple data entry. Start by creating the headings for your columns, using the cell formatting tools for background color and font style. Personally, I find it convenient to give the whole page a background color, making data-entry cells white. (You can select the entire page by selecting the box in the upper left corner—just above the row identifier 1 and to the left of the column identifier A.)

The easiest and most powerful trick this worksheet uses is its ability to filter the table by column values. This feature is called AutoFilter, and you can set it up by doing the following:

  1. Select the column headers across the top of the table—in the example, row 3, from UI Element/Use Case through Edit.
  2. On the Data menu, click Filter, then AutoFilter.

There, you’ve done it. Now each column header contains a drop-down arrow that lets you display a list that includes all of the values in that column, plus the options All and Blanks—if some cells are blank. It also includes Sort Ascending and Sort Descending commands.

Tip—A good practice is to reset all filters to All when you’re finished, so the next user doesn’t panic when she opens the file and all of her topics seem to have disappeared.

The other trick this table uses is to provide drop-down lists where a limited set of data entries is allowed—for example, In Process or Done under Status or the names of the writers. Start by defining the lists in an out-of-the-way portion of the worksheet, as shown in Figure 5.

Figure 5—Defining drop-down list values

Defining list values

Then, follow these steps to create a drop-down list for data entry in a cell.

  1. Place the insertion point in the first data-entry cell at the top of the column.
  2. On the Data menu, click Validation.
  3. In the Data Validation dialog box, shown in Figure 6, select List in the Allow drop-down list.
  4. Place the insertion point in the Source box, then highlight the cells that contain the values you want—or type the values in the box.
  5. Click OK.

Figure 6Data Validation dialog box

Data Validation dialog box

Copy the cell format to the other cells in that column by clicking the cell to select it, then dragging its lower-right corner to highlight all the cells in that column.

What Else Can You Do?

“You could track project milestones other than just development and editing, record a context-sensitive link URL for each component, or use Excel’s formulas to help calculate durations.”

Although the information model I’ve demonstrated here is a complete tool, you can add other columns as you see fit. For example, you could track project milestones other than just development and editing, record a context-sensitive link URL for each component, or use Excel’s formulas to help calculate durations.

To calculate durations, you need to define some additional columns. To continue our example, let’s say each page in the UI had tabs, and we decided to scope the size of our effort by the number of tabs on each page. You could add a Tabs column and designate a cell for your sizing constant (k). The sizing constant in this case is the number of days you estimate it would take to document a tab.

Next, write a formula in the first duration cell to multiply the number of tabs by the sizing constant shown in Figure 7.

Note—You must put a $ in front of the constant’s column and row identifiers, making that address an absolute address. This is very important for the next step, when I’ll copy that formula to all the other cells.

Figure 7—Duration formula based on the number of tabs and a sizing constant

Duration formula

Next, copy that formula into the other cells in that column by clicking the cell to select it, then dragging its lower-right corner to highlight all the cells in that column.

Now, each component’s duration is the product of the number of tabs times the sizing constant. It is easy to experiment with different values for the constant. You can see the durations change instantly. Of course, you could use more advanced formulas, but it is this write-once / play-many scenario that lets you change variables and immediately assess their outcomes.

Conclusion

“Development processes defy precision. What has proven more valuable is to be able to maintain an up-to-date snapshot of where we are today and what still needs to be done.”

After years of trying to manage projects with precision, I realized development processes defy precision. What has proven more valuable is to be able to maintain an up-to-date snapshot of where we are today and what still needs to be done. And I have found Excel to be a great tool for this purpose. Your information model can change as a project’s requirements change. For example, you can retire some columns and add others as your need for information changes or add or remove rows as a project expands or contracts. Try this useful and flexible approach to project management on your next project!

7 Comments

Spreadsheet as project planning tool? I wonder what the project complexity and time span should be so Excel will suffice?

One of the big features you lack in your tool of choice is dependency tracking. Without this, I see no easy way to answer basic questions such as: When is the project going to end? Will the addition of new people make it faster? Why can’t I see progress in the work done?

The next feature missing is resource allocation and usage reporting. Coupled with dependency tracking, it’s an essential instrument to explain when the delivery date may actually happen.

I also fail to see how setting task duration beforehand is wrong in other planning tools. Every project I’ve seen of any scale required effort estimation to understand whether the business case is valid. Are you actually giving a quotation to your customer or presenting your deliverables along with the bill? I suggest you look closely at what difference there is between “define project” and “track, incorporate progress” modes in a project.

For the capacity you describe, there is another tool in which you might be interested. If your company uses Outlook, Task Request is the feature. It has all the assignment, progress, and due dates, plus email notifications and progress tracking.

Great article, Mike! You not only provide the contextual framework for using Excel for project management, but gave us enough concrete formulas to actually get started doing so. Nice job guy.—mw

Valentin,

Your points are all valid. Project management tools are more robust for project management than a spreadsheet. My point is that, when push came to shove, I have always felt I put more into those tools than they gave back. Dependency tracking, for example, only worked if the project behaved in a linear fashion. The assumption was that we cannot document task such-and-such until screen such-and-such is finished. Well, those who were responsible for screen such-and-such could not tell me when it would be finished; therefore, the dependency and placement in a time line didn’t help me a lot. What did help was what I demonstrated in this article: a tool for tracking what tasks people have finished, what’s left to do, and who’s got what.

I think you misunderstood my point about durations. Of course, we must estimate them ahead of time. But project management tools don’t help us do that. They take those estimates and let us apply them across a time line. All well and good, but I have found that while aggregated estimates can be fairly accurate, applying them at a granular level has never been that useful to me.

Finally, the solution I describe applies to some folks and not to others. Obviously, the project management tools have served you well, and I would not try to discourage you or others in similar situations to abandon them for less powerful tools like a spreadsheet. My solution is for those who, like me, just didn’t think their payback was worth the effort of using them and would like a less overhead-intensive solution—though it offers a smaller set of outputs, but a set that meets their particular needs.

Valentin, it seems that you might be used to contract settings that are by nature thoroughly defined enough in advance to allow for a WBS and quote in advance. The in-house doc teams I’ve worked with have generally not enjoyed the opportunity to plan that concretely. Instead, constantly changing resource allocations, project priorities, and all manner of other monkey wrenches and unknowns make setting task duration early in the project impossible. I haven’t tried Mike’s method in practice yet, but it appears to support the type of environment in which I have worked in the past.

As for “when the delivery date will actually happen,” I have never ever been asked to predict this using any tool. I have always been told when the product will be ready to ship, and it was my job to shuffle resources and manage expectations and, sometimes, make quality choices to meet that deadline. That’s why Project is a bad tool in such environments. It’s a lot harder to tell it when the project will end and work back from there—although it pretends to support that. It wants you to input task durations—which you don’t really know—so it can tell you when the project will end. In almost every environment I’ve worked in, that is just not the paradigm. Mike’s tool seems to meet a lot of the needs of this sort of environment.

Michael and Marta, thanks for your replies. I think we’re talking about different project levels. I’ll try to answer point by point.

On tool of choice: Process is meant to select the tool, not the other way round. My position is that there is no reason why you can’t do it in a spreadsheet, but once project complexity goes above a certain value, it will become hard to manage. What value is it? WBS (Work Breakdown Structure) size, team size, team distribution, third-party dependencies? Can this apply to the documentation process? For sure. You can have a large software package split into a number of phases; you can have documentation written in dozens of languages by a set of outsourced translation agencies; and so on.

On estimation: One of the main reasons to apply a measuring stick to a project is to be able to do the next project better. The measurement in this area could be time to market, resource usage, quality, project cost, and—what matters for documentation—time spent after delivery on issue solving. You should note the difference between defining a project—when you set WBS, durations—and tracking progress—when you put in work finished and remaining work. When you and your team are used to project tracking and estimation your granular tasks will be precise.

On dependency tracking: Dependency defines the relationship between two or more tasks in terms of precedence. Do you have this in the documentation process? Again, for sure. A simple example: You cannot ship documentation for preprinting or inclusion in the software image unless everything is written and checked. Does this prevent parallelism on a project? No. All the other tasks could be managed concurrently based on the resources available. The key here is to combine work estimates, dependency tracking, and resource allocation. Try, for example, doing resource leveling in Microsoft Project.

On contracts: I work with projects on fixed-price, cost-reimbursement, or time-and-materials contracts. In all of these cases, I have to understand what was the estimated project cost and what is the running project cost to simply answer the question: Will I profit or am I going to pay? And, truthfully, none of the projects were executed 100% on the initial project plan, because of the very same reasons of priorities and unknowns. However, this is expected. This is what makes a risk plan for a project necessary. No one says a project’s Gantt chart should be carved in stone and placed on the wall—it’s a living thing. A living thing that can be measured easily and in well-known terms. A project plan or risk assessment plan written once and not updated during the execution is worth nothing.

On adding resources: How would you know whether adding a headcount or two on a task would improve the schedule?

On the “delivery date actually happening”: I guess this is a problem. This means documentation is not considered an essential part of a product. The situation you’ve depicted means that, at the time someone set the shipment date, he or she was not seeing the whole project picture. There are several actions you could take to mitigate this risk. First, explain the importance. Points could be: compare issue rates on non-documented, documented, and well-documented projects. Second, present your project plan ahead of time and hope for the best. In both cases, you’ll need history and statistics.

Mike, great article and a nice example for people to follow. I have used Excel—as limited as it may be, Valentin—for many purposes, because it is so quick and simple to set up. The drop-down lists, validations, and auto-filter options are great time savers for inventory tracking, scheduling, and now, thanks to you, project management spreadsheets. While it may not have all the bells and whistles of Project, when you take into account the ease of use—adding new tasks, lists, columns, or rows is a snap—I am going to use it for some small project management documentation. With newer features allowing for charts and graphs, which could display data in other ways, maybe Excel doesn’t need to be the ugly stepsister to Word anymore.

Mike, my heart leapt when I saw the first screen shot in this article. Your spreadsheet is almost identical to the one I use.

My spreadsheet has a few extra columns: Priority, Origin of Task, and Document.

I also have a separate tab that summarizes the time estimates and the remaining time available. For example, it shows that we have 15 days of work remaining to finish the Installation Guide and only 11 days in which to complete it. In addition, it also shows that while Bill has 19 days of work on his list, Mary has only 8.

This separate summary tab helps me juggle tasks, and it also helps me answer promptly when anyone asks “How are the writers going?”

Join the Discussion

Asterisks (*) indicate required information.