Exercise 2: Performing Analysis with Excel and Excel Services

In this exercise you will work with the Microsoft Office Excel 2010 client application. Throughout this exercise you will utilize the Excel client integration points in SharePoint sites and Excel Services.

Task 1 – Create the Chart web part

1. In the browser, navigate to the site at the Lab05 sub site, e.g., https://contoso.sharepoint.com/Lab05.
2. Create a new document library named Excel Workbooks so you have a location to publish Excel workbooks.
   1. Select Site Actions » New Document Library.
   2. Name the document library Excel Workbooks and configure it to have a Document Template of type Microsoft Excel spreadsheet.
   3. Click the Create button.
3. At this point, you should be at the page with the default view for the Excel Workbooks document library. Click on the Documents tab in the contextual Library Tools menu of the ribbon, and then click on the Upload Document button.

4. Select the file C:\%Office365TrainingKit%\Labs\5.1\Source\Before\Ex2Before.xls. This file contains similar revenue information to the data contained within the list in the previous exercise.

5. Once you have uploaded the file, click the dropdown next to the document and select Edit in Microsoft Excel to open it in Excel.

6. Now it is time to begin work inside the workbook. Begin by creating a chart from this data.
   1. Select the range of cells to include the first and third columns of data, as seen in the image below
   2. In the ribbon, select the Insert tab.

   3. Drop down the Pie menu and select the first Pie chart.

7. Once you have created the chart, you will need to resize and relocate it. Take a few moments to apply formatting to the worksheet by adding chart titles, legends, etc.
8. Now save your work using the standard Excel Save command.
9. Name the workbook Ex2Before.xls, and save it in the Excel Workbooks document library, e.g., https://contoso.sharepoint.com/Lab05/Excel%20Workbooks.
10. Now you will publish the workbook to Excel Services using the following steps.

    1. Select the File button (i.e. the green button at the top-left of the screen).

    2. In the left column, click on Save & Send.
    3. In the middle column click on Save to SharePoint.

    4. In the right column click the Current Location of Excel Workbooks (see image below).

    5. Click Save As.

    6. The Save As dialog appears. Unlike the usual Save As dialog, this dialog has a button in the bottom section with the caption Publish Options…. Click this button to display the Excel Services Publish Options dialog.

    7. In the Show tab, change the value of the dropdown list from Entire Workbook to Items in Workbook. This dialog allows users to select what they would like published using Excel Services.
    8. Unselect all checkboxes except Chart 2 so that only the chart is published.

    9. Click OK to save your changes and dismiss the Excel Services Options dialog.

    10. Click Save in the Save As dialog to publish the workbook to Excel Services. If you receive a prompt asking you if you want to overwrite the existing file, confirm by clicking OK. After you complete this step Excel will begin the publishing process. If this is the first time Excel Services has been started, it may take a minute to complete. When the publishing process is completed, you should now see your chart inside the browser.