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

#272
Dear Dashboarders

We've unlocked one of the widgets for you below so those of you who haven't purchased the Commercial Version of the dashboard spreadsheet can 'look under the hood' and 'kick the tyres' a bit.
Widget Duplication Exercise.jpg
(13.61 KiB) Downloaded 1745 times
Take a look at the tutorial below.

Hope this will hope you figure out how to duplicate and configure these widgets.

Warm regards

DashboardWidgets
User avatar
By DashboardWidgets
#273
As promised, here is a quick Duplicating Widgets Tutorial using the unlocked widget from the previous post.

Step #1

Create a new Excel spreadsheet with three tabs as follows..

1. My Dashboard Page
2. My Configuration Page
3. My Calculations Page

Step #2

Select the up/down widget from the old spreadsheet ‘Widget Duplication Exercise’ Tab and copy onto your new spreadsheet ‘My Dashboard’ Tab three times.

Your new dashboard should look something like this…
#2.jpg
Step #3

Select the configuration table from the old spreadsheet 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 stretched the cells a bit so we can read the text)…
#3.jpg
Step #4

Select the calculation table from the old spreadsheet and copy onto your new spreadsheet ‘My Calculations Page’ Tab three times.

Your new ‘My Calculations Page’ should look something like this (note that I stretched the cells a bit so we can read the text)…
#4.jpg
Step #5
Go to the “My Configuration Page” and change the titles of the three configuration tables as follows..

1. Monthly Website Visitors
2. Monthly New Website Visitors
3. Monthly Existing Website Visitors

Enter six values into the boxes entitled ‘widget value’ and ‘previous value’ (the value from the previous month).

Step #6

Go to the “My Calculations Page”. Select the first widget VALUE cell (you’ll notice in the formula box it says “=#REF!”) and link it to the “My Configuration Page” first widget value. The best way to do this is as follows..

1. On the “My Calculations Page” select the first widget VALUE cell.
2. Type “=” into the formula bar.
3. Use your mouse to select the “My Configuration Page”.
4. Select the first widget VALUE box.
5. Press enter.

Repeat the exercise for the PREVIOUS VALUE cell.

Repeat the exercise for the second and third widgets calculation tables.

Press F9 to update the spreadsheet and you’ll notice that the PERCENT and UP/DOWN cells will now update automatically.

Now comes the fun part – the “My Dashboard Page” is now ready for building!

Step #7

Go to the “My Dashboard” tab (which should have the three unlinked up/down widgets).

On the ribbon, select “FIND & SELECT > SELECTION PANE”.

Your screen should look a little like this…
#5.jpg
In the selection pane you should see the elements that make up the three up/down widgets.

You may find it helpful to rename the widget elements #1, #2, and #3 as follows…
#6.jpg
Step 8

Follow these steps to link the text boxes to their correct data sources.

1. Select “Up / Down Widget #1 Title” in the selection page on the right hand side.
2. In the formula box type “=”.
3. Using the mouse click the “My Calculations Page” tab.
4. Click the widget #1 VALUE cell.
5. Press enter.

Your dashboard should look like the one below…

IMPORTANT NOTE: This process always seems to revert the textbox back to its default text font size (typically Arial 10). You will need to manually change the font size back to the settings you like!
#7.jpg
Step 9

Repeat Step #8 and link the rest of the text boxes in the RHS selection pane to their correct data sources.
Your final dashboard should look like this..
#8.jpg
You can download this final dashboard here….
(16.39 KiB) Downloaded 1432 times
If you are really adventurous, you could add a dropdown selection box (to choose the month) which automatically looks up the values for the month in a very simple database (without using any macros). There is an example dashboard that does this in the post here…

http://www.exceldashboardwidgets.com/ph ... p?f=9&t=14
Health Facility Dashboard.jpg
Please feel free to get in touch if there is anything that is not clear.

Good luck with your dashboard project.

DashboardWidgets
By bpt
#274
Thanks for the two quick replies and the extensive tute.
I'll have an experiment

Matt.

Download and try for free!