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
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:
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.