The fourth video in my "Complete Introduction to" series focuses on Excel Power Pivot. Power Pivot is another incredibly powerful feature within Excel. It was first introduced to Excel in 2010 as an add-in, which was freely available to all Excel 2010 users. In Excel 2013 Microsoft integrated Power Pivot into Excel, but sadly created separate licencing for the product so now you require a Pro Plus license to access it.
Why is Power Pivot so important to Excel users? Because it is a powerful data analysis tool that does not have a number of the restrictions that Excel does and eliminates the need to use complicated formulas like VLookUp and HLookUp. It can store millions of records of data, easily create relationships between different tables of data and introduces the DAX formula language, which is broadly based on the Excel formulas we already know. Once data is loaded into Power Pivot, you can use Pivot Tables and Charts to analyze the data, just as you would using standard Excel. When you start using DAX (Data Analysis eXpressions) in PowerPivot, you will quickly see the similarities it shares with standard Excel formulas.
In the video I create some simple calculations to show the concept of a calculated column and how to add a measure to the Power Pivot data model. Measures are calculated in the context of the Pivot Table or Pivot Chart and this allows the creation of powerful business intelligence calculations within Excel. In addition to the standard Excel formulas, DAX adds a wide range of new formulas to enhance your data analysis capabilities. One example is the distinct count function which counts the number of unique entries in a field, something that would be very helpful in Excel!
Relationships are another powerful capability in Power Pivot. You will see in the video how quick and easy it is to create the relationship between two Human Resource tables, and once there is a relationship between the two tables, it is easy to create Pivot Tables using fields from both tables
Power Pivot is another amazing Excel technology that will drastically improve your data analysis skills and also the speed to understand your data, without the steep learning curve associated with learning other data analysis programs.
Excel is an amazing tool for analyzing data, but the challenge is understanding what your data is telling you. A problem I find people regularly battle with is that when you start exploring different aspects it’s easy to end up with Analysis Paralysis and getting overwhelmed by how much information is available. In this video I will show you to create powerful interactive dashboards using nothing complex, just simple drag and drop techniques, which will give you the big picture as well as the ability to filter and sort as needed.
Dashboards are the ideal tool for providing a high level overview of what is happening in the business, department, team or project and are traditionally made up of tables of data and graphs that can quickly and easily convey important information.
Before starting to set up a dashboard, you need to decide on your key metrics. These are normally important totals that you want to share, for example, total sales, total profit or average profit per customer. I suggest that the Key Metrics are bold and placed in the top right area of the dashboard as this is where most people will start viewing your dashboard.
Visual elements are another important element of a dashboard as graphs convey a lot of information very simply. A trend graph easily communicates the general trend of data, is the data moving up, down, stable or volatile? Line or area graphs are normally used to communicate trends.
Column and bar charts are used to communicate which items are highest or lowest. These types of graphs provide an understanding of magnitude differences between items and are used to communicate comparisons.
Pie charts can be used to communicate percentage contribution to the whole. It is easy to overcrowd a pie chart by using too many items - so the general rule of thumb is to keep the number of items down to no more than 5 to 7 items.
Colour also plays an important role because they convey subconscious messages. When designing dashboards, I suggest not using too many colours and to keep in mind the impact of the colours you choose (ie. green is associated with good and red is associated with bad). My advise is to use use soft grays, browns or blues for backgrounds and bold colours for the key things you’d like to highlight.
A key point I’d like to bring your attention to, is that while I’m going through the processes of how to create an interactive dashboard in Excel using only Pivot Tables, Pivot Charts and Slicers, I never use complex formulas, VBA or macros. Microsoft have really moved away from these complicated methods and made things simple for all Excel business users.
I hope you enjoy this video and if you are interested in learning more about creating interactive dashboards with Excel, please have a look at our on-site classroom courses and online or virtual courses.
In my opinion, Pivot Tables are the most powerful feature in Microsoft Excel. They make aggregating, summarising and performing powerful data analysis quick and easy.
Sadly, most seasoned Excel users have very little or no understanding of what Pivot Tables can do so the most powerful feature in Excel is also the most under-utilised. There seems to be a widespread misconception that Pivot Tables are difficult to work with, but this couldn’t be further from the truth. In this video I will show you how easy Pivot Tables are to use. You will see there are no complicated formulas and all I do is use drag and drop techniques to create management reports in a few clicks of the mouse.
One of my most popular classroom training courses is a two days course on Pivot Tables and the amount of joy I see in people's eyes when they realise how much time they can save by simply changing the way they work is indescribable!