ExcelDashboardWidgets

Helping you create stunning dashboards in Microsoft Excel including dials, speedometers, thermometers, and much more...

A few simple tutorials and samples to help you get started with your dashboard.

Moderator: DashboardWidgets

#277
Dear Dashboarders,

Here’s a little guidance on duplicating the speedo dials including how to configure the needle.

Hope it helps you set up your own dashboard.

If you have problems, feel free to upload 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 dashboard should look something like this…
The attachment #1.jpg is no longer available
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)…
#1.jpg
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..
#2.jpg
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..
#3.jpg
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..
#4.jpg
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..
#7.jpg
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.
#8.jpg
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..
#5.jpg
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 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..
#6.jpg
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.
#9.jpg
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 dashboard should look a little like this!!
#10.jpg
(64.88 KiB) Downloaded 1528 times
Please note that the unlocked version of this dashboard can be downloaded 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 dashboard and we’ll have link it together for you and send it back!

Good luck with your dashboard project.

DashboardWidgets
Attachments
#11.jpg
By Connelly
#1568
glh2e22 wrote:Hi DashboardWidgets...quick question. I purchased your product earlier today and love it. However, I am in a rush and would like to one that you attached in this thread. Is it possible to provide the pw for this sheet?
Hey DW, thanks so much for taking the time to write this helpful tutorial.
#1015
The instructions seem very clear but when completing the copy/paste of a dial widget everything copies over with exception of the needle. In reviewing the Selection Pane I see the following listed:

d) Light Non-Linear Dial Widget
Dial Main Value
Dial Needle Circle
Dial Main Title
Dial Scale Text
Dial Needle Chart

...then the scale values and text but no 'needle'. What am I missing?

I addition the copy/paste does not carry formulas from the widgets calculations tab.
#1020
Hi Danna

Sorry for the delay and sorry to hear you are struggling a little - Excel can be a beast at times!

To quickly answer your message, the dial is there - it's the 'Dial Needle Chart' object! The dial uses an Excel XY graph with three lines to draw the three sides of a triangle that make up the needle!

The best way to see how this widget is set up is to look at the example in the Excel Dashboard Widgets Showcase. Use the selection pane to see each of the objects that make up the widget. You can see the needle chart object and where it gets its source data from.

It's always hard to explain in writing, however there are a couple of videos on our YouTube channel that show how to set up the Dial Widget. If these videos don't help and you need us to make a new video specific to your needs just let us know!





Hope this helps a little..

Cheers

DashboardWidgets
By glh222
#1153
Hi DashboardWidgets...quick question. I purchased your product earlier today and love it. However, I am in a rush and would like to use the one that you attached in this thread. Is it possible to provide the pw for this sheet? My email is Gregg@FerrariLi.com.....you should see my order was processed earlier. Thanks!
#1154
Hi Gregg

Have you looked in the Commercial License Holder's Download Area at the bottom of the main forum index page? Your username has been added to the list of license holders so if you are logged in it should appear. Alternatively click here.. http://www.exceldashboardwidgets.com/phpBB3/index.php

You can find the unlocked versions of all of the dashboards there. If the version you are after is not there, let us know exactly what you are looking for.

Cheers

DashboardWidgets

[EDIT] OK - I found the dashboard you are looking for - you can download it here http://www.exceldashboardwidgets.com/ph ... ?f=25&t=90
By glh222
#1238
Hi, not sure if this has been asked but im figuring its fairly simple so i guess im just having a hard time finding the answer....is there any way to quickly add two more identical speedometers? As in, i would like to take my current sheet that contains 3 speedos, and add two more. What would be the fastest way to do so? thanks!
#1244
Hi glh222

It's as easy as copy > paste.

However after the widgets have been copied, you need to update the data source links.

Take a look at the videos earlier in this thread.

If you have more questions please feel free to get in touch.

Regards

DashboardWidgets

Download and try for free!