Excel Integration

Contact Us or call 1-877-932-8228
Excel Integration

Excel Integration

Import Excel Spreadsheet to List

This walk-through will show you how to import data from an Excel spreadsheet and create a new SharePoint list.

  1. Create a new list by using the Import Spreadsheet template.
    1. Click the Settings menu and then choose the Add an app link.Site Actions menu More Options circled
    2. Locate the Import Spreadsheet App template and click the link. Create dialog form
    3. In the new list form that opens enter Golf Clubs in the Name field and List of golf clubs and their average loft angle and distances in the Description field.Import spreadsheet name form
    4. Click the Browse button to select the spreadsheet for the import.
    5. In the Choose File to Upload dialog window, browse to the folder you downloaded the class files to and select the club-specs.xlsx file. Click the Open button to close the dialog window and complete the selection.
    6. Click the Import button. This should open Microsoft Excel with the selected file loaded. A second dialog window titled Import to Windows SharePoint Services list should also open as shown in the following image.Import to Windows SharePoint Services list dialog
    7. In the Range Type drop-down, select Named Range and in the Select Range drop-down field choose ClubData from the list.Named range dialog

      Optionally you could choose Range of Cells from the Range Type field. This would then allow you to use your mouse to click and drag across your spreadsheet selecting the range of cells to import. Having a named table as you do in this example makes it a bit easier to just select the table.

    8. Click the Import button to complete the import process.
  2. Add the new imported list to the Quick Launch menu.
    1. Note how the new list "Golf Clubs" does not have a link in the Quick Launch menu. The Import Spreadsheet template you used does not have an option to add the list to the Quick Launch menu as part of the import and creation process.
    2. Click on the List tab to open the toolbar and click the List Settings link button. List toolbar
    3. Click the List name, description and navigation link under the General Settings group on the List Settings page.List settings with Title, description and navigation link circled
    4. Click the Yes option under the section Display this list on the Quick Launch? and then click the Save button to save your changes.
    5. Note how now there is a new link Golf Clubs under the Lists group within the Quick Launch menu.Quick Launch with Golf Clubs link circled

      The text for the link in the Quick Launch menu is based on the title of the list. The lists title can be changed on the Site Settings page using the same List name, description, and navigation link used to add the list to the Quick Launch.

Export List Data to Excel

This walk-through will show you how to export the list data from the Golf Clubs list to an Excel spreadsheet. It does not matter that the data originally came from an Excel spreadsheet; you could perform these steps on any list. The walk-through does require that you have done the previous walk-through to create the Golf Clubs list.

  1. Export the Golf Clubs list to Microsoft Excel.
    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Golf Clubs link in the Quick Launch menu to navigate to the list.Quick Launch with Golf Clubs link circled
    3. Click the List tab to open the toolbar.List toolbar
    4. Click the Export to Excel in the Connect & Export area of the List tab toolbar.List tab toolbar with Export to Excel circled
    5. Click the OK button on the Message from Webpage notifying you about a compatible applicationMessage from webpage.
    6. In the Open or Save dialog, click the Open button to open the owssvr.iqy file.IE Open or Save dialog

      The image shows the dialog you get from IE 9 and 10.

    7. Click the Enable button on the Microsoft Excel Security Notice dialog window to enable the data connection back to the SharePoint server.Excel Security Notice
    8. Microsoft Excel will open with the Golf Clubs list data in a linked spreadsheet. Note that the link is one way. You can only get updates from the SharePoint server, and any changes you make to the spreadsheet data do not sync back to the list in the site.Excel spreadsheet with Golf Clubs data
  2. Create a chart of the Favorite Cars list data.
    1. Inside the Excel spreadsheet, select the Club Name column heading then click and drag your mouse over the data and stop on the Fast Swing Yards column and the last row. The selected region should look like the following image. Note that you are including the column headings in our selection.Excel spreadsheet with Golf Clubs data
    2. With the two data columns selected, click the Insert tab on the Excel toolbar.Excel toolbar Insert tab circled
    3. Click the Line drop-down menu in the Charts region of the Excel Insert tab toolbar and click the first 2-D Line option.Excel Insert tab Bar charts menu
    4. There should now be a 2D line chart of the Golf Clubs data displayed in the center of your spreadsheet.Excel Chart of Golf Clubs
  3. Update the Golf Clubs list data and sync the updates to the linked Excel spreadsheet.
    1. Go back to your browser window with the Golf Clubs list All Items view showing.
    2. Click the Driver link to open the item's properties form.Driver properties dialog
    3. Click the Edit Item link on the Driver properties form.
    4. Change the Fast Swing Yards field of the Driver to "325" and click the Save button to save the change.Driver properties dialog
    5. Click back in your Excel window to bring the it back to the foreground.
    6. Click the DATA tab in Excel to open the toolbar.Excel toolbar Design tab circled
    7. Click the Refresh All button in the Connections region of the Data tab toolbar.Excel toolbar Refresh tab circled
    8. The Excel spreadsheet and chart should now reflect the changes we made to the list.Excel Chart of Golf Clubs
    9. Close Microsoft Excel and click Don't Save when asked if you want to save the changes to 'Book1'.
Next