• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Dash board Tutorials

I am neither a beginner nor an advanced user in Excel. I have not been using VBA scripts so far. I need to learn about dynamic Excel dash boards. Do I need to possess knowledge of VBA if i intend to pursue this course? Thanks
 
Hello shenbagavalli. Although it depends on what your exact requirements and level of complexity are, the short answer is No, absolutely not.


I began learning Excel, virtually from scratch, in August 2011. I can now build dynamic and interactive dashboards and still have not used, nor yet learnt, VBA.


However, With hindsight, for me, the priority of learning and correct approach would be as follows:


1. Understand PIVOT CHARTS (If Excel 2010, Include Slicers - excellent).


I have (yersterday) just learnt that I can create 95% of my Excel models in Pivot Charts in 10 minutes. I used to create very complex formulaic models over days (usually my evenings). I have, for example, created a chart which ranks personnel by performance and updates automatically once data is refreshed from an sql server. This chart has several sub charts, making it a dashboard. The formulaic model took me a week to create and is slower and much harder to hand over to managers that have little understanding of Excel.


2. Learn formualas from the Chandoo.org Topics on the home page.


The most powerful, fun and most used for me are:


a. SUMPRODUCT

b. IF

c. INDEX/MATCH - WOW, wow, wow

d. Offset (inc row(), Column() and Indirect())

e. Named Ranges - a must for dynamic anything

f. Date formats (to include Coersion for date manipulation)

g. Controls from Developer Tab.

h. How to create labels for dynamic labelling within charts and tables.

i Arrays - for when sumproduct will not do.


In learning the above, you will be taken on a journey of discovery and pick up so much more than you thought possible. Where you go from there is up to you.


Let me just say again though, PIVOT CHARTS - really, these are so very amazing. I overlooked them, and learnt formulas first, as I was desperate to understand Excel quickly. I was so very very wrong. Having just re-created most of my reports in PIVOTS (proving to myself that it can be done), I will now 'prototype' with Pivots at work. Only when there is a need will I create formula models of pivot charts. Pivots have freed me up in my spare time. I will now now relax in the evenings and not panic that I can't produce what is needed for work.


Hope this helps.


LeonK
 
Back
Top