Loading Data with Power Pivot in Excel

There are two ways to input data into Power Pivot: Data may be immediately loaded into PowerPivot, populating the database, or it can be loaded into Excel and added to the Data Model. You may either create connections and/or use the existing connections to import data into the Power Pivot Data Model from multiple data sources. Use the second method to get the data for Power Pivot without telling Excel anything. This is due to the fact that the data will only be loaded once and in a highly compressed manner. The SQL Server relational database is one of the data sources that Power Pivot supports. Text files, Excel files, Microsoft Access databases, SQL Server Analysis Services, and many more.

Loading Data Directly into PowerPivot

Step 1: First create a table in Excel.

 

Step 2: Select the table. Go to the Power Pivot tab on the top of the ribbon and then select Add to Data Model option.

 

Step 3: Create Table dialog box appears. Here select the table range and check my table has a headers option and then click on OK.

 

Step 4: Then it loads the excel table to Power pivot for excel.

 

How to Open PowerPivot for Excel

Step 1: Open the new workbook in excel. Go to the PowerPivot tab on the top of the ribbon and then from the data model group select manage.

 

Step 2: Then it will open a new PowerPivot for the excel workbook.

 

Loading Data from Access

Step 1: Open a new Power Pivot for Excel. Navigate to the home tab on the top of the ribbon, then go to the From database option and then select From Access.

 

Step 2: Now the Table import wizard dialog box appears. Here browse the database name and then click on Next.

 

Step 3: Then choose Select from a list of tables and views to choose the data to import and then click on next.

 

Step 4: Then select the Festival_data table and click on Finish.

 

Step 5: Then it loads the access table to Power pivot for excel.

 

Loading Data from Excel File

Step 1: Open a new Power Pivot for Excel. Navigate to the home tab on the top of the ribbon, then from Get External Data Group selects From Other Sources.

 

Step 2: Now the Table import wizard dialog box appears. Here select the excel file option from the text files and then click on Next.

 

Step 3: Now in the Excel file path browse the file and check the Use first row as the column header and then click on Next.

 

Step 4: After that select sheet 1 and then click on Finish.

 

Step 5: The sheet with name data is successfully imported with all the rows and columns. Now just click on Close.

 

Step 6: Now as you can see data table is successfully imported with all the fields.

 

Loading Data from Text File

Step 1: Open a new Power Pivot for Excel. Navigate to the home tab on the top of the ribbon, then from Get External Data Group selects From Other Sources.

 

Step 2: Now the Table import wizard dialog box appears. Here select the text file option from the text files and then click on Next.

 

Step 3: Now in the file path browse the file (The file should be in CSV) and in the column separator selects the comma and make sure to check the Use first row as the column header and then click on Finish.

 

Step 4: The sheet with name data is successfully imported with all the rows and columns. Now just click on Close.

 

Step 5: Now as you can see data table is successfully imported with all the fields.

 

Loading Data From Existing Connections

Step 1: Open a new Power Pivot for Excel. Navigate to the home tab on the top of the ribbon, then from Get External Data Group selects Existing Connections.

 

Step 2: Now the Existing Connections dialog box appears. Here select the text data file from the workbook connections option and then click on Close.

 

Step 3: Now as you can see data table is successfully shown with all the fields.

 

Loading Data from Clipboard

Step 1: First select a table in Microsoft Word and then copy the table with ctrl+c.

 

Step 2: Open the New Power Pivot for Excel and navigate to the home tab on the top of the ribbon, then from the clipboard group choose the paste option.

 

Step 3: Then a Paste Review dialog box appears that shows all the data now just check the Use first row as the column header and then click on OK.

 

Step 4: Now as you can see data table is successfully shown with all the fields.

 

we have covered how to open the power pivot and different ways to load the data in the power pivot. We learned how to directly load data into PowerPivot, Load data from access, Microsoft word, other sources, and many more.