Tables

Link Excel Range to selected Table

To populate an existing table in PowerPoint, do the following:

  1. Click ‘Show Workbook’ and select the range of Excel data to be linked to a table in PowerPoint

  2. In the presentation, select a native PowerPoint table

  3. Select the cell where you want the range to start appearing.

  4. Click 'Add Link to the selected Table' in the Task Pane, or click ‘Table->Link Excel Range to selected Table’ in the ribbon tab.

  5. OfficeReports will now populate the PowerPoint table with the selected Excel data range

Link Excel Range to a new Table

Alternatively, if you want to let OfficeReports add a new table in a standard OfficeReports layout, you can:

  1. Click ‘Show Workbook’ and select the range of Excel data to be linked to a table in PowerPoint

  2. Click 'Add Link to the selected Table' in the Task Pane, or click ‘Table->Link Excel Range to selected Table’ in the ribbon tab.

  3. OfficeReports will now create a PowerPoint table in a default OfficeReports layout, and populate it with the selected Excel data range

A table can be populated with data from a multiple number of different data ranges. Select a new starting point in the PowerPoint table, and repeat the steps in 3.1.1

Copying a table with linked data

When copying, and pasting a PowerPoint table containing an OfficeReports link, the copied table will link to the same range in Excel.

 

Table settings

OfficeReports Automation includes a list of settings to control how the Excel data is displayed in PowerPoint tables.

Most features speak for itself. If you e.g. check 'Use Excel background color', the background colors of the cells in the table will match the background colors of the cells in the linked Excel range.

When checking 'Use Images/Logos', OfficeReports will try to find image files having the same name as the texts in the table cells. When a match is found, the text is replaced with the image. You define in where OfficeReports looks for image files in 'Logos and Images' in the ribbon tab.

Remove characters will remove characters from numbers, so this is a way to get rid of significance characters.

 

Use Icons

OfficeReports Automation can add icons to tables, depending on the background color of the linked cells in Excel.

The background colors in Excel above are displayed as shapes in the PowerPoint Table. Read more about Significance Shapes.


 

Dynamic Sizing

Building reports and presentations where the number of rows and columns in tables are variable often means a lot of manual work. OfficeReports adds 'Dynamic Sizing' features which automates a typical time-consuming manual process.

The following text describes the functionality in Dynamic Sizing. The recommended workflow might vary depending on the situation.

 

Range defines the number of rows/columns in the Table

 


1. Add or select a table in PowerPoint. If a new table is added, the PowerPoint default size is 5 columns and 2 rows. Put the cursor in the cell in the upper left corner, if this is the starting point for adding data.

 

2. Select a data range in Excel (2 columns, 7 rows):

 


3. In the task pane, click 'Add Link to selected Table'.

 

4. Check ‘Range defines the number of rows/columns in the Table’. and we will get the table below.

 

Keep the current Table size

OfficeReports will keep the table size the same when changing the number of rows and columns. In some cases when adding rows/columns this might not be possible.

Variable number of Columns

Check ‘Variable number of Columns’. OfficeReports will check whether the columns to the right, next to the selected range contains data. If we find data, this feature will expand the PowerPoint table column wise:


 

Variable number of Columns - Offset

When using a Dynamic Sizing, and a Variable number of Columns, OfficeReports will include all Columns until we meet the first empty column outside the selected data range. In some situations, the last 1, 2 or 3 columns are not wanted in the populated PowerPoint table. These can now be extracted by using the feature ‘Offset X Columns’ When defining ‘Offset 1’, OfficeReports will exclude the last column containing data. An example follows for ‘Variable number of Rows – Offset’, below.

Variable number of Rows

Similar functionality as described for ‘Variable number of Columns’.

Variable number of Rows - Offset

The ‘Range defines the number of rows/columns in the Table’, but we would like to exclude the last row. Therefore we check ‘Variable number of Rows’ and ‘Offset’ = 1 row.



The result is a table where the last rows from the linked Excel range is removed.

 

Hide Zero Columns


In situations where the defined data range contains Columns with no data, Office Reports provides an option to hide these Columns in the populated PowerPoint table.

Check ‘Hide Zero Columns’ and you get the following result:


Hide Zero Rows

Similar functionality as described for ‘Hide Zero Columns’.

 

Advanced (Run Macro)

If there is a need for functionality that go beyond what is standard in OfficeReports Automation, it is possible to define to run a specific VBA Macro anytime OfficeReports updates a link. This makes it possible to create any kind of table. Read more about VBA Macros.