Exceleration

One of the things I have been telling my students for a couple of years now is that the world of data analysis is changing #rapidly. A couple of weeks ago I was very fortunate to be invited to attend the Unlock Excel conference held by SUMPRODUCT.com and CPA Australia. The experts at the conference, whom I admire greatly, were equally saying as much. Microsoft had a presence and were showing some of the cool new tools which will be coming in future iterations of Excel and in the Power space. The whole BI montage is changing - the movement in the environment is startling.

Power Pivot, Power Query and particularly the big daddy, Power BI, are omnipresent when it comes to transforming, manipulating and reporting on data. Gaining their knowledge will be standard fare one day and best you get on board sooner rather than later. The programs effectively use an in-memory process that runs directly within Excel (Power Query and Power Pivot). It is referred to as an Internal Data Model and the speed at which it performs is mind blowing. Its real power comes when you have the tables connected and have written your DAX (Data Analytics Expression – I know DAE right? - I guess DAX sounds sexy) and it is time to report. The traditional pivot table is wholly one dimensional, on the most part linked to a single data source. However, the Power Pivot's back end allows you pull multiple tables together making your pivot tables multi-dimensional.  

Right now, is such is such an exciting time and if you are in the data analysis space - tool up now, as you don't want to find yourself short of the mark if redundancy comes unexpectantly not so much knocking but pounding at your door. If you have not taken those tentative first steps, just remember that the first steps are the hardest. These sage words might help – ‘knowledge builds on knowledge’, what once seemed difficult to me is now simple. 

A few years ago I noticed an Excel samurai, who goes by the handle of Jindon, based out of Tokyo, delivery game changing VBA code, predominantly at the time, on Ozgrid. I was amazed at how insightful the coding was and at how quickly it ran. Buoyed by this, I went about learning how to code using Excel’s Scripting Dictionary. The first steps were difficult but every time I went back to the well, it became a little easier to grasp the concept. Scripting code is more prevalent now, especially on sites like Stack Overflow (which I think is the most on point VBA dev site on the web) but still very few people either know about or can write and understand it. (This article might help Scripting Simplified). However, the point is, the journey started with a single decision, to start and I am delighted I did. 

So the mantle is over to you – take those first steps towards understanding how to manipulate, manage and report on data with Excel’s Power BI tools and you will be laughing. Above is an example of an Excel front end with a Power Pivot back end. The results draw from over 300,000 rows of data and bring the information into a succinct summary which tells a compelling story. 

Wednesday week, 16 May 18, I am presenting at the University of Queensland on all things Excel – basically around financial modelling and reporting but I shall be telling the students about the changing nature of data management in the workplace. Don’t get left behind – jump whole heartedly into the BI space, Microsoft have the most wonderful platform!!!

About

Excel is more of a passion than a program for me. Creating my own site was like creating a small part of me. Everything has been built with due love and attention and I have designed my site with the premise 'what do I find interesting'. This seems to have held me in good standing as I regularly search for things and see my site on Google for some obscure post I devised years ago - it always makes me laugh out loud.

My company Thesmallman.com is the preferred supplier for CPA Australia Excel training program. I try and make my training insightful – with every lesson focused on practical application of Excel in finance, based on 15 years in Big Four, investment banking and industry exposure. I want the office guru and the person learning Excel to equally walk away with something when they attend my training. 

I have been recognised by blog ranking sites, Rankedblogs.com, Blog.feedspot.com and Spreadsheeto. I will continue to post what I find interesting in the wonderful world we all like to Excel in.