Book Image

Instant Creating Data Models with PowerPivot How-to

By : Taehyung Lee
Book Image

Instant Creating Data Models with PowerPivot How-to

By: Taehyung Lee

Overview of this book

Microsoft PowerPivot is a free software designed to enhance Microsoft Excel. It allows the user to make extensive use of his/her computer's powers to draw data from various sources, do analysis across millions of rows of data, and present results in a very unique format. Instant Creating Data Models with PowerPivot How-to is a concise and to-the-point guide that helps you get a jump start on using this strong business intelligence tool, while still working in an environment similar to Excel. You will begin with data generation and manipulation, learning a new feature at each step by building onto the old file, and ultimately creating a comprehensive file. Instant Creating Data Models with PowerPivot How-to will guide the user through database installation, importing data from various sources, creating pivot charts and tables, utilizing a unique feature of PowerPivot called slicers,adding custom columns, and setting custom relationships between data to generate the ultimate customized dataset for analysis. By the end of the book and all the sections of Microsoft PowerPivot for Excel, the reader will be fully experienced and ready to utilize this powerful software.
Table of Contents (7 chapters)

Installation (Simple)

To get started with PowerPivot, you first need to install or enable it depending upon the version of Excel you are using. Not all versions of Excel may support PowerPivot. The majority of the functionality of PowerPivot mentioned in this book have been included in the new Excel 2013, such as managing data relationships. However, PowerPivot now comes as an add-in that must be enabled in Excel 2013 to fully utilize its capabilities such as writing custom data using Data Analysis Expressions (DAX) formula. For more information, please refer to What's new in PowerPivot in Excel 2013 at

This task will detail the steps required for installing PowerPivot in Excel 2010.

Getting ready

Microsoft PowerPivot for Excel requires Excel 2010 or above. Earlier versions of Excel, such as 2007, are not compatible with PowerPivot. Please ensure that Excel 2010 is installed properly.

How to do it...

  1. Click on the Download PowerPivot button on the main page of

  2. Click on the Instructions link on the left-hand side of the page under the Quick Links tab. Follow the instructions to install .NET Framework 4.0 and Visual Studio 2010 Tools for Office Runtime.

  3. Go to the top of the page and download the PowerPivot for Excel accordingly. x86 should be installed if the Excel is 32-bit, and x64 should be installed for a 64-bit Excel. The version of Excel 2010 can be checked by checking the About Microsoft Excel section under the File | Help menu as seen in the following screenshot:

  4. After the installation is completed, the features of PowerPivot will be accessible under the new PowerPivot ribbon as highlighted in the following screenshot:

  5. Ensure that it operates properly by clicking on the PowerPivot Window button as highlighted in the previous screenshot. It will open up a new window where we can import, organize, analyze, and publish data from various sources as shown in the following screenshot. This is the tool that we will be using throughout all the tasks.

  6. Now we are fully equipped to use the Microsoft PowerPivot add-in for Excel 2010 – the business intelligence tool.

How it works...

The PowerPivot ( link will redirect you to the official Microsoft page where we can download the PowerPivot add-in for Excel. The installation of PowerPivot requires two programs to install properly (.NET Framework 4.0 and Visual Studio 2010 Tools for Office Runtime). We should download these two programs before we proceed with the download. The operating system has two types: 32-bit (x86) and 64-bit (x64). Downloading the appropriate version will enable PowerPivot to perform at its best in accessing and calculating the data.

PowerPivot is easy to use. It integrates itself with Excel, appearing as if it is the built-in property of Excel, and shares many of the same features as Excel, which makes our life easier. The new tab requires us to restart Excel to be seen. The actual data import, calculations, and other items are performed all within this external window. This window is PowerPivot, which supports millions of rows of data, faster calculations, and other features.

There's more...

It is important to install the correct bit version of PowerPivot. The 64-bit Excel and 64-bit PowerPivot will enable the computer to store, access, and calculate more items at once, which is a key feature in PowerPivot that allows users to analyze data greater than one million rows—something that regular Excel 2010 cannot do.