Excel Integration - Exercise

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

Excel Integration

Duration: 15 to 25 minutes.

In this exercise, you will learn how to export a SharePoint 2010 list to Microsoft Excel.

  1. Export the Favorite Cars list to Microsoft Excel.
    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Favorite Cars link in the Quick Launch menu to navigate to the list. Quick Launch with Favorite Cars circled

      If Favorite Cars is not listed under the Recent group in the Quick Launch menu it might be listed under Site Contents. The Recent list only displays the last five lists or libraries created.

    3. Click the Export to Excel in the Connect & Export area of the List tab toolbar.List tab toolbar with Export to Excel circled
    4. 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 or 10.

    5. 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
    6. Microsoft Excel will open with the Favorite Cars list data in a linked spreadsheet. Note that the link is one way. You can only get updates from the SharePoint server; any changes you make to the spreadsheet data do not sync back to the list in the site.Excel spreadsheet with Favorite Cars data
  2. Create a chart of the Favorite Cars list data.
    1. Inside the Excel spreadsheet, select the data in both the Model and Top Speed columns by clicking and dragging your mouse over both columns of data.Excel spreadsheet with Favorite Cars data
    2. With the two data columns selected, click the Insert tab on the Excel toolbar.Excel toolbar Insert tab circled
    3. Click the Bar drop-down menu in the Charts region of the Excel Insert tab toolbar and click the 3-D Bar option.Excel Insert tab Bar charts menu
    4. There should now be a 3-D chart of the Favorite Cars top speeds displayed in the center of your spreadsheet.Excel Chart of Favorite Cars top speed
  3. Update the Favorite Cars list data and sync the updates to the linked Excel spreadsheet.
    1. Go back to your browser window with the Favorite Cars list All Items view showing.
    2. Click the Ferrari 458 Italia link to open the item's properties form.Ferrari Italia properties dialog
    3. Click the Edit Item link on the Ferrari properties dialog form.
    4. Change the Top Speed field of the Ferrari to "300" and click the Save button to save your change.Ferrari Italia properties dialog
    5. Click back in your Excel window to bring it back to the foreground.
    6. Click the DATA tab in the Excel 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 you made to the list.Excel Chart of Favorite Cars top speed
    9. Close Microsoft Excel and click Don't Save when asked if you want to save the changes to 'Book1'.