You probably create links to web sites in Excel all the time. But did you know that you can create a hyperlink to different worksheets in the same workbook? Internal hyperlinks will save you time and – if you create an “Overview” sheet at the front of workbooks you want to share with team members – enable users to quickly link to the exact place they need to go without hunting through multiple worksheets.

Creating an Internal Hyperlink

  • Launch Excel and open the workbook to which you want to add an internal hyperlink.
    For demonstration purposes, we will have a workbook with four worksheets: (Resources, Team Members, Budget and an Overview page like this:

hlink-1

  • Select the cell you want to make your hyperlink.

hlink-2

  • Insert > Hyperlink from the Ribbon
    Click Place In This Document on the left
    Specify the text you want the linked cell to display in the Text To Display text box
    (Go to Worksheet)
    Select a worksheet
    Click OK

hlink-4

  • Your sheet will now look like this:

hlink-5

  • Clicking on the link will take you to the top-left of the Resources worksheet.
    If you wanted to go to a specific cell on that worksheet, such as D12, you can specify that cell in the hyperlink dialog:

hlink-6

  • Now you sheet would look like this:

hlink-7

  • Clicking on the cell link will take you to that exact cell on the referenced worksheet
  • Hyperlinks can also be used to quickly move around a single sheet which has many entries and categories

We all know we can link to a video that is sitting somewhere on the Internet from PowerPoint. But what do we do when: 1) An Internet connection is not available (or fast enough) where we are presenting or 2) The video we want has unexpectedly been moved or deleted or 3) The video is one we created and is not on the Internet.

Not a problem! Just follow these steps:

(Instructions for PC ~ Instructions for Mac ~ Presentation Issues)

FOR PCs

For PCs ONLY – go here for Mac instructions.

1 Create the slide from which you want to play the video. I am calling this slide “An Example of Stop-Action Video“.
  video-1
2 Make sure the “Developer” tab is being displayed.
Click on the Windows “Button“, then click on PowerPoint Options.
  video-3
  video-2
  Check the box in front of “Show Developer Tab“. Click OK.
3 Click on the Developer Tab
  video-4
4 Click on “More Tools“.
Scroll through the list and select the Windows Media Player.
This will give you a mini-player right inside PowerPoint that allows you to start, stop and replay the video.
Click OK.
  video-5
5 Your cursor will now look like a cross-hair on the slide.
While holding the LEFT mouse button down, draw a rectangle of the size you want your player to be. When you release the LEFT mouse button, your slide will look like this:
  video-6
  If you accidentally release the button before you have drawn the rectangle, just start again at step 4.
6 Once you h ave the player inserted, Right-Click on the player. Click on “Properties“.
  video-7
7 At the top of the Properties input dialog, click on the “” button in the “Custom” row.
  video-8
8 Browse to find your video file in the Windows Media Player Properties dialog.
You can also set other variables in this box. For the purposes of this tutorial, we are taking all default settings.
  video-9
  Click OK.
  Click on the small “x” at top-right to close the Properties input dialog.
Your slide will now automatically play the embedded video when it is displayed.
Click here to save a short, 3-slide PowerPoint show that illustrates this task. Save the file to your computer and then double click on it to run the show.

 

FOR Macs

Presentation Issues

There are many issues that you can run into when presenting PowerPoint shows with embedded or linked files. These include:

  • Your presentation can’t find the file
  • Your presentation slows down significantly
  • Your video won’t play

These almost always relate to:

  • What version of Windows or Mac OS you are running
  • Where you linked files physically reside
  • Whether you are using the same machine you created the PowerPoint on to display the presentation

The permutations of factors makes it impossible to list specific solutions for any situation. However, here are some hints that MicroSoft offers:

  1. If possible, always run your presentation from the SAME machine you create it on
    This eliminates almost ALL issues
  2. If you have to use another machine, be sure you TEST the presentation before you show it
    This gives you plenty of time to troubleshoot issues before you go “live”
  3. Always LINK files that are larger than 50MB
  4. EMBED files that are 50MB or smaller
  5. If you have to present on a “stranger” machine (such as equipment in a convention hall) PACKAGE your presentation for CD in order to keep all linked files where your presentation expects to find them

