Excel


The near universality of Excel means that it is often used as a form to capture data and input it to database systems. Excel files can be easily circulated and though InfoPath is a better tool for this job, the familiarity of Excel often leads to it being used instead of InfoPath forms.

To get anywhere near to the functionality available out-of-the-box using InfoPath, some pretty tortuous tricks need to be used in Excel, using data validation and named ranges. This post tries to capture some of the techniques I used recently at a client to make Excel a better tool for data capture.

In the scenario we faced, an international organization is using Excel as an input form to gather information from a range of partner organizations around the world. The institution has to work in six official languages, but for the most part, the interface in Excel has to exist in three principal languages, English, French and Spanish. Alongside the language choice, the organization collects information from three main sectors of activity; let’s call them Services, Industry and Agriculture, for the sake of argument. Now though a number of users are now moving to Office 2010 and the though the norm is still Office 2007, the institution has to cater to the lowest common denominator, which remains the Excel 2003 binary file format. In addition, a number of partner organizations cannot use macro-enabled spreadsheets and so the requirement was for an Excel interface which allowed for the easy switching of languages and that the contents of any drop-downs boxes should reflect the language choice which had been made, all done with only formulas and standard Excel functionality.

For the language choice, we set up a cell with data validation, based on a list containing the values English, Espagnol and Français as follows.

The cell was then given a Name, Language as follows:

Translations for headings are put in a separate worksheet called Translations, which will later be hidden from users. Headings in English are put in row 2, the equivalent translation in Spanish put in row 3 and that for French in row 4, as the mock-up below demonstrates:

A formula is used with nested IF statements to display the headings in the form, dependent on the language chosen.

The full formula is shown below:

=IF(Language=”English”,Translations!B2,IF(Language=”Espagnol”,Translations!B3,IF(Language=”Français”,Translations!B4)))

So far, so good and the technique provides a very simple interface which allows labels to be switched by the user at their own convenience.

However, the form must also contain drop-down boxes with suggested values under certain columns, and these choices must also change with reference to the language selected and also be constrained with reference to another drop-down we will call Sector.

First we will create the translations for the choices available in the drop-down box in the different languages:

The first column contains a Named Range called Agriculture covering the 7 rows of choices that are then provided in the next three columns in English, Spanish and French. For each cell in the Agriculture Named Range, a formula calculates the contents according to the Language chosen:

=IF(Language=”English”,B8,IF(Language=”Espagnol”,C8,IF(Language=”Français”,D8)))

In this way, the appropriate contents of the drop-down box are shown, relating to both Language and Sector selected.The same technique is used for the other two Sectors, Industry and Services.

These techniques are relatively simple but the end result is a more powerful multilingual form, with data validation but no use of macros.

Advertisements

REST can be used to connect to named ranges, charts, tables and pivot tables as well as individually defined cell contents using a simple URI via Excel Services. In the following Excel file, held in a normal document library in SharePoint 2010, each element can be referenced.

A single cell – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Sheet1!b2’)

A Named Range – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Ranges(‘Range1’)

A Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Table(‘Table1’)

A Chart – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/Charts(‘Chart%202’)

A Pivot Table – server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model/PivotTables(‘PivotTable1’)

However, as soon as an Excel file contains elements which are not compatible with Excel Services such as data validation or password protection, then REST no longer functions for that file and no elements can be accessed.

If no element is specified, and atom feed is returned listing all named elements which can be accessed using REST.

server/site/_vti_bin/ExcelRest.aspx/xl/1.xlsx/Model

Macro-enabled Excel files, with the extension .xlsm, though not compatible with Excel Services, may still be displayed and may still be accessible via REST.

Unsupported Features in Excel Services

 Information Rights Management (IRM) protection.

 ActiveX controls.

 Embedded smart tags.

 PivotTables report with multiple consolidation ranges.

 External references (workbooks that contain links to other workbooks).

 Workbooks saved in formula view.

 XML expansion packs.

 XML maps.

 Data validation.

 Query tables, SharePoint lists, Web queries, and text queries.

 Workbooks that reference add-ins.

 Workbooks that use the RTD() function.

 Workbooks that use workbook and worksheet protection.

 Embedded pictures or clip art.

 Cell and sheet background pictures.

 AutoShapes and WordArt.

 Ink annotations.

 Organization charts and diagrams.

 DDE links.

Features That Are Not Displayed

Workbooks that contain one or more of the following features will load in Excel Services, but the features will not be displayed. The features won’t be removed from the file, so the next time you open the file in Excel 2007, they will be displayed again.

Split and frozen panes

Headers and footers

Page layout view

Cell patterns

Zoom

Microsoft SQL Server Analysis Services’ member properties in ScreenTips

Some cell formatting, such as diagonal borders and border types not supported by HTML

Excel Services displays all charts that were created using Excel 2007. However, depending on how the charts are designed, some minor visual differences occur when some charts are displayed using Excel Web Access. For example, Excel Web Access cannot show new chart effects such as shadows, bevels, and reflections.

Features with Limited Interactive Support

Certain features that are interactive in Excel 2007 are not completely interactive in Excel Web Access.

PivotChart reports

PivotChart reports are not interactive when displayed in a browser. You cannot filter a PivotChart report directly, but if you interact with the PivotTable report that supplies data for the PivotChart report, the PivotChart report updates accordingly. This means if you filter the PivotTable report, the PivotChart report updates to show the filtered data set.

PivotTable reports

You can sort, filter, expand, and collapse data in PivotTable reports in a browser, but you cannot use SQL Server Analysis Services actions, add or remove fields, or rearrange fields.

Go To

In a browser, you can use the named range object view or the provided navigation controls to move around a workbook. However, there is no Go To feature.

Zoom, minimize, and maximize

Users cannot zoom, minimize, or maximize worksheets when viewing them in the browser.

Switching to page layout view

Page layout view is a new view in Excel 2007. You cannot switch to this view when viewing a workbook using Excel Web Access. Page layout view is designed to facilitate printing. If you want to print a workbook, it is best to use Excel 2007.

Goal Seek and Scenario Manager

You cannot use Goal Seek or Scenario Manager when viewing a workbook using Excel Web Access.

Formulas

You cannot audit formulas using trace precedents, trace dependents, show formulas, and so on.

Changing a workbook’s calculation mode

You cannot change a workbook calculation mode using Excel Web Access.

Watch window

You cannot use the Watch window when using Excel Web Access.

Any SharePoint list can be accessed as XML. If you use a URL like the following:

http://anycompany.com/sites/yoursite/_vti_bin/owssvr.dll?Cmd=Display&List=%7B6DD52014%2D168C%2D4298%2D9F60%2D08234232A394%7D&XMLDATA=TRUE

and use Excel 2003 to open it, you can link to it like any other XML.

  1. Choose Use the XML Source task pane:

  2. Choose the Elements you want to map:

  3. And then your List is linked to your Excel file:

  4. Use the List Toolbar in Excel to synchronize changes:

 

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:

=OFFSET(Volumes!A$2,-1+$J$3,0)

and for the Values row:

=OFFSET(Value!A$2,-1+$J$3,0)

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