Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
Three week ago I visited the Newton Excel Bach web site where I spotted the Dynamically Defined Dancing Pendulums NewtonExcelBach.
Having noticed that Doug had done a nice animation in Strand7 (a Finite Element Analysis program) Strand7, I thought “I can do that in Excel” and so I did.
This post will not go through the logic of constructing and animating the pendulums in Excel as I have described that over at Excelhero.com and readers who are interested are encouraged to visit there and explore the techniques used.
During the Pendulum project I came across two sub-projects which I felt are worthy of a post here at Chandoo.org as they are a great demonstration of some basic VBA techniques as well as demonstrating the ability of VBA to quickly simplify repetitive tasks.
Sample files are attached for Excel 97-2003 and Excel 2007/10 users to follow through the examples.
PENDULUM SIDE PROJECTS
The Pendulum project consists of 16 Pendulums. Each Pendulum requires 4 Named Formulas, meaning that the projects needs 64 Named Formulas for the 16 Pendulum, as well as adding 16 Series to the chart.
(The above animated GIF is a very poor representation of the smooth scrolling achieved in the Excel animation)
I thought it would be a great idea to see if these jobs could be automated and hopefully save some time during the process.
The result was 2 simple VBA routines which will be described below:
NAMED FORMULAS
Each Pendulum in the project was based around 4 Named Formulas
Named Formula for each Pendulum
p1Len =’1′!$B$9 The Length is stored on the worksheet.
p1o =OMax*SIN(SQRT(g/p1Len)*t) Current angular position of Pendulum 1 at time t
p1x =p1Len*SIN(p1o)*{0;1} Current orthogonal X position of Pendulum 1 at time t
p1y =-p1Len*COS(p1o)*{0;1} Current orthogonal Y position of Pendulum 1 at time t
The only difference between the formulas for Pendulum 1 and Pendulum 2 etc is the replacement of the names of p1 with p2 in the various Named Formulas and of the associated formulas.
On a worksheet Named Formulas a number of formulas were written which display the Named Formulas as required above. Then a small VBA routine was written which loads the Named Formulas.
How
On the Named Formulas worksheet, I have added two columns of formulas for the various Named Formulas required.
For the Pendulum Length Named Formulas
For the Pendulum Angular Position Named Formulas
For the Pendulum X Position Named Formulas
For the Pendulum Y Position Named Formulas
When the formula above are copied down they adjust for the various pendulums numbered 1 to 16 based on the row numbers.
I then setup a VBA routine, Load_Named_Ranges, shown below which loads the Named Formulas.
To Use
Select some or all of the required Named Formulas from the Name Column. That is the code will only load the Selected Named Formulas, allowing the user to load 1 or 2 Named Formulas, for testing purposes, or all the Named Formulas if you choose.
Then Execute the Load_Named_Ranges subroutine either using the Big Red Button or directly within the VBA Editor.
The following will load Named Formulas p3Len to p7Len.
The Load_Named_Ranges subroutine is shown below:
Sub Load_Named_Ranges() Dim c As Range For Each c In Selection ActiveWorkbook.Names.Add Name:=c.Text, RefersTo:=c.Offset(, 1).Text Next End SubWhat does the code do?
The code:
1. Defines the start and name of the subroutine,
Sub Load_Named_Ranges()
2. Defines a variable c as a Range object,
Dim c As Range
3. It then loops through each cell in the selection and assigns it to the variable ‘c’;
For Each c In Selection
4. It then adds a new Named Formula,extracting the Name from the Text Value of ‘c’ and extracts the formula from the cell directly to the right of cell ‘c’;
ActiveWorkbook.Names.Add Name:=c.Text, RefersTo:=c.Offset(, 1).Text
The Name and Formula (RefersTo) both use the Text of the cell, which is what is displayed.
5. It then loops through each cell in the selection until it has done them all;
Next
6. Defines the end of the subroutine;
End Sub
Lets Test It
To test the subroutine we will first delete all the Named Formulas beginning with “p”
Goto the Formula Ribbon Bar and select Name Manager
Select all the Named Formulas that begin with “P” and press the delete button
Accept any warnings
Try and Run the Pendulum’s
Nothing happens as there are no formulas
Ensure the Pendulum are turned off, as the code is still running behind the scenes.
Now Goto the Named Formulas Page
Select all the Named Formula Names in Name Column; B3:B66
Click the Load Named Formulas, button
Go back to Page 1 and try and run the Pendulums now.
ADD CHART SERIES
The second sub-project was the addition of 16 Chart series to the Chart, 1 for each Pendulum.
Using the logic of the Named Formulas VBA code above, the 16 Chart Series Names, X Values and Y values were developed using formulas on the Add Cht Series worksheet and then loading into a chart using a simple VBA routine.
The Add_Cht_Series subroutine is in the Add Cht Series sheet object in the VBA editor.
How
On the Add Cht Series worksheet, I have added three columns of formulas for the various Named Formulas required.
For the Pendulum Name, X Range and Y Range.
When these formulas are copied down they adjust for the various pendulums numbered 1 to 16.
I have then setup a VBA routine, Add_Chart_Series, shown below which loads the Named Formulas.
To use Select some or all of the required Chart Series from the Pendulum Name column.
Then Execute the Add_Chart_Series subroutine using the big red button.
The Add_Chart_Series subroutine is shown below:
Sub Add_Cht_Series() Dim sNumb As Integer Dim c As Range Worksheets(“1”).ChartObjects(“Chart 5”).Activate For Each c In Worksheets(“Add Cht Series”).Range(“B19:b20”) sNumb = ActiveChart.SeriesCollection.Count + 1 ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(sNumb).Name = c.Text ActiveChart.SeriesCollection(sNumb).XValues = c.Offset(, 1).Text ActiveChart.SeriesCollection(sNumb).Values = c.Offset(, 2).Text Next End Sub
What does the code do?
The code:
1. Defines the start and name of the subroutine,
Sub Add_Cht_Series()
2. Defines a variable sNumb as an integer, and a variable c as a Range object
Dim sNumb As Integer Dim c As Range3. It then activates the Chart containing the pendulum
Worksheets(“1”).ChartObjects(“Chart 5”).Activate
4. It then loops through each cell in the Range defined by the Range, in this case B19:B20 and assigns it to the variable ‘c’; You can adjust the Range to suit.
For Each c In Worksheets(“Add Cht Series”).Range(“B19:B20”)
5. It then counts how many existing series are in the chart and sets the next Series Number sNumb to that value + 1.
sNumb = ActiveChart.SeriesCollection.Count + 1
6. The next 4 lines add a new series to the chart and setup the new series Name, X Value and Y Values. The Name, X Value and Y Values are retrieved from the Text of the cell c and the adjacent two cells using a Range Offset modifier
ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(sNumb).Name = c.Text ActiveChart.SeriesCollection(sNumb).XValues = c.Offset(, 1).Text ActiveChart.SeriesCollection(sNumb).Values = c.Offset(, 2).Text7. It then loops through each cell in the selection until it has done them all;
Next
8. Define the end of the subroutine;
End Sub
Lets Test It
To test the subroutine we will first delete a few of the Chart Series
Select the Chart
Select two Pendulums noting the series Number of the Bobs eg: 9 & 10
Goto the Add Chart Series Worksheet
Note the Range Corresponding to the 2 missing Pendulum B11:B12
Goto the VBA Editor
Adjust the Line
For Each c In Worksheets(“Add Cht Series”).Range(“B11:B12”)
With your cursor in the Subroutine press F5 once only
Go back to Page 1 and you should now have 2 New Pendulum
Run the Pendulums now.
You will have to manually set the shape of the Bobs to a Circle and size 15 and re-arrange the order of the series to ensure they are in order, but you can practice that manually.
SUMMARY
The post has shown how using some very simple VBA and a bit of lateral thinking to put together some simple tools to simplify 2 common and repetitive tasks.
In the Named Formulas case, the code took less than 2 minutes for me to write and then another 5 minutes to do the formulas for the Named Formulas. I didn’t try but I am sure it would have taken a good 20+ minutes to enter 64 Named Formulas.
Writing this post took much longer than doing the whole Pendulum Project.
Two examples during my working career, where VBA code has been used to save massive amounts of time and money:
In the first case I wrote some code to combine data from several hundred workbooks with varying numbers of sheets up to 30 and differing quantities of data on each sheet, a task that could have taken weeks manually with the included opportunity for errors to be introduced, into a subroutine which took 30 minutes to run and gave a printout of the results including what files, sheets and rows of data were included in the import.
In a second case a Number of Workbooks, a Word template and some VBA code was used to replace a person whose sole job was to manage that data. This job saved the company $50k+ per annum and the task was given to a clerical person who could now do the task in their spare time.
LINKS
Huis Excel Hero Pendulum: http://www.excelhero.com/blog/
Pendulum Physics: http://hyperphysics.phy-astr.gsu.edu/hbase/pend.html
Newton Excel Bach: http://newtonexcelbach.wordpress.com/2011/05/25/dancing-pendulums-2/
What could your simplify by using automation within Excel ?
What could you simplify or speedup using Excel automation?
Let us know in the comments below:
65 Responses to “Make Dynamic Dashboards using Pivot Tables & Slicers [Video & Download]”
WOW, is all I can say.
I could not have imagined a dynamic dashboard without getting approved software budget and a team of people involved to create it. Given that I am a relative newbie to excel and actually got here by looking for pivit table help, I imagine that i would not be able to make anything myself. But armed with the demo excel sheet I will press buttons (and I will report back how that went;-)
Claudia
Good stuff Chandoo, thanks
The slicer buttons take up quite a bit of room on the dashboard
Is there a way to make the buttons smaller so we can have more room for charts, tables, and commentary?
Kind regards,
Winston
You can resize the slicers! When you click the slicers you can change the height and width of columns and slicers. You can also, under slicer style click "New slicer style" where you can define your own style, which enables you to change most things, including font size.
I hadn't seen the Group Option used as you did for the Duration PivotTable. And thanks for showing how to remove the Field Buttons on a PivotChart, I loathe them with all my heart.
Fantastic design and a great dashboard.
@Claudia.. I am glad you like it. Do let us know how your adventures go.
@Winston: You can resize slicers or increase the number of columns inside. Unfortunately, we can not readjust the font sizes in slicers. So when you resize, you will see partial text.
@Gregory: Thank you. I am happy you like it 🙂
Hi Chandoo, your dashboards are really professional and simple. I do have some question, if I have the following scenario, could you help to advise : -different data sources eg monthly
-calculations percentile
-%difference between financial year
Thank you so much!
Hi,
Thanks for your great information.It has helped me a lot.
Now,I can build my excel addin for Excel 2010 better with your tips.
Hi chandoo i am new reader for ur site.and really found good stuff and temp. But i suggest u 2 put a guidance step sheet in temp so anyone can understand easily.and also help me to become awesome as ur noume.
[...] [Related: Dynamic Dashboard using Pivot Tables & Slicers] [...]
Chandoo, Wow these are very powerful reports. I will be implementing them straight away. It will save me hours of work. Thankyou so much.
Hi Chandoo,
I love the Slicer, but how do I link a slicer for different data sheets e.g.: Client data on one tab and products on another tab, as I find that as long as you use pivot tables off the same data you can link the Pivot tables using Slicer connections.
Regards
Paul
I appreciate the work you have posted on your website - very informative and easy to understand. I just wanted to inform you that you can make selections within the slicer too by using Ctrl and selecting the fields you want to group and use as filter.
I had a question regarding the data used in pivot tables. Is there a way to update the data (eg. a new customer entry) and have the pivot tables and the linked charts in dashboard automatically update? I will search for the answer in other posts so ignore if you have covered it elsewhere.
Thanks again and keep up the good work.
-Vivek
Dear All,
Me too is a die hard fan of Slicer. it's requirement was arise when management is feeling it difficult to juggle with filters for sales of a particular location, Product Category in Pivot Table.
Got very positive response when introduced to tackle the above situation. furthermore in slicer setting there would be option to enable or disable deleted data is handy for particular scenario.
These are eye catching color themes would be like icing on the cake.
There is one more feature of excel 2010 which proves to be tool for great time saving is "Repeat Labels" in Pivot Tables.
This is fantastic!! Your steps were super to easy follow. I can't wait to show my new dashboard off to the boss. Thank you so much!
This might be a little unrelated but I'd like to know which software was used to record your on screen actions? I'd like to use it for tutorials on models that I build for my customers. Thanks!
@Van
Have a look here: http://chandoo.org/wp/about/what-we-use/
The slicers are coming in a sorted order... How can i get it in the way it appears in my original data.... The settings show to sort them A to Z or the other way round but they are option boxes and can not be unchecked... What are my options????
[...] Using slicers to make a dynamic dashboard in Excel [...]
I watched the video and then worked through an example of my own, also telephone costs by coincidence. It took me about 30 minutes to do everything. Once you've understood the basics of pivot tables and slicers, all that limits you is your imagination!
The only thing missing from the video is now to change the number of columns in a slicer: Right click a slicer then Size and Properties, Position and Layout, Layout, Number of Columns ...
Good page and video.
Duncan
How do you insert 'Year' in the Pivot Table Field List if it doesnt exist in the Master table???
Thanks
Hi,
Can I disable the multi-selection of the slicer to only allow one selection at a time?
Thanks in Advance
@Manu.. as of Excel 2013, this is not supported yet. But you can remove slicer heading, clear filter button and style it so that it looks like a single selection. You can also use Macros to ignore previous selection upon multiple selection, but I would not recommend it.
For an example on styling see - Interactive Pivot Calendar
Awesome guide! The dashboard I made blew people away. I do have one question. I want the chart title to match what I have selected. How can I do this without writing macros?
@Devin
Lets say what you have selected in in A1
Select the Chart then Select the Title
Click in the Formula Bar and type =A1
enter or click the small arrow to the left of the Formula Bar
Enjoy
Love the slicers and use them often in my dashboards. Question about the data (specifically the date) I see the "date of call" column but was wondering how were you able to filter on slicers by year and month when there is only a date of call entered into the data?
Thanks for your help!
Thanks for taking the time to create this interesting and very useful tutorial!
I was able to create a similar dashboard in a short time after watching your tutorial. The problem I am having now is how to update the pivot tables and dashboard graphs when a change is made in the raw data. I tried two methods; Change data Source and Refresh. When I used Change Data Source (Options-> Change data source) the values in the pivot tables didn't update. When I tried refresh the values in the pivot tables disappeared as well as the information in the graphs, since the data in the pivot tables no longer existed.
I have been searching for a solution for a while now but I have unfortunately not been able to solve this problem yet. Any help someone can provide is GREATLY appreciated.
All the best
Hi, looks great, but how valuable is power view when it comes to financial data? I've been having trouble trying to visualize how I would use power view to report of financial data.
Hi Chandoo, you are awesome! Thanks for the good work!
there is duplication for my slicer, probably cause i choose date, time as my options. i changed it to date but still theres a duplication of the same date
Just Great! Thank you for the time to put this together and teach us.
Alex Cardoso from Indaiatuba, Brazil.
First of all I would like to thank you guys for this post I used this amazing tool with the help of your tutorial to create a dashboard for one single account and my regional manager said "good job, it looks very profesional" she was so impresed that now she wants one daschboard with all the acounts and services she is going to replace her KPI reports with my report !! I smell a promotion!! My demand was a new laptop with MS 2010 and it was granted. now I have allot of work and many many questions to post .. kudos
Hi Chandoo
I want to say thanks first because i loved ur tutorials
i have a small doubt how to insert slicer from external connections
i searched every where could you please explain how to insert a slicer from external source
@Krishna Prasad
use external source data as pivot table then you will be able to use slicer.
Hello Chandoo,
How to get rid of the > items in Months slicer?
They are appearing when there is a grouping on the date field in pivot
Thanks
Hi Chandoo,
One problem always bothers me when i use slicer. I have no idea aobut how to change the number format in slicer. Want to display number in slicer as general format, but it always displays other number format such as date.
I check my source data and it doesn't effect the number format.
Look forward you or any EXPERTS to solve it. Thanks very much!
In the end, This website is awesome!!!
Hi Emma,
Were you able to resolve your query? I have a similar problem. I use Excel 2013 and the field I'm dropping into the slicer is a currency field ($1.00, $1.05, $1.10 etc.) representing the exchange rates that the user can choose from. The items in the slicer revert back to general format (1, 1.05, 1.1, etc.) although the source field is formatted as currency field. Is there a way to fix this?
@Sunil & Emma: You can create a new column in your raw data which has currency as text, using the TEXT formula like this =TEXT(currency_val, "$#,##.00"). Use this column to create the slicer.
Thanks for the response Chandoo. It works as you suggested. However, if the users were to pick more than one item in the window I'd like to know what is the max value and utilise that value in a DAX formula.
Also... there is no issue if I were to throw a slicer over a normal pivot. The trouble comes when I choose the 'Add this data to the Data Model' option which I need for the PowerPivot.
Hi Chandoo (Or others)
Is there a way to make the color change, when the value changing after the use of a slicer?
Lets say the value is 4,5, when i press the slicer, and the value change to 3,5 i would like the color to change. Can anyone help?
Thank you.
Hi Chandoo,
It was very useful video for me. Thanks.
But I have one question to ask.
How can I connect data which is growing in size (rows, records) by time (daily, monthly etc.) to this kind of dashboard?
Or it is only on select number of data?
Thank you.
Chandoo zindabad!
Hi Chandoo,
I have been able to create something similar quite easily. The problem that I am facing is that I want to keep the Top 10 filters permanently. If I select one option and then clear the filter, the chart removes the Top 10 filter; I want it to go back to Top 10 filter.
Is there a solution to this problem?
Regards
Thanks a lot for the tutorial and for the demo file!
I have the same problem of Angela: after clearing the filter applyed on P1, the filter on P1 shows all the customers without filtering top 10 (as it was before).
Thanks!
Federico
Go to your pivot table, right-click and choose "pivot table options." On the "Totals & Filters" tab check "Allow multiple filters per field."
Justin, thank you so much!
now after clearing the filter applyed on P1, the filter on P1 shows again top 10 customers.
[…] Slicers – how to use them – case study […]
Chandoo!
Just find out your website, I´ll follow your tutorials from now, very useful!
Great thanks from Brazil!!!
Very useful. Learned a new skill today. Thanks a ton!
Hi Chandoo,
This is fantastic! It's going to really help me with some operational reports I develop regularly. Two questions I'm hoping you can answer for me:
1. How can I use one slicer to manipulate two different pivot charts that came from two different pivot tables?
2. If I have a slicer in an excel and share that with someone who is on older versions of Excel - what will it look like to them?
thanks!
Hello Chandoo!
I love the dashboards and have been able to make quite a few, my puzzle is when I am connecting the pivot charts to the slicers, I have to do each individual one and check every single slicer (usually I have about 12, so I end up having to check the 12 check boxes 12 times to connect everything) am I missing something? Is there an easier way to do this?
Thanks!
elisa
Hello Chandoo,
You make my life easier, am in love withe the slicers!
I greatly appreciate
Thanx
Hama
[…] Slicers. Easy for me to do, but not as easy to explain how I did it. Fortunately, Chandoo has a Make Dynamic Dashboards using Pivot Tables & Slicers video and download that will do the job nicely. Suffice it to say it took me <3 minutes to put […]
thank you very much..... 🙂
You are a legend!! Thank you so much - very clear, very helpful indeed.
nice player...
i like to play like chandoo sir.
i learn somthing about slicer by watching posts.
it was too difficult to watch and easy to prepare..
thank you boss.
God Bless You
Hi,
I've built a dashboard on Excel 2010 using Pivot tables and slicers.
What I would like to do now is duplicate the dashboard on another tab, having it extract from another data source (format is identical to the 1st data source).
I'm extracting the same metrics, but each data sources measure different product lines.
Could anyone help me out?
Thanks in advance,
M
@M
Can you please post the question at the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file for a quicker more targeted response
Thank you so much. I learned so much about the slicer because of the video. Just got a quick question. Say I got 100+ Customer name bottons in one of the slicer, and it is time consuming to scroll up and down to find the one to select. Is there anyway I can set in the slicer setting that when I type "E", it automatically take the selectionto to where all the "E" starts? Thanks
Hi there,
This looks great - is there a way I can use it to compare vs budget, forecast? Is it just a case of renaming one of the field Comparison with the data being "Actual, Budget, Forecast"?
Thanks!
hello master!
please help me.
i am looking for many file example for Dashboard, but because my English is weak i couldnt fint it in hear.
please help me.
thankyou so much.
@An
Goto: http://chandoo.org/wp/welcome/
Have a look under dashboards http://chandoo.org/wp/excel-dashboards/
Also use the Search Box at the Top right of every page at Chandoo.org and search for Dashboard
thank you brother.
i love all of you!
Dear Excel Guru,
Hope everything is fine with you?
Can you please help in this Logic, it is a thought only to increase my knowledge SIR?
Please note that I have been working in Excel file contains two times of our teammates who claims overtime an each calendar month
My excel file as like this :-
ROW 1 Days of Month
ROW 2 Date of Month
Cell -1 [Time IN(06:00Hrs)], cell -2 [Time OUT(15:30Hrs)] no break in our factory and anything after Eight hours assume as overtime as standard in all across.
Appreciate if you could help me in providing the best an Exclusive Excel formula to calculate each day overtime excluding staff eight hours regular duty and Friday consider as full day overtime.
Kindly help me at the earliest convenience.
awaiting for your expertise.............
Best Regards / Ikram Siddiqui
Thank you for video , will you please provide pivot table with header and sub header like year main header and under that three sub header. How to make dashboard for that.
Dear Sir,
How to seperate amount, mention in remarks.