Linking Excel and PowerPoint is a powerful solution to automate reports and a major time-saver. In this post, we will lay out the pros and cons of each link solution.
What are the main benefits of linking Excel and PowerPoint?
Nowadays, PowerPoint, Excel and Word are still among the most widely used applications in business environment. No less than 1.2 billion people use Microsoft Office 365 globally.
Excel is by far the reference application for preparing, crunching and analyzing data, whether it is for sales analysis, survey results, budget reports or any kind of financial analysis. In most cases, the results and conclusions are then presented through PowerPoint which is easier for sharing them in a visual way, while providing context and comments around the data.
In many instances, analysts will prepare the data in Excel and then simply copy and paste their charts and tables into PowerPoint in order to present them.
If the data is updated, they will need to copy and paste all the updated charts and tables back into PowerPoint in order to update the presentation as well.
While manually copying and pasting is simple, it can quickly become a dangerous nightmare:
It is heavily time-consuming: if you have 100 tables and charts to update, you will likely spend an hour just copying and pasting them into PowerPoint. Oh, and what if you realize that there was some error in the data? Well, you can start all over again!
It requires you to reposition and reformat the charts each time
The risk of manual error is very high: humans are imperfect, and multiple copy-pasting like this will likely result in some chart being forgotten. Whilst perfectly understandable, this can greatly damage your reputation.
For these reasons, creating links between Excel and PowerPoint is an elegant solution for automating and securing reports. Not only will it save you time and reduce the risk of manual mistakes, it will also give you greater flexibility. Your boss needs a report udpated for a meeting in 5 minutes? No problem!
Oh, and icing on the cake: linking PowerPoint charts and tables to Excel data usually makes your presentation lighter, so you can email it more easily. Read more tips for reducing the size of you PowerPoint and Excel files.
So how can you do that?
Let's see 3 different methods, each with their pros and cons.
Method 1: using the regular "Paste" options to create the Excel-PowerPoint link
This method is very easy and you might even use it without knowing it. Let's see how it works.
Creating the link between Excel and PowerPoint 🔗
From Excel, select the chart
Right-click the chart and click "Copy" (or press CTRL+C)
Go to the PowerPoint slide on which you want to insert the chart
Go to the Home tab and click the arrow button under "Paste", then select one of the following 2 options:
Choose "Use Destination Theme & Link Data" to convert the chart to your presentation's colors and style
Choose "Keep Source Formatting & Link Data" to maintain the original appearance of the chart from Excel
That's it, the chart in PowerPoint is now linked to the data from Excel!
Updating the link
Once the link is created, of course the next step will be to udpate the PowerPoint chart when your data in Excel has changed.
Just after creating the link, your charts in PowerPoint will be updated automatically if you change the data in the Excel workbook.
Later, select the chart in the PowerPoint presentation. Right-click it and hit "Edit data". The Excel file will open to allow the update (if it was not open already).
Advantages ✅
Linked charts remain chart objects, so you can easily customize the format afterwards
Links do not trigger any security warning (unlike Method 2 that we will see just after)
Updates run fast
Limitations ⛔️
If you rename the closed Excel file, the links will be broken
If you duplicate the Excel file, the links will not work with the new version
If you move the closed Excel file, the links will be broken
If you email the files, the links will be broken for the recipient
If you move the source range of data in Excel while the PowerPoint is closed, the chart will still reference the old (hence wrong) range of data
These links cannot be created for tables
These links also do not allow linking values within your textboxes and shapes
👉🏻As you can see, these limitations are pretty severe. If the links get broken 🔌, instead of saving time, you might find yourself unexpectedly and urgently updating your reports manually in panic mode before your meeting. 😱
Method 2: using the "Paste Special" options to create the Excel-PowerPoint link
This is a little less-known, but once you know it, almost as easy as the previous one.
Creating the link between Excel and PowerPoint 🔗
From Excel, select the chart or range
Right-click and hit "Copy" (or press CTRL+C)
Go to the PowerPoint slide on which you want to insert the chart or table
Go to the Home tab, click the arrow button under "Paste", and hit "Paste Special"
The dialog box below will open. Select "Link" at the left and then "OK".
Updating the link
After the link is created, you will be able to update like this:
Just after creating the link, your charts and tables in PowerPoint will be immediately updated when the data changes in Excel.
Later, open the PowerPoint presentation. A security prompt will show up, like the one below. Click "Update links" and all links will be updated at once. The Excel file does not need to be open.
Or, once PowerPoint is open, you can also update each chart or table individually. In the presentation, select the linked object, right-click it and hit "Update link", like in the screenshot below. The Excel file does not need to be open.
Advantages ✅
Links can be created for charts as well as tables
Limitations ⛔️
Objects are pasted as pictures, so you will not be able to make any changes to their format. If you resize them, the picture could also get a bit blurry.
Updates are quite long to run, even for a single linked object.
Links trigger anoying security warnings everytime the PowerPoint presentation is opened
If you rename the closed Excel file, the links will be broken
If you duplicate the Excel file, the links will not work with the new version
If you move the closed Excel file, the links will be broken
If you email the files, the links will be broken for the recipient
If you move the source range of data in Excel while the PowerPoint is closed, the chart will still reference the old (hence wrong) range of data.
These links also do not let you link values within your textboxes and shapes
👉🏻That's a long list!
And, again, there is a significant risk for the links to get broken and put you back in panic mode to update manually at the last minute. 😱
So the main interest for these links is clearly the ability to link tables while the first method cannot.
Method 3: using Power-user to create robust links between Excel and PowerPoint
Due to the limitations of Methods 1 and 2, many users prefer to rely on manual copy-pasting, and waste sometimes considerable amounts of time in the process.
To solve these issues, Power-user designed an intuitive and robust Excel-PowerPoint link allowing you to automate your reports. This link comes as part of the Power-user add-in which also includes dozens of features for PowerPoint, Excel & Word.
Creating a link between Excel and PowerPoint for a chart, table or range 🔗
From Excel, select the chart, table or range
Right-click and hit "Copy" (or press CTRL+C)
Go to PowerPoint
From the Home tab, click "Paste" (or press CTRL+V).
Click "Link" from the contextual button at the top left of the chart to create the link:
Inserting values in the middle of your PowerPoint texts that are linked to an Excel cell 🔗
This is a unique and very powerful feature that only Power-user offers. Not only can you automate your reports by updating charts and tables, but you can also write text comments in PowerPoint that include dynamic values linked to Excel data. This is the ultimate step to make truly automated reports.
For instance, you can have a slide title that says "Online sales stand at +17,2% this month" with the value "+17,2%" being automatically updated from a cell in Excel.
From PowerPoint, select a text and place the cursor where you want to insert a linked value
Right-click and then choose "Insert value from linked Excel cell"
Using the selection dialog that opens, navigate to the Excel cell you want to link with your presentation and validate
Updating and managing your links
After a link is created, you can easily update it by clicking the buttons at the top left.
From here, you can automatically update all the linked objects (charts, tables, texts) to take the new data from Excel.
You can also open the Link Manager, from where you will find additional options:
View the list of all linked objects and easily navigate from one to the other
Update each link independently, or all at once
See the location of the data source
Opens the folder containing the Excel source or go directly to the link source in Excel
See the date and time of the last update, and who made the last update
Break the link
...
Advantages ✅
Linked charts remain chart objects, so you can easily customize the format afterwards
You can link anything: charts, tables, ranges, cells and even values in your text
Links do not trigger any security warning
Updates run fast
The links will resist if you rename the files or have multiple versions (v1, v2, v3 etc.).
The links will resist if you move the files
The links will remain if you email the files
If you move the source range of data in Excel while the PowerPoint is closed, the link will resist
You will have easy access to multiple options for managing links
At any time, just open both the PowerPoint and the Excel file and hit update, it will re-establish the links with the currently opened version.
If the recipient doesn't have Power-user installed, the links will be converted into Method 1 links to ensure compatibility.