To ensure your success in this course, you should have experience working with Excel and PivotTables. You should already understand spreadsheet concepts and be comfortable creating and analyzing basic PivotTables.
What You Will Learn
Get started with Power Pivot
Visualize Power Pivot data
Work with advanced functionality in Power Pivot
About This Course: We are now truly in the age of big data. Data is being collected constantly and for increasingly detailed transactions. This can lead to an overwhelming amount of data, which brings about a requirement for people who can analyze large amounts of data quickly. Luckily, Excel provides Power Pivot to help you organize, manage, and report on your data in the best way possible.
Getting Started with Power Pivot (2010, 2013 2016)
As an experienced Excel user you know that there is no way to work directly with the source data when you are working with multiple tables. If you want to interact directly with complex sets of independent data sources, you need to get started with Power Pivot. In this lesson, you will learn how to enable the Power Pivot add-in in Excel and navigate Power Pivot’s interface, including the Data View and Diagram View. Once you understand how to work with the Data Model, you will also know how to prepare your data for reporting by importing data, creating relationships among tables, and creating a hierarchy for your geographic data. By using Power Pivot you will perform powerful data analysis and create sophisticated models.
Enable and Navigate Power Pivot
Maybe you’ve heard of Power Pivot, but don’t know how to access it. Nothing to worry about. In Excel, you don’t have access to Power Pivot unless you enable it. In this topic, you will be shown how to enable the Power Pivot add-in and navigate within its interface. Power Pivot is an add-in built by Microsoft that you can use to create sophisticated relationships among various data sources and perform powerful data analysis. The advantages to use features of this tool are huge – like working with massive volumes of data; with Power Pivot Excel can process millions of rows of data no matter where the data source is located; it can combine at once data from database,a table in Excel or a text file; it allows you to analyze them all within the same report. Also Power Pivot makes no changes to the source data, so you should not worry about errors. With Power Pivot, you can use the Excel tools and features that you are already familiar with and add new analytical capabilities.
Manage Data Relationships
Before you can analyze your data, you must first enter the data into Power Pivot. In Excel there are quick and easy importing options which you want to know about. You will learn to manage data by importing and creating relationships among the imported data tables, so that you will be able to report on your data.
Visualizing Power Pivot Data
In this lesson you will learn an important skill you need when presenting data – the ability to summarize your data in a Power Pivot report. You will find out how to create a Power Pivot report utilizing a slicer and Quick Explore in order to analyze your data. Once you are ready to visualize your Power Pivot data into reports such as tables and charts, but want to view information in a specific way, you will need to know how to manipulate data by applying calculations to it. You will need to use basic formulas in Power Pivot to create calculated columns and fields that will enhance your reporting.
Create a Power Pivot Report
Viewing rows and rows of data in different tables is exhausting. Can anyone make any sense out of it? Yes, you can. Data only becomes meaningful when presented in a form of report. As you go through this topic, you will understand how by creating a Power Pivot report using the data in your Data Model, you can organize and manipulate it in ways that it can be analyzed and understood.
Create Calculations in Power Pivot
You have created a Power Pivot report, but there may be additional information that you want to include into your analyzes which is not included in the Data Model. Fortunately, with Power Pivot, you can create new columns and fields by performing calculations. In this topic, you will create basic calculations in Power Pivot by using Calculated Columns, columns of data added to a table in the Data Model that is created using a Data Analysis Expressions (DAX) formula.
Working with Advanced Functionality in Power Pivot
Now that you have mastered creating reports from your Power Pivot data and used basic calculations in them, you are ready for next step. This lesson will focus on more advanced functionality included in Power Pivot that helps measuring performance and analyze data over time. Being able to incorporate performance measures into your reporting and compare data over time will enhance your ability to analyze large amounts of data quickly and easily. In this lesson will learn to measure performance against targets using key performance indicators which is very helpful in sales and finance.
Create a Key Performance Indicator
If you need to measure the performance of data against a target then you want to spend some time learning of Excel’s way to measure performance in your data with key performance indicators. In Power Pivot, key performance indicators (KPIs) are visual measures of performance. Their purpose is to help you quickly evaluate a value and status of a metric against a defined target. Suppose you need to verify if the target amount to sell per quarter is being met by each sales agent in your department? Invest your time now in learning how to create KPIs.
Work with Dates and Time in Power Pivot
You are measuring performance with a KPI and much of your data analysis involves comparing data over time. You already know how you can group dates and use a timeline to organize and filter PivotTable data. Additional Power Pivot functions will enable you to compare calculations over time periods. To work with more advanced formulas in Power Pivot, you will need to employ the use of Data Analysis Expressions (DAX). DAX is a formula language used in Power Pivot. DAX is similar to the formulas that are regularly used in Excel, but DAX is designed to work with tables and columns of data rather than individual cells. You can use DAX to create custom calculations for calculated columns and fields. Congratulations! You have successfully completed the Microsoft Office Excel 2013: Data Analysis with PowerPivot!
Microsoft Excel PowerPivot was last modified: March 8th, 2017 by darrell