Office Q&A: A confusing Word control and a simple Excel conditional formatting rule
Some readers require professional Office developers because their needs are more complex than I can handle in a brief question-and-answer session. When this happens, I’m glad to help put readers and developers together. However, most questions come from people who can do their own work but need a little guidance. This month, I discussed a confusing control behavior with Karen; I couldn’t give her a solution, just information. Obie received a simple conditional formatting solution.
I’m using Office 2013 on a Windows 7 system. The Word control discussion doesn’t need an example file and doesn’t apply to Word 2003. For the Excel solution, you can work with any simple data set or download the demonstration .xlsx or .xls file.
Word’s confusing Date Picker Content Control
Karen is using Word’s Date Picker Content Control to input a date and time. This control offers a dropdown calendar that makes specifying a date easy. If you’ve never used this control before, you might assume there’s also a dropdown for entering the time, but there isn’t. You must enter the time value manually and doing so isn’t particularly user-friendly or intuitive. This control’s inconsistent interface will confuse users who are unfamiliar with it.
We can best illustrate this problematic behavior with a simple example. To add this type of control to a document, do the following:
- Position your cursor where you want to insert the control.
- Click the Developer tab.
- Click Date Picker Content Control (Figure A) in the Controls group.
- To format the control, click Properties in the Controls group.
- In the Date Picker properties list, choose the format that combines the date and time (Figure B). A Title isn’t necessary, but if you decide to automate the control, a Title is a useful property.
- Click OK.
To use the control to enter a date, click the dropdown arrow and select a date, as shown in Figure C. After choosing a date, the time defaults to 12:00 AM. If you click the dropdown arrow a second time, expecting to see time values, you’ll be disappointed. The dropdown still shows a date calendar.
Entering a time value isn’t difficult because the control is smart, even if it isn’t intuitive. On the other hand, a user without specific knowledge might be lost at this point.
By default, the cursor is flashing at the beginning of the control. You don’t have to move the cursor, select the time, or anything else. Simply enter the time value, as shown in Figure D. The only requirement is that you must enter the AM or PM component. (If you choose a 24-hour format, you don’t need the AM/PM component.) When you exit the control, Word will format the date and time correctly, as shown in Figure E.
The problem isn’t one of flexibility; it’s a problem of expectations. Users expect to choose a time from the dropdown the same way they choose the date. When that doesn’t work, they might leave the time value at 12:00 AM.
You can use two controls and format one to accept and display date values and the other to accept and display time values. Unfortunately, you still won’t dispel the confusion because you can’t inhibit the dropdown (to the best of my knowledge) for the time control. If the format isn’t critical, consider using a text control instead of a date picker to accept and display the time value. Instructive labels will help either way. In its current state, this control isn’t a great option for entering time values.
Note: If the Developer tab isn’t visible, do the following to display it:
- Click More Commands from the Quick Access Toolbar’s dropdown or click the File menu and choose Options.
- In the left pane, click Customize Ribbon.
- In the list to the right, check Developer in the Main Tabs list.
- Click OK.
Formatting the minimum and maximum value
Obie has a large set of values and he wants to highlight the minimum and maximum value in each row. Fortunately, this is easily handled by Excel’s conditional formatting feature. Figure F shows a simple data set with a grand total to the right for each row.
We’ll add two helper columns. In the first, we’ll use the MIN() function to find the minimum value in each row. In the second, we’ll use the MAX() function. Then, we’ll apply a simple conditional formatting rule that highlights matching values in the Jan through Dec values for the corresponding minimum and maximum values in the helper columns.
Start by adding the helper columns, as shown in Figure G:
- In O4, enter the following function and copy it to O5:O10: =MIN(B4:M4)
- In P4, enter the following function and copy it to P5:P10: =MAX(B4:M4)
When entering the two functions, be careful not to include column N, the totaling column. In addition, don’t worry about the error tags Excel displays. In this case, they’re warning you that the expression doesn’t include the adjacent column, which is what you want; you don’t want to include the totaling column (column N). If you want to omit these tags, insert the two helper columns between the data set and the totaling column. The tags are harmless though (and don’t apply to Excel 2003).
With the helper columns in place, you’re ready to add the conditional formatting for the minimum value as follows:
- Select the data set, B4:M10.
- Click Conditional Formatting in the Styles group and choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu and skip to step 4.
- Click Use A Formula to determine which cells to format in the top pane.
- Enter the following expression in the control below: =B4:M10=$O4 In Excel 2003, choose Formula Is from the Condition dropdown and enter the expression.
- Click Format and in the resulting dialog, click Fill (Patterns in Excel 2003), choose a color (red), and click OK (Figure H).
- Click OK a second time to return to the sheet, which now highlights the value in each row that matches the corresponding value in column O (the minimum value in that row).
- Repeat steps 1 through 6, using the formula =B4:M10=$P4 in step 4 and choosing the color green in step 5. Figure I shows the resulting sheet.
Helper columns lend a great visual tool when working through a solution. But in this case, the rules are simple enough that you might not need them. You could skip the helper columns and add the MIN() and MAX() functions to the expressions (step 4). However, once others see the helper columns, they usually decide they can use them in other ways. In Obie’s case, he already had the helper columns in his data set. All he had to do was add the conditional formatting rules.
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. Please mention the app and version that you’re using. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at [email protected].
View the original here: