Friday , 19 July 2019
October Office tip mailbag: Outlook settings and Excel conditional formatting

October Office tip mailbag: Outlook settings and Excel conditional formatting

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.JPG2013226.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.JPG2013230.JPG

2013231.JPG2013231.JPG

Also read:

  • September
    Office tip mailbag: A little magic
  • August
    mailbag: The benefits of Excel helper columns
  • July’s
    mailbag: Microsoft Office upgrade angst

Leave a Reply

Your email address will not be published. Required fields are marked *

*