Hey BPT
Here’s a little guidance on duplicating the speedo dials including how to configure the needle.
By the way, the SERIES function hasn't been deleted in Excel 2007, it's still an integral part of the charting package.
We’re using Excel 2007 for this demonstration.
Hope it helps you set up your own excel dashboard.
If you have problems, feel free to attach your dashboard, we’ll link it together and send it back to you.
Warm regards
ExcelDashboardWidgets
Duplicating the Speedo Dial Widget Tutorial
Step #1
Create a new spreadsheet with three tabs as follows..
1. My Dashboard Page
2. My Configuration Page
3. My Calculations Page
Step #2
Select the speedo widget from the ExcelDashboardWidgets spreadsheet ‘Widgets Showcase’ Tab and copy onto your new spreadsheet ‘My Dashboard’ Tab three times.
Your new excel dashboard should look something like this…
Step #3
Select the configuration table from the ExcelDashboardWidgets “Widgets Showcase” tab and copy onto your new spreadsheet ‘My Configuration Page’ Tab three times.
Your new ‘My Configuration Page’ should look something like this (note that I reformatted the cell sizes a bit so we can read the text)…
Now we are ready to start adding some data to our dashboard…
Step #4
On the “My Configuration Page”, start to enter the titles, units, actual dial value and dial ranges that you want to show. We’re using the following configuration for our example..
Dial #1: Weekly Customer Service Emails Received [Value: 43.2]
Dial #2: Answered Within One Working Day [Value: 28.3]
Dial #3: Answered Within Three Working Days [Value: 12.5]
Units: x 1,000 Emails / Week
Scales: 0 – 50
Now we need to add some content to the “My Calculations Page” that will create the dial needle and gauge scales.
Step #5
Select the calculation table ‘A – Linear Dial Widget’ from the ExcelDashboardWidgets “Widgets Showcase Calcs” tab and copy onto your new spreadsheet ‘My Calculations Page’ Tab three times.
Your “My Calculations Page” should look a little like this..
The next step is to link the yellow squares on the “My Calculations Page” to the “My Configuration Page”. Following this, the blue squares will automatically create the Speedo Widget needle and scale intervals.
Step #6
We will link the yellow squares to the “My Configuration Page” with the following steps..
6a) Select the “Min Value” cell for the first dial.
6b) In the formula bar type “=”.
6c) Using the mouse select the “My Configuration Page” tab.
6d) Select the “Minimum Value” cell for the first dial.
6e) Press “Enter”.
Repeat the process for the remaining yellow squares on the “My Calculations Page”.
The final stage is to link the widgets on the “My Dashboard Page” to the “My Configuration Page” and “My Calculations Page”
Step #7
Go to the “My Dashboard Page”.
Open the “Selection Pane” from the ribbon (HOME > EDITING > FIND & SELECT > SELECTION PAGE). See image below..
With the selection pane open, you will now see all of the text box and chart elements that make up the Speedo Dial widgets. See image below..
Our main task now is to link the following widget elements to the correct cells on the “My Configuration Page” and “My Calculations Page”..
- Linear Dial Title
- Linear Dial Main Value
- Linear Dial Needle
- Linear Dial Units
- Linear Dial Scale Values
Let’s start with something relatively easy. In the selection pane click all of the minus (-) signs so you just end up with the three widgets as follows..
I would recommend renaming these as follows...
Linear Dial #1
Linear Dial #2
Linear Dial #3
You can also re-order the widgets using the up and down buttons at the bottom of the selection pane (currently they are listed in the order in which we pasted them onto the sheet).
Choose the Linear Dial #1 Title from the selection pane. You’ll notice in the formula bar that the text box is currently linked to cell “$F9” – however, we want to link it to our configuration page.
To link the Linear Dial #1 Title text box carry out the follow steps (with the Linear Dial #1 Title text box selected)...
7a) Delete the current contents of the formula bar.
7b) Type equals “=”.
7c) Using the mouse, select the “My Configuration Page” tab.
7d) Select the dial #1 title.
7e) Press ENTER.
You’ll notice that the font size has gone back to the default setting so change it to SIZE 24pt BOLD.
Step #8
Repeat the process for Linear Dial Main Value as follows..
8a) Select the Linear Dial #1 Main Value in the SELECTION PANE.
8b) Delete the current contents of the formula bar.
8c) Type equals “=”.
8d) Using the mouse, select the “My Calculations Page” tab.
8e) Select the Dial #1 Actual Value.
8e) Press ENTER.
Back on the “My Dashboard Page” you’ll notice that again, the formatting of the text box has gone back to the default setting. Change this back to FONT SIZE 22pt and BOLD and WHITE COLOR.
Repeat this process for the scale values, linking them to the scale values in the “My Calculations Page”
The last widget element to configure is the Speedo Dial Needle..
Step #9
To configure the Speedo Dial Needle, we need to temporarily ungroup the widget elements. To do this, select the top right hand corner of the widget, right click and choose “GROUP > UNGROUP”. See image below..
You’ll notice that the Speedo Dial Widget has now ungrouped itself and you can see all of the different parts selected.
Now select a cell anywhere on the excel worksheet (to deselect the Speedo Dial elements) then select the Speedo Needle and you’ll see the old charting formula in the formula bar. See image below..
To link this Speedo Needle to our “My Calculations Page” we need to do the following...
9a) Select the formula box.
9b) Replace the link to the external spreadsheet inside the single quotes ‘[ExcelDashboardWidgets Spreadsheet5 - Unlocked.xlsx]Widget Showcase Calcs' with a link to the current “My Configuration Page”. In my case, the final formula is as follows..
Code: Select all=SERIES('My Calculations Page'!$H$6,'My Calculations Page'!$G$7:$G$11,'My Calculations Page'!$H$7:$H$11,1)
You’ll notice in the image below that my Speedo Needle data can be found on the “My Calculations Page” tab in cells G7 to G11 and H7 to H11 those are the cells I need to put in the formula. See image below.
Step #10
Finally, we need to group up all of the elements back together into one single widget. To do this, select the top right hand corner of the widget, then right-click and select “GROUP > REGROUP”.
Step 11
Now repeat the process for the other two Linear Dial Widgets!
Your final excel dashboard should look a little like this!!
(67.4 KiB) Downloaded 496 times
This version is locked but commercial license holders can download the unlocked version in the commercial license holder's download area here
http://www.exceldashboardwidgets.com/ph ... ?f=25&t=90.
If any of these steps aren’t clear – please feel free to let us know.
Also feel free to attach your excel dashboard and we’ll have link it together for you and send it back!
Good luck with your dashboard project.
DashboardWidgets
