LinkGetting Started

OfficeReports v6 – April 2016

LINK A WORKBOOK TO A PRESENTATION 3

LINKING AN EXCEL RANGE TO A TABLE IN POWERPOINT 4

LINKING AN EXCEL RANGE TO A CHART IN POWERPOINT 5

LINKING AN EXCEL RANGE TO A SHAPE IN POWERPOINT 6

CONDITIONAL FORMATTING IN POWERPOINT (E.G. HEAT MAPS) 7

LOGOS, ICONS AND STICKY COLORS 8

SETTINGS 8LOGOS 8ICONS 8STICKY COLORS 9

REUSING THE PRESENTATION (AUTOMATION) 10

CHANGE DATA 10REPETITIVE REPORTS 10FLEXIBILITY: LINKING VARIABLE SIZED RANGES 11

USING MACROS 11

Page | 2

Link a workbook to a presentationIn order to link a workbook to a presentation, do the following:

1. Make sure the workbook you want to link is closed!2. In PowerPoint, click ‘Link Workbook’ in the OfficeReports ribbon, and choose the workbook.

If the workbook does not have the same name as the presentation, a window will popup telling you that OfficeReports will make a copy of the workbook and give it the same name as the presentation. The workbook will open, and is now linked to this presentation. After linking, the ribbon changes:

Clicking ‘Show Workbook’ will get the workbook in focus. Clicking ‘Remove Link’ will removed the link, and the ribbon will change back showing a ‘Link Workbook’ button again. We will explain the ‘Change Data’ button and all the other buttons in the rest of this document.

Page | 3

Note: OfficeReports will open the workbook when necessary. In case you already have opened the workbook yourself, OfficeReports will ask you to close the workbook before continuing. OfficeReports can only access the workbook, when OfficeReports has opened the file.

Page | 4

Linking an Excel Range to a Table in PowerPointTo populate an existing table in PowerPoint, do the following:

1. Click ‘Show Workbook’ and select the range you want to show in the table.2. In the presentation, click in the table cell where you want the range to start appearing.3. In the OfficeReports ribbon, click ‘Table->Link Excel Range to selected Table’

The contents from the Excel range will appear in the table, and the ‘Edit Link’ window pops up:

OfficeReports does not just link to the data. It is also possible to use some of the settings from Excel. We will explain the other options in chapter “”.

OfficeReports names the range you link to make sure the link still works, even if you add rows and columns in the sheets in the workbook. (see Excel Named Ranges)

Video example: link to YouTube

Page | 5

Linking an Excel Range to a Chart in PowerPointTo populate an existing table in PowerPoint, do the following:

1. Click ‘Show Workbook’ and select the range you want to show in the chart. 2. In the presentation, select the chart.3. In the OfficeReports ribbon, click ‘Chart->Link Excel Range to selected Chart’

The contents from the Excel range will appear in the chart and the ‘Edit Link’ window pops up again (see “Linking an Excel Range to a Table in PowerPoint”).

Page | 6

Note: The same window will appear when selecting this table in the presentation and clicking ‘Edit Selected Infographic’ in the OfficeReports ribbon. Now you can change the link settings.

Note: If you change the contents in the workbook, and click ‘Apply’ in this window, or click the ‘Refresh’ button in the OfficeReports ribbon, the table will be updated with the contents from the workbook.

Note: It is possible to populate multiple Excel ranges in the same PowerPoint table!

Page | 7

Note: The same window will appear when selecting this chart in the presentation and clicking ‘Edit Selected Infographic’ in the OfficeReports ribbon. Now you can change the link settings.

Note: If you change the contents in the workbook, and click ‘Apply’ in this window, or click the ‘Refresh’ button in the OfficeReports ribbon, the chart will be updated with the contents from the workbook.

Note: Using the “Fixed” option, OfficeReports will not change the data range used by the chart. This means the data you populate has to look exactly like the data behind the chart to start with. This enables easy populating of Combo charts and other advanced charts.

Note: If you do not use the “Fixed” option, you can populate any number of rows or columns in the chart. OfficeReports will tell the chart which data to use. If possible, also populate the category texts.

Linking an Excel Range to a Shape in PowerPointTo populate an existing shape in PowerPoint, do the following:

