If you regularly create PowerPoint presentations that include information from Excel, you will know how frustrating it can be. Having to re-copy and re-paste the information every time a change is made in the original Excel documents. Have you come across the feature which enables you to link your Excel workbook directly into your PowerPoint presentation? When you use this feature, the presentation changes automatically each time your workbook changes. This is what we will be covering in this post.
Linking an Excel document to a PowerPoint presentation
The process of linking an Excel document to a PowerPoint presentation is pretty straightforward. Simply copy a range from Excel (Home -> Copy in the Ribbon, or Ctrl+C as a shortcut). Then, in PowerPoint click Home -> Paste -> Paste Special . . .
From the Paste Special window select Paste link, click Microsoft Excel Worksheet Object, then click OK.
The pasted image will now be linked to Excel. If the Excel file and PowerPoint presentation are both open any changes will update automatically.
Formula Magic with Dynamic Arrays
Have you ever faced these spreadsheet scenarios?
- How can I use VLOOKUP to return all the matching items, not just the first?
- How can I sort my information using a formula, so I don’t have to keep clicking the sort button?
- How can I quickly create unique lists of items to use with my SUMIFS calculation?
- How can I stop copying down formulas every time my source data changes.
- How can I build a PivotTable-like report, but using formulas so I don’t have to click refresh ever again.
Well, I’m here to give you some good news. with dynamic arrays, all these can be achieved easily 🙂
Update and edit links
Once the links are created in the presentation, you may need to update them at a future point. If the Excel file is not open it is possible to update links using the following methods.
Updating when opening the presentation
When opening a presentation, PowerPoint will ask if you wish to update the links. Just click the Update Links button and the presentation will be updated with the latest information.
Updating on demand
If you just want to update a single linked image you can right-click the image, then select Update Link from the menu.
This menu also gives you the option to Edit the link to the original document.
Update with the “edit links” option
On the ribbon click File -> Info -> Edit Links to Files (it’s in the bottom right corner).
The Links window will open, which provides you with various options to update, edit and break links.
My top tips
If you link Excel files to PowerPoint presentations often, you will soon find it doesn’t always behave how you might expect. Here are my top tips to get the best results:
- Use named ranges. Adding rows and columns in a worksheet will not change the link reference in PowerPoint. For example, if PowerPoint is linked to Cell A1, then you inserted a new column at the start, Cell A1 would become Cell B1. But, PowerPoint still references Cell A1. This would now be the wrong cell. A named range helps to ensure the correct range is displayed in the presentation.
- PowerPoint updates links one at a time. If you have 100 links to the same Excel workbook it will open the same Excel workbook 100 times, which can be very time consuming. If you open the Excel document first the updating process is much faster.
- PowerPoint will not know if you change the name or file path of a linked file (see point 4 below for the exception). You need to ensure the most up-to-date version of the Excel file always has the same file name and file path, so that PowerPoint can find it.
- The fastest method of changing the source of an Excel file is to have both the Excel and linked PowerPoint files open. Then save the Excel file with a new path or file name. In this circumstance, PowerPoint will update the reference to the file.
Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.
By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.
If you’ve found this post useful, or if you have a better approach, then please leave a comment below.
Do you need help adapting this to your needs?
I’m guessing the examples in this post didn’t exactly meet your situation. We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.
But, if you’re still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it’s clear and concise. List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: