Outlook Top 5 Tips and Tricks

Outlook is an awesome tool to stay productive. Here is a quick list of 5 tips you can start using to work faster.

  1. Drag a file into the email body while composing to automatically create and attachment. This works great if you have a document on the desktop that you want to quickly attach.
  2. Drag an email to the Calendar to quickly save an appointment. In Outlook 2007 and 2010, drag the email to the To-Do Bar. In Outlook 2013, drag the email to the Calendar icon from the navigation pane. Then save after editing the date/time. All of the notes that were in the email body will now be apart of the appointment details.
  3. Switch your Outlook signature with a right click. Outlook allows for multiple signatures. Access the signatures from the File tab–>Options–>Mail–>Signatures.
  4. Right click someone’s email and then choose “add to contacts”.
  5. Create re-usable text with Quick Parts. Select your text, then click on the Insert tab and choose Quick Parts–>Save Selection to Quick Part Gallery. When you want to reuse the text, simply click on Quick Parts and then choose your item you want. This is fast way to repurpose content.
Posted in Outlook | Tagged | 6 Comments

How to Format Dates In Excel That Are Over One Year Old To Turn Red, Yellow If Eleven Months Ago, And Green If Between One and Ten Months Ago

I’m writing this post in response to a comment that Charles wrote in my “How to change the color of an Excel date to red if over 1 year old”  post. It was such a good comment I thought I’d share my thinking with the rest of you in a new post.

I am tracking the training status of the people on my project. All training has to be taken annually. I want to set it up where the cell turns green if the date in the cell is between 1 and 333 days from today’s date i want to set it up where the cell turns yellow if the date in the cell is between 334 and 364 from today’s date I want to set it up where the cell turns red if the date in the cell is 365 days or more from today’s date.” – Charles

Let’s start with making the dates turn red if the training date has occurred over 1 year ago. In my screen shots below I’m using date cells that start with B2 so I select the first date all the way down to the last date. I click Home tab–>Conditional Formatting –>New Rule. Then I choose “Use a formula to determine which cells to format.”

I click format and select a red fill and then I write the formula.

=B2<=TODAY()-365

Over 1 year red

The TODAY() function is volatile and updates to the current date. Excel has a serial number value for each date. For example Jan 1, 1900 =1, Jan 2, 1900 =2, Jan 3, 1900 =3, Jan 4, 1900 =4 and Sep 19, 2014 =41,901. Everyday is another plus one. So we can use the greater than > and less than < operators to compare dates against the TODAY() function. You have to think in numbers and older dates will always be lesser than newer dates. So if you were born a long time ago you actually have a smaller Excel number than a someone from a younger generation.  And this is coming from one who just bought his first pair of reading glasses. :)

After I create the first conditional format with red cells, I  select the same cells and repeat the setup for the formula new rule for yellow colored cells. This will format cells yellow if the training date occurred 11 months ago.

=AND(B2>=TODAY()-364,B2<=TODAY()-334)

This uses the logical AND() function which looks at 2 conditions to check if the date is at the 11 month ago mark. The AND() function looks to see if all conditions are true. Specifically, it checks to see if the date is between 364 and 334 days ago. So it will turn yellow to help recognize a date that is close to reaching the 1 year mark.

1 month to expire training yellow
The final step is to select the date cells then click the create new rule for conditional formatting with a formula.

=AND(B2>=TODAY()-333,B2<=TODAY()-1)

 

Between 11 months and today green

The Rules Manager is used to make edits to the conditional formatting. So if your rules gets messed up you would need to click Conditional Formatting –> Rules Manager.

 

Excel Rules Manager

Note: When you are writing the formula you do not want to use your mouse to click on the first cell of dates because it would make B2 absolute referenced to $B$2. If that happens just delete the $ signs. You only need to have a relative reference and Excel will automatically apply the formula to all cells selected. The “apply to” section should have the absolute reference since that is the range that is not being copied anywhere. The format painter is also a nice choice is you want to copy the conditional formatted cells from one worksheet to another. Even works from one workbook to another workbook. So feel free to download my sample file spreadsheet that has all these rules in it.

If you like this post and be sure to sign up for the mailing list so you will receive an email for each of my new post. And be sure to check out my YouTube video on Conditional Formatting examples.


On a parting note, be sure to stop by my wife’s blog to see what’s for dinner tonight.

Posted in Excel | Tagged | 4 Comments

Use Excel’s Color Scales to Spot an Outlier

Can you spot the cell that is different than the rest in the screenshot below?

Before

excel before

 

Select the cells, click the Home tab then Conditional Formatting–> Color Scales. Your outlier will stick out with a different color than the rest.

After

excel color scales

 

Posted in Excel | Tagged | Leave a comment

Excel Conditional Formatting on Sales Greater Than $500,000 [Video]

Posted in Excel | Tagged | 1 Comment

Create Labels in Word Using a Mail Merge

This post I’ll share how to make labels in Word using a mail merge. First you click on the Mailings tab (Word 2007, 2010, and 2013) and click Start Mail Merge. Then click Labels.

In the Label Options box, choose the vendor and product name. In this example, I have selected Avery US letter 5160. This is popular template with 30 labels per page.

 

start mail merge labels

 

label options

The next step is to connect to a data source like Excel. As long as you have a header row with fields like first, last, address, city, state, and zip you will be good to go.

Click Select Recipients and then Use and Existing List. This will open up a box to browse for your address file.

use exisiting list word

You will then need to add the address block to the top left cell. Click the address block command from the mailings tab. If the fields do not match up you can click match fields.

address block goes in top cell

match fields

You can click Update Labels. This is an important step that makes all the labels show up instead of just the first one.

update labels word

After you click preview results and you like the format you are ready to finish the mail merge.

Preview Results

Click Finish and Merge then either print or edit individual documents. Print will print all pages and edit individual documents will create a new file with all the pages filled with addresses.

 

 

 

 

Posted in Word | 2 Comments

My Top 7 Excel Keyboard Shortcuts

  1. Control + Semicolon to enter today’s date
  2. Control + Shift + arrow key  to select range of cells
  3. Control + Tab to switch between workbooks
  4. F4  to repeat formatting
  5. Control + C  to copy
  6. Control + V  to paste
  7.  Control + mouse wheel  to zoom in and out

Download my Excel Keyboard Shortcuts PDF guide for more Excel shortcuts.

excel 3013 logo

Posted in Excel, Shortcuts | Leave a comment

Don’t Show the Backstage when opening or saving files in Office 2013

If you have Office 2013, you no doubt have experienced a delay with opening and saving documents. That is because the backstage view appears and gives you many options. My assumption is that this allowed a view/ exposure to drive curiosity to the OneDrive account. I’m in favor of saving to the cloud, but I also want to be able to use my Control + O to open documents from the normal open dialog box with files on my computer. I found a handy solution to this this in the Options. Get to options from Excel, Word or PowerPoint by clicking the File tab, then click options. Click the Save category then check “Don’t show the backstage when opening or savings files.” And if I want to save to the cloud using my OneDrive account then I’ll happily click File–>Save As–>OneDrive.

Word backstage save and open

Posted in Excel, PowerPoint, Word | Leave a comment