How to Link PowerPoint To Excel for Dynamic Data Updates

Preparing financial documents or major business stats from Excel data often involves presentation in slides. Transforming all the data in the PowerPoint presentation is not often a great idea. Not only is it inefficient and time-consuming, but we also don’t need to show all the data in slides. The best way to do this is to link Excel to PowerPoint dynamically. Here, dynamically, it will not only link the file. It will also update the link in the presentation with each update in the Excel file.

The most common way to link your Excel data is via the traditional copy and paste approach. This method is ideal for both tables and charts. Without the hassle of manual editing time and again, this helps you to paste once and reuse as many times as you want.

Steps:

➤ Open both your Excel file and the PowerPoint file.
➤ Select the range of the data you want to present in the slide.
➤ Copy the data by pressing  Ctrl  +  C  or the Copy option by right-clicking.

➤ Go to your PowerPoint slide where you want to paste the data.
➤ In the Home tab, select the Paste option at the left-most corner.
➤ It will show the drop-down menu with the Paste Special option below.

➤ Clicking on Paste Special will launch a new window with two checkboxes – Paste and Paste Link.
➤ Select Paste Link.
➤ Choose Microsoft Excel Worksheet Object for tables and press OK.

➤ It updates automatically with any change made in the Excel file.

Instead of inserting your data in image or text format, you can also insert it as an object. It is another fast and efficient method that works best when dealing with small datasets. Enabling you to edit the data from the slide itself helps you maintain data accuracy.

Steps:

➤ Open the PowerPoint slide and choose the slide where you want to link the data.
➤ Go to the Insert -> Object option.
➤ In the new window, select the Create from file option to link to an existing file.
➤ Write the file path or click the Browse option to locate your file.
➤ Click on OK to paste the dynamic link of the entire table.

➤ The updated slide will look like this –

VBA Macro Applications can be a good alternative for automatically updating data when working with multiple files and large databases. Though this requires a manual update option, it can simultaneously adjust all rows and columns with a single action. You can opt for this method blindly if you need multiple links in Excel.

Steps:

➤ Go to the Developer Tab on PowerPoint and select Visual Basic.

➤ This opens the VBA editor window.
➤ Click Insert on this new window and choose Module from the dropdown menu.

➤ In the blank space, paste the following VBA script-

Sub InsertLinkedExcelRange()
    Dim excelApp As Object
    Dim excelWB As Object
    Dim filePath As String
    Dim rangeAddress As String
    Dim sheetName As String
    Dim fullRange As String
    Dim pptSlide As Slide
    Dim tempWorkbookOpened As Boolean
    Dim fd As FileDialog
    ' File picker dialog (PowerPoint-compatible)
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Title = "Select Excel File"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls; *.xlsx; *.xlsm"
        If .Show = -1 Then
            filePath = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With
    ' Prompt for range input
    fullRange = InputBox("Enter the full sheet name and cell range (e.g., Sheet1!A1:F13):", "Enter Excel Range")
    If fullRange = "" Then Exit Sub
    If InStr(fullRange, "!") = 0 Then
        MsgBox "You must enter in format: SheetName!A1:F13", vbExclamation
        Exit Sub
    End If
    sheetName = Split(fullRange, "!")(0)
    rangeAddress = Split(fullRange, "!")(1)
    ' Start Excel
    On Error Resume Next
    Set excelApp = GetObject(, "Excel.Application")
    If excelApp Is Nothing Then
        Set excelApp = CreateObject("Excel.Application")
        tempWorkbookOpened = True
    End If
    On Error GoTo 0
    Set excelWB = excelApp.Workbooks.Open(filePath, False, True)
    excelApp.Visible = True
    ' Copy range
    excelWB.Sheets(sheetName).Range(rangeAddress).Copy
    ' Paste into PowerPoint
    Set pptSlide = ActivePresentation.Slides(ActiveWindow.View.Slide.SlideIndex)
    pptSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoTrue
    MsgBox "Linked Excel range inserted successfully!", vbInformation
    ' Clean up
    excelWB.Close False
    If tempWorkbookOpened Then excelApp.Quit
    Set excelApp = Nothing
    Set excelWB = Nothing
End Sub

➤ Save the file as a Macro-Enabled PowerPoint Presentation (.pptm).
➤ Close the VBA window, and go to the Developer -> Macros.

➤ The Macros window shows the name of the custom function.

➤ Click on Run to proceed.
➤ This will ask you to select the Excel file you want.
➤ After selecting the file, it will ask you to provide the Sheet and the cell range.

➤ With the OK button, it will dynamically paste the range of cells.

➤ To get the update each time, right-click on the link and select Update Link.

Common Mistakes to Avoid When Linking Excel to PowerPoint

Though linking Excel files is quite simple, missteps can make it complex. Often, people don’t get to visualize the actual values and figures due to missing or broken links. These are some common issues you can easily avoid if you follow the guidelines properly.

• Moving or renaming the Excel file: When the original linked file location is changed, a new file path is generated, different from the previous link. Try to avoid this, and if done, update the link properly.
• Using regular Paste: The regular pasting option does not make your data table dynamically linked or updated. It is a static version of the data.
• Merged cells: In terms of transformation and linking, it is not preferred to use merged cells. It makes the data complex and hard to link with the PowerPoint slides.
• Disabled Macros: Saving the files in the Macro-Free format disables the VBA scripting. In that case, your data will not be updated automatically if you rely on VBA for linking.
• Copying an entire worksheet: Do not copy the whole worksheet, especially in the VBA and Paste Link approach. It’s better to copy the specific range of cells.
• Cells containing complex formulas: Once in a while, you might experience complex formulas not being updated correctly in the slides. To avoid these, try to reuse easy-to-use formulas.

Frequently Asked Questions

Can I link multiple sheets of the same Excel file to a single PowerPoint Presentation?

You can link multiple Excel sheets to a PowerPoint Presentation in Excel using the Paste Link option or a customized VBA script. However, you can’t do that using the Insert object option.

What happens if I move the Excel file after linking it to PowerPoint?

After linking it to PowerPoint, if you move the Excel file, it will break the link. That means, no new change will be dynamically updated to the pasted object. To fix this, you need to create a new link.

How do I embed Excel data into PowerPoint without a link?

To embed the data in the PowerPoint file without any linking feature, copy the selected range of cells. Go to the Paste Special option from the Paste menu in PowerPoint, and choose the Embed or Picture option.

Can I link only a specific range from Excel instead of the whole sheet?

You can use the Paste Link option or VBA Macros to link only a specific range of Excel files. You can only select the special range you want to copy for Paste Link. On the other hand, you can modify your VBA code to only copy a specific range.

Why is my linked Excel chart not updating in PowerPoint?

Excel charts may not be updated to PowerPoint due to issues like broken links, placing files in different folders, or update issues. To fix this, link the portion again and check the automatic update options.

What happens when I send someone else a PowerPoint file with linked Excel data?

If you send the PowerPoint file with linked Excel data to someone else, they can see the updated data when they open it. However, if the files are not sent to the recipient and if it is not located under the same folder, they will remain static after that. No new change will be visible in the slide.

Concluding Words

Linking your Excel file directly to your PowerPoint slides can be life-changing. The report can be updated regularly by mitigating manual errors and extra time. With various methods like Paste Link, Inserting by Object, and even automation using VBA Macro, you can make your presentation interactive, dynamic, and scalable depending on your needs. With the right approach and steps, your slides will always represent the latest figures.

If you find any issues or are confused about any of the steps, feel free to share your feedback. Also, don’t forget to download the worksheets and PowerPoint slides for reference.