[Answered] Auto update of Excel Charts

[mfaisal.ce]

Hello,
I have excel charts in excel which are copied to an powerpoint file.

How i can update the excel charts in power point file when i open it without manual update of each chart. Is there any possibility?

Regards,
Faisal

 

[shamimarita]

Hello,
I have excel charts in excel which are copied to an powerpoint file.

How i can update the excel charts in power point file when i open it without manual update of each chart. Is there any possibility?

Regards,
Faisal

Hi Faisal,

You can link Excel charts to a PowerPoint presentation so that they update automatically when you open the PowerPoint file.

Follow these steps to do so:

1. Copy the Chart from Excel
2. Open your PowerPoint presentation.
3. Go to the slide where you want to insert the chart.
4. Right-click and choose Paste Special.
5. In the Paste Special dialog box, select Paste Link, choose Microsoft Excel Chart Object, and click OK.

The chart in PowerPoint is now linked to the Excel file. When you update the data in Excel, the chart will also be updated in PowerPoint.

If the Excel file is updated, PowerPoint will prompt you a dialog box then select Update Links to update the chart automatically.



Make sure that the Excel file remains in the same location; otherwise, PowerPoint won’t be able to locate and update the charts.

This method ensures your charts in PowerPoint are always in sync with your Excel data without the need for manual updates each time.

 

[mfaisal.ce]

Thanks for your input.

 

[shamimarita]

You are most welcome. Keep exploring Excel with ExcelDemy. Be an active member to help each other.

 

[mfaisal.ce]

Dear,

There is any issue in the link file that if you move the link file away then Excel chart in PPT file will not be updated. Is there any way by macro or vb that i just run the program and it updated the associated charts in PPT file instead of manually updating each time.

regards,
Faisal

 

[shamimarita]

Hello Faisal,

In this case, you can use the VBA code to auto-update your PPT file if the linked Excel file is moved.

It will loop through each slide in your PPT and update all linked charts automatically. You won't need to update them manually, even if the linked Excel file has been moved.

Sub UpdatePPTCharts()
    Dim pptApp As Object
    Dim pptPres As Object
    Dim pptSlide As Object
    Dim pptShape As Object
    
    ' Create PowerPoint Application object
    Set pptApp = CreateObject("PowerPoint.Application")
    
    ' Open the PowerPoint file
    Set pptPres = pptApp.Presentations.Open("C:\Path\To\Your\Presentation.pptx")
    
    ' Loop through each slide and each shape in the slide
    For Each pptSlide In pptPres.Slides
        For Each pptShape In pptSlide.Shapes
            ' Check if the shape is a linked chart
            If pptShape.Type = msoLinkedOLEObject Then
                ' Update the chart
                pptShape.LinkFormat.Update
            End If
        Next pptShape
    Next pptSlide
    
    ' Save and close the presentation
    pptPres.Save
    pptPres.Close
    
    ' Quit PowerPoint application
    pptApp.Quit
    
    ' Clean up
    Set pptShape = Nothing
    Set pptSlide = Nothing
    Set pptPres = Nothing
    Set pptApp = Nothing
    
    MsgBox "All charts updated successfully!"
End Sub

Remember to replace "C:\Path\To\Your\Presentation.pptx" with the actual path to your PowerPoint file.

 

[ttb_2007]

Hi, how come my chart does not get updated automatically when i already format my data as table?

Does it require an particular excel version to do that?

 

[shamimarita]

Hello,

When you've formatted your data as a table in Excel, charts linked to that table should automatically update as you add or modify data. If your chart isn't updating automatically, here are several potential reasons and solutions to consider:

Ensure the Chart is Properly Linked to the Table
Make sure the chart's data range directly references the table. To check this:

• Click on the chart >> go to the Design tab.
• Ensure that the Chart data range refers to the table (e.g., =Table1), not a static range like A1:B10.

Recreate the Chart from the Table
Sometimes, recreating the chart ensures it's properly linked:

• Select any cell within your table.
• Go to the Insert tab >> choose the desired chart type.
  This method typically ensures the chart is dynamically linked to the table.

Check Excel's Calculation Settings
Automatic Calculation: Ensure that Excel is set to recalculate formulas and update charts automatically.

• Go to the Formulas tab >> click on Calculation Options >> select Automatic.

Excel Version Compatibility

• Auto-updating charts with tables are supported in Excel 2007 and later. However, newer versions like Excel 2016, 2019, or Microsoft 365 offer enhanced features and better stability for dynamic charts.
• If you're using an older version, consider updating to access the latest features and improvements.

Table Structure Integrity

• Ensure that your table has clear headers without any merged cells.
• Avoid having blank rows or columns within your table, as they can disrupt the dynamic range.

External Links or References
If your chart references data from another workbook or sheet, ensure that.

• The source workbook is accessible and not been moved or renamed.
• Links are properly maintained. You can check this via Data >> Edit Links.

 

[ttb_2007]

Thank you for your help but tried everything, still not work, don't know why

Hello,

When you've formatted your data as a table in Excel, charts linked to that table should automatically update as you add or modify data. If your chart isn't updating automatically, here are several potential reasons and solutions to consider:

Ensure the Chart is Properly Linked to the Table
Make sure the chart's data range directly references the table. To check this:

• Click on the chart >> go to the Design tab.
• Ensure that the Chart data range refers to the table (e.g., =Table1), not a static range like A1:B10.

Recreate the Chart from the Table
Sometimes, recreating the chart ensures it's properly linked:

• Select any cell within your table.
• Go to the Insert tab >> choose the desired chart type.
  This method typically ensures the chart is dynamically linked to the table.

Check Excel's Calculation Settings
Automatic Calculation: Ensure that Excel is set to recalculate formulas and update charts automatically.

• Go to the Formulas tab >> click on Calculation Options >> select Automatic.

Excel Version Compatibility

• Auto-updating charts with tables are supported in Excel 2007 and later. However, newer versions like Excel 2016, 2019, or Microsoft 365 offer enhanced features and better stability for dynamic charts.
• If you're using an older version, consider updating to access the latest features and improvements.

Table Structure Integrity

• Ensure that your table has clear headers without any merged cells.
• Avoid having blank rows or columns within your table, as they can disrupt the dynamic range.

External Links or References
If your chart references data from another workbook or sheet, ensure that.

• The source workbook is accessible and not been moved or renamed.
• Links are properly maintained. You can check this via Data >> Edit Links.

 

[shamimarita]

You're welcome! Since the chart isn't updating automatically, could you please provide more information to help troubleshoot further? Here are a few questions that might help identify the issue:

• Which version of Excel are you using?
• Are there any external data sources linked to your table that might be causing issues?
• Is the chart using any complex formulas, or is it based on straightforward data?
• Can you share if there are any specific error messages or strange behavior happening when trying to update the chart?

Feel free to provide any screenshots if it helps!