1. Click ‘Show Workbook’ and select the range you want to show in the shape.2. In the presentation, select the shape.3. In the OfficeReports ribbon, click ‘Chart->Link Excel Range to selected Chart’

The contents from the Excel range will appear in the shape and the ‘Edit Link’ window pops up again (see “Linking an Excel Range to a Table in PowerPoint”).

Page | 8

Page | 9

Note: If you select multiple cells in Excel, OfficeReports will add a space between the contents of cells on the same row, and a newline for each new row.

Note: Also in shapes it is possible to show logos and Icons!

Conditional formatting in PowerPoint (e.g. Heat maps)By checking ‘Use background color’ in the ‘Edit Link’ window, even conditional formatting definitions in the workbook are made visible in the presentation. Not as a picture (distorting the fonts), but in a native PowerPoint Table!

Page | 10

Logos, Icons and Sticky ColorsSettingsBefore OfficeReports can add logos and icons to charts, tables and shapes, you have to tell OfficeReports where to find them. Do this by clicking “Images/Logos” in the OfficeReports ribbon in PowerPoint. You can define 2 folders in which OfficeReports will look for the images and logos. Please use the 'common' folder for images that you use generally across projects. The 'project' folder is for images only used for this specific presentation.

LogosOfficeReports can replace texts in tables, charts and shapes by images/logos:

1. Check 'Use Image/Logo' in the ‘Edit Link’ window2. OfficeReports will look for image files in the folders defined below with the same name as the texts

in the table, chart or shape. If OfficeReports finds such a file, the text is replaced by the image/logo.

IconsOfficeReports can add icons to tables, charts and shapes, depending on the background color of the linked cells in Excel. You define which icon to use for a specific background color by clicking “Images/Logos”.

1. Click the ‘Icons’ button and check 'Use Icons' in the ‘Edit Link’ window2. OfficeReports will try to find the background color in the defined ‘Icon List’. If the color is found, the

matching icon from the image folders is displayed in the table, chart or shape.

Page | 11

From Excel To PowerPoint

LINK

Note: If you are a bit creative in Excel, you can show a specific smiley in PowerPoint depending on a specific value in Excel

Sticky ColorsIn the same way as OfficeReports applies logos to tables, charts and shapes, you can also define colors for specific texts. Do this by clicking “Sticky Colors” in the OfficeReports ribbon.

1. Check 'Use Sticky Colors' in the ‘Edit Link’ window2. OfficeReports will try to find the text in the defined ‘Sticky Colors’ list. If the text is found, the

matching color is used in the chart or shape (does not work for tables yet).

Page | 12

Reusing the presentation (Automation)Change DataUpdating the presentation with new data is easy:

1. Click ‘Change Data’ in the OfficeReports ribbon.2. Select the workbook containing the new data in the File Dialog.3. Click ‘Refresh’ to update the presentation with the new data.

Repetitive ReportsIn case you need to create many reports from different workbooks, you can use the “Repetitive Reports” function:

1. Click ‘Repetitive Reports’ in the OfficeReports ribbon2. In the window popping up, select the folder where the different workbooks are.3. In the list box, select the workbooks for which you want a presentation generated4. Check ‘PDF’ if you want the presentation saved as PDF file.5. Click ‘Ok’ and OfficeReports will generate the requested reports.

The presentations are generated in exactly the same way as if you had used ‘Change Data’ for each report.

Page | 13

Note: OfficeReports copies the data (only values, not formulas) from the selected workbook into the linked workbook. This means the formulas, conditional formatting and other definitions made in the linked workbook will still work!

Note: OfficeReports copies data from sheets to sheets in the attached workbook with the same name. Sheets in the attached workbook without a matching sheet in the selected workbook are not changed.

Flexibility: Linking variable sized rangesIf the number of rows or columns are variable, click the ‘Advanced’ button in the ‘Edit Link’ window, and you will have the following options:

Using MacrosIf our standard functionality is not enough for you, it is possible to define to run a specific macro anytime OfficeReports updates a link. This makes it possible to create any kind of table/chart and automate any kind of report mass production. It goes beyond the scope of this ‘Getting Started’ manual to explain in detail. If you are interested in this functionality, please contact us.

Watch the videosPlease have a look at our videos. They will make everything clear to you!

General introduction to Link

Automation

Page | 14