2003 to 2007 Structured References
Working with formulas and functions has not always been the most intuitive process. In Microsoft Office Excel 2003, multiplying gross revenue by the profit margin might mean typing something like =B7*E14. In Microsoft Office Excel 2007, structured referencing enables you to use the column headers in table data to build your formulas. Instead of cryptic cell locations, you can simply multiply =[Gross Revenue]*[Margin].
To use structured referencing, you first need to format your data as a table.
1. Click Format as Table in the Styles section of the Home tab.
2. Select the My table has headers check box.
3. After you have a table, click the cell where you want to insert the formula, and then type the left bracket ([).
4. Select the column that you want to use from the list that appears, press the TAB key, and then close it with the right bracket (]).
5. Repeat as you build out your formula.
In general terms dashboards pages are a useful way to present key headline data to the right people (managers for example) without the need for them to have access to underlying systems such as accounting packages. This principle, in addition to saving on license costs, promotes a self-service culture and also allows managers to basically only see the key data that is relevant to them. Our example will take an Excel spreadsheet and explain how this can be ‘published’ to a SharePoint page for easy access via a web browser.
Initially you need to choose (or create) an appropriate document library in SharePoint and then upload your spreadsheet into this document library.
Once the document is uploaded open it and then select the ‘Office icon’ at the top left followed by publish and excel services. This ‘publish’ step is the key to making your spreadsheet available in Sharepoint via a web browser.
The ‘save as’ window (shown below) then lets you save the file to your SharePoint document library ensuring that the ‘open in Excel services’ checkbox is selected. This publish function allows for the spreadsheet to be viewed via a web browser.
The next step is to either create a brand new dashboard page in SharePoint or to add a web part to an existing dashboard page to display your spreadsheet. Our example shows the Sales by Region spreadsheet published to a sample dashboard page in Sharepoint.
Within the web part functionality of SharePoint you have numerous formatting options to allow your data to come to life. The two screenshots below simply show examples of spreadsheets presented on a dashboard page in tabular format and chart format. This would avoid the user having to physically open the spreadsheet.
All of the Excel formatting functionality is available here and depending on your audience you may wish to use these features in a big way.
If you are using Excel 2007 you can also take advantage of the conditional formatting functions to make your spreadsheet easier to interpret. Conditional formatting allows you to highlight trends and spot exceptions. There are numerous examples of this but by using data bars, colour scales and icon sets you can make your data much easier to interpret, as opposed to looking at raw numbers. The published sheet will also of course look more professional.
So to re-cap, this document hopefully gives you some ideas around dashboards and how relatively straightforward they are to configure, there is certainly no bespoke code involved with any of this. You are simply using Excel which you will likely already own (the 2007 version admittedly has better formatting option when compared to 2003) and Windows SharePoint Services which can be downloaded free from Microsoft so long as you have Windows Server 2003.
Please log in if you would like to leave a comment on this article
