Category Archives: Office Skills

How to add a Video to Spice up your Presentations

Including videos into your presentations is a great way to garner the attention of your audience and get your message across clearly and effectively.  The features of PowerPoint make it easy for you to add videos to your presentations,  not only from your computer, but also from online sources such as the Cloud, your Facebook account, YouTube or from any website that provides the embedded code.

Once inserted, you can format your video in much the same way that you would format a regular graphic.  For example, you can resize the video, adjust the brightness and color contrast, add a picture frame and more.  PowerPoint even allows you to trim your videos so only a specific portion of the video will play.

Insert a Video Clip From a File

  1. Click the Home Tab > Slides Group > New Slide and Select the Content with Caption Layout
    insert slide in PowerPoint 2016
  2. Add the Title “Deer Grazing“.
  3. Click the Insert Video button in the content placeholder to open the Insert Video dialog box
    Insert a video in PowerPoint
  4. From the Insert Video dialog box, choose From a File
    Insert a video from a file in PowerPoint 2016
  5. Click the Browse button to navigate to the folder where your video resides and select the video file you want to insert and then click the insert button
  6. The video will be added to the slide and should look something like the example below.  Notice the playback and volume controls are displayed in the control panel below the video.

Video inserted in PowerPoint

Insert an Online Video

it’s possible to insert an embedded video from an online source such as YouTube or Facebook by using the Insert Tab > Media Group > Video and choosing Online Video.

Insert a video from an online website in PowerPoint 2016

In the Insert Video dialog box, choose the source of the video that you want to insert.  If you have a personal Microsoft OneDrive account, you can navigate to your OneDrive account from here.  You can also search on YouTube for videos or paste the embedded code from a website of your choice.  Keep in mind that you will need to be connected to the internet when you are giving your presentation for your online video to work.

Formatting your Video

After you insert your video, you’re ready to start formatting it.  Note:  Most of the formatting available on the Playback toolbar on the Ribbon can only be used on videos inserted from a PC on your local computer.  They will not work on embedded videos.

  1. Click on the video to select it .  Notice the selection handles appear around the video.  The Video Tools contextual toolbar will appear on the Ribbon with all your formatting options and the playback toolbar will appear below the video.
  2. To preview your video, click the Play button on the Video Toolbar or click the Play/Pause button on the Playback toolbar right below your video.

video playback toolbar in PowerPoint

Trim a Video

After previewing your video clip, you may want to remove certain parts of it.  You can trim from the beginning or the end of your video clip by adjusting the Start Time (green marker)  or End Time (red marker).

  1. Select the video frame.  The Video Tools contextual Playback tab will appear
  2. Under the Video Tools Playback tab  > Editing group, click Trim Video.  The Trim Video dialog box will appear
  3. Press Play to determine where you want to trim the video.  In our example below, we are trimming from the end
  4. Click the red marker and when you see the two headed arrow, drag to the desired ending position.  In our example, it is an end time of approximately 00:06.957. Replay the video to make sure you have trimmed your video as desired.
  5. Click OK and your video will be trimmed.

Trim a video in PowerPoint

Resize a Video

  1. Click the Format tab > Size Group
  2. Click in the Height box and type “5” or your desired width and press enter.  The video width will adjust proportionately.

Resize a video in PowerPoint

There are several other options that you can set for your videos such as fade in and fade out timings, restricting the playback to certain segments, looping a video clip and manually or automatically starting the video playback. It’s a good idea to experiment with these options to customize your presentation as needed.

  1. Click the Slide Show button on the Status Bar to display the slide with the video in Slide Show mode.
    playing a slide show in PowerPoint
  2. When you see the mouse pointer, click the mouse button to view your video.  When the video is finished, press the ESC key on your keyboard to exit the slide show.

Slide show mode in PowerPoint

Apply a Style to your Video

After you view your presentation, you may want to do some finishing touches to your video. You can apply styles and effects to your videos just the same as you can to images.  You can also change the video shape and add a border if desired.  Below we’ll add a Video style to our video.

  1. First select the video and then on the Format tab> Video Styles Group click the More button to to display the Video Styles Gallery.
    More button in PowerPoint
  2. Under the Moderate section, click Compound Frame, Black to add a subtle frame. The style will be applied to your video.

