Whether your model is used for corporate planning, M&A decision making, bid pricing, refinancing, cash flow, project finance, budgeting, structured finance or any other form of data/scenario analysis; you will, hopefully, have produced some data tables or charts with the aim of effectively illustrating the key results or outputs of the model to interested parties.
Whilst theses tables and charts display everything that you wish to show results of the analyses carried out by your model, they aren’t very useful to you just sitting in Excel. In our experience clients commonly present outputs from a model using PowerPoint and Word, so we thought we’d share some tips about the different options available when transferring your “results” from an Excel workbook into a PowerPoint or Word document.
Key terms –
Excel Object – The data or chart in Excel that you want to transfer
Source file – The Excel spread sheet containing the original data
Destination file – The PowerPoint or Word document into which you wish to place the Excel object
Embedded file – A copy of the source file that is embedded into the destination file
Step 1: Select the Excel object that you wish to put into your destination file, and copy it (Ctrl+C). Ensure that you have selected the whole object and not just part of it, by clicking on the outline when you select it
.
Step 2: In your destination file, whilst on a slide/page, in the home tab click on the arrow under the paste button and MS Office will give you the following paste options to choose from;
- Use Destination Theme & Embed Workbook
- Keep Source Formatting & Embed Workbook
- Use Destination Theme & Link Data (Only Charts)
- Keep Source Formatting & Link Data (Only Charts)
- Picture (Only Charts)
- Text (Only data tables)
Selecting paste special opens up another dialog box which displays the full list of paste options available for the object currently in the clipboard, split between Paste and Paste link. Selecting Paste displays the options already covered above with different picture formats, whilst selecting Paste link allows the user to choose between a Microsoft Excel Worksheet Object and Attach Hyperlink.
If we leave the formatting options to one side, as these are fairly self-explanatory, this essentially boils down to 3 different options; embed workbook, link data and picture/text.
Embed Workbook
Embedding an Excel Object essentially creates a static copy of the source file and places it into the destination file, meaning that the Excel Object becomes part of the destination file. Any changes made to the data in the source file will not affect the Excel object in the destination file. The formatting applied to the Excel object can still be adjusted by the destination file user. If you want to make changes to the Excel Object, you can still access the embedded file. Right clicking on the embedded Excel Object will bring up the Edit Menu and MS Office will open the embedded file.
Pros:
- Keeps the Excel functionality of your Excel Object
- No need to maintain any other documents as everything is in one file.
Cons:
- Increases the size of the destination file
- The embedded Excel workbook can only be accessed through the destination file (i.e. doesn’t allow the two to be worked on independently).
- The Excel object is not linked to the source file. If any changes are made to the source file, the Excel object in the destination file will not be updated.
- Conversely, any changes made to the embedded file will not be reflected in the source file.
Linked Data
Linking an Excel Object essentially points the destination file to the source file containing the data. This means that when data on the source file is updated, the linked Excel object can easily be updated to reflect this change.
There are various options available for controlling how your destination file updates when using linked Excel Objects. These can be found by clicking on File >> Info >> Related Documents >> Edit Links to Files. Here you will find options to set updates to Manual/Automatic, change the Source File and Break Links to the source file.
There is no one button to update all linked objects in the destination file. One way to get around this is to simply close and re-open the destination file. On restart MS Office will ask the user whether they want to update links and by clicking ‘Update links’, MS Office will try ( fails if the source file has been moved or renamed) and update all the linked objects in the destination file.
Break links is a useful option to use if the destination file will be distributed to various individuals who may not have access to the source file and on opening doesn’t prompt the user about update links. This effectively fixes the data in the Excel object as at the last update, but it retains the option for the user to relink the Excel Object to the source file.
Pros:
- Retains the Excel functionality of your Excel Object
- Keeps your destination file at a sensible size
- Allows the source file to be updated independently to the destination file.
Cons:
- Can become tricky to manage numerous links.
- The user could forget to update the links in the destination file after updating the source file.
Picture/Text
Pasting an Excel Chart as a picture essentially takes a screen print of the chart and puts it into your destination file as a picture. Pasting an Excel data table essentially removes all formatting and just pastes data in text format which is free to be edited.
Pros:
- The destination file user can’t make any changes (accidentally or otherwise) to either the data or formatting of the Excel Object
Cons:
- Excel Object becomes a static picture and cannot be edited or updated with new data
- The picture will need to be deleted and re-inserted every time the changes are made to the source file.
Please share your own experiences below in the comments as they are always welcome. If you would like any further information or advice on how to best manage this type of data transfer or linking, then please contact the team.
