Jumbabox

This is part 1 of many tutorials on how to link cell values, tables and charts in a PowerPoint presentation to an Excel worksheet. This will be helpful when creating regularly updated presentations, for example: A quarterly business performance presentation. Once it is setup all you will need to do is update your Excel worksheet and it will flow through to your PowerPoint presentation … Yes, dynamic presentations!

This post will look at linking cell values.

Related Posts :

• Part 2 - How to link a table in Excel to a PowerPoint presentation
• Part 3 - How to link a chart in Excel to a PowerPoint presentation
• Part 4 - How to link a Pivot Chart in Excel to a PowerPoint presentation
• Part 5 - How to link a Pivot Table in Excel to a PowerPoint presentation

Step 1 - Formatting your Excel data

To get started, firstly open up a blank PowerPoint presentation and the Excel worksheet which contains the data you want to link.

Lets say I want the title of presentation to be dynamic, so for example I want my title to be:

2008 Marketing
Quarter 2 Review
10/06/2008
By Prashant Raju

The underlined words are going to be linked and therefore dynamic.

In the Excel worksheet we already have open, create a blank sheet and label it the slide number of the title slide, in this case it is the first slide, so slide1 is an appropriate name.

As the formatting flows from Excel to PowerPoint we will need to remove some of the formatting that Excel does by default. Firstly, we don’t want to see the gridlines in the PowerPoint slide so lets remove those first.

Click on Tools and then Options.

Make sure you’re in the View tab.

Then locate the Gridlines radio box and make sure it isn’t ticked, then click the OK button.

Now, your worksheet should have no Gridlines.

Step 2 - Setting up your Excel data

The next step is to type in the content, lets first set up some “data entry” cells in relation to the underline words I mentioned earlier, some of these cells will need to be updated manually and others will be automatic.

The coloured cells are manually entered while the others are automatically calculated using formulas.

This is what the sheet looks like without the formulas showing.

Now we have the underlined values setup via manual or automatic data entry, lets create all the content for the first slide in our presentation in Excel. For this example, every line for the title of the presentation has a separate cell. Lets start with the first line, 2008 Marketing.

I picked a cell in the middle of the worksheet and inserted the following formula :

= CONCATENATE($B$1,” “,$B$3)

Now the first line looks like this.

All that has to be done now is to format this cell to exactly what we want to see in the PowerPoint slide. In this example I increased the font size and left the font as Arial.

The next step is to make sure that the cells which the text “2008 Marketing” encompasses are merged. To do this click on the Merge and Center button found on the Formatting tool bar.

Step 3 - Let the linking begin!

Now, lets link this cell title to our PowerPoint title slide. While you are still in Excel, select the merged cell which contains the 2008 Marketing text and press CTRL+C (Copy), now switch to your blank PowerPoint presentation (ALT+TAB), click on the Edit then Paste Special option.

Once you have clicked on Paste Special… option make sure the next dialog box looks similar to the one below, you want to Paste Link As Microsoft Office Excel Worksheet Object and click the OK button.

Once you have clicked OK you should now see the cell from the Excel worksheet containing the 2008 Marketing text.

Obviously we have to remove the original text place holders and it should then look a lot more cleaner.

Once you have repeated the steps above for all your text, your Excel worksheet without formulas should look like this:

and with formulas showing…

and your final slide in PowerPoint will look like this, after some rearranging and aligning…

Step 4 - Updating the linked values

This post would be useless without knowing how to update the linked objects in your PowerPoint presentation! Firstly, within PowerPoint click on Edit and then navigate to the Links option.

A Links manager should pop up and list all the individual links that are within your presentation.

Next, highlight all the links (SHIFT+CLICK), all the available options will now be unlocked, here’s a brief description of each option:

• Update Now : Refreshes the highlighted links once clicked
• Open Source : Opens up the Excel worksheet referenced in the particular link
• Change Source : Change the source of the reference link
• Break Link : Breaks all links so that you can have a point in time copy of the values, similar to Paste Special As Values. *Make sure you Save As… your document after breaking the links as you will loose all links if you hit save!
• Update : Automatic - Links are updated Automatically
• Update : Manually - Links are updated only when you force an update (it is good to select this option if your source file is not on your local disk and maybe on a network share drive)
  

In this example I had my options set as the following:

Once, you have highlighted everything click on the Update Now button and your links will be refreshed, or you can simply just save and close the document and re-open it and it will ask you to automatically update your links once you reopen it:

So that’s how you can link cell values from Excel in PowerPoint - it may seem cumbersome and long winded but I tried to go through every step without skipping anything and when you do it once, it’s very easy to catch on and repeat the steps without any hiccups.

Updated : There is another way you update your linked objects thats by right clicking on your linked object and selecting Update Link option.

Points to Remember!

• Make sure you never move your source file out of the directory where you created your links as it will break all your links! (So think and plan well before you start linking!)
• Make sure all your formatting is done in Excel as it flows through to your PowerPoint presentation
• Use the align tool within PowerPoint (under the Drawing toolbar - Draw > Align and Distribute) to align your linked objects accurately (screenshot here)
• You can update your links by either using the Links manager or by saving, closing and reopening your PowerPoint presentation.

Stay tuned for my next post on how to link tables, it will be a lot shorter as the basics have been covered thoroughly in this post!

If you have questions or suggestions please comment.

Enjoy!