Office Q&A: Word field codes, Excel values, and conditional formats
In this month's Office Q&A column, you'll hear from four readers. Susan has trouble with a little-known problem that renders Word's {StyleRef} field useless. Valeria wants to convert values entered as text to real numbers. Shailesh is looking for a simple navigating method in a long Word document. Stan needs a conditional format to use with dates. For your convenience, you can download the .xlsx or .xls demonstration file.
Little-known restriction with {StyleRef}
Susan is using a {StyleRef} field to repeat information throughout a document. She's using two custom styles: Cover Title and Cover Subtitle. Unfortunately, Susan sees the error shown in Figure A. This error means Word can't find the referenced style in use. After troubleshooting, Susan was able to determine that the styles were indeed in use. Everything looked correct.
Figure A
This error means the style isn't used in the document.
Usually, this error points to a simple typo or misapplied style. Once you determine the mistake and correct it, pressing [F9] updates the fields. This wasn't happening for Susan; she continued to see the error.
Susan's problem is a little-known restriction, and there's a clue in Figure A. You can't reference text that's in a text box. The {StyleRef} field can't see text in a text box. This restriction required Susan to rethink her document a bit, but once she eliminated the text box, the field worked as expected. I'd like to take credit for helping Susan, but she figured this one out on her own.
Convert text values to numbers
Valeria inherited a workbook with a column of values formatted as text instead of numbers. She was unable to format them as numeric values by changing the format. With a little sleuthing, Valeria found a space character preceding each value. This situation usually occurs when data is imported but not thoroughly inspected.
Excel can mathematically evaluate numeric text values, so you might not realize the stored values are text. As you can see in Figure B, the expression in C2 mathematically evaluates the text value in A2.
Figure B
Excel can mathematically evaluate text values.
A switch to General or a numeric format is usually the quick fix. That didn't work in this case, because each value has a preceding space, and removing those spaces was going to mean tedious work for Valeria.
With background error-checking enabled (see the circled triangles in Figure B) Valeria can convert those text values with little effort. Select the values and Excel displays the error tag shown in Figure C.
Figure C
This icon warns you that there's a potential problem.
Click the icon's drop-down to see the contextual list of potential errors shown in Figure D.
Figure D
The tag lists potential errors.
Choose Convert to Number and Excel will remove the offending space characters and apply the General format, as shown in Figure E. That was probably easier than you thought!
Figure E
Excel removes the space character.
Use the Navigation pane instead of scrolling
Shailesh has a large document and moving from section to section is a bit tedious. He could add bookmarks as shown in "How to create and use Word bookmarks to navigate a long document." This solution is slick, but it's overkill for most of us. If you're using Word 2010, use the Navigation pane instead.
To view the Navigation pane, click the View tab and check the Navigation pane option in the Show group. This feature relies on the built-in heading styles: Heading 1, Heading 2, and so on. The example document in Figure F is only one page, but you can quickly see the pane's potential.
Figure F
Use the Navigation pane to quickly access different sections of a document.
The Navigation pane provides a visual review of your document's structure and content by displaying up to nine heading levels (more than most of us will ever need). To access a section, simply click the appropriate heading in the Navigation pane. No scrolling!
Conditional format for date range
Stan's looking for a conditional format that highlights dates that are six days before or after a specific date--a date that he wants to change as needed. For example, if the date is April 2, 2015, the conditional format should highlight any date between March 27, 2015 and April 8, 2015. The simple expressions shown in Figure G return these two dates.
Figure G
Simple expressions return a date range.
The simple expressions =B1-B2 and =B1+B2 return the first and last possible date, respectively. Those two dates and all the dates that fall between comprise the time period Stan wants to highlight. Figure H shows two more helper expressions =B5>=$C$1 and =B5<=$D$1 in C5:D8; when both expressions return True, the value is in the time period.
Figure H
Helper columns compare dates.
With the two variables, April 2, 2015 and 6, only one date falls within the time period of March 27, 2015 and April 8, 2015 and that's April 1, 2015.
Now, we can turn all we've learned into a single expression that we can apply as a conditional format rule as follows:
Select B5:B8.
Click Conditional Formatting in the Styles group (on the Home tab). In Word 2003, choose Conditional Formatting from the Format menu and skip to step 4.
Choose New Rule.
Select Use a formula to determine which cells to format in the top pane. In Word 2003, choose Formula Is from the Condition 1 drop-down.
Enter the AND() formula =AND($B5>=$B$1-$B$2,$B5<=$B$1+$B$2)
Click Format.
Click the Fill (Patterns in Word 2003) tab, choose a color, and click OK (Figure I).
Figure IFigure I Click OK to see the results shown in Figure J.
Figure J
The rule highlights any date that satisfies the conditional rule.
If you change the date or the day variance value in B1 and B2, respectively, the format updates accordingly, as shown in Figure K. In this figure, the rule considers a 90-day range.
Figure K
The conditional format responds to updating either variable.
Breaking the requirements into easy-to-understand expressions clarifies the final rule's logic, but once you have the rule in place, you don't need the helper expressions in columns C through E.
Send me your question about Office
I answer readers' questions about Microsoft Office 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. Please mention the app and version that you're using. 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.
