Embedded Excel® table
Similar to editing a standard PowerPoint® chart, you can edit the data of the chart with the aid of an embedded Excel® table.
To do so, click on the action point labeled Edit Data in the action bar above the chart. Alternatively, you can also instantly open the Excel® table by performing a double click on the chart in order to edit its contents. The embedded Excel® table will open as you are used to from native PowerPoint® behavior.
This Excel® has been enhanced to on the one hand load faster, but also offer easy access to functions such as the sorting, formatting and transposing of data, as well as inserting and deleting columns into the table. Position and sizing of this Excel® window will be saved and reapplied when you re-open the Excel®. If you wish to open the standard Excel®, you can do so by clicking on the Excel icon to the top of the window (1). When editing diagram data in the Excel® window, the header row of the table is switched off by default for new data charts. This allows you to format the first row as a date axis or to work with formulas in the first row. The header row can also be switched on again (2).
You can now edit, add and remove data, as well as select the data range that is to be displayed by the chart.
In addition, it is possible to reorder the data displayed in the empower® chart data rows by row or column of the embedded Excel® table. The external Excel® window allows you to manipulate data in multiple ways:
- Reverse rows (with formulas) (1)
- Reverse columns (with formulas) (2)
- Transpose table (values only) (3)
- Sort rows (4)
- Sort columns (5)
By default, the initial sorting option is ascending. Clicking the button, a second time, will perform the opposite action.
Where possible formulas contained in the table are preserved. The options for ‘transpose’ and ‘sort’ will convert any formulas contained in the table to values.
During a copy or cut procedure (cell contains a selection frame), an insertion of cells or columns is not possible.
External Excel® data
Apart from using embedded data, empower® charts also allows you to access external Excel® data sources. In order to do so, click the action point Data and then on Excel Link. A new dialog window will open in which you can either select a local Excel® file or choose a file from your SharePoint.
If you want to open a local Excel® file, select Open local file. Granted you already have multiple Excel® sheet opened, they will be displayed in a list. From here you can open the table with a single click. If you do not wish to include currently opened Excel® sheets (or do not have any opened) click on (Browse…) in the drop-down menu. A Windows Explorer window in which you can select the desired file.
To open an Excel file from your SharePoint, select Paste SharePoint URL. Paste the link to the file into the entry field. To copy the link, simply click on Open menu to the right of the Excel® file (this is the button with three dots) and copy the link from the menu that has opened, or by clicking on Copy link. After the insertion, click on the button labelled Open link. A connection to the selected file will be established. This may take a short period of time and may also require the entry of your SharePoint credentials.
empower® charts supports both SharePoint as well as files located on OneDrive or Microsoft Teams. It is recommended that you open the Excel file first, and then link it to the chart.
As soon as you have opened an an Excel® sheet it will be positioned to the right of your PowerPoint® window. Now select the data range you wish to include in the chart; empower® charts automatically recognizes data that is to be selected. Use the cursor to adapt the selection if required. A window opens in the Excel® sheet which displays the selected range; a click on OK will confirm your selection.
You can not only select an entire range, but also connect partial areas with each other. By that you can exclude certain columns from the source file from integration in charts. To do this, use your cursor to select a range, then hold down Ctrl and select another range. You confirm your selection by clicking on OK.
Once you have selected the desired data range, you have the option to have the chart display the data by Series or Column. You have additional options once you have selected the required data range.
To link the chart with the Excel® table, click on OK again. The data of the Excel® sheet should now be displayed by the chart.
Automatic data refresh after opening
If you have linked a chart with an external source of data you have the ability to set the chart to update its data once its presentation is opened (locally or from empower®). Toggle the switch Refresh data automatically on open to either Yes or No accordingly.
Using relative paths
If you have linked a chart with an external data source you can set to use Relative Paths. Instead of using an invariable path this setting will allow use of a relative path of the respective PowerPoint® and Excel® file. If you wish to send a PowerPoint® or Excel® file (the charts in the PPT are linked with the Excel® file) as an email attachment, their recipient is able to save these files to their local hard drive. Even though the connection to the chart refers to a path that is inaccessible to this recipient, a link to the Excel® data can be established via the relative path, provided the files are saved in a similar fashion. If, for example, the original files have been placed in the same folder, it is necessary that these files are also placed in the same folder when saved locally.
If you activate the function to use Relative paths you are required to ensure that the path of the Excel® file does not change.
Post hoc editing of external data sources
If you wish to remove the link to the external data source, click on the action point Break link in the Action Bar above the chart. If you want to edit the Excel® link, select the option Edit link
Once you click on Open link source you may also directly open the lined file in order to change its data. If you use multiple Excel® links in your presentation, you can click on Excel Links in order to manage all links. This button is located on the top right of the empower® charts section.
As soon as you have opened the Excel Link manager, you will see an overview of all Excel® files that are linked to charts in your presentation. On the left is listed, on which slide the linked chart is located. The save location of the Excel® file is also specified, which you can open by simply clicking on the path. If you select multiple items you are able to update all charts at once or delete their connection. On the right you have the possibility to activate or deactivate Refresh Data on Open as well as Use relative path. In addition, you can change the original file of links for multiple connections all at once. To do so, select the respective link and click on Select new Excel® file. A window will open in which you can select the file chart is to be linked to.