Microsoft

Office Q&A: Adjust options and conditional format rules

In this installment of Office Q&A, Susan Harkins helps readers recover AutoFormat and extend a conditional format rule.

There are so many options in the Office applications that it's impossible to know them all. However, being aware that they exist and that you can make simple adjustments will help you work more efficiently. Excel's conditional formatting feature is similar, because a simple adjustment can make all the difference in the world.

AutoFormat lost

Alex has grown fond of an AutoFormat setting that lets him enter 1) to create an indented numbered list. It's a simple shortcut -- to create an indented numbered list without clicking the Numbering option in the Paragraph group, do the following: enter 1) and press Tab. Word will automatically indent the item and enable the numbered list feature for you. If you'd rather display a period character instead of the parenthesis, enter a period character instead of the parenthesis before pressing Tab. Without knowing how or why, this feature suddenly stopped working for Alex.

There are a number of options that Office enables by default. Generally, someone has to disable the option for it to stop working. Occasionally, an add-in or some other newly-installed software is the culprit. It's hard to know why an option stops working, and it's frustrating if you don't know how to reset it. In Alex's case, the fix is simple:

  1. Click the File tab and choose Options in the left pane. If you're using Word 2003, choose AutoCorrect Options from the Tools menu and skip to #4.
  2. Select Proofing in the left pane.
  3. To the right, click the AutoCorrect Options button in the AutoCorrect Options section (Figure A).
    Figure A
    Figure A
  4. Click the AutoFormat As You Type tab.
  5. Check the Automatic Numbered Lists option in the Apply As You Type section (Figure B).
    Figure B
    Figure B
  6. Click OK twice.

There are many options, and I encourage you to spend a little time reviewing them. Then, if a feature suddenly stops working, you'll probably remember that an option exists and go exploring. Most likely, you'll find it quickly enough and set things right, even if you don't remember the exact setting at first. Knowing where to look is more important than memorizing every option.

Reply text option in Outlook

When Stephanie receives a reply to an ongoing conversation from one of her friends, she only sees the friend's most recent response -- the earlier conversation is missing. This setting isn't something Stephanie can fix on her end. Her friend needs to reset her Outlook options to include the entire thread. Fortunately, the fix is easy:

  1. Click the File tab and choose Options in the left pane.
  2. Choose Mail in the left pane.
  3. In the Replies And Forwards section, you'll find two drop-down menus for controlling the text that appears in replies and forwards. To include the original message text in either or both, choose Include Original Message Text (Figure C), accordingly.
    Figure C
    Figure C
  4. Click OK.

I suspect that Stephanie's friend will find this option set to Do Not Include Original Message. Now, the hard part is getting her friend to change her setting. There's nothing Stephanie can do on her end to reclaim the discarded thread in replies from others that don't include earlier text.

Extending a conditional format

Mike wants to highlight rows conditionally. So far, he's been able to highlight the conditional values, but he wants to highlight the entire row. The solution is simple: select all of the columns that comprise the data range before you apply the conditional format rule. We'll use the simple data range in Figure D to demonstrate this technique. First, we'll apply the conditional format to the conditional column using a conditional rule. Then, I'll show you how to extend the highlight to include all the columns that comprise the record. You can work with any data you'd like, or you can download the example .xls or .xlsx file.

Figure D

Figure D

A simple data set.

To assign a conditional format to column A, do the following:

  1. Select A2:A5.
  2. On the Home tab, click Conditional Formatting in the Styles group. In Excel 2003, choose Conditional Formatting from the Format menu and skip to step 4.
  3. Choose New Rule.
  4. In the resulting dialog, choose Use A Formula To Determine Which Cells To Format. If you're using Excel 2003, choose Formula Is from the Cond 1 drop-down menu.
  5. Enter the formula =$A2="Cat"
  6. Click the Format button (Figure E).
    Figure E
    Figure E
  7. In the resulting dialog, click the Fill tab (Pattern tab in Excel 2003), choose a color, and click OK.
  8. Click OK, and Excel will highlight the cells in column A that contain Cat (Figure F).
    Figure F
    Figure F

The simple formula =$A2="Cat" returns true when the value in column A is cat. When the formula is true, Word applies the conditional format you chose.

You should know that there are other ways to achieve this simple conditional format, and that's where you might run into trouble. Other rules, especially the built-in ones, aren't as flexible as the formula rule. You should use them of course, but you can't always amend them. When these rules aren't adequate, try the formula rule.

Now, let's extend the highlight to include columns B and C:

  1. Select A2:C5. Instead of selecting only the column that contains the conditional values, select all of the columns that comprise the data range.
  2. Repeat steps 2 through 8 above. Figure G shows the extended highlights.

Figure G

Figure G

Extending the format is as simple as selecting more columns.

It really is that simple if you use the formula rule option. Simply change the original selection before you apply the conditional format rule. As I mentioned, this simple change works only because I used the formula rule. If you've applied the conditional format using another option, you might have to delete it and start over, because you probably can't extend it to include the entire row.

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 instance, "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.

Get the latest information about Microsoft, including some tips and tricks, by automatically subscribing to TechRepublic's Microsoft in the Enterprise newsletter.

About

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.

0 comments

Editor's Picks