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.
Our previous post "Data or People? What is the most important resource in a business?" discussed the importance of developing your staff data analysis capabilities.
Here we will outline the key steps in the journey to being able to use the latest technologies and techniques to produce reports and analysis. The following is focused on users who primarily work with spreadsheets and use Excel.
Step 1 - Learn to use Pivot Tables
Pivot Tables will speed up the time taken to produce reports and analyse your data tremendously. An important aspect of Pivot Tables is that you need to understand how to structure and format your data for Pivot Tables to work effectively.
Step 2 - Create a Data Model
New technologies such as Power Pivot and Power BI allow Excel users to easily create powerful data models. Data models allow you to easily centralise the storage of your data, calculations (business logic) and create relationships between the tables of data. Data models make it much quicker and easier to develop reports and speeds up your processes.
Step 3 - Learn some Analysis Techniques
Once you have an engine for the production of your reports, you need to learn how to ask questions of your data. There are many different types of analysis that can be performed on your data - comparison, trend, ranking, contribution, variance, frequency etc. are some examples. Add a bit of simple statistics and you will find a whole new world of interpreting and understanding your data.
Visual analysis is a growing field of importance. Learn which graphs to use with which type of data. Visual analysis provides critical understanding of patterns and relationships within data.
Step 4 - Time for New Data
Technology such as Power Query makes it easy to access new data and to mash them up with existing tables of data. However the data may not always arrive in a format that is easy to use for data analysis. This is where you need to know how to use different methods to transform, append and merge data that creates powerful new data sets for different insights.
Step 5 - Share your Insights
Data analysis is of no use if we are not able to share the new insight and knowledge. Learning how to create effective reports and dashboards that can easily communicate key insights and information to your audience. Being able to tell a story about what your data means is a skill that will develop over time as your experience develops.
Unfortunately often trial and error is required to find the best methods of what resonates with your audience and what does not. My advice is start simple with your presentations. Your audience will quickly tell you what they want.
Power BI for Office 365 is Microsoft's latest Business Intelligence solution. It is cloud-based and works from within Excel and Office 365, to analyse and visualise data quickly and easily.
The BI solution is designed to help business users gain insights from their data and, according to Microsoft, includes the following:
- Power Query
- Power Map
- Power Pivot
- Power View
Find out how the Excel "Power" Tools - Power Pivot, Power Query and Power View - can change your life!
There is no shortage of data available today, with huge amounts of it being constantly generated by ERP systems, POS systems, PLC systems and Biometric systems, to name but a few.
Companies are needing to find ways to effectively analyse the data, to leverage the information and make informed decisions, however many of the options available are very expensive and require a lengthy period of implementation, plus human natures makes most staff resistant to learning new systems so all in all, decision makers are faced with a challenge on many levels.
Excel has become the main tool for manipulating and analyzing data, and developing reports in business today, and with the release of Excel 2013, Microsoft introduced even more powerful business intelligence tools.
To understand how the Excel Business Intelligence tools work together in Excel 2013, you need to understand the function of each tool and how it fits into the “traditional” Excel model.
Extract, Transform, Load
Power Query is an intuitive, easy to use tool for importing, transforming and working with data.
Spreadsheets are the basic feature of Excel. Launched in 1985, there are now over 700 million Excel users worldwide.
Tables were introduced in Excel 2007, they allow you to do quick sorting and analysis of your data.
Power Pivot was introduced as a free add-in for Excel 2010, it gives you the ability to access, process and handle well over a million rows of data, more than a standard spreadsheet can process.
Pivot Tables are an easy and convenient way to build intelligent, flexible summary tables, allowing you to quickly derive insight from your business data.
Pivot Charts enable you to visualize a PivotTable. You can quickly change a portion of data displayed, making PivotChart ideal for presentation of data in reports.
Power View is an interactive data exploration, visualisation and presentation tool that gives you strong insight in to your business .
So what is the bottom line?
Before spending time and money on new data analysis tools, find out exactly how learning how to use all the Excel Business Intelligence Tools effectively will allow you to quickly and easily analyse your data and gain insight into it without any major resource investments.
Over a billion people around the world use Excel today, far more users than any other BI vendor can claim.
Power Pivot is an incredibly powerful tool that allows you to analyse more than a million rows of data as well as giving you the ability to link more than one table of data. See how easy it is to use.
This is the first video in a five-part series showing you how powerful the Excel "Power" tools are and how learning to use them can vastly improve your productivity, while at the same time giving you insight into your business far beyond anything you ever imagined.
The Complete Introduction to Business Data Analysis teaches you how to apply different methods of data analysis to turn your data into new insight and intelligence.
The ability to ask questions of your data is a powerful competitive advantage, resulting in new income streams, better decision making and improved productivity. A recent McKinsey Consulting report has identified that data analysis is one of the most important skills required in the American economy at the current time.
This course focuses on the following different methods of analysis. During the course you will understand why the form of analysis is important and also provide examples of using the analysis using Excel 2013.
The following methods of analysis are included:
The Complete Introduction to Business Data Analysis is designed for all business professionals who want to take their ability to turn data into information to the next level.
If you are an Excel user then you will want to learn the easy to use techniques that are taught in this course. This course is presented using Excel 2013. Excel 2010 can be used for the majority of the training exercises. Small parts of the course do use Excel Power Pivot and Power View.
Please note that this course does not include any complicated formulas, VBA or macros. The course utilizes drag and drop techniques to create the majority of the different data analysis techniques.
For more information, visit our Online Course page
The business world has changed fundamentally over the past few years.The speed of change means that mangers need to be able to make decisions quicker, but at the same time need to be able to process far more data to be able to make an informed decision.
Business today has no lack of data – transactional systems record every part of the business process. The key challenge for managers is how to turn the vast quantities of data into useful insight and information.
In recent years new technologies has allowed vast quantities of data to be processed, analysed and increasingly visualised. The new technologies allow management to understand trends, identify outliers and to see new patterns in the data that could not be processed previously.
Excel as a Business Intelligence Tool
Excel has become the main tool for manipulating and analysing data, and developing reports in business today. With the release of Excel 2013, Microsoft introduced even more powerful business intelligence tools.
To understand how the Excel Business Intelligence tools work together in Excel 2013, you need to understand the function of each tool and how it fits into the “traditional” Excel model:
How is this relevant to Business Users?
If you work with Excel, using Spreadsheets and Tables to build reports, you probably find yourself regularly copying and pasting data between worksheets then clicking the same sequence of buttons to clean and shape the data.
You’ve probably found that complex formulas, dirty data and inevitable errors take up a lot of your time, making month end reports a nightmare that consumes large portions of time you don’t have.
Learning how to use all the Excel Business Intelligence Tools effectively will free you from the dull, repetitive tasks and give you time to focus on what’s important, analyzing your data and gaining insight into it.
This will enable you to make good decisions about your business.
PowerPivot is not new to you, it's just an extension of what you already know Remember, its Excel with PowerPivot, built by Microsoft. It's not a brand new tool set, it just makes Excel more powerful.
The size of a data set is no longer an automatic disqualifier of Excel as a tool for analyzing and reporting on it. You truly can take your Excel abilities up to the industrial scale.
It reduces the time you spend applying spreadsheet logic to other data sets, and maintaining sets of workbooks that all do basically the same thing. With better data capacity and performance, its suddenly an option to put everything into one master file and then filter as needed to focus on specific corners of the business.
Professional grade formula’s in PivotsThe feature that lets you add formulas (calculated fields) to your pivots has never been improved, it has remained limited, with nowhere near the richness of the formulas you can write in the regular Excel grid.
However, with PowerPivot, the engine has been revised and retrofitted so that you can use Excel formula syntax against it.
It's as if the Excel team spent years improving the Pivot calculated fields feature, and then sprung it on us all at once!