Excel Dashboards Interactive Controls - Excel Dashboards

What is Interactive Controls in Excel Dashboards?

  • If you want to display more data on the dashboard then it does not fit into a single screen. So use Excel Visual Basic.
  • The most commonly used controls are scrollbars, radio buttons, and checkboxes.
  • By using these controls you can make interactive and allow the user to view the different facets of the data by possible selections.
  • Providing interactive controls such as scroll bars, checkboxes and radio buttons in your dashboards you can facilitate the recipients to dynamically view the different facets of data being displayed as results.
  • A particular layout of the dashboard can be decided along with the recipients and use the same layout then onwards.

The Excel interactive controls will be available in the DEVELOPER tab on the Ribbon.

Developer tab


If you do not find the DEVELOPER tab on the Ribbon, do the following:

  • Click on Customize Ribbon in the Excel Options box.
  • Select Main Tabs in the Customize the Ribbon box.
  • Check the Developer box in the Main Tabs list.

developer tab ribbon

  • Click the OK. You will find the DEVELOPER tab on the Ribbon.

Scroll Bars in Dashboards

In dashboard component can be compacted as much as possible. Suppose your results look as follows :

Compact

If the above table want with a scroll bar as given below, then it would be easier to browse through the data.

Scroll data

Dynamic Target Line in a Bar chart with scroll bar may also be present.
By moving the scroll bar up and down, the Target Line moves up and down and those bars that are crossing the Target Line will get highlighted.

Creating a Scrollbar

Copy the headers of the columns to an empty area on the sheet to create a scrollbar for a table as shown below:

Create scroll bar

  • Insert a scrollbar.
  • Click on the DEVELOPER tab on the Ribbon.
  • Click on Insert in the Controls group.
  • Click on Scroll Bar icon under Form Controls in the dropdown list of icons.

Insert scroll bar

  • Take the cursor to the column I and pull down to insert a vertical scroll bar.
  • Adjust the height and width of the scroll bar and align it to the table.

Adjust scroll bar

  • Right click on the scroll bar.
  • Click on Format Control in the dropdown list.

Format control

Format Control dialog box appears.

  • Click on the Control tab.
  • Type the following in the boxes that appear.

format control dialog

  • Click the OK button. Scroll bar is created. By chosing the cell O2 as the cell link for the scroll bar, takes values 0 – 36 when you move the scroll bar up and down. A copy of the data in the table has to be created with a reference based on the value in the cell O2.
  • In the cell K3, type the following = OFFSET(Summary[@[S. No.]],$O$2,0).

cell link

  • Hit the Enter button. Fill in the cells in the column copying the formula.

Column copying

  • Fill in the cells in the other columns copying the formula.

Formula copying

Dynamic and scrollable table is ready to be copied to your dashboard.

Dynamic scrollable

  • Move the scroll bar down.

Move scrollable

From the above figure you can see that the value in the cell - scroll bar cell link changes, and the data in the table is copied based on this value. At a time, 12 rows of data are displayed.

  • Drag the scroll bar to the bottom.

Drag scrollbar

From the above you can see that last 12 rows of the data is displayed as the current value is 36 (as shown in the cell O2) and 36 is the maximum value that you have set in the Form Control dialog box.
In dynamic table you can change the relative position, change the number of rows to be displayed at a time, cell link to scroll bar, etc. based on your requirement. These needs to be set in the Format Control dialog box.

Creating a Dynamic and Interactive Target Line

Suppose you want to display the sales region-wise over the last 6 months. You also have set targets for each month.

Target line

Follow the below given steps:

  • Create a column chart showing all this information.
  • Create a Target Line across the columns.
  • Make the Target Line interactive with a scroll bar.
  • Make the Target Line dynamic setting the target values in your data.
  • Highlight values that are meeting the target.

Create a column chart showing all this information

Select the data. Insert a clustered column chart.

Clustered column

Create a Target Line across the columns

The chart type need to be changed to combo. Select chart type as Line for the Target series and Clustered Column for the rest of the series.

Change chart

Base table is created for the Target Line. You will make this dynamic later.

Base table

From the above table Change the data series values for the Target Line to the Target column.

Edit series

