Microsoft® SharePoint® Server 2007 Bible

Implementing KPIs from Excel

Indicators can also be created for data that is stored in an Excel workbook. In this scenario, Excel is doing all the calculation work, and the indicator is simply getting the value of the indicator from a particular cell that you enter. You can enter the goal and warning values as fixed values in the indicator definition or to be driven by values in the Excel spreadsheet.

Identifying your Excel KPI source

Out of all the data stored in Excel workbooks throughout your organization, you will identify the source of a KPI as a cell in a workbook that has meaning and whose location will not change regardless of the growth of the data in the spreadsheet or any possible updates to the spreadsheet from an outside data source. If that seems like a daunting task, think of it this way: The cell is most likely going to be part of a total or summary row and should be the cell that brings all the data in the rows and columns together, such as a sum of Q3 sales for all regions, as shown in Figure 16.11.

Figure 16.11. Sales Summary Data

Tip

If your spreadsheet will be growing so that the location of the summary cell will change, you can create a summary worksheet that grabs the value of the cell calculated on another page. This way when the details page is updated with more rows, Excel manages the relationship between the details and the summary worksheets and you ...