Importing data from Excel into PowerPivot requires utilization of "Table Import Wizard" that can be started by clicking on "Get External Data" of the PowerPivot Home ribbon. In the Table Import Wizard you are prompted to select Friendly connection name and Excel File Path. In addition, you can select to use first row as a header so that data is ignored in your PowerPivot. Also, you may test connection to this file to make sure that your network access is not restricting you to select data from this file.
Pic 1: Import from Excel
You are presented with the list of tables from the source Excel file in PowerPivot Table Import Wizard. These tables are usually worksheets with continues data as displayed in each Excel worksheet. However, you may set up your Excel 2007 file to have tables in it. In order to explicitly set up tables in your Excel file you need to convert Excel lists into table via selecting a list and clicking on “Format as Table” button on the Styles group of the Home Ribbon.
Excel data does not have natural referential data tracking mechanism, but we can still draw relationships with PowerPivot.