Learn how to use Microsoft PowerPivot to do self service Business Intelligence. This course will guide you through importing data, working with it, then creating pivot tables and charts all in Excel 2010.
Robert C. Cain (arcanecode.com) is a Microsoft MVP, MCTS Certified in BI, and is the owner of Arcane Training and Consulting, LLC. He is also a course author for Pluralsight, team member at Linchpin People, and co-author of 4 books.
Working With Data In PowerPivot Hi, my name is Robert Cain. In this lesson, we're going to work with data inside Microsoft PowerPivot. In module one, we learned that PowerPivot was Microsoft's answer to self service business intelligence. We learned how it filled the gap between a simple Excel spreadsheet and a full blown business intelligence application using a tool like SQL Server Analysis Services. We noted some limitations, namely that PowerPivot is only compatible with the 2010 versions of Excel and SharePoint. We then saw a demonstration of PowerPivot in action importing data and using it to create Pivot Tables and Charts. In this module, we'll build on what we learned in module one by learning how to work with data inside PowerPivot. In PowerPivot, there are basically two types of users. The first is creators. Creators are the ones who set up the initial PowerPivot workbook. They are advanced users, maybe even IT folks who are familiar with the layout of databases, the sometimes obtuse names of columns, and knowing just what columns are important and what are not. Consumers are the people who will be using the data. While not familiar with the inner workings of databases, they are very knowledgeable when it comes to the business and want to create Charts and Tables as quickly and simply as possible. PowerPivot offers a variety of tools that will let creators streamline the data to make it simpler and easier for consumers. Some of these techniques include limiting the data that is imported in the first place, refreshing the data once we already have it, or naming columns and hiding them so that users can focus on what is really important, and combining data from multiple data sources.
Working With PowerPivot Pivot Tables Hi, my name is Robert Kane. In this lesson we'll take an in-depth look at working with the pivot tables that are generated by Microsoft PowerPivot. A quick review, in Module 1, we took an introductory look at PowerPivot, learning what its capabilities are. Then we did an example of pulling data in and creating PowerPivot tables. In Module 2, we dove deeper into working with the data. We showed techniques for filtering data, both during the import and afterwards. We also looked at techniques for hiding columns and for adding calculated columns to the tables. In this module we'll look at some advanced techniques for working with the pivot tables that PowerPivot generates. We'll start by learning how to most effectively work with dates within PowerPivot. Next, we'll look at the many ways PowerPivot has to display data, so that users can gain many insights by looking at the same data in different ways. Then, we'll look at how to add calculations, also known as measures, to an existing table. After that, we'll examine how to alter the layout and the coloring of your pivot table to provide the most impact to your users. Finally, we'll see how to use Excel 2010's new feature, Sparklines with PowerPivot. So, let's roll up our sleeves and get to work.
Working With PowerPivot Pivot Charts Hi, my name is Robert Cain. In this lesson, we'll learn how to create and modify charts within Microsoft PowerPivot. A quick review first. In module 1, we took an introductory look at PowerPivot, learning what its capabilities are. Then we did an example of pulling data in and creating a basic PowerPivot table. In module 2, we dove deeper into working with data. We showed techniques for filtering data both during and after the import process. We also looked at techniques for hiding columns and for adding calculated columns to the tables. For our third module, we went into detail on PivotTables, formatting them using data analysis expressions and more. In this module, we'll look at creating charts in PowerPivot with the end goal of assembling useful dashboards for our clients. We'll begin with the basics and create a simple chart. Next, we'll look at the properties charts have, which allow us to alter the layout of the chart. There are many types of charts available. In this next section, we'll cover some of those. Since nearly all charts have numbers of some kind involved, we'll look at important techniques for formatting them. After that, we'll look at ways to format our charts to really give them that visual pop that will make your work stand out. Finally, we'll use the knowledge that we've gained to assemble a full dashboard. Well, we have a lot of work ahead, so let's get to it.