I’m just back in the office after attending Excel Summit South here in Sydney last week and I’m still processing it all. For the past few years, some of the Excel MVPs from around the world do a tour Down Under and tell us all about the cutting edge features of our favourite software:) MVP stands for Most Valuable Professional awarded by Microsoft to just a handful of professionals in recognition for their contributions to the Excel community. It’s just a fantastic experience to have two full days surrounded by these experts of the industry. I had a great time catching up with old friends, and making new ones.(more…)
Heat maps have evolved considerably in recent versions of Excel from a matrix of colours to become a powerful graphical representation of data displayed in a dashboard. Excel has the capacity to add conditional colour to images based on an agreed set of parameters or key performance indicators. With a little corralling, a heat map nestled inside a dashboard can add a powerful visual element to an otherwise plain and simple report.
You will hear from Dashboards and VBA expert, Marcus Small who leads us in this highly practical and hands-on workshop session. This 60-minute presentation covers:
- The creation of an Excel heat map from scratch. Generates the scalable vector file (SVG) from Wiki Commons and passes it through Inkscape to Excel.
- Builds in the formulation and creates the VBA which will generate a predetermined colour scheme for a dashboard where the heat map will form the centre piece.
- Layout and design when creating a dashboard, maximising design with very little effort.
By the end of the session you will know how you can create an interactive heat map using just about any imaginable image and include it in your own Excel Dashboards.
Marcus Small holds a Master’s Degree in Finance from ANU and has worked in the Big 4, Investment Banking and within industry both here and overseas. He is the founder of www.thesmallman.com, a website dedicated to Excel and VBA development with an acute focus on dashboard design and financial modelling. Additionally, Marcus moderates both Ozgrid and Chandoo forums, which are amongst the web’s largest online Excel resources.
Nothing conveys complex information more quickly and effectively than a well-built and visually appealing infographic. They are designed to entertain as well as inform, and the popularity of the infographic in social media has made them an effective way to distill complex information and draw attention to issues that may have otherwise been overlooked.
The Table feature in Excel (not to be confused with PivotTables or Data Tables!) is a great tool for organising and analysing large amounts of data. For this reason, like PivotTables, they are sometimes not used in pure financial modelling, but as a very useful tool in reporting and analysis, they are also worth a brief mention. See this article for more information about the use of Tables in Financial Modelling.
The two useful features of a Table are:
Some of you will know that ever since Indian Excel guru and blogging sensation Chandoo visited Australia in 2012 to run his extremely popular Advanced Excel and Dashboard courses, I’ve been trying to convince him to come back Down Under and run another series. We are hoping that he’ll find time to make another visit in 2015 and we’ll certainly keep you posted and hope to announce it soon. He hasn’t agreed to come just yet, but as we were chatting last week, he did talk me to into joining
If you have sluggish Excel models, it might not just be the file size that is slowing things down. Often it’s the memory being used and when things get really bad, you might even get a “Not enough Memory” or “Not enough system resources to display completely” error message. When you get this frustrating message, the only solution is to close Excel and restart it, so let’s look at some ways to improve the performance of your Excel models.
Why do they keep changing my Excel? As we all rely on Excel every day for our financial modeling and analysis work, it’s only natural that any new release will generate a lot of excitement as well as anxiety amongst users.
LOOKUP functions, especially VLOOKUPs are very commonly used in financial modelling – sometimes a little too commonly used! Knowing when and how to use them – and use them well is a critical skill for any financial modeller. Let’s explore some of the problems with LOOKUP functions and how to make their more robust.