Microsoft Excel is the most commonly used tool for management accountants. Its versatile software enables the analysis, reporting and sharing of important data. However, many accountants are unaware of two clever Excel add-ins that massively extend the functions of this indispensible software.
Built by Microsoft, Power Query is a data discovery and query tool, allowing accountants to shape and collate data before a row has even been added to the spreadsheet.
It saves time on importing data, as it collects only the data needed from external sources, including big data sources like Facebook. It also merges data from a variety of sources with ease, bringing the results into Excel as a single table, ready to be used.
After all the relevant data has been collected by Power Query, it can be used with Power Pivot for further modelling and analysis to enrich your spreadsheet.
Also built by Microsoft, it slots in seamlessly to Excel, with a new tab added to the toolbar. It also extends what users already know about working with data, so any Power Pivot training need only be brief.
It’s still working with formulas, but with the capability to do much more than would be possible on standard Excel.
All data can be enhanced for analytics in Power Pivot, including creating relationships between tables, adding new formulas, creating key performance indicators (KPIs), and organising fields into hierarchies.
Power Pivot adds more than 100 new functions. No longer limited to old favourites like SUM, extras include ‘Countrows’ and ‘Switch’. These all work in pivots, and can auto-adjust if the pivots change. Once a formula has been written, it can be re-used anywhere, conveniently eliminating the need for re-writes. This is incredibly convenient as it eliminates the need to find somebody with a very rare set of skills or invest in lengthy training – the work is done for you.
Users can also be let loose on these functions without the need for IT or accounts staff to help them, meaning managers in a business can create self-serve reports.
The data capacity of Power Pivot is virtually limitless, so no matter the amount of data, calculations can be done in the same way. Hundreds of millions of rows can be treated the same as a hundred – even with multiple or linked tables.
Alongside creating so many new capabilities to Excel, it speeds process up, saving valuable time. Data sets can be linked and spliced almost instantly. Analysis that normally take days can be reduced to less than an hour, and any adjustments that need to be made only take seconds – a huge bonus for any business, as manual adjustments can sometimes take as long as starting from scratch.
The Power Pivot Management Dashboard also enables IT administrators to monitor and manage any shared applications, increasing the security, availability and performance of your spreadsheets.
Power Pivot reports can be shared with others and automatically update as the data changes, meaning no more out-of-date reports circulating around a company.
Both Power Query and Power Pivot, although they are time and cost-saving and hugely increase the capabilities of Excel, are free add-ins, so there is no reason why management accountants explore how they can improve data collection, organisation, and analysis for businesses.