Click the OK button.

Clustered color scheme

Change the color scheme for the Clustered Column. And the Target Line into a green dotted line.

Target green line

Make the Target Line interactive with a scroll bar

  • Insert a scroll bar and place it below the chart and size it to span from January to June.
  • Enter the scroll bar parameters in the Format Control dialog box.

targetline interactive

  • Create a table with two columns − Month and Target.
  • Enter the values based on the data table and scroll bar cell link.

Month target

This table displays the Month and the corresponding Target based on the scroll bar position.

scrollbar month target

Make the Target Line dynamic setting the target values in your data

Now, set to make your Target Line dynamic.

  • Change the Target column values in the base table you created for the Target Line by typing = $G$12 in all the rows.

The cell G12 displays the Target value dynamically.

Targetline setting

From the above we can see that the Target Line moves based on the scroll bar.

Highlight values that are meeting the target

This is the final step. Highlight the values meeting the target at any point of time.

  • Add columns to the right side of your data table − East-Results, North-Results, SouthResults and West-Results.
  • In the cell H3, enter the following formula = IF(D3 >= $G$12,D3,NA())

Highlighted

  • Copy the formula to the other cells in the table. Resize the table.

Resize table

From the above you can see that the values in the columns: East-Results, North-Results, South-Results and West-Results change dynamically based on the scroll bar (i.e. Target value).
Values greater than or equal to the Target are displayed and the other values are just #N/A.

  • Change the Chart Data Range to include the newly added columns in the data table.
  • Click on Change Chart Type.
  • Make the Target series be Line and the rest Clustered Column.
  • For the newly added data series, select Secondary Axis.
  • Format data series in such a way that the series East, North, South and West have a fill color orange and the series East-Results, North-Results, South-Results and West-Results have a fill color green.
  • Enter a Data Label for the Target Line and make it dynamic with the cell reference to the Month value in the dynamic data table.

Dynamic table

The chart with dynamic Target Line is ready for inclusion in the dashboard.

Dynamic targetline

Secondary axis should be cleared because it is not required. When you move the scroll bar, Target Line moves and the Bars will get highlighted accordingly. Target Line also will have a Label showing the Month.

Targetline moved

Excel Option (Radio) Buttons

The Radio buttons are used to select an option from a given set of options. It is always depicted by a small circle with a dot in it when selected. Only one radio button has to be selected from a set.

radio button option

Radio buttons are referred to as Option Buttons in Excel.

Excel Option Buttons can be used in charts to choose the data specifics the reader wants to have a look at. For example, the example in the previous section you have created a scroll bar to get a dynamic Target Line with target values based on Month. Use Option Buttons to select a Month and thus the target value, and base the Target Line on the target value. Following will be the steps :

  • Create a column chart showing all this information.
  • Create a Target Line across the columns.
  • Make the Target Line interactive with Option Buttons.
  • Make the Target Line dynamic setting the target values in your data.
  • Highlight values that are meeting the target.

The Steps 1 and 2 are same as in the previous case. There for after second step the following chart will be appeared.

target green line

Make the Target Line interactive with Option Buttons

  • Insert an Option Button.
  • Click on the DEVELOPER tab on the Ribbon.
  • Click on Insert in the Controls group.
  • Click on Option Button icon under Form Controls in the dropdown list of icons.

Interactive option

Place it at the top right corner of the chart.

Right corner

Right click on the Option button. And click the Format Control option in the dropdown list.

Right click format control

Enter the Option Button parameters in the Format Object dialog box, under the Control tab.

Format object

The cell F10 is linked to the Option Button. Make 5 copies of the Option Button vertically.

option vertically

From the above figure you can see that all the Option Buttons have the same name, referred to as Caption Names. But, internally Excel will have different names for these Option Buttons, which you can look at either in the Name box. Further, as Option Button 1 was set to link to the cell F10, all the copies also refer to the same cell.

Click on any of the Option Buttons given.

linked cell

From the above figure you can see that the number in the linked cell changes to the serial number of the Option Button. Rename the Option Buttons to January, February, March, April, May and June.

 Rename option

Create a table with two columns : Month and Target. And enter the values based on the data table and scroll bar cell link.

Enter values

