O'Reilly logo

Microsoft® SharePoint® Server 2007 Bible by Simeon Cathey, Wayne Tynes, Wynne Leon

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required