Software

Two Office technique enhancements from readers during 2016

Throughout the year, readers have shared their expertise and advice with other readers. Sometimes--as in the case of these two suggestions--the result has been an improved technique.

    hero

    Image: iStockphoto.com/DragonImages


    Some of my articles generate a bit of conversation among readers. I especially enjoy when someone suggests a valuable enhancement to a technique. In this article, I'll share two reader comments that are especially helpful:

    • A clarification of the term "default" in Outlook
    • An Excel expression that displays meaningful information to the user

    I'm using Office 2016 on a Windows 10 64-bit system, but both techniques will work in earlier versions. You won't need an example file for the Outlook section. You can use any Excel file you like for the second enhancement or download the demonstration .xlsx or .xls file.

    Clarifying Outlook default terms

    In the April article Office Q&A: Simple solutions for Outlook, Excel, and Word, I showed a reader how to set a default account:

    1. Click the File tab and choose Advanced in the left pane.
    2. If the default Inbox shown in the Outlook Start And Exit section isn't the default folder you want, click Browse (Figure A) and choose the appropriate folder.
    3. Click OK to return to Outlook.
    4. Close and reopen Outlook; the default account will be selected.

    Figure A

    2016122a.jpg
    This sets the folder Outlook selects when you launch the program.

    Officetrain was confused by this route—rightly so—and offered another:

    1. Click File.
    2. Click Account Settings and choose Account Settings.
    3. Choose the account to be the default.
    4. Click Set As Default (Figure B) in the toolbar.
    5. Click Close to return to Outlook.

    Figure B

    2016122b.jpg
    Choose a default account.

    The problem was my terminology—I used the word account when I should have used the term folder. I know the difference, and I shouldn't be lax about the correct use of terms; I apologize. My instructions set the default folder—the folder Outlook selects when launched. Officetrain's method sets Outlook's default mail account. Now, you might be wondering why it matters.

    Outlook's default account matters when a user has multiple email accounts configured in a single profile—there has to be a default. If the user doesn't intervene, Outlook uses the first mail account the user creates. This is the account that Outlook will default to when sending email, unless the user switches to another account. (There's more to it, but most users won't get beyond the mail account.) The behavior makes perfect sense, but of course, it trips us all up occasionally. That's why some users change the default account, and Officetrain offered instructions for doing so.

    Thank you Officetrain!

    SEE: Microsoft Office 365 is suffering from the dreaded feature creep

    Conditionally display descriptive text

    The August article How to use built-in Excel features to find duplicates discusses using filters and conditional formatting to find duplicates. derek.finch suggested adding a second conditional rule to display meaningful text that explains the format. You see, my rule takes for granted that the user understands the format, but that might not be the case. Figure C shows the final conditional formatting rule from the above article. Column G concatenates all the data using the function:

    =CONCAT(B4:F4)

    The expression in column H:

    =IF(COUNTIF($G$4:G4,$G4)>1,"Duplicate")

    counts the concatenated strings in column G and returns "Duplicate" when found; otherwise, the expression returns FALSE. The conditional rule shown in Figure C relies on the values in column H.

    Figure C

    2016122c.jpg
    The conditional rule formats a row when it's a duplicate but does not format the first occurrence.

    The text in column H is only meaningful for finding duplicate rows, and you could easily hide (and protect) both columns G and H. Users don't need to see these values, and they might confuse them.

    Or as derek.finch suggests, you could extend the technique by displaying more meaningful content in column H. To accomplish this, we'll modify the COUNTIF() expression to return "This is a duplicate record!" in a red font when appropriate and to display nothing otherwise. derek.finch recommended a second conditional format to do this, but there's an easier way. The first step is to alter the expression in column H as follows:

    =IF(COUNTIF($G$4:G4,$G4)>1,"This is a duplicate record!","")

    The logic is the same but the modified expression returns a meaningful message when a duplicate is found and nothing otherwise. After updating the expression in this column (H4:H14), use the Font Color tool to change the font color to red so it stands out.

    After making this change, the conditional format for B4:F14 will disappear (row 11 will lose its blue fill color). That's because the original condition was "Duplicate" not "This record is a duplicate!". The original rule's still working but isn't encountering the conditional value "Duplicate". We need to update the rule's condition as follows:

    1. Click the Conditional Formatting dropdown and choose Manage Rules.
    2. If necessary, change the Show Formatting Rules For Selection setting.
    3. Select the rule you're updating and then click Edit Rule.
    4. Change the rule to =$H4="This is a duplicate record!" (Figure D).
    5. Click OK twice to return to the sheet (Figure E).

    Figure D

    2016122d.jpg
    Update the rule's condition.

    Figure E

    2016122e.jpg
    The text in column H displays a helpful message about the format.

    Thank you derek.finch!

    And thank you all for your generous and kind responses throughout the year. I enjoy hearing from you, so please let me know when you find an article useful or if you have questions. Holiday greetings to all of you!

    Send me your question about Office

    I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. 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. 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 susansalesharkins@gmail.com.

    Also read...

    About Susan Harkins

    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.

    Editor's Picks

    Free Newsletters, In your Inbox