Three Excel hacks for technical writers

-by Ken Schatzke, Senior Member, Alberta Chapter

When it comes to numbers, technical writers run the gamut. Some of us love them, and some of us love to hate them. Regardless of where you are on this spectrum, you likely need to deal with numbers in some form or other as part of your work.

Microsoft Excel is a great tool for number crunching. Unfortunately, some of its most powerful and useful features are not immediately obvious. This post introduces you to three features that are particularly beneficial to technical writers.

I’ve tested these features using Excel 2016, which is the latest version of this venerable software. These features should work with older versions of Excel as well, but some steps might vary.

A quick overview of formulae and functions

Formulae and functions are key concepts in Excel. I will be discussing formulae and functions quite a bit in this post. If you’re familiar with these concepts, feel free to skip over this section. Otherwise, read on.

In a typical Excel spreadsheet, most cells contain data—text, numbers, dates, and so on. Some cells are different; they contain formulae that calculate values based on data in other cells.

To enter a formula in a cell, simply type = followed by the formula. Formulae can contain references to other cells and simple arithmetic functions. For example, this formula adds the values in cells A1 and B1:

=A1+B1

Most formulae contain more complex functions, which allow you to do more sophisticated things than adding, subtracting, multiplying and dividing. For example, the AVERAGE function calculates the mean for a defined range of cells:

=AVERAGE(A1:A10)

Functions consist of a name (AVERAGE in the above example) followed by one or more arguments inside parentheses. If a function contains more than one argument, the arguments are divided by commas:

=IF(A1>B1,”A1 is bigger”,”B1 is bigger”)

Click the insertfunctionbutton button in the top-left corner of the Excel window to open the Insert Function dialog box. You can use this dialog box to browse all the functions that Excel supports.

Now that you understand formulae and functions in general, let’s look at some particularly useful ones for technical writers.

Converting measurements

At some point in every technical writer’s career, he or she will need to convert measurements from Imperial to metric or vice versa. This is particularly true for those of us who write hardware and scientific documentation and who work with American clients.

If you need to convert one or two measurements, you can use Google or Windows Calculator. If you need to convert many measurements, Excel’s CONVERT function is a better alternative. For example, if you want to convert a measurement in cell A1 from inches to centimeters, use this formula:

=CONVERT(A1,”in”,”m”)*100

The CONVERT function converts the measurement in A1 from inches (in) to meters (m). The formula then multiples the measurement in meters by 100 to determine the measurement in centimeters.
If the measurement in cell B1 is in centimeters, you can convert it to inches using a similar formula:

=CONVERT(B1/100,”m”,”in”)

This formula first divides the measurement in B1 by 100 to determine the measurement in meters. The CONVERT function then converts meters to inches.

By default, the output of formulae displays as decimals. For example, if the value in B1 is 6, the above formula will output 2.36220472. You can display the number as a fraction rather than as a decimal. This is particularly useful when converting to an Imperial measurement like inches.

  1. Right-click the cell containing the formula, and then select Format Cells.
  2. Click the Number tab.
  3. Select Fraction in the Category list, and then select As eighths (4/8) or As sixteenths (8/16) in the Type list.
  4. Click OK.

You can use the CONVERT function to convert many types of measurements, including lengths, areas, volumes, weights, temperatures and times. You aren’t restricted to converting between metric and Imperial. If you grew up after Canada’s conversion to metric in the 1970s and aren’t quite sure how many feet are in a mile (5,280) or how many fluid ounces are in a U.S. gallon (128), you can also use the CONVERT formula to convert between Imperial measurements:

=CONVERT(C1,”mi”,”ft”)

=CONVERT(D1,”gal”,”oz”)

You can even convert more exotic measurements, like light years to miles:

=CONVERT(E1,”ly”,”mi”)

See Excel’s online Help for more information on the CONVERT function.

Counting business days