style video

Preview your slide show again and practice with the different styles such as borders, brightness and contrast and color themes as desired.

Practice: – Insert a Video in PowerPoint

Open the wildlife presentation provided for you here and download and insert the deer-movie.

  1. Following the instructions in the above tutorial, click on slide 5 and insert a new slide.
  2. Insert a title and the deer movie
  3. Trim your movie from the end to approximately 00:06.957.
  4. Resize the movie to a height of 5″.  The width should adjust accordingly.
  5. Apply a style of your choice to the video
  6. Preview your presentation and verify your movie plays as desired.

How to Format a Business Letter

Business letters are still important, even in our digital age, as they are used to document purchases, legal and insurance information, retirement awards, cover letters and many other business transactions that require formal delivery.  A properly formatted business letter contains several major parts which, when used properly, convey a level of formal professionalism required in business.

Most business letters are usually typed on company letterhead that already contains the company name, the address, website, telephone and fax information already pre-printed on the paper so there is usually no need to begin with the sender’s name and address in the top left of the letter.

Business Letter Formats

The two most commonly used letter formats are the block style and the modified block style.  The most popular style used in business today is the block style, shown below, where the entire letter is left justified and single spaced except for a double space between paragraphs.

The other common letter style is the Modified Block Style where the Body of the letter and sender’s and recipient’s address are still left justified and single spaced, but the date and complimentary closing are centered.

Major Parts of a Business Letter

No matter which style of business letter style you choose, the major parts of a business letter are still the same.  They include the Date, Inside Address, Salutation, Body, Closing and Enclosures.  Sometimes the Typist Initials and optional Attachment and Copy notations are also included.

Major Parts of a Business Letter

Major parts of a business letter

Date Line:  If you are printing on company letterhead, letters usually start with the date as the date lets the receiver know when the letter was sent and is useful when referring back to it at a later date.  The date is typed 2 inches from the top of the paper which can be accomplished by tapping ENTER 5 times from the top of the document when using single line spacing in Word.

Inside Address:  This is the name and address of the person receiving the letter.  It is usually typed four lines below the date.

Salutation (also called greeting):  For formal business letters, the salutation or greeting should end with a colon.  The greeting usually begins with the word Dear followed by the name of person receiving the letter.  For example, Dear Ms. Brown.

Body:  The body of the letter is started two lines after the greeting.  The body is the main part of the letter and is usually at least two paragraphs in length.

Fonts:  In this example, our letter is typed using word’s default font, Calibri, in the New Blank document template.  The most common serif fonts such as Times New Roman or Arial in 10 or 12 point font size are often used in business correspondence.  Be sure not to use overly decorative or specialized fonts in business letters as they’re perceived as unprofessional and are more difficult to read.

Complimentary Closing:  Type the closing two lines after the ending of the body of the letter. The most common closing lines for general business purposes are Sincerely or Yours truly.

Signature Block:  Four lines after the closing, type the name of the signer.  This allows enough space for the signature.

Enclosure Notation: Sometimes typed as enclosure, Enc or Encl, and often accompanied by a number such as Enclosures (3). This indicates that additional information was enclosed with the letter and how many pieces were included.

Attachment Notation:  The attachment notation is optional and sometimes used instead of the enclosure notation.

Copy Notation and Typist Initials:  The copy notation is used if you need to send a copy to another person.  The typist’s initials are also optional and, if used, are keyed after the sender’s name.

Business Letter Example

Modified block style business letter

Modified block style business letter example

Examples of  Writing the Basic Business Letter and Business Letter Samples can be found at the OWL Purdue Online Writing Lab.

Learn how to prepare and format common business documents in Word by taking the Word 2016 course officeskills.org.

How to make the most of themes in PowerPoint

PowerPoint 2016 is loaded with features and themes that you can apply to your visuals to give your presentations clarity and make them interesting. Your goal is to gain the attention of your audience and to make sure your audience grasps what they see and hear.

The presentation theme is a set of design elements that include colors, fonts and effects that give your presentations a unique look.  Since, the advent of themes in PowerPoint, you no longer have to worry about setting a particular font size or keeping your format consistent as themes will do all that work for you.

