Microsoft

Office Q&A: Modifying built-in Word styles and using a little-known Excel function

In her monthly Microsoft Office Q&A column, Susan Harkins offers some tips for working with Word styles and Excel's conditional formatting.

Questions vary, but many readers ask about Word styles and Excel's conditional formatting. Both are powerful features, and knowing the basics is key to working efficiently. For example, Chris is inserting pictures into a Word document and he doesn't like the default caption attributes. The solution is simple, if you know where to go. Tara wants to use Excel's conditional formatting to highlight project due dates when they're too demanding. Again, the solution is simple if you know about Excel's DATEDIF() function. Because this function isn't documented, many users don't take advantage of it. Now, let's move on to a couple of solutions that are easier to implement that you might anticipate.

You won't need a downloadable example file for the Word technique, but you can use the downloadable .xls or .xlsx file for the Excel technique.

Word's built-in styles

Chris is using Word 2013 on Windows 7. When he adds a caption to a picture, Word italicizes the text, which Chris has to change with each new picture he inserts. The solution is simple: find the default style and modify it. You're probably wondering which style. Word displays the style in use in the Quick Style Gallery, as shown in Figure A. Once you know the style's name, you can modify it.

Figure A

Figure A

Word's built-in styles have meaningful names.

To modify a style, do the following:

  1. Click the Styles group's dialog launcher (the small arrow in the bottom right corner) to open the Styles dialog (Figure B).
    Figure B
    Figure B
  2. Chances are that you won't see the style listed, so click Manage Styles (the third icon at the bottom).
  3. In the resulting dialog, choose Caption in the Select a style to edit list. (If Caption is dimmed, it's because the style isn't currently in use.)
  4. Click Modify.
  5. In the resulting dialog, click the Italics icon to turn off italics. If you want to make other changes, make them now. For example, you might want to make the font size larger and bold (Figure C).
    Figure C
    Figure C
  6. Before clicking OK, decide if you want this change in all new documents or only in the current document. The default is the current document. To update the Caption style in all new documents, click the New documents based on this template option.
  7. Click OK twice.

After making this change, the caption text will be non-italicized, larger, and bold, as shown in Figure D. Notice that the existing caption automatically updates.

Figure D

Figure D

You can modify built-in styles.

DATEDIF() and conditional formatting

Tara enters project information that includes a submission date and a needed-by date; each project receives at least two months. Tara would like a conditional format to highlight needed-by dates that are less than two months from the project's submission date. This is an excellent opportunity to apply the technique I discussed in "Pro tip: Accommodate changing conditions in Excel's conditional format feature." In this case, we'll make the two-month buffer an updatable value in the sheet. That way, Tara can change the buffer without updating the conditional format rule.

Figure E shows a sheet with a simple data set. As you can see, I've entered the value 2 in B1 to represent the buffer requirement of 2 months. Now, we'll add a conditional format rule that incorporates that value into the conditional rule

=DATEDIF(B4,C4,"m")<$B$1

Figure E

Figure E

Add a conditional format to highlight inadequate needed-by dates.

This rule uses a little-known function, DATEDIF(), which does exactly what it sounds like — it returns the number of specified intervals between two dates. In this case, the third argument (m) specifies months. When the difference between the two dates in B4 and C4, in months, is less than the value in B1, the expression returns True. Now, let's put this rule into place:

  1. Select C4:C6.
  2. Choose New Rule from the Conditional Formatting drop-down in the Styles group (on the Home tab).
  3. In the resulting dialog, select Use a formula to determine which cells to format in the top pane.
  4. In the lower pane, enter the formula =DATEDIF(B4,C4,"m")<$B$1 (Figure F).
    Figure F
    Figure F
  5. Click Format.
  6. On the Fill tab, choose a color, and click OK three times to return to the sheet shown in Figure G.

Figure G

Figure G

The new rule highlights dates that don't allow for the full two months.

Right now, only one record, project 1, satisfies the new rule. September 20 isn't a full two months beyond the submission date of August 1. If you change the value in B1, say to 3, the formatting changes, appropriately, as shown in Figure H.

Figure H

Figure H

Change the highlight by changing the value in B1.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox