For years now Accountants and bookkeepers around the globe have used Excel as their Spreadsheet tool of choice. It offers massive flexibility, speed and is as accurate and reliable as the users.

But to this day, some Excel users never go beyond the basic totting and cross totting of rows and columns. Excel has come a long way since it was released. Every few years we see a new and improved version adding more and more capabilities.

Excel is no longer just a tool for totting and cross totting but a powerful business intelligence tool equipped to handle masses of data on the fly. With the ability to create sustainable models and templates effecently.

Once, Pivot Tables were once hailed as the most powerful feature of Excel. They allow slicing and dicing of a data table so you can analyse and report within a matter of minutes. They can carry out complex calculations on data that without Pivot tables you would need to know complex array formula.

Excel Power Tools – The Future of Excel

in 2010 Microsoft introduced Power Pivot as the first power tool. Equipped with its own language know as DAX (Data Analysis Expressions), Power Pivot can slice and dice data like never before. Complex calculations can be carried out across different tables of data with ease. No longer are you limited to row count. Data is stored in a data model and not the spreadsheet and give you massive compression. If you know how to use pivot tables, they you are half way there. This Excel tool is awesome, and anyone can learn to use it..

In 2010 I hailed PowerPivot as the most powerful feature of Excel. But this was just the first step by Microsoft to bring the power of Data Analysis to the Excel User.

2013 saw the launch of Power Query and Power View. Power Query, known as Get and Transform Data in Excel 2016 will allow you get data from any source. Internal data, external data, structured data and unstructured data. You can then transform that data into a usable format and run it through Power Pivot. There you can carry out any complex calculations, comparisons or modelling that you need.

But what is Business Intelligence without visualizations? Why spend hours poring over tables of data that you have prepared, when you can use visualizations to tell its story? Power View, the second power tool addition in 2013 will allow you do just that. It will allow you create interactive dashboards to visualize the data that you have.

With Excel, you now have the ability to take advantage of the Big Data trend by using both internal and external data. From this you can gain actionable insights to your business without the need for a complex IT department.

2016 Excel BI is the most powerful feature of Excel.

Demonstrating Excel Power Tools – Power Pivot

The first demonstration is on the first Power Tool, Power Pivot. This was introduce in 2010 as an add in. Ipower pivot 2010. If you are using Excel 2010 you will need to head over to the Microsoft website and download it. It is free, so you don’t need to worry about payment or licences.

In this demonstration we will look at creating standard pivot tables. When creating a standard pivot table, it is often necessary to preform several VLOOKUPs to pull data from other tables into the one table. This can be rather heavy on resources, especially if you have a large set of data. This is a limitation of standard pivot tables. Another limitation is once you create a standard pivot table, you do not have the option to move the data from the pivot table into different places on your workbook.

With Power Pivot both limitations have been removed. You no longer need to carry out multiple VLOOKUPs to add the data into one table as PowerPivot will allow you pivot data from multiple tables. With PowerPivot you can also removed the limitation of formatting the data.

This demonstration shows how you can overcome some simple problems with Power Pivot. With Power Pivot you can do a lot lot more than what we shown. This is an amazing tool that will allow you carry out complex calculations and modeling using DAX (Data Analysis Expressions). DAX functions are based on Excel formula with a few more functions thrown in for extra power.