All of the PowerPoint themes have a set of colors, fonts and effects built in.  By selecting a  specific theme, your presentation will be formatted with the predefined combination of colors, fonts, and effects of that theme.  Even the blank presentation has a theme, known as the Office theme, which comes with a white slide background and a standard set of text and accent colors.  However, there are approximately 30 built-in Office themes for PowerPoint that you can choose from.

Themes in PowerPoint 2016

PowerPoint has many built-in themes to choose from.

It’s also easy to quickly change the look and feel of a theme or template by using variants in PowerPoint.  Each theme has variations where the fonts or colors have been changed that you can apply to your presentations with just the click of the mouse.  You can also customize your presentation by applying different theme colors, fonts or effects as desired.

Another nice feature of themes is that you can apply them to your entire presentation, to only one slide in the presentation or to a whole section of the presentation.  You can also create your own theme or use colors, fonts and effects from other themes if you wish.

Apply a standard theme to the entire presentation.

  1. From the backstage view, click on New
  2. Click the Blank Presentation thumbnail
    PowerPoint Blank Presentation
  3. Click on the Design Tab > Themes Group and click on the More button to launch the Themes Gallery. Hover your mouse over the different themes to preview the changes to your slides.
  4. Click on a theme of your choice. Below we select the Wisp theme. This theme will be applied to your presentation.

Applying a design theme in PowerPoint

Change the Color Scheme of the Presentation

If you like the design of the theme but would like a different color scheme, it’s very simple to change the look and feel of your slides using Variants.

  1. Click the Design tab > Variants group and click the More button.
  2. On the Variants menu, click Colors.  You will see the available color schemes gallery appear
  3. On the Colors menu, click a color scheme of your choice.  In our example, we chose the Blue Green color scheme

Change the color scheme in PowerPoint themes

Change the font Set of the Presentation

It’s also possible to change the font set of your presentation.  There may be times when you may want a more bolder font or perhaps your company requires a specific font type.  Changing the font set or the color scheme will not change the actual theme that is applied to the presentation, just it’s look and feel.

  1. Click the Design tab > Variants Group and click the More button to view the Variants menu.  Note:  The More button will disappear once you launch the Font gallery 
  2. On the Variants menu, click Fonts
  3. On the Fonts menu, click the font set of your choice.  In our example we selected Calibri.

Changing the font set in PowerPoint

Changing Theme Effects

Lastly, there may be times when you want to use theme effects.  These affect the preset shape styles that you can apply to your shapes or SmartArt graphics in PowerPoint. These effects can also be applied from the Format tab whenever you select a shape or SmartArt graphic.

applying theme effects

Practice: – Apply a Theme in PowerPoint

Open the wildlife presentation provided for you here.

  1. Apply the Facet theme which fits the theme of the subject of the presentation.  Make sure you preview all the other themes first
  2. Select a Color variant of your choice that matches the theme
  3. Choose a Font variant of your choice.
  4. Your presentation will look something like the following:

sample powerpoint presention

How to lookup information in a worksheet using VLOOKUP

The VLOOKUP function comes in very handy when you want to look up information in large worksheets that hold long lists of items such as customer information, product information, shipment details, etc.

The VLOOKUP function essentially finds a value in the leftmost column of a range of cells, such as a table, and then returns the found value to the cell where you placed the VLOOKUP formula.  At first glance, the VLOOKUP function can be very intimidating and just looking at it can scare people off from using it.  However, once you understand how it works, it’s actually quite easy to use.

Using VLOOKUP

Keep in mind that before you can use the VLOOKUP function, you need to identify a primary key column that contains a unique value for each row in your worksheet.  An example of a primary key would be a customer ID. The primary key is usually the first column in your table of data.

Next, decide what values you want to lookup.  In the example below, we want to lookup the Cost of Goods for a customer by entering a unique CustomerID.  Now, set up two cells in your spreadsheet as shown below, one to enter your search value (CustomerID) and the other to contain the result found (Cost of Goods).

For demonstration purposes, we are using the following spreadsheet that contains the CustomerID field as our primary key field.

An excel table for use with VLOOKUP

The VLOOKUP Formula

Now let’s look at the VLOOKUP formula itself.  VLOOKUP requires 4 pieces of information:

VLOOKUP( lookup_value, table_array, col_index_number, range_lookup).

  1. lookup value (this is the value you are looking up).  It is the cell number where you will be entering the lookup value
  2. table_array – This is the table or range of cells you want to lookup
  3. col_index_number (this is the column which contains the search result)  You have to count the number of columns from the left beginning with the first column in your table or range of cells and that will be the column index number
  4. range_lookup. This can be TRUE or FALSE.  If you want an exact match returned, the argument should be set to FALSE.   If you want an approximate value, the argument should be set to TRUE.

VLOOKUP Example

In our example of VLOOKUP, we want to look up the Cost of Goods in our worksheet by entering a unique CUSTOMERID.

If the =VLOOKUP(B3,B6:E14,4,FALSE) formula is used, when you enter CUS109 in cell B3 and press ENTER, the VLOOKUP function will search the first column of the table until it finds an exact match, and then it returns the value $155.36.

VLOOKUP values

VLOOKUP formula Explained

The VLOOKUP formula is explained below. Keep in mind that you have to enter the VLOOKUP formula in the cell where you want the result displayed.  In our example, it is cell C3 as shown below.

VLOOKUP formula explained

  1. B3 is the lookup_value which is the cell number where you will be entering your unique lookup value.  In our example, we will be entering a unique CUSTOMERID in cell B3
  2. B6 to E14 (highlighted in yellow in the table above) is the table_array, or the cell range where the lookup value is located.
  3. 4 is col_index_num, or the column number in table_array that contains the return value. In our example, the fourth column in the table array is where the Cost of Goods value we are looking for resides so the formula output will be a value returned from the Cost of Goods column.
  4. FALSE is the range_lookup we want to use as we want an exact match returned
  5. Output of the VLOOKUP formula is 155.36, the Cost of Goods for CUS109, the CUSTOMERID we entered in cell B3.

Practice: – Look up information in a worksheet

Open the CustLookupTable spreadsheet provided for you here or create your own with the data demonstrated above.  Note:  This spreadsheet was created with Excel 2016.

  1. Sort the values in the CUSTOMERID column in ascending order
  2. In the cell C3, the cell where you want the result to appear, enter the VLOOKUP formula explained above.  This formula contains the  unique arguments you need to display the result
  3. Make sure you enter FALSE to get an exact match returned
  4. Test your formula by entering a different CUSTOMERIDs as lookup values in the cell B3
  5. Edit the formula so that it finds the CustomerName value for different CUSTOMERIDs

Other useful resources to help you learn

Quick Reference Card – This is a handy VLOOKUP two-page reference card that explains what the arguments mean and how to use them from Microsoft.  The VLOOKUP reference card opens as a PDF file in Adobe Reader that you can download to your computer.

Troubleshooting Tips from Microsoft – This is a three-page reference card that offers basic troubleshooting tips for VLOOKUP.

 

New Free Outlook 2016 and Excel 2016 Tutorials from Microsoft

Free Microsoft Office tutorialsIf you’re looking for some free Microsoft Office Tutorials for Office 2016, the free Microsoft online documentation center for all Microsoft Office Applications is a great resource especially if you don’t use a product yet but would like to find out more about it.

These courses are freely available and can be found alongside the hundreds of courses and videos in the Office Training Center that covers all major Office applications including Word, Excel, PowerPoint, Access, Outlook, OneNote and even OneDrive.  Training is available for all major releases on Windows, Mac, iphone and ipad.  It’s also possible to get training for past versions of Office if so desired.

Microsoft  also recently partnered with LinkedIn to provide some amazing high quality LinkedIn Learning training courses that focus on Outlook 2016 and Excel 2016.

For example, many times we’re asked the question of how to to set up an email account in Outlook and this is answered in the “Getting Started with Outlook” video series that consists of over 41 videos that covers multiple Outlook tasks including how to add and remove email accounts;  how to import gmail into Outlook; sending, receiving and organizing messages; how to add a signature; manage contacts and calendars; manage meetings; and collaborate and share with others

In the Getting Started with Excel 2016 series which consists of over 37 separate videos, you’ll learn how to enter and organize data, create formulas and functions, build charts and PivotTables, and use other time-saving features.

You can find the direct links to the Getting Started Videos below:

Getting Started Series with Outlook 2016 Training

Getting Started Series with Excel 2016 Training