Charts

Link Excel Range to selected Chart


To populate an existing chart 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 a chart

4. Click ‘Add Link to selected Chart’ in the task pane, and you will see the following window:

 

Using the default settings, you get the following result:

Show Texts in a Table

This feature remove the texts from the category-axis of the chart and replaces it with a PowerPoint Table, which will contain the category texts instead. The table is easier to format and edit, and makes it possible to replace texts with images.

 

Link Excel Range to selected Chart (fixed)

When an existing chart is defined by say 8 categories and 3 series, then, by default when linking an Excel range of 4 categories and 4 series, OfficeReports will add 4 categories and 4 series in the chart. When using the fixed format, OfficeReports will add data to a chart that expects 8 categories and 3 series (fixed), and will now populate the first 4 categories and leave the rest empty. Similar with the series, where only 3 of the 4 series will be added to the chart, as the chart is defined by 3 fixed series.

 

Copying a chart with linked data

When copying and pasting a PowerPoint chart that contains a link to an Excel range, the copied chart will be linked to the same range.

 

Chart settings

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

Most features speak for them selves and work mostly just like they work for Tables. We will say a little bit more about a few of them:

Switch Row/Column


In a number of situations, we need to switch the rows and the columns. With OfficeReports the switch is easy as you can toggle ‘Switch Row/Column’:



Use Sticky Colors

The charts above all contain the same categories and all charts are sorted descending. But it is difficult to read the charts and compare the charts. Using 'Sticky Colors' we can make the chart more readable:


Read more about 'Defining Sticky Colors'.

 

Use Excel background color

With OfficeReports we can overrule the default PowerPoint chart color settings, based on the linked data from Excel. E.g. the colors of bars/columns/lines can be defined based on Excel background colors:


The Excel colors above control the colors of the bars in PowerPoint below.


Use Images/Logos

Note: This only works if you have selected "Show Texts in a Table" when linking the Excel range to the chart.

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 Icons' in the ribbon tab.

 

Using 'Images' this chart looks like this:

Icons

This works exactly as described for tables. Click here.

 

VBA Macro

In case OfficeReports does not have the functionality you, you can add it yourself by writing a macro. Running macro 'ORTop2Bracket' adds brackets to the chart:

 

Read more about VBA Macros.

 

Handle overlapping data labels

Depending on the chart type selected, there is a menu tab ‘Chart Labels’ in the OfficeReports Task Pane. E.g. when editing a Stacked Column chart the ‘Chart Labels’ tab will be visible.

Checking 'Repositions overlapping labels results in:

When also checking 'Color fill sqeeuzed label boxes', we get the following result: