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.
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:
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.
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:
This works exactly as described for tables. Click here.
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: