Complete Course on Dashboards Creation with form controls

If you want pre-build templates with dashboards, check out Simple Sheets. 

Having a large number of records about countries and information split in 2 lists I cannot get any useful insights about my data, unless I create a Dynamic Dashboard… That answers questions about my data in a graphical summary that fits in one screen so that information can be monitored at a glance.

In this Project I create a powerful dynamic dashboard.

You can Download the exercise file and follow along by clicking on the button here below:

I start by creating a combination chart that shows few countries at a time

Population in column Chart and Density / Km2 as a line chart

With a scroll bar Control I select different countries from the source list.

With a Spin Button I control the number of countries represented: I can show more or less

For the countries displayed I can get all information about a specific country by selecting an option from a drop list

Population – Density – land Area – Median Age – World Share

That’s only part of the magic

Because for any country I select I get a map and much more information below it

Capital – Abbreviation – Official Language – Calling Code

Everything is totally dynamic

From Data to Dynamic Dashboard … All In One Dashboard with form controls

Let’s build it from ground up in Excel

The source data was imported from

https://www.worldometers.info/world-population/population-by-country/

and from Wikipedia. I cleaned it and transformed it using Power Query to get the 2 lists that I’ll be using for this project. I also preferred to choose only 84 countries to focus on the concept avoiding some complications like excluding countries with very large population (like China and India) for a better visibility of the column Chart.

Now let’s start our project

Part One

Adding the Developer tab to the Ribbon.

1. Creating Scroll Bar control

The Scroll bar specifies a subset of countries that appear on the chart say 6 or 7 at a time. Clicking on the scroll bar selects another group of countries.

Inserting the Scroll bar ActiveX control

On the Developer Tab click on “Insert” in the “Controls” group then click on Scroll Bar under ActiveX. Click and drag to create a scroll bar

Setting the Scroll bar Properties

On the Developer Tab click on Properties in the “Controls” group and set the properties as follows:

• 1. Setting a Min of 1 and a Max of 68 (I have a total of 83 countries)
  2. Changing the Back color to blue (to match the column chart)
  3. Locking the control (TRUE)
  4. Link the Scroll bar to cell J18

Creating a Spin Button control

The Spin Button will control the number of countries that appear at any time on the chart say 6 or 7 at a time. Using the spin Button, I can increase or decrease the number within the specified limit in the properties of the control, as follows

Inserting the Spin Button ActiveX control

On the Developer Tab click on “Insert” in the “Controls” group then click on Spin button under ActiveX. Click and drag to create a Spin button.

Setting the Spin button Properties

On the Developer Tab click on Properties in the “Controls” group and set the properties as follows:

1. Setting a Min of 1 and a Max of 15 (Since I have a total of 83 countries the maximum of the scroll bar is 68 and the Maximum of the Spin button is 15 = 83 ►So we do not get errors by selecting the Max or min on either controls.)
2. Changing the Back color to Orange (to match the Line chart)
3. Locking the control (TRUE)
4. Link the Scroll bar to cell J19

Preparing some labels for Textboxes

• 1. Type: From Country in cell I18, Visible Countries in cell I19 and To Country in cell I20
     remember that J18 is linked to the Scroll bar & J19 is linked to the Spin button
  2. In cell J20 create a formula that calculates the ending country on the Column Chart: = Sum (J18:J19)
  3. In Cell J21 create a label using the joining operator: =I18&”: “&J18+1
  4. In Cell J22 create a label using the joining operator: =I19&”: “&J19
  5. In Cell J23 create a label using the joining operator: =I20&”: “&J20

Create the first textbox (Insert Tab ►Textbox)

• • 1. Position it below the left side of the scrollbar
    2. Put it in the Edit mode (F2) ► The blinking cursor appears in the formula bar
    3. Type =J21
    4. Format: Berlin Sans FB Font, size 14, Blue color + No outline

Create a second textbox (Insert Tab ►Textbox)

1. Position it below the Right side of the scrollbar
2. Put it in the Edit mode (F2) ► The blinking cursor appears in the formula bar
3. Type =J23
4. Format: Berlin Sans FB Font, size 14, Blue color + No outline

Create a third textbox (Insert Tab ►Textbox)

1. Position it Above the spin button
2. Put it in the Edit mode (F2) ► The blinking cursor appears in the formula bar
3. Type =J22
4. Format: Berlin Sans FB Font, size 14, Orange color + No outline

Creating a Sample Chart with part of the Source data

