13 Excel data links
When the source data for your data-driven charts is available in Excel, you can create charts directly from the Excel application. When data in Excel changes, you can either update the charts on command or have think-cell do the update automatically.
- 13.1
- Creating a chart from Excel
- 13.2
- Transposing linked data
- 13.3
- Updating a linked chart
- 13.4
- Data Links dialog
- 13.5
- Maintaining data links
- 13.6
- How to compile the data
- 13.7
- Frequently asked questions
13.1 Creating a chart from Excel
For a step-by-step guide on how to create a chart from your Excel data using think-cell, please consider the example from ‘Introduction to think-cell’. This is how the example chart data looks in Excel:
To create a chart from Excel, select the desired data range in your Excel workbook, including series and category labels:
The layout of your data must match the layout of think-cell’s internal data sheet: Column charts are usually created from data columns, whereas bar charts are created from data rows. Nevertheless it is possible to transpose the data source (see ‘Transposing linked data’). In addition to the data, some cells to the left and on top are reserved for category and series labels.1
Then select the desired chart type from the Charts menu in Excel’s think-cell toolbar:
When you click on this menu item in Excel, the PowerPoint window is activated. If PowerPoint is not yet running, it starts automatically. In PowerPoint, when the mouse pointer is on a slide, the familiar insertion rectangle appears.
Switch to the slide where you want to insert the chart, or insert a new slide, and place the chart as usual: Click once, or click, hold and drag to change the chart’s initial size. For a detailed description on how to place, resize and align a new chart, please refer to ‘Inserting a new chart’.
After insertion, the chart looks and behaves like a regular think-cell chart that has been created in PowerPoint:
Please refer to ‘Adding and removing labels’ and ‘Styling the chart’ to learn how to configure and style the chart.
As well as creating a new chart, you can link a selected data range in your Excel
workbook to an existing chart in a PowerPoint presentation. Simply select the 
button from the Charts menu in Excel’s think-cell toolbar and click on the chart in
PowerPoint that you wish to link to.
Note: Text fields in PowerPoint can contain up to 57 characters. Any additional text from the Excel data source will be truncated.
13.2 Transposing linked data
Having linked a data range to a chart, you can alter how the data is interpreted
using the 
Transpose Link button in the think-cell context menu of the data
range. Transposing a data range swaps the chart’s row and column data
interpretation, letting you, for example, create column charts from rows rather than
columns of data.
Note: Transposing a linked data range does not swap the data in the rows and columns.
13.3 Updating a linked chart
The most important feature of a chart that is linked to an Excel workbook is the option to update the chart according to changes in the underlying data. In our example, we want to add a third data series. Double-click the chart to switch to the linked data range in Excel. Enter the additional data below the linked range:
Then, select the linked range by clicking on its border to show the familiar think-cell user interface. Drag a corner of the selection to include the newly entered data:
Finally, return to PowerPoint. When selecting the linked chart, it is flagged to
indicate that an update of its linked data has been detected:
Click the 
Update button next to the flag to incorporate the data changes from
Excel in the chart. The chart is updated and the flag disappears:
After pressing Update, this button changes to 
Revert. Click it to take back the
changes you just made. The Revert button is available until the PowerPoint file is
closed or the Excel data source changes again. In the later case, it is replaced by the
Update button.
Even if the chart is not updated, or is updated and then reverted again, the link to the Excel data source remains. Thus, you always have the option to update the chart later.
You can also let think-cell automatically update the linked chart for you. This option is available in the Data Links dialog (‘Data Links dialog’).
Note: Linking a chart to data in Excel using think-cell does not depend on the file names of the Excel or PowerPoint file. Each chart as well as each data range in Excel is assigned a unique identification number that is used to maintain the link. The technical requirement for reestablishing a link is that both the Excel file containing the data range and the PowerPoint file containing the chart are simultaneously open on the same computer, irrespective of the file names.
13.4 Data Links dialog
If you have a large number of charts in your presentation, finding and updating all linked charts manually is not practical. For a better overview and a more directed approach to update your charts, go to the More menu in PowerPoint and open the Data Links dialog:
The data links dialog shows all linked charts of the current presentation and their update states:
For each linked chart, the following information is available in the data links dialog:
- Slide number and a thumbnail that shows the position of the chart on the slide.
- Name of the Excel file that contains the linked data.
- Time stamp of the last modification of the linked data.
- Update or Revert button. The linked Excel file must be open for these buttons to be available.
- Auto checkbox, indicating whether or not the chart is being updated automatically as soon as changes in the linked data are detected.
- The chart entry appears in bold when an update is pending.
You can leave the data link dialog open while you continue editing your slides and data. Using the data links dialog, you can efficiently manage all linked charts and their data sources:
Click on the heading of any of the described columns to sort the list of charts by that column.
Single-click on a row to select the chart (in Normal view) or the slide (in Slide Sorter view).
A double-click on a row has the same effect as a double-click on the chart itself: The linked Excel workbook is opened and the linked data is selected. If the data source is not available, the internal data sheet is opened instead.
The Update and Revert buttons are the same as the ones in the upper right corner of the chart: Click the Update button to incorporate data changes from Excel in the PowerPoint chart. Click Revert to undo the update and return to the chart’s previous state. You can also click Revert to undo the latest automatic update.
Check the Auto checkbox to let think-cell automatically update the chart whenever changes in the linked data are detected. The chart is not highlighted or flagged, but you still have the option to manually revert the automatic update. Change detection works only while both files, Excel and PowerPoint, are open at the same time.
Note: If the linked Excel file for a chart is not available, and the internal data sheet is opened and edited, then automatic updating for the linked chart will be disabled. This ensures that any changes made using the internal data sheet are not automatically overwritten when the linked Excel file becomes available.
Update, Revert and changing the Auto setting all work on multi-selections as well. Use Shift-click to select a range of links, and Ctrl-click to toggle the selection of individual charts.
13.5 Maintaining data links
After the creation of a link between Excel data and a chart in PowerPoint, both Excel and PowerPoint files continue to be self-contained independent files:
- You can pass on or edit the files independently.
- You can rename the files. The data links will be reestablished as soon as the Excel and PowerPoint files are open at the same time. To store the information about the reestablished data links the files should be saved.
- You can copy and paste linked charts within the same presentation or even to another presentation. The copy of the chart will then be linked to the same data source as the original.
- You can send the Excel and PowerPoint files by e-mail. The data links will be reestablished as soon as the Excel and PowerPoint files are open at the same time on the computer of the receiver. To store the information about the reestablished data links the files should be saved.
- You can make copies of linked Excel files and use the copies as alternative data sources for the data links. The original and copied Excel files can be independently edited. The link between a PowerPoint presentation and the desired Excel file will be established when opening one of either the original or the copied Excel files together with the PowerPoint file.
13.5.1 Editing linked files
If you edit the PowerPoint file without the linked Excel file(s) being available, linked charts simply behave as regular charts with an internal data sheet. Using think-cell, you can change the chart’s design and data without restriction.
Similarly, you can freely edit an Excel workbook without the linked PowerPoint file(s) being available. With think-cell installed, any linked data ranges that are not currently connected to an open PowerPoint presentation, are highlighted with a light red frame.
As soon as you open both the data source in Excel and the chart in PowerPoint at the same time, the link is reestablished. In Excel, the highlight of the linked data range changes from red to green. In PowerPoint, the chart’s current data is compared to the data source. If changes are detected, the chart is highlighted in the data links dialog, and it is flagged when selected. You can now manually update the chart to the latest data from Excel.
13.5.2 Changing and removing links
To disconnect a chart from its linked data source, simply select the button
Break Excel Link from the chart’s context menu:
From that point on, the chart only uses the think-cell data sheet in PowerPoint.
Similarly, you can disconnect a linked data range in Excel from its charts: Select
the linked range by clicking on its frame. When the frame turns blue, indicating a
think-cell selection, right-click on it and select the 
Delete button. The think-cell
frame is removed while the data itself remains untouched. Any linked charts will no
longer be able to connect to this data range as their data source, and will instead use
their internal data sheets.
Note: You cannot undo the removal of a linked frame, but you can create a new link with an existing chart, when you need it (see below).
Whenever you remove one participant of a link, either the data range in Excel or the chart in PowerPoint, the other side does not get deleted with it. Charts in PowerPoint fall back to using their internal data sheets, and linked data ranges in Excel simply remain in place until they are explicitly removed. This is important because there may be other charts in other presentations linking to the same data source.
If you want to link an existing chart to an Excel data source, first go to the Excel
workbook and select the desired data range. You can also select an existing linked
range, indicated by the colored think-cell selection frame. Then switch to PowerPoint
and select the desired chart. If the chart is already linked to some other data source,
disconnect it as described above. Now the Excel link button in the chart’s context
menu has changed to 
Establish Excel Link. Click this button to establish a
link from the selected chart in PowerPoint to the selected data range in
Excel.
Note: If you want to remove all data links from a presentation, use the data links dialog (‘Data Links dialog’). In the data links dialog, you can simply select all charts and press Delete to remove all links in a single action.
13.6 How to compile the data
When creating charts directly from Excel, you probably already have your data in Excel. In some cases, however, you may not wish to create a chart using all the data you have in your Excel sheet. Alternatively, the data may be in a fixed format that can not be easily altered. Here are a few suggestions that may help you in such situations:
- Add a new sheet to the Excel file and compile the data for your charts on this “interface” sheet. If you create this sheet using Excel references, then the data in the sheet and the linked charts in PowerPoint will be updated whenever the original referred to data changes. Using a dedicated sheet as the source for your charts also facilitates preparing the data specifically for the intended purpose. For example, you can use think-cell round to consistently round the data before presenting them in a chart (see ‘think-cell round’).
- You can simply hide entire rows or columns of data from being reflected in the chart by using Excel’s Hide command. Keep in mind that the hidden data will appear in the chart as soon as you use Excel’s Unhide command and update the chart.
- Similarly, you can use Excel’s Group and Outline feature to hide data that you do not want to be shown in the chart.
13.7 Frequently asked questions
13.7.1 What are the advantages over Microsoft OLE?
You may be familiar with OLE (Object Linking and Embedding) in Microsoft Office. The idea is similar to think-cell’s linking, allowing the creation of charts in PowerPoint that can later be updated with data changes from Excel. However, think-cell’s linking technique has several advantages over OLE:
- The charts are always the same, whether they are linked or not. Even linked charts are native MS Graph charts, not Excel charts pasted into PowerPoint.
- Charts can be linked to Excel at any time, not only when they are created.
- Linked charts remain editable, even when the linked data source is not available.
- Links in think-cell are independent of the file name or path. Simply open both files and any existing links between the files are reestablished.
13.7.2 What happens when copying a linked chart?
You can copy a linked chart just like any other chart by duplicating a slide, duplicating the chart, Ctrl-dragging the chart or copy-pasting the chart to another slide or to another presentation. In fact, even making a copy of the presentation file is a way to copy a contained linked chart.
In any case, the original and the copy are indistinguishable. Both link to the same Excel data range and, if they are open simultaneously, will both receive an update when Excel data changes.
13.7.3 What happens when a linked chart is deleted?
When you delete a linked chart, the link in PowerPoint is removed together with the chart. If there is no other chart in an open presentation linking to the same Excel data range, the frame in Excel turns from green to red, indicating that the link cannot be established.
The linked data range in Excel is not affected in any other way by the deletion of a linked chart. After all, it is possible that a copy of the linked chart has been made, which, when opened, should reestablish its link.
13.7.4 How can I get rid of the red frames in my Excel worksheets?
Different coloured frames in the Excel worksheets indicate linked data ranges. A green frame indicates that a link has been established between the framed data range and a chart in an open PowerPoint presentation. In contrast, a red frame indicates that no linked chart is currently open. A red frame does not necessarily mean that there are no charts at all linking to that data range. It just means that in the currently open presentations there are no such charts.
If you are sure that the framed data range is no longer required as a data source
for any charts, you can remove the frame with the Delete button from the context
menu. Together with the frame, the link information is removed from the Excel
workbook. Any charts that are linked to the concerned Excel data range will not be
able to establish their links in the future. Instead, they will use their internal data
sheets.
The Excel data itself is not affected.
13.7.5 How can I see which charts in a presentation are linked, and where their linked data sources are?
If you are unsure about the data sources of the charts in a presentation, go to the More menu and open the Data Links dialog. This dialog displays a list of all linked charts in the current presentation. Click on an entry to display the corresponding chart.
The file name of the Excel file where the linked data range was last found is also available. The Auto checkbox indicates if the chart has been set to update automatically.
For a detailed description refer to section ‘Data Links dialog’.
13.7.6 What happens to a linked chart when its data source is unavailable?
When the Excel workbook that contains a chart’s linked data range is not open and cannot be found in the same file location where it used to be, the chart cannot establish its link. Instead, it will use its internal data sheet, which always contains a copy of the linked data, and allow you to change the chart.
The internal data sheet of a linked chart is similar to the one of a regular chart. Even if there are multiple charts linked to the same Excel range, their internal data sheets are independent.
If you want to reestablish the link to the Excel data range, simply open the Excel workbook and the presentation with the linked chart at the same time. The chart in PowerPoint and the data in Excel will then automatically connect. When updating the chart with the linked data, any changes you made to the data in the internal data sheet will be overwritten.
13.7.7 How can I use the internal data sheet instead of the linked Excel data source?
To disconnect a chart from its linked data source, use the Break Excel Link
button from the chart’s context menu. From now on, the chart only uses the
think-cell data sheet in PowerPoint.
You can remove the link from a chart, whether the linked Excel data range is currently open or not. For the Excel side of the data link, breaking the link of a chart has the same effect as removing the chart.
For further information read section ‘Changing and removing links’.
13.7.8 How can I enable or disable Auto-updating?
Go to the More menu and open the Data Links dialog. This dialog displays a list of all linked charts in the current presentation. The Auto checkbox determines if a chart is set to update automatically.
When selecting a chart in the presentation, the corresponding entry in the Data Links dialog will be selected, and vice versa.
For a detailed description refer to section ‘Data Links dialog’.
13.7.9 Why is my linked chart not updating although it is set to Auto-update?
The PowerPoint side and the Excel side of a data link can only connect when both files are open at the same time. Therefore, when the data in Excel is changed while the PowerPoint presentation is not open, and then the presentation is opened without the Excel workbook being open at the same time, the linked charts will not detect the changes in their data sources. As soon as the Excel workbook containing the linked data range is open, the link will be established and the changes will be detected. If the chart is set to Auto-update, it will update now. Otherwise, the chart will be highlighted in the Data Links dialog, and it will be flagged when selected. You can then decide whether you want to incorporate the changed data into the chart or not.
For more information read section ‘Updating a linked chart’.
13.7.10 Why is my linked chart indicating a pending update although the data in Excel did not change?
Whenever a link is established between an open PowerPoint presentation and an open Excel workbook, the data from the internal data sheet is compared to the linked data range. If they are not the same, the chart is flagged to indicate that an update is due. If Auto-update is enabled, it is updated immediately.
A “pending update” may also be detected when the data in the Excel workbook is unchanged, but the data in the internal data sheet has been modified. For a linked chart, the linked Excel data range takes priority over the internal data sheet. Therefore, an update will always overwrite any changes in the internal data sheet.
To protect you against data loss, whenever you make changes in the internal data sheet of a linked chart, Auto-update is disabled for the concerned chart.
13.7.11 How can I link an existing chart to a data range in an Excel workbook?
Go to the Excel workbook and select the desired range. Then switch to
PowerPoint, select the desired chart, and click the Establish Excel Link button in
the context menu.
For a detailed description, refer to section ‘Changing and removing links’. To learn how to insert a chart directly from Excel, read section ‘Creating a chart from Excel’.
13.7.12 My linked chart seems to ignore part of the Excel data range. Why?
In addition to the main data, the linked range in Excel contains some cells to the left and on top, which are reserved for category and series labels. If the selected range encloses only the main data, part of the data will be interpreted as labels and will not show up in the chart.
If you are unsure about the required layout, simply insert a new chart of the desired type in PowerPoint and refer to the layout of its data sheet.
For further information read section ‘Creating a chart from Excel’.
13.7.13 Why is Excel slow when I have lots of linked data ranges?
Excel performance can be improved by disabling automatic error checking. To do this select from Excel’s Tools menu Options, click on the Error Checking tab and uncheck the Enable background error checking box.
13.7.14 I have a lot of Excel files to visualize with think-cell. Can I run think-cell in batch mode?
It is possible to program think-cell. think-cell offers a function to create presentations from templates (see ‘Programming think-cell’).
Previous chapter [12 Project timeline (Gantt chart)] Next chapter [14 More tools]