This table displays the Month and the corresponding Target based on the selected Option Button.

Selected option

Make the Target Line dynamic setting the target values in your data

Now, set to make your Target Line dynamic.

  • Change the Target column values in the base table you created for the Target Line by typing = $G$12 in all the rows.

The cell G12 displays the Target value dynamically.

Selected targetline

From the above we can see that the Target Line is displayed based on the selected Option Button.

Highlight values that are meeting the target

This is the final step. Highlight the values meeting the target at any point of time.

  • Add columns to the right side of your data table − East-Results, North-Results, South-Results and West-Results.
  • In the cell H3, enter the following formula = IF(D3 >= $G$12,D3,NA())

Highlighted

  • Copy the formula to the other cells in the table. Resize the table.

Resize cells

From the above figure you can see that the values in the columns − East-Results, North-Results, South-Results and West-Results change dynamically based on the scroll bar (i.e. Target value). Values greater than or equal to the Target are displayed and the other values are just #N/A.

  • Change the Chart Data Range to include the newly added columns in the data table.
  • Click on Change Chart Type.
  • Make the Target series be Line and the rest Clustered Column.
  • For the newly added data series, select Secondary Axis.
  • Format data series in such a way that the series East, North, South and West have a fill color orange and the series East-Results, North-Results, South-Results and West-Results have a fill color green.

Format data series

  • Add a dynamic Data Label to the Target Line with value from the cell $G$12.
  • Clear the secondary axis as it is not required.
  • Under the VIEW tab on the Ribbon, uncheck the Gridlines box.
  • Change the Label option to High in the Format Axis options. This shifts the Vertical Axis Labels to the right, making your Target Line Data Label conspicuous.

The chart with dynamic Target Line and Option Buttons is ready for inclusion in the dashboard.

Inclusion dashboard

By selecting an Option Button, Target Line is displayed as per the target value of the selected Month and the Bars will get highlighted accordingly. Target Line also will have a Data Label showing the target value.

Target value

Excel Checkboxes

Checkboxes are used to select one or more options from a given set of options. They are always depicted by small squares with a tick mark when selected. When you have a set of Checkboxes, it is possible to select any number of them.

For example,

checkboxes selected

The Excel Check Boxes can also be used in charts to choose the data specifics the reader wants to have a look at.

For example, the example in the previous section, created column chart that displays the data of 4 Regions – East, North, South and West. Here we can use Check Boxes to select the Regions for which data is displayed. You can select any number of Regions at a time.

  • Start with the last step of the previous section :
  • Insert a Checkbox.
  • Click on the DEVELOPER tab on the Ribbon.
  • Click on Insert in the Controls group.
  • Click on Check Box icon under Form Controls in the dropdown list of icons.

insert checkbox

  • Place it at the top left corner of the chart.
  • Change the name of the Check Box to East.

change name

  • Right-click on the checkbox. And Click on Format Control in the dropdown list.
  • Enter the Check Box parameters in the Format Control dialog box, under the Control tab.

Checkbox parameter

  • Click the OK button. We can see that in the linked cell C19, TRUE will be displayed if you select the Check Box and FALSE will be displayed if you deselect the Check Box.
  • Copy the Check Box and paste 3 times horizontally.
  • Change the Names to North, South and West.

Checkbox horizontal

From the above figure you can see that when you copy a Check Box, the linked cell remains the same for the copied Check Box also. Since Check Boxes can have multiple selections, you need to make the linked cells different.

  • Change the linked cells for North, South and West to $C$20, $C$21 and $C$22 respectively.

multiple selections

The next step is to have only the selected Regions’ data in the Chart.
Create a table structure as shown below :

Table structure

  • Type = IF($C$19,H3,NA()) in the cell C21.
  • Type = IF($D$19,I3,NA()) in the cell D21.
  • Type = IF($E$19,J3,NA()) in the cell E21.
  • Type = IF($F$19,K3,NA()) in the cell F21.
  • Fill in other rows in the table.

Other rows

  • Add the Target column.
  • Change the Chart data to this table.

chart data

By this Chart displays the data for the selected Regions that are more than the target value set for the selected Month.

Chart display

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel Dashboards Topics