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 (Figure 8). A new dialog window will open in which you can either select a local Excel® file or choose a file from your SharePoint.
Excel® files, that are stored in SharePoint or OneDrive but have been as well synchronized locally can be linked locally. This mode is called the hybrid mode. It increases the performance of the links and enables relative paths. Furthermore, online available, linked files can also be opened from PowerPoint®. In addition, the Open Link Sources feature is now available for all Excel® files.
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.
TTo 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.
MFA (Multifactor Authentication) is also supported in empower® charts. If you create an Excel-link with data from an MFA protected SharePoint location, a login window for entering your login data opens after selecting and opening the SharePoint URL.
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. The hybrid mode makes it possible not only to link files stored online. This makes it easier to work with locally stored files, as they do not have to be uploaded to process them as a chart.
As soon as you have opened 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.
Alternatively, Excel-Links can also be created directly from Excel®. To do so, there are ribbon buttons in Excel® on the Insert tab. It can be both a new and an existing PPT target object linked. Native PPT charts can also serve as a target and are directly targeted when linked converted.
To create a link to a PPT object, simply click Link to existing object and select the desired object in PowerPoint®.
empower® automatically recognizes whether a chart or only a table or text can be linked based on the data selection in the Excel® file.
If you have linked a data chart to an Excel® table object via an Excel-Link, the linked data range and thus also the chart automatically grow and shrink when the linked Excel® table becomes larger or smaller. If rows/columns are hidden in linked Excel® files, they are transferred hidden to PPT. Thus, this data is still available when breaking a link.
Additional Excel® Link Options
With the help of empower® charts, in addition to data charts, tables and text boxes can be linked to Excel® files. This allows you to link entire reports to Excel® files. In principle, the same procedure is followed as with data charts.
To link a table to an Excel® file, you can create similar to data charts a link between your PowerPoint® table and an Excel® file using Excel-Link and then Create Excel-Link.
Here, as with charts, a new window will open where you can open an Excel® file (local or on a network drive) or from an Excel® file from your SharePoint/OneDrive.
Here you can also connect partial areas and exclude certain columns.
If cells in the Excel® file do not have a background color (transparent or white), it is inherited from the table design from PowerPoint®. In this case, the text color from Excel® is not transferred to the table.
For tables, the data range should always be adjusted to the set of Excel® cells, but there is no automatic adjustment of column widths.
When linking tables, formatting (text color, cell fill color) can optionally be adopted. To do this, you can simply right-click or click on the Create Excel-Link (table) option via Excel-Link. Once the file is linked, you can copy the colors from Excel®.
In addition to tables, you also have the option of linking any text boxes as well as individual words or text passages with Excel® files.
To do this, you can use the same procedure as for tables, such as linking a title placeholder to an Excel® cell.
To link individual words or longer text passages, you can select the desired area and click on Create Excel-Link (text) or via Excel-Link.
Linked shapes, tables and texts have hover icons on the right side to update the object, edit the link, open the source, and delete the link.
In the Excel‑Link Manager, you can define the desired decimal separator and the thousands separator when linking tables and texts. To do this, you can simply click on the Excel‑Link Manager and use the Configure Separators to change the separators by clicking on Manual so that they are displayed differently from the Excel® table.
If these are set to Automatic, the settings are taken from Excel®.
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.
Excel Link Manager
If you use multiple Excel® links in your presentation, you can click on Excel Link and the Excel Link Manager 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 elements in your presentation.
On the left is listed, on which slide the linked element 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 elements at once or delete their connection.
You can also exchange the original file for single or multiple links at the same time. To do this, simply select the corresponding links and click Edit Link. This gives you the option to directly switch links pointing to a particular file to another file if it has the same structure. Simply select the file you want.
To maintain consistency, the new Excel Link Manager offers the feature Rescan Presentation. This gives you the opportunity to check the currently opened presentation for existing links and displays for instance all linked objects including sketch of their position on the respective slide.
Depending on the type of linked source, different icons are displayed in the Excel-Link Manager.
Article is closed for comments.