October Office tip mailbag: Outlook settings and Excel conditional formatting

Susan Sales Harkins helps a reader find an old setting in a new version of Outlook and provides a unique Excel's conditional formatting rule.

This month, I help a reader find a familiar and friendly Word setting. In addition, I show a reader where to find a mail setting after upgrading to Outlook 2010. I also supply a unique conditional formatting rule that combines AND() and OR() - a bit unusual, but doable.

An Outlook setting

Jerry upgraded to Office 2010 and like most of us, he lost a few custom settings. By default, if you open a message and delete it, Outlook will return to the folder. However, Jerry prefers to go directly to the next message. This is a simple setting in Outlook 2010 and 2013:

1. Click the File tab.
2. Choose Options (under Help).
3. Select Mail in the left pane.
4. In the Other section, choose Open The Next Item from the After Moving Or Deleting An Open Item dropdown.
5. Click OK.

An Excel conditional format

Note: To help follow this tip a demo file is available as a free download.

Many readers ask for help with complex conditional formatting rules. Shannon wants to use two conditions to format, but putting them together in one rule was a challenge. The two conditions are:

• Value in column A is FALSE.
• Value in column B is one of five characters: K, D, S, V, or M.

To combine them into one formula, Shannon needs AND() and OR() as follows:

=AND($A1=FALSE,OR($B1="K",$B1="D",$B1="S",$B1="V",$B1="M"))

Now, let's apply this rule to a simple data range:

1. Select the range. Using the sample range shown below, that's A1:B15.
2. Click the Conditional Formatting option in the Styles group (on the Home tab). In Excel 2013, choose Conditional Formatting from the Format menu and skip to #4.
3. Choose New Rule.
4. In the resulting dialog, click the Use A Formula To Determine Which Cells To Format option. In Excel 2003, choose Formula Is from the Condition 1 dropdown.
5. Enter the rule shown above.
6. Click Format.
7. In the resulting dialog, click the Fill tab, choose a color, and click OK twice. In Excel 2003, click the Patterns tab. If both of the AND() conditions return TRUE, Excel formats the record.