If you manage a team of technical writers or work as a contract technical writer, you’ve probably been asked to calculate the number of business days for a project or the number of business days between a forecasted delivery date and an actual delivery date. Of course, you could just count the days on a calendar, but if a project spans several months—or even years—you have another option: Excel’s NETWORKDAYS function.

The NETWORKDAYS function calculates the number of days between two given dates, excluding weekends and holidays. Before using the function, you need to enter all the holidays that fall between the two dates somewhere in your Excel file. Here is an example that includes all the holidays for Alberta in 2017:

holidays

I recommend creating a separate tab in your Excel file for holidays, but this isn’t a requirement.

Once this is done, you can create a formula to calculate the number of business days between two dates. In the following example, the start date is in cell A1, the end date is in cell B1 and the holidays are in cells A1 to A11 in a separate tab named “Holidays”:

=NETWORKDAYS(A1,B1,Holidays!A1:A11)

The NETWORKDAYS function includes both the start and end dates in its count, so if you calculate the number of business days between Tuesday, January 3, 2017, and Friday, January 6, 2017, the result will be 4. If you want to exclude the start date or the end date (to count the number of days a deliverable is overdue, for example) simply subtract 1:

=NETWORKDAYS(A1,B1,Holidays!A1:A11)-1

See Excel’s online Help for more information on the NETWORKDAYS function.

Creating dynamic drop-down lists

If you use Excel to create forms or team spreadsheets, you can use Excel’s Data Validation feature to add a drop-down list to a cell. You and other users can use the drop-down list to select an appropriate value for the cell:

dropdownlist

To create a drop-down list, first enter the values for the list somewhere in your Excel file. I recommend creating drop-down list values in a separate tab.

Once this is done, complete the following steps:

  1. Select the cell or cells you want to add the drop-down list to.
  2. In the Data ribbon, click Data Validation.
  3. Select List from the Allow drop-down list.
  4. Click the button1 button beside the Source box.
  5. Go to and select the cells contains the drop-down list values, and then click button2.
  6. Click OK.

You now have a drop-down list, but what if you need to add a value to this list or delete a value? You can do this by placing the cell values in a table. A table is like a mini-database inside your Excel file. You can add and remove rows (records) to it at any time. Anything that references the table, including drop-down lists, updates automatically.

To create a table for your drop-down list, do the following:

  1. Select the cells containing the drop-down list values.
  2. In the Insert ribbon, click Table.
  3. If your table has a header row, select the My table has header cells check box. Otherwise, leave this check box blank.
  4. Click OK.
  5. Select the cells of the table excluding the header cells.
  6. n the Formulas ribbon, click Define Name.
  7. Type an appropriate name for the table in the Name box.
  8. Click OK.

You have formatted your drop-down list values as a table and assigned the table a name. You can now refer to the table rather than the individual cells when creating drop-down lists:

  1. Select the cell or cells you want to add the drop-down list to.
  2. In the Data ribbon, click Data Validation.
  3. Select List from the Allow drop-down list.
  4. Type the name of the table in the Source box.
  5. Click OK.

If you add rows to or remove rows from your table, the drop-down list updates automatically.

See Excel’s online Help for more information on data validation, tables and names.

Learning more

These are only some of the features of Excel that are useful for technical writers. For information on other features, check out Excel’s online Help or simply search for what you’d like to do with Excel in Google.

No matter your comfort level with numbers and mathematics, Excel can make light work of your number crunching tasks.

One thought on “Three Excel hacks for technical writers

  • January 14, 2017 at 9:06 pm
    Permalink

    Great article, Ken. Not only does Excel handle dates really well, it will also perform calculations on time values. This is handy if you are tracking how many hours and minutes were spent on a project. An invoice form created in MS Word can be linked to those values in the spreadsheet to ensure values are always correct if last minute updates are made.

Leave a Reply

Your email address will not be published. Required fields are marked *

Wordpress Login | Google Sites