vrijdag 17 februari 2017

Excel: Visual Interaction, Color Linked Charts

Power BI

In Power BI  you can put more charts on one page. Next you can choose to make these charts interactive, given a connection between the sources.

In this example we can see two charts, created in Power BI. The upper one shows amounts per year; the lower obe amounts per year per month.


In this case a click on one of the years in the lower chart hightlights the corresponding data in the upper chart.

Excel

Could something the like be done in Excel. Visual interacting by clicking part of a chart does not work in Excel (without using VBA). But it is possible to create a similar visual interaction by clicking a year (G2:G5)  in the examples below.


When we pick another year within the range G2:G5 we get:


Etc..

To do this I used the function CELL("ADDRESS"). To make Excel recalculate whenever the selection changes, we need a little VBA. When we use a cell with data validation based on the years in G2:G5 we need no VBA at all.

You can download the example ExcelInteractief.xlsm through:

https://drive.google.com/drive/u/0/folders/0B7HgkOwFZtdZVmhRQUZFM28yc1U
Een reactie posten