The Problem: You have an Excel spreadsheet of names of clients/students/alumnae, and they are all in UPPER CASE.

The Task: You need to convert them all to “Proper Case” so you can use the list in a mail merge (for example). And, of course, there are about 2,000 names!

The Good News: It’s not as tedious as you might think.

Converting all uppercase text to proper case (as in proper names) is a 3-step process.

Step 1: Making Room for the Conversion

Create a blank column next to the column that contains the upper case text. If both first and last names are in the same cell, you only need 1 extra column. If the first name is in one column and the last name is in another, you will need 2 extra columns.

Here is a 2-column example:

case-2

Step 2: Converting the Text

  • Position your cursor in the blank column, in the cell immediately to the right of the first name you need to convert
  • Click on down-arrow in the Function Icon

    case-3

  • Click on More Functions…
  • This will display the Insert Function dialog box

    case-4

  • If the PROPER function is not listed (as it is in the above example) type the phrase “all caps” in the Search for a function text box.
  • Click on PROPER
  • Click OK
    This will display the Function Arguments dialog

    case-5

  • Click on the selection grid icon

    case-6

  • Then click on any cell containing all UPPERCASE text
  • Press ENTER
    The dialog should now look something like this:

    case-7

  • Click OK
  • Now the two cells should now look like this:

    case-8

  • Next, we will populate this function into all the new empty cells
  • Copy the single converted cell CTRL-C or Ctrl-OpenApple-C (You are actually copying the function)
  • Position your cursor in the top-most cell that remains to be converted and drag your mouse to select all the remaining items in the column that need to be converted

    case-9

  • Paste the function into all selected cells CTRL-V or Ctro-OpenApple-V
  • Now all your text should be converted

    case-10

  • Copy a converted cell and repeat the paste operation in the second column if you have one
  • This example now looks like this:

    case-11

Step 3: Converting the Function into Plain Text

The problem we have now, is that if we delete the upper case column, the Proper Case text in the new column will also disappear. This is because the contents of the column is actually the function that is converting the text – not the text itself – and the function is referring to that upper case column.  For example, if we were to select one of the converted cells, we will see in the text entry box of the spreadsheet a function statement – not the text of the name. (as shown below)

case-12
So we have to do something to convert the function into plain text.

 To do this:

  • Select all converted text
  • Copy it CTRL-C or Ctrl-OpenApple-C
  • Click on Paste > Paste Special >Values
  • Click OK

    case-13

This will replace the function with just the value that it returns. So instead of PROPER(M16), the cell now contains “Doe”. If we were to click on the cell containing converted text, we see the actual text in the text entry box as shown below:

case-14

Now you can delete the columns containing all caps and use the text in any way you wish.

case-15


To annotate or write on slides while you are presenting them:

  1. Open your PowerPoint presentation
  2. Go into your slide show as you normally would
  3. Slide Show > From the beginning (or any other position)
  4. Once in the slide show, Right-Click on any slide you wish to write on
  5. Select “Pointer Options
  6. Select a writing tool and color
  7. Notate, draw or mark up your slide
  8. When you exit the Slide Show, you will be asked if you want to save your notations.

Click here to view a video clip illustrating this feature.


There are always those “little things” that become “big things” when you can’t figure them out that drive you crazy. Like: creating a registered trademark symbol; inserting an entire file into the middle of a document; indenting the content in an Excel cell…and so on.

Read more »


If you have taken advantage of the new SubSite Tools in CommonSpot to include Upcoming Events or News announcements, you can also provide your readers with the option of subscribing to your feed.  Just follow these easy directions:

Read more »


Sometimes There is an “Easy” Way

To clear the contents of a table Select the table
Press DELETE
To remove a table and its contents Select the table
Press BACKSPACE
To add a row at the end of a table Click in the last cell
Press TAB
To insert a tab character in a table cell Click in the cell
Press CTRL+TAB
To number rows in a table Select the left column
On the Home tab, in the Paragraph group:
Click Numbering
To number columns in a table Select the top row
On the Home tab, in the Paragraph group: click Numbering
To insert a blank line before a table Click before any text in the upper-left cell of the table
Press ENTER
To move a table row and its contents up or down Select the row
Press ALT+SHIFT+UP ARROW
or
ALT+SHIFT+DOWN ARROW