In Excel, it is often the case that you will have rows of data with similar characteristics, which need to be graphed but you do not want to create a graph for each row. Via a list box and the use of the OFFSET function, one graph can be used to illustrate each row of data in turn with the user choosing the row from the list.

Imagine you have sales data from a number of different brands. The data are monthly sales volumes measured in kilo-units for the year and monthly sales totals in a common currency. You need a graph which displays both data sets in the same graph, with two different Y axes for any one brand, of which there may be anything up to, say, one hundred.

Let’s say your volume data are on a worksheet called Volumes in rows 2 to 100 with column headings in row 1. Column A contains the name of the brand and columns B to M the volume figures. The same pattern occurs in the Value Sheet for sales by value. In each case we add four Quarter totals in columns N to Q.

In the Graph worksheet , we will create our graph and create two rows of data which will contain an offset so we can move to different rows, depending on the choice made in the list box, which refers to the column containing the brands. Before defining the offset formula, we need to insert a list-box which will reference the brands in column A of either the Volumes or Values sheet. This is the input range and the cell link can be any free cell such as J3, which will then be hidden behind the list-box.

The OFFSET function for the Volumes row is as follows:


and for the Values row:


The resulting graph and list box look like this:

The workhorse computer application in most finance departments is still Microsoft Excel. Despite the existence of customized accounting suites and ERP systems such as SAP , Excel offers the advantages of greater flexibility and now, in its 2007 incarnation, considerably more power, for a user community that has spent years getting to know all its features.

Excel 2003 was already capable of storing quite a few rows of data, around 65,000 but the number of columns was limited to only 256. Only is a relative term here, for many users this limit was no real barrier. But in other cases, the 256 column limitation proved to be a real problem. The “Big Grid” provided by Excel 2007 allows for over 1 million rows of data and 16,000 columns. With a limit of 32,000 characters per cell, the density of information stored in spreadsheets will inevitably spiral even where row and column constraints are not important. All this means that typical Excel spreadsheets are likely to grow in size and so an increasingly important question becomes, where should all this data be held to avoid the risk of loss?

This is typically a real problem for finance professionals, especially in the post-Sarbanes-Oxley world (properly known as the Public Company Accounting Reform and Investor Protection Act of 2002) which tightened accounting standards following the Enron and WorldCom corporate scandals. One of my clients, a major multinational headquartered in Geneva, Switzerland, recently faced this challenge when using Excel to keep a grip of financial controls across the business covering more than 70 separate entities in a large number of countries.

The answer was to store all the data in SharePoint lists. The data were broken down into simple lists which could be easily updated by a group of authorized users. The SharePoint infrastructure provided the necessary security in terms of access control, with versioning and approval cycles built-in. And it meant that the data were backed-up, providing what is often termed “one version of the truth.” But then, what about the monster Excel file, which had provided the data in the first place? The finance professionals were used to using this file to get an overview of what was required and did not want to lose this functionality. The answer consisted of writing a series of macros within Excel to rebuild the file dynamically, exactly as it appeared in its previous form, with the same layout, but looking up the live data from SharePoint. In this way, the best of both worlds was achieved, with one central source of data which could be tightly controlled, yet with the possibility to automatically re-create snapshots of these data in a form which was useful and could easily be copied for distribution.

Complex Excel Spreadsheets can be built from data held in SharePoint using macros

« Previous Page