Extracting data from a linked Excel graph; Issue #367 July 5, 2016

When I work on the slide makeovers for my customized workshops, I often run into graphs that can be formatted in PowerPoint, but the data can’t be accessed. This is common because when you use the default paste (Ctrl+V) to paste an Excel graph into PowerPoint, you get a graph that has the data linked back to the source Excel spreadsheet.

The advantage is that you can quickly access all the data and calculations by opening the Excel spreadsheet from PowerPoint. The problem is that if you don’t have access to the spreadsheet, because you are not connected to the Internet or you don’t have permission to access the location, you can’t access the data. You will see a message like this when you try to edit the data for the graph in PowerPoint.

Data not available error

This recently came up on a mailing list for Excel MVPs that I am subscribed to. The question was how to extract the data from the graph when you can’t access the spreadsheet. I wanted to share the answer I gave in hopes that it may help you if you find yourself in this situation.

One of the options in formatting a PowerPoint graph is to add a table of data below the graph. In the Data Table option, select Show Data Table with Legend Keys so it is easier to identify the data later. To display all of the data you may have to set the font to be very small and/or use the Arial Narrow font so that all the decimal places are shown (you can change the font of the data table by selecting the data table and using the font face and point size settings). Here is a graph with the data table added:

Graph with data table

Next, save the slide as a PDF file. You can use the PowerPoint Save As function and select the PDF file format. Save just this one slide or the entire file. Open the PDF file in Acrobat (the Reader will work, you don’t need the full version) and go to the slide with the graph. Acrobat knows this is text, so you can select it. Highlight the text in the table and press Ctrl+C to copy it.

Select table in PDF

Paste the data into Excel. It usually pastes the data as a single column of values as shown below, so just move the values into the columns and rows as required.

Paste into Excel

Now you can use the data as needed to create a new graph or perform different calculations. Remember this method next time you receive a PowerPoint file that has a graph linked to a source Excel spreadsheet.