The selected data will serve in building the combo chart but all the source data will be modified in Step 7 when we link the chart to our controls.

Create the Chart

• 1. Select Sample data, the Range from A1:C15 ► Insert Tab ►In the Charts group, click on the square in the lower right corner (The dialog box launcher)
     
     The “Insert Chart” dialog box opens, it has 2 tabs ►Click on the “All Charts” tab and select the last category “Combo”
     Make sure that the chart type for “Population” is Clustered Column and the for “Density” it is Line.
     Check the box for Secondary Axis for the Line Chart ►hit OK.

The chart is created and can be moved and resized (Floating on the grid). Position the chart above the Scroll bar.

Formatting the Chart:

• • 1. Select the Primary Vertical (Value) axis, the open the “Format Axis Pane” either from the Right-click menu or by using the shortcut CTRL + 1
    2. In the Format Axis pane select “Millions” for the Display Units.
    3. With the axis selected apply some Font formatting on the home tab and select blue color.
    4. Select the Secondary Vertical axis and format it with orange color.
    5. Select and Delete: Chart Title, Legend and Horizontal grid lines.
       
    6. Click on one of the blue columns, and in the Format Data Series pane (CTRL + 1) ►on the Series Options Tab ►drag the gap width slider to the left to reduce the gap between columns.
    7. Select the Line chart and in the Format Data Series pane ► on the Fill and Line Tab ► Check the box for “Smoothed Line”
       
       
    8. Click on the Chart outer border and in the Format Chart Area Pane (CTRL + 1) ► Click on the Size and Properties Tab ►Under Properties, select “Don’t move or size with cells”
    9. With the chart area still selected ► Click on the Format Tab of the ribbon and under Shape Outline ► Select No Outline.

And now our Chart is almost finished and looks so much better!

However, the chart is static and is not linked to our Scroll Bar or our Spin button.

Linking the sample chart to the Scroll bar and Spin Buttons.

The scroll bar serves for selecting a certain number of records starting from the record having the number returned from cell J18 which is the cell linked to the scroll bar. Because I start counting from country number 1 and move downwards by adding the contents of Cell J18 (this will be automatically done in the next step of defining a name). Example:

If the scroll bar returns 10 to cell J18 ► Then the first country that appears on the chart will be 10+1 = 11

Creating Defined Names

Our goal is to create 3 defined names that will be used for dynamically selecting the source data to be plotted on the chart. Each defined name will use an OFFSET function.

The OFFSET function has 5 arguments: The first 3 arguments describe a starting point, while the 4th. Argument specifies the Height of the range to store and the 5th.argument specifies the width of that range.

=OFFSET(reference,rows,cols,height,width)

The function that I’ll create will always start at the row of the first country Row # 2.and I create it inside a Named Range. So we open the Define Name dialog box on the Formulas Tab of the Ribbon ► Give a name to our range and in the Refers to Box type the Offset Function.

You can open the Define Name by using the shortcut CTRL + ALT + F3

• • 1. Defined Name for the Category Axis (country names)
       In the define name dialog box ► give a name “CountryX” and create the OFFSET function

=OFFSET(Filtered!$A$2,Filtered!$J$18,0,Filtered!$J$19,1)

This function means I start at cell A2 (the first country), I move down for a certain number of rows equal to J18 (the number returned by the scroll bar), I want to move the starting point zero columns, I select a height for the range equal to J19 (the number returned by the Spin button) and the last argument is the width of the stored range +1 column.

• • 1. Defined Name for storing the Population

In the define name dialog box ► give a name “PopulationY” and create the OFFSET function

=OFFSET(Filtered!$B$2,Filtered!$J$18,0,Filtered!$J$19,1)

• • 1. Defined Name for storing the Density

In the define name dialog box ► give a name “DensityY” and create the OFFSET function

=OFFSET(Filtered!$C$2,Filtered!$J$18,0,Filtered!$J$19,1)

• 1. Linking the Defined Names to Source Chart data.

Select the chart ► on the chart design tab click on Select Data”. The Select Data Source dialog box opens showing the 3 Axis.

• 1. Select Population ► Click Edit ► For the series value: type “=”, click on the sheet tab, then type the Defined name “PopulationX”. Note that the exclamation mark which means a sheet reference, is added automatically ► OK

=Filtered!PopulationX

• 1. Repeat for the Density (Secondary Axis)

• 1. For the Category Axis we MUST do it twice, each time selecting one of the Vertical Axis. Select Population then Click on Edit Category Axis

