Excel Power Tools - The Future of Excel

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.

Take a FREE course with us

Enroll in FREE course NOW 

Updated* Now includes XLOOKUP

The Ultimate Excel Formulas Course Excel 365

 Explore The Ultimate Excel 365 Formulas course 

Become a Power Pivot Hero

Preview Power Pivot Excel 365 - From Zero to Hero

Before we move on with the next demonstration, I just want to go back over a few things on both Power Pivot and Power Query.

The data that you work on in Excel and in the Power Pivot window is stored in an analytical database inside the Excel workbook.  A powerful local engine loads, queries, and updates the data in that database. Because the data is in Excel, it is immediately available to PivotTables, PivotCharts, Power View, and other features in Excel that you use to aggregate and interact with data. 

Power Pivot was introduced in Excel 2010.  It is an ad in. You have to go to the Microsoft website and download it if you are using 2010.  From excel 2013 and 2016 it depends on which version of MS office you have.

Power Pivot is available in the following versions of Excel

Office 365 ProPlus

Office 365 E3

Office 365 E4 andE5

Its also available in Excel Standalone.

Power Pivot was the first power tool and it was released a few years before power query.  Power Pivot had several options available for connecting to data. Today we kept things rather simple and connected to a linked table but having so many data sources available was the start of allowing Excel users mash up and analyse data from many different sources.  And unlike excel, with these external data sources you are not limted to 1m rows.  As power query has since been released, much of the connection options is now done via Power Query and not Power Pivot.

Demonstrating Excel Power Tools - Power Query

The second demonstration is on Power Query.  In Excel 2016 this is known as Get and Transform data. In this demonstration we will show you how you can quickly create a template that will allow you combine files contained with in a folder.  This is a massive time saver and will make you a lot more efficient with tasks that you might carry out on a regular basis.

This demonstration shows you how power query can be a VBA macros killer. In this video you will learn how to combine a folder of CVS files. We will walk through a complex transformation using Power query(get and transform data) that previous would require a macro to store the steps if you wanted to re-use it.. We will then use this transformation to create a dashboard that can be updated when new files are added to the folder with the click of a button.

GET and TRANSFORM DATA like a PRO
Access Power Query Excel 365 Preview section now and find out more

Best Value

Access All Areas - Unlimited Excel and Excel Power Tool Training

Demonstrating Excel Power Tools - Power View

In this final example we are going to explore some data using power view.  I have preloaded some tables into Power Pivot and carried out some calculations.  The data-set is a makeup coffee shop chain and we wish to use visualisation to explore the data.  The first table has product details, the second table has sales details, the third table employee details and the forth table has store detail.

in this example we will set up a chart and see how visualisations can be used for data exploration and to revile insights about your business.

Getting data into power view is simple really.  If you get the data in power query, you select load to model.  If you have loaded data directly into Power pivot, you just need to go back to excel view and select insert Powerview worksheet

Once you insert a powerview worksheet a new powerview ribbon will appear, you will get a blank canvas and all the tables will be shown in the field list, just like in power pivot.  But instead of just building tables of data we can now create interactive visualisations.

Let hop back over now to our demo and see how we can use power view to visualise data and see how this can be used to identify trends or other areas in need of analysis. 

 

SIGN UP FOR OUR NEWSLETTER AND GET EXCEL & POWER BI TIPS TRICKS AND TUTORIAL TO YOUR INBOX

SIGN UP NOW

Earn and Learn

Comment below and be in with a change of earning STEEM rewards for your engagement.

Did you share this post?  Comment below and let us know

Which Excel Power Tool do you use the most?

Where would you put your Power Tools skills?

1. poor
2. average
3. above average

Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.

We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.

Find out more now and start earning while you are learning Excel and Power BI

SIGN UP FOR OUR NEWSLETTER AND GET EXCEL & POWER BI TIPS TRICKS AND TUTORIAL TO YOUR INBOX

SIGN UP

---

Cross posted from my blog with SteemPress : http://theexcelclub.com/excel-power-tools-the-future-of-excel/

---