Excel Hacks for Help Writers
Published: July 21, 2008
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
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
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.
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
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
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
How to Build This Information Model
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:
- Select the column headers across the top of the table—in the example, row 3, from UI Element/Use Case through Edit.
- 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
Then, follow these steps to create a drop-down list for data entry in a cell.
- Place the insertion point in the first data-entry cell at the top of the column.
- On the Data menu, click Validation.
- In the Data Validation dialog box, shown in Figure 6, select List in the Allow drop-down list.
- 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.
- Click OK.
Figure 6—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?
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
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.
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!