Microsoft

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.

Mail-envelope.svg.png
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.

2013226.JPG

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.

2013230.JPG

2013231.JPG

Also read:

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.

3 comments
mslizny
mslizny

When I taught Outlook 2010 last week one of the students found that a previous Outlook feature, which allowed her to see her supervisor's email subjects in a different color than other emails in the mail list, was gone.  The setups done in an earlier version were still in a different color in the mail list, but the ability to use this formatting to identify email by the sender seems to have evaporated.

Mark W. Kaelin
Mark W. Kaelin moderator

Do you have a particularly vexing problem with Microsoft Office? Are you having a difficult time figuring out how to accomplish a task? Perhaps Susan can solve it for you?

Editor's Picks