Latest

Excel: Interactive charts – CM Web

Tony De Jonker Excel Excel: Questions and Answers

By using multiple graphs based on the same input source, you can show certain insights that are not normally immediately visible. You can use this principle in the context of storytelling. The charts are linked by a control element. This article shows you how to create such interactive charts.

Example of interactive charts

The example shows two graphs. By moving the vertical scroll bar on the right, you can move the focus to a set of five countries with corresponding quantities. In the left graph you see a bar chart of the five chosen countries with corresponding quantities. The right graph shows all countries with different color for the set of five chosen countries.

Ingredients for creating the interactive charts

This model contains the following ingredients:

  • Conditional formatting with formulas
  • Help table with INDEX function
  • Linked image
  • Slider bar

Setup of the input source

Make in a separate sheet (called Input) the following table:

Proceed as follows:

  • Highlight the area from B2 through C51 (last active cell) and give it a light gray background.
  • Highlight the area C2 through C51 (last cell) and choose from the Ribbon: Start> Styles> Conditional Formatting> Data Bars> Solid Fill> Orange Data Bar.
  • Highlight the area C2 to C51 (last cell) and choose from the Ribbon: Start> Styles> Conditional Formatting> Manage Rules> Edit Rule> Show Bar Only> OK.
  • Highlight the area from B2 to C51 (last active cell) and choose from the Ribbon: Start> Styles> Conditional Formatting> New Rule> Use a formula to determine which cells are formatted> Format values ​​to which this formula applies: = ROW () = $ E $ 6 + 5> Format> Border> Bottom Border> OK.
  • New line>> Use a formula to determine which cells are formatted> Format values ​​to which this formula applies: = ROW () = $ E $ 6 + 1> Format> Border> Top Border> OK.
  • New line>> Use a formula to determine which cells are formatted> Format values ​​to which this formula applies: = AND (ROW ()<=$E$6+5;RIJ()>$ E $ 6)> Format> Border> Left Border> OK.
  • New line>> Use a formula to determine which cells are formatted> Format values ​​to which this formula applies: = AND (ROW ()<=$E$6+5;RIJ()>$ E $ 6)> Format> Border> Right Edge> OK.

Create help table

You make in the sheet Input the following help table as a placeholder for the five countries:

Enter the following information:

  • Cell C6: enter the number 1 here for now. This cell serves as an anchor cell for the scroll bar and conditional formatting.
  • Highlight cells E5 through E2 and type the following formula: = E6 + 1 followed by Control Enter.
  • Highlight cells F2 through F6 and type the following formula: = @ INDEX ($ B $ 2: $ B $ 51; E2) followed by Control Enter.
  • Highlight cells G2 through G6 and type the following formula: = @ INDEX ($ C $ 2: $ C $ 51; E2) followed by Control Enter.

Create charts

Create a separate worksheet called Chart.

To create the first bar chart:

  • Mark cells F2 through G5.
  • From the Ribbon choose: Insert> Show All Charts> All Charts> Grouped Bar.
  • Delete the title.
  • From the Ribbon, choose: Graph Design> Graph Styles> Style 3.
  • Remove the vertical dividers.
  • Cut this graph (Control X) and place it with on sheet Chart (Control V).

You create the second graph as follows:

  • In sheet Input make the column C wider (for example 20 points).
  • You highlight cells B1 through C51 and press Control C.
  • You go to the magazine Chart and select from the Ribbon: Start> Clipboard> Paste> Other Paste Options> Linked Image.
  • You place this graph to the right of the first graph and align it at will.

Create scroll bar

As a last step, create the scroll bar as follows:

  • From the ribbon, choose: Developers> Controls> Insert> Form Controls.
  • You draw this element to the right of the second graph in the vertical direction.
  • If you right-click on the element, choose from the shortcut menu: Format control and enter the following settings and click OK.

Via the cell link (E6) the value is changed by moving the slider, after which the other values ​​in the auxiliary table change as well as the colors of the conditional layout in the second graph.

Author: Tony De Jonker

This article is published in cm: 2020, Ep. 6.

Here you can read more Excel tips and tricks by Tony De Jonker

Knowledge Day Power BI

Tags

Related Articles

Back to top button
Close
Close