THIS IS A MAIN HEAD



Simple Scheduling

Mike Sawyer

For small- to mid-scale project tracking, spreadsheets exemplify simplicity and functionality. Spreadsheets are ideal for managing and presenting lists of information. This progression introduces Microsoft Excel tables, formulas, standard and conditional formatting, sorts and filters, and sharing and archiving strategies for the information. For the latest version of this guide and other supporting materials, see bit.ly/simpleschedule.

Is your workload too big for sticky notes but the budget’s too small for a complex tracking tool? Learn how to use Microsoft Excel conditional formatting, tables, and formulas to list, sort, and automatically mark priority projects and track your progress. This ubiquitous tool can do more than most of us realize!

This session covers spreadsheet layout for a doc schedule, types of fields and data, formatting, helpful formulas, best practices, and on-the-cheap alternative spreadsheet programs.

Assumptions

The presentation assumes a familiarity with spreadsheets in general, and Microsoft Excel in particular. Resources are provided, though, for learning basic spreadsheet principles and alternatives to Excel.

Layout

Although you can design the sheet however you want, these are some guidelines I’ve used with success:

• Although an Excel spreadsheet can have thousands of columns and over a million rows, try to keep your sheet narrow enough that it can print its full width onto a landscape sheet.

• List your projects in the left-most column, and project data fields along the top in a non-scrolling heading row.

• Small fonts are your friends. Narrow fonts are especially useful in keeping information compact.

• For widescreen viewing and wide printing, define the page size as Legal (or a similar wide format).

[pic]

• Define the rows and columns as a data table. This enables easy sorting and filtering (explained later). To define your list as a table, select the range of cells, then click Table in the Insert ribbon.

• Define the first column as sequential row numbers that don’t sort or change order with the rest of the data. This makes it easy to refer to row numbers when discussing the schedule with others.

• To more easily work with printouts of the schedule, create a header or footer that contains page numbers, print date, filename, and file location.

Column Titles (Fields)

The fields you define as columns in your sheet will depend completely on your own job’s needs. If you are an internal department, your focus may be on product development schedules and related milestones. If you do work for external clients, you may focus on tracking billable hours (and expenses). You’ll get the clearest understanding of your needs by first starting to use a prototype tracking sheet, then modifying it as you go. Here are some fields to consider:

• Part number (or some unique identifier)

• Product name

• Code name (used until product name is official)

• Deliverable type (user guide, brochure, online help, etc.)

• Priority

• Assignments (writer, editor, designer)

• Goals & gates: Draft due, final due, product ship date

• Historical: Date entered/requested, date edited, date approved, date sent to press

• Time measuring: Elapsed time since start (work days vs. true days), time left before due. Hours (for billing), budget (hours or money)

• Cost: Budget planned, budget used, budget remaining

• Miscellaneous notes (often the widest field)

Formatting

You’ll want the data to be dense, but readable, and you’ll want the significance of priority data to stand out. To accomplish this, you can use direct formatting and conditional formatting.

Direct formatting

You’re a technical communicator, so you’re already well-versed in basic text formatting. But there are some quirks to Excel formatting to keep in mind:

• You are the audience for this document, so use formats that work well for you.

• You can edit selected characters/words within a cell, but it’s easiest to apply edits to the cell as a whole. Think of cells as mini-paragraphs; just click to select a cell, then click a format on the Home ribbon to apply to the cell.

• Along with the familiar horizontal alignment formats, in cells you can set the vertical alignment. For some reason, Excel aligns cell content at the bottom by default (who reads from the bottom first?), so change that in any new spreadsheet by selecting the entire sheet and clicking Top Align on the Home ribbon.

[pic]

• You can format a cell’s fill color, but you can also format its pattern (for a “shaded” look). Be conservative in applying fill color or pattern—what looks good on-screen may not be readable when printed. And if you print only to black & white, keep that in mind when you’re selecting formatting for cell shading (avoid shades of similar darkness, for example).

• Try formatting text or shading to mark urgent projects with red, or completed projects with green.

• To prevent long text from getting truncated at the cell’s border, select the entire sheet and click Wrap Text on the Home ribbon.

• When using dates, use short date formats to save room. Excel “remembers” the full date (along with the year day of the week), even if the date format you select is a short “xx/xx” version.

• When using currency, use short currency formats to save room. Excel “remembers” the full money amount, down to whatever fraction you specified, even if the currency format you select is a shortened or rounded version.

Conditional formatting

Conditional formatting is “programmed formatting” that’s applied automatically when certain conditions are met. The “Starter Spreadsheet” available at bit.ly/simpleschedule contains examples that you can try out and copy. Conditional formatting is powerful, and it’s fun to experiment with.

Examples:

• Automatically shade a project’s Part Number cell green when the project’s “Priority” column is marked “A.”

• Mark the “Due” cell red if the date is less than a week away.

• Automatically draw a progress bar in background of a “% Complete” cell to visually depict the number.

[pic]

To apply conditional formatting (this will be demonstrated in the session):

1. Select the cell(s) to apply the conditional format to.

2. On the Home ribbon, click Conditional Formatting, then click New Rule.

3. Select the rule type and parameters, then click OK.

Sorting and filtering

After you’ve defined the data as a table, sort buttons appear to the right of each column label. To sort on any column, click its sort button, then select which order you want the data sorted by.

[pic]

You can also temporarily hide rows of data by using a filter. To use a filter, click the column’s sort button, then select the rows to display or hide:

[pic]

Formulas and functions

Formulas transform the spreadsheet from a record-keeping tool to a computing tool. Formulas automate repetitive tasks and do math for you. You can think of formulas as equations—because that’s what they are, only these equations start with the “=” symbol.

To enter a formula in a cell (this will be demonstrated in the session):

1. Type the equals symbol (=), then type the function name (such as AVERAGE).

4. After the function name, type the range in parentheses (if a range is required).

5. Press Enter. The formula disappears, and the value it returns is displayed.

When you type a formula into a cell, Excel “remembers” what you typed there, but it displays only the result of the formula. “Functions” are terms you can use within a formula to help it complete its computation (such as “MEAN” to compute the mean value of a range of cells). The “Starter Spreadsheet” available at bit.ly/simpleschedule contains working examples of formulas and functions. Here are some of my favorites:

• AVERAGE Computes the mean value of a range of cells.

• CELL Can show the sheet’s filename. Useful for headers/footers.

• CONCATENATE Joins several text strings into one text string.

• COUNT Counts the number of cells within a range that contain numbers.

• COUNTBLANK Counts the number of empty cells within a range of cells.

• COUNTIF Computes the number of cells within the defined range that meet the criteria you specify.

• MAX Returns the largest value in a set of values.

• MEDIAN Returns the median value in a set of values.

• MIN Returns the smallest value in a set of values.

• ROUND Rounds a cell’s fractional value to a specified number of digits.

• SUM Adds all numbers in a range of cells.

Time functions:

• DAYS360 Returns the number of days between two dates. Useful for counting days remaining before deadline.

• NETWORKDAYS Returns the number of “business days” (M-F) between two dates.

• NOW Returns the current date and time, down to the second.

Best practices

Although you should use whatever process works for you, here are some guidelines I’ve used with success:

• Create (and name with the date) a new copy of the sheet (a “tab”) each week.

• Name the sheets by date (for example, YYYY-MM-DD).

• Maintain at least one quarter’s history of sheets, archiving old sheets at the end of each quarter.

• Save a copy of the sheet each week, named by date, and store in a location that is backed up.

• Maintain a master project history separate from the doc schedule. The doc schedule itself is dynamic and transitory—it can change daily, and projects can be removed from the sheet as they are completed to prevent the schedule from becoming cumbersome.. A master project history records every project, when it was completed, and who completed it. It can also record where the project’s files are stored, which revision is current, and what changes are requested for the next revision. I only add to the master history when a new project begins and is completed.

• For recording work hours, consider recording only the totals on your doc schedule, and keep the details to specialized time tracking software. (Recording individual, day-by-day hours in the doc schedule can result in numerous columns and an ungainly worksheet.)

• If your company or department doesn’t already use a part numbering scheme to uniquely identify each project, start now. Unique part numbers simplify record keeping and archiving in a big way.

Additional tips

For a list of cheap/free Excel alternatives, search online or see the list at on bit.ly/simpleschedule. See the same website for more tips on designing doc schedules.

Resources

Sawyer, Michael D. “Simplified Scheduling.” (8 May 2015). stc/schedule.html.

References

Excel Easy. “Formulas and Functions.” Excel- (8 May 2015). introduction/formulas-functions.html.

Tech on the Net. “MS Excel: ALL Formulas/Functions.” (8 May 2015). excel/formulas/.

Microsoft Office Support. “Overview of formulas.” Support. (8 May 2015). support.en-ca/article/Overview-of-formulas-7abfda78-eff3-4cc6-b4a7-6350d512d2dc.

Small Business . “4 Free Spreadsheet Alternatives to Microsoft Excel.” (8 May 2015). tools/article.php/10730_3939366_2/4-Free-Spreadsheet-Alternatives-to-Microsoft-Excel.htm.

Author Contact Information

Mike Sawyer

Documentation Manager

Control4

92 West 1565 North

Orem, UT 84057 U.S.A.

385.209.7164

Author Biography

Mike has 20 years of experience in technical documentation as an employee, freelancer, and manager. He loves writing, editing, and designing, and also has a background in human factors engineering and ergonomics. He’s worked for WordPerfect/Novell, Gateway, and Linksys, as well as many smaller companies and clients. He likes organizing information and helping others, so is happy with his career choice. Before starting his writing career, he worked as a bag boy, cashier, fireman, and bank teller (but not at the same time). He graduated in Communications Studies, minoring in English, at Brigham Young University. He completed coursework for his master’s in Human Factors Engineering at the University of South Dakota.

He’s lived in California, Idaho, Iowa, and Utah, and served as a missionary in Japan for two years. He has five kids, one wife, five cats, and two litter boxes. He considers himself wealthy, by litter box standards.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download