PowerPivot for Excel 2010

Do we want to import a huge amount of data into our spreadsheet, and we don’t know how?

Do we need to analyze a big amount of data and to create reports, for the upcoming meeting that we have with our boss?

Do we want to analyze a big amount of data and present them very quickly in a Excel spreadsheet?

Do we want to use all the new features that Excel 2010 has to offer us, so our reports will be valid and understandable to the audience?

02

In all these questions the answer is one‼! We can all do that with the free tool (addin) from Microsoft which is called PowerPivot.

powerpivot-logo

What can we do with PowerPivot?

We can handle easily and quickly, large amounts of data.

We can import data into a spreadsheet of Excel very easily and fast. This data could be databases of companies, spreadsheets of Excel, reports data and feed data.

We have the access to create Pivot Tables, and other analysis tool that Excel has to provide us for creating reports.

We can export our conclusions from the data analyzing without getting into codes and functions that we do not know and don’t want to get involve in.

We can share our data analysis through SharePoint 2010.

In a few words Excel’s PowerPivot is THE Data Analysis Tool, for presenting the results of analysis in Excel. We can use familiar characteristics of Excel that we already know, for the better visualization of our data, such as creating Pivot Tables, Pivot Charts and Slicers. It is the most simple and easiest way to create data analysis tools filled in with also with interactive action

For us to find and download the addin PowerPivot for Excel 2010, we must follow the following address:

http://www.microsoft.com/downloads/details.aspx?FamilyId=e081c894-e4ab-42df-8c87-4b99c1f3c49b&displaylang=el

We will have to download the appropriate version depending on the Office that we have, and we can also select the appropriate language for us.

To find out how PowerPivot works we can visit the following website http://www.powerpivot.com/ where we can:

Watch Videos

02a

View Demos

03

Try Hands on Lab

04

05

After we have downloaded and installed the addin PowerPivot, we must start Excel and at the right of the Ribbon we will notice a new Tab named PowerPivot, as you can see at the following image.

01

To add external data to our spreadsheet we must select the command PowerPivot Window which is located at the area Launch of the PowerPivot Tab. After we select the command a new window will appear which it will be named PowerPivot For Excel – Book1. This window consists of two Tabs on the Ribbon. The first Tab is named Home, and the second Tab is named Design.

From the area of the ribbon which is named Get External Data we can import external data from one of the following:

1. Opening the drop down menu by the command From Database we have three options to select from:

  • From SQL Server
  • From Access
  • From Analysis Services or PowerPivot

03

2. From Report: A window named Table Import Wizard will appear, where in area named Report Path we will have to put the right path of our Report.

04

As we can see in the image above, all the Reports must have the extension rdl (report definition language).

3. From Azure DataMarket, we can import free and commercial data from various content providers.

05

Selecting the command From Azure DataMarket the Table Import Wizard will appear, where in the area Azure DataMarket dataset Url we must type in or paste the Url address so that we can move on to the next step of the wizard.

06

If we don’t know what the Windows Azure DataMarket is, we can select the link of the above window which is named View Available Azure DataMarket datasets.

Windows Azure Icons (1)

Once we are at the site, we must login with our Windows Live Account in order to have access to the free data.  Once we have logged in the site in the menu My Data, we are provided with an Account Key to be used later when we import the data from the site.  We have to type or paste it in the area Account Key of the Table Import Wizard.

This site has to offer a lot of free data, where once we have chosen the one that we need we will have to select the command Explore This Data Set, which will reach at the last step where we can select the command Export to Program: Excel PowerPivot as we can see in the image below.

Windows Azure Icons (6)

4. From Data Feeds: (RSS Feeds – Really Simple Syndication) which is located at the Get External Data area of the ribbon, the Table Import Wizard will appear that has got to do with Data Feeds. In the area Data Feed Url we must input the feed address so we can move on to the next step of the wizard.

07

5. From Text: Which is located at the area Get External Data of the ribbon, the Table Import Wizard will appear where in the area File Path we will have to input the path of the txt file or csv file in order to download the data and to move to next step of the wizard.

08

6. From Other Sources: Which is located in the area Get External Data of the Ribbon the Table Import Wizard will appear with all the sources that we can use to import external data into our spreadsheet.

09

The sources that are available for us to get external data are the following:

Relational Databases:

  • Microsoft SQL Server
  • Microsoft SQL Azure
  • Microsoft SQL Server Parallel Data Warehouse
  • Microsoft Access
  • Oracle
  • Teradata
  • Sybase
  • Informix
  • IBM DB2
  • Others (OLEDB/ODBC)

Multidimensional Sources:

  • Microsoft Analysis Services

Data Feeds:

  • Reports
  • Azure DataMarket Dataset
  • Other Feeds

Text Files

  • Excel Files
  • Text Files

Now, I will describe a couple of examples of how to Get External Data in a spreadsheet of Excel with the use of PowerPivot. For the first example I have downloaded some practice files from the following Url:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=EAC83429-C6E5-48A6-87CF-00A4141E5441

1st Example: Excel File.

I select the command From Other Sources in the area Get External Data of the ribbon, and from the window that appears named Table Import Wizard I select that I want to import data from an Excel File and then I press the button Next to move to the second step of the wizard.

10

In the second step of the wizard I have to select the button Browse and from the window that appears to navigate and select the Excel File that I want to use to import data from. In my example, I used one of the files that I mentioned above.

11

After I have selected the Excel File, I press the button Next to move on to the third step of the wizard. In the third step all the names of the Sheets appear in the area Tables and Views. In my example I have selected all the sheets and then I pressed the Finish button.

12

Straight away, the process of the import operation begins where we can see the process of importing the data, and seeing how many data is remaining to be imported.

13

Once the import operation is been completed with success, I then press the Close button.

14

After a while all the spreadsheets that I used from the External Excel File has been imported into my Workbook as we can see in the image below.

15

With the data that I have imported, I can Filter them out if I want to, or to Sort them out, or even to create a Pivot Chart, or to use Slicers so I could visualize the data in a more understanding and comprehensive way.

16

2nd Example: RSS Feeds.

I select the command From Data Feeds in the area Get External Data of the ribbon, and from the window that appears named Table Import Wizard, I either type the Data Feed Url or paste it in the appropriate area (once I have copied it) and then I press the Next button to move to the second step of the wizard.

17

Once I press the button Next to move to the second step of the wizard, we are notified to wait, while  PowerPivot is connecting to the data source.

18

Once the connection is established, we see in the area Tables and View the table or tables that are in our connection from the external data.

19

Straight away, the process of the import operation begins where we can see the process of importing the data, and to see how many data is remaining to be imported.

In my example I only have one table with data, which is automatically selected. I press the Finish button so the process of importing the external data begins where we can see the progress.

20

After a while once the process is successful, I then press the Close button so all the data will be imported into the spreadsheet. As we can see below the table containing all the data has been imported to the spreadsheet.

21

About officesmart

MVP: Honored with the MVP (Most Valuable Professional) for OFFICE SYSTEM title for the years 2011, 2012, 2013, 2014 and 2015 by Microsoft, for my contribution and commitment to the technical communities worldwide. Microsoft Master Specialist: This certification provides skill-verification tools that not only help assess a person's skills in using Microsoft Office programs but also the ability to quickly complete on-the-job tasks across multiple programs in the Microsoft Office system

Posted on May 15, 2011, in Excel 2010 English, Microsoft Office 2010 English and tagged , , , , , , , , . Bookmark the permalink. Comments Off on PowerPivot for Excel 2010.

Comments are closed.