Repeat the previous step while selecting Density

And now the chart is linked to our controls: The Scroll Bar and The Spin Button. Test by selecting different countries from the Scroll bar and a different number from the Spin button

Do not click on the scroll bar itself, just click on the left and right arrows.

Now let’s finalize the chart!

Select any column and click on the Format Tab ► Select Shape Effect ►Bevel ►Round Bevel

Resize the chart (make the width equal to the length of the scroll bar. Increase the height of the chart area (without the plot area) leaving a white space below the chart.

To select and move the scroll bar ►Click on the Developer Tab ► Click on Design Mode ► drag the Scroll bar in the white space below the plot area.

Position the 2 text boxes on either side of the Scroll bar just below it.

Part Two

Getting Insights about the data

Extracting the records for the selected countries

Create a Defined Name that stores the records represented in the Chart:

On the Formulas Tab ► Define Name and I name it “ExtractionCard”

Using an Offset Function:

=OFFSET(Filtered!$A$2,Filtered!$J$18,0,Filtered!$J$19,6)

Next, I’m going to extract this list dynamically somewhere in my worksheet, in preparation to use it with a Combo box control.

I’ll be using an INDEX function for this task in Cell I26:

=IFERROR(INDEX(ExtractionCard,ROWS($A$1:A1),COLUMNS($I$26:I26)),””)

Notes:

Copy the function to the right to column N, then down to row 42 (6 columns & 15 Rows (The Max of the Spin button).

ExtractionCard ► That’s the array from which the INDEX function returns a value.

The ROWS Function► Increments when copying Up and Down (I select the same cell twice and lock the first one).

The COLUMNS Function ►Increments when copying Right and Left (I select the same cell twice and lock the first one).

I wrapped the INDEX Function in an IFERROR to handle any errors returned in the number of records is less than 15 (controlled by the Spin Button)

Important Step:

Select any record in the original source list (click and drag from Column A to column F for one single row ► on the Home Tab click on Format Painter ► the mouse pointer changes to a brush and we want to paste the same formatting to the range extracted with the Index function (I26:N42)

Let’s test one more time by changing our selection from the Scroll Bar and the Spin Button.

Create a Combo Box

Will be used to select one of the countries represented on the chart.

On the Developer Tab, click on the down arrow for Insert (Controls group) and select” Combo Box” ActiveX Control ► Click and drag to position your control above the left side of the chart.

Set the properties of the Combo Box

Make sure you are in “Design Mode” and click on Properties.

Linked Cell: L1

ListFillRange: I26:I40 (the first column of the result of the INDEX function)

Close the Properties dialog box, turn off the Design mode and test selecting from the Combo box ► Whatever country you select appears in Cell L1 (you may consider changing the font color of cell L1 to white, in order to hide it).

Test by changing the number of visible countries with the spin button, then check the contents of the combo box.

Creating a Set of Labels to be used for the insights:

Copy the labels from cell B1 to F1 then paste them in cell I43

Now, I want to extract the record of the selected country (from the Combo box) to row 44, below the corresponding labels (from the previous step).I’ll be using a VLOOKUP function, wrapped in an IFERROR function in cell I44 then drag to M44

=IFERROR(VLOOKUP($L$1,$I$26:$N$40,COLUMNS($N$45:O45),0),””)

Bring the entire record of the selected country (from the Combo Box) on top of the chart by using a Linked Picture:

Important Step:

Hide the Gridlines for the entire sheet by going to the View Tab and uncheck gridlines. We do not want to see borders around the country information.

Select the range I43: M44 and copy (CTRL +C)

Then click to the right side of the Combo box and Paste Special as Linked Picture, by clicking on the down arrow of the Paste command (Home Tab) and select the last option in the last row.

Selecting an option from the Combo box extracts all the information for that country

Select the linked picture and open the “Format Picture” dialog box either by right clicking or using the shortcut CTRL + 1
in the Size & Properties tab under Properties select the radio button for “Don’t move or size with cells” then close the Format pane.

Part Three

Map Visualization

When we select a country from the combo box as explained, we get the entire record extracted and returned in a Linked picture. We need to get the geographic map of the selected country to the side as well as an informative title and some extra information about the capital, abbreviation, official language and calling code.

So, I copied the Original source list and pasted it as it is few rows below the first one (row 121). This second version will be used for the Map

I also brought another list having the extra information we are looking for in a second sheet named Info.

Preparing for Creating an Advanced Filter

The second version of the list is the ListRange of the Advanced Filter

We also need a criteria range linked to the combo box. I typed the label “Country” in Cell H120 and in the cell below I typed =L1 (the cell linked to the combo box.

When I run the advanced filter, It will return the entire record corresponding to the selected country in cell H129

Create a Macro that is triggered by making any change in the combo box.

Open the visual basic editor ALT+F11 or

Right click on the Sheet tab and select View Code.

In the VBE there are 2 drop lists at the top ► Click on the Left drop List and select Combobox1 ► automatically a change event is created.

Between the Private Sub and End Sub we’ll write our simple code as follows:

Private Sub ComboBox1_Change()

On Error Resume Next

ActiveSheet.ShowAllData

Range(“A121:F204”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range(“H120:H121”), CopyToRange:=Range(“H129”)

End Sub

This code will release any filter then creates an advanced filter using the condition returned from the combo box and extract the record in cell H129.

Create the Map:

Select the Source data for the map ► Range H129:I130

On the Insert Tab of the ribbon ►Click on the down arrow for Maps ► select ► Filled Map ► The default map pops up.

On the Chart Design Tab of the Ribbon ► Select the Black Style 4. You may want to change the Theme colors as well.

Click on the Chart Element Button (Plus sign to the upper right corner of the chart) deselect Chart Title and Legend and Select Data Labels ► More Data Labels Options ► Check Category Name (The country name).

Click on the Data Label in the chart and format the font on the Home tab. Now the selected country fills the entire map and looks like this:

Move the Map and position it to the right side of the Combo Chart.

While the map is still selected ► hit CTRL +1 ►in the Format Chart area pane ► Size and Properties ► Select Don’t move or size with cells

Create a Dynamic Title above the map.

The dynamic title should read:

Total Population of Russia is 145,934,462

It Represents 1.87% of The World!

We build the title by combining strings of text ► in Double Quotations using the & symbol

With Cell Values to return the Population and % of World population ► Cells I44 & M44

We also Format the numbers from the above 2 cells with a custom format within the concatenated title by using 2 TEXT functions

And we split the Title in 2 lines by using a CHAR(10) function ► New line

And the function in P43 should look like this:

=”Total Population of “&L1&” is “&TEXT(I44,”###,###,###,000″)&CHAR(10)&” It Represents “&TEXT(M44,”#0.00%”)&” of The World!”

Now that I have my dynamic title in cell P43,I want to see it above the Map.

On the Insert Tab ► Select Text Box ► Click and drag where you want to see the Text Box► Hit F2, and you’ll see the blinking cursor in the Formula Bar ► Type an equal sign and click on cell P43 ► Enter

Now format the text box however you like and remove the outline.

Capturing More insights from the second Sheet.

In the second worksheet I have the same list of countries (from a different source) showing additional Information for each country

Create a Textbox below the Map following the same steps of the previous text box but link it to cell L1 (the country name) ► Then Format it as needed.

Type and Format some labels in Cells Y18, Y20, AA18, AA20 to read “Capital”, “Abbreviation”, “Language” and “Calling Code” respectively.

Bringing Dynamic data from the second worksheet.

Select Cell A1 in the second sheet ► hit CTRL + A to select the entire list ► Click in the Name Box (to the left of the Formula Bar) ► type “MoreInfo” (as a name for the range) ►Hit “enter” for the name to stick.

Back to our main sheet (our Dashboard) we’ll create 4 VLOOKUP functions in cells Z18, Z20, AB18 and AB20 as follows (respectively):

=VLOOKUP($L$1,MoreInfo,3,0)

=VLOOKUP($L$1,MoreInfo,2,0)

=VLOOKUP($L$1,MoreInfo,5,0)

=VLOOKUP($L$1,MoreInfo,4,0)

Then format as needed

The Final step in our project is to reset the Combo box whenever we make a change from the Scroll Bar:

Right click on the Sheet Tab ► Select “View Code” ► Back to the VB editor.

Click on the Left Drop list at the top of the module ► Select ScrollBar1 ►

A Private Sub is created ► Between the 2 lines copy and paste this simple code (just 2 lines):

Private Sub ScrollBar1_Change()

On Error Resume Next

ComboBox1.Clear

End Sub

Close the Visual Basic Editor to go back to Excel .

Your project is now completed. Test the different functionalities:

Scroll Bar , Spin Button, Combo box, watch the chart and Map, watch the labels and text boxes.

Congratulations … You created a nice Dashboard.