David Lyford-Smith is here again with my latest musings on all things Excel from ICAEW.
This month, I got involved in several discussions around data analytics – the value of Big Data, the power of analytics in audit, and so on. It’s all very impressive – the idea of sucking in masses of structured and unstructured data, and performing powerful techniques to extract meaning, detect anomalies, or even to predict the future. But to an average small practitioner, this can all seem like the realm of the big boys – only for giant firms with specialist departments and access to top tech. But Excel can be used for some of this as well.
First in Excel 2007 and increasingly since, Microsoft has been adding more and more to the analytics capabilities of Excel. This began as the analysis tool PowerPivot, and then extended to the extraction and formatting suite Power Query, and the analysis and presentation package Power BI, also available as a free separate software package in its own right. For me, this month was all about looking at the ICAEW IT Faculty Twitter accounts (@icaew_excel and @icaew_itfaculty, in case you were wondering). What could we learn?
Any Twitter user can output a csv file of historic tweets and their performance – retweets, likes, and so on. But the client won’t let you output more than three months’ tweets in one file. How can you turn a folder full of a half dozen csv files into one usable one, and make it so that you don’t have to restart the process the next month? That’s the challenge that I had.
This isn’t so hard as it might seem in Excel. The full explanation is longer than is suitable for this blog, but in essence a properly designed Power Query can take every file saved in a particular location, stitch them together into a single, long table, and then strip out the junk and add other useful information to create a data file ready for pivoting and proper analysis. For me this meant being able to see what time of day had the best response for linking to content, or for starting discussions, and seeing how our success had varied over time. But the same tools could be used for anything where data in a reliable format needs to be drawn together and analysed.
If you want to try it for yourself, there are some great starter videos out there on using (ExcelIsFun on YouTube is particularly excellent), or for the more serious business user, the ICAEW Academy is running a series of CPD courses – on PowerPivot, Power Query and Power BI.
Tip of the Week highlight – Basic statistics functions
Continuing the analysis theme, let’s talk a little about some essential statistics and how to calculate them in Excel. This is drawn from Excel Tip of the Week #176.
|Standard deviation (sample)||=STDEV.S(range)|
|Standard deviation (whole population)||STDEV.P|
|Quartiles (excluding the median from the 1st and 3rd)||=QUARTILE.EXC(range, quartile number)|
|Quartiles (median included)||=QUARTILE.INC(range, quartile number)|
Principle of the Month
This month’s selection from the Twenty Principles for Good Spreadsheet Practice is #16:
Avoid using advanced features where simpler features could achieve the same result.
This is one of the hardest for me personally – having learned so many exciting pieces of Excel-fu, it can be hard sometimes to leave things on the table, or to stop adding bells and whistles. Sure, a macro button could be made to add a new row to a table – but an Excel Table will achieve the same goal more easily. Likewise, a fancy array formula might get the calculation result we want in just a single cell, but using helper columns and splitting the calculation out could get to the same result without sacrificing clarity and ease of editing.
Advanced features have their place, but introducing them unnecessarily raises the bar of Excel knowledge needed to understand your spreadsheet, making it less useful. It also raises the chance that you will make a mistake by using something you’re not as familiar with. Try and stick to the basics!
David Lyford-Smith is a technical manager within the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more. Find out more here