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 http://office.microsoft.com/en-001/excel-help/whats-new-in-powerpivot-in-excel-2013-HA102893837.aspx.
This task will detail the steps required for installing PowerPivot in Excel 2010.
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.
Click on the Download PowerPivot button on the main page of http://www.PowerPivot.com.
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.
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:
After the installation is completed, the features of PowerPivot will be accessible under the new PowerPivot ribbon as highlighted in the following screenshot:
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.
Now we are fully equipped to use the Microsoft PowerPivot add-in for Excel 2010 – the business intelligence tool.
The PowerPivot (http://www.powerpivot.com/) 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.
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.