1530.0 - ABS Forms Design Standards Manual, 2010  
ARCHIVED ISSUE Released at 11:30 AM (CANBERRA TIME) 25/01/2010  First Issue
   Page tools: Print Print Page Print all pages in this productPrint All  
Contents >> Electronic Forms >> Excel Forms >> Main Features of Excel Forms

MAIN FEATURES OF EXCEL FORMS

While aspects of these standards will be of interest to those outside the ABS, they were developed for internal use. As such, some information contained in these standards will not be applicable to an external audience. ABS staff should refer to the Corporate Manuals database for the most recent version of these documents, as some details (names, phone numbers etc.) have been removed from the online version.

Contents


Introduction

This section outlines the main features of Excel forms and includes brief notes to aid in their construction.

In addition to Excel-specific standards, Excel forms should also conform to the common standards for all electronic forms. (See the remainder of the ABS Forms Design Standards Manual for principles and guidelines regarding layout, question construction and formatting.)
Main features of Excel forms

The main features of Excel forms are specified in this section, divided up into:
  • General Construction and Layout
  • Macros
  • Worksheets and Sections
  • Colour
  • Data Entry and Movement
  • 'Total' Entry Cells
  • Text Entry Cells
  • Cell Formats
  • Dimensions, Margins and Borders
  • Radio Buttons
  • Tagging data items

General Construction and Layout

There should be an additional "E" at the end of all form IDs on the front of form to clearly indicate, in the Forms Repository view, that it is the Excel version of the paper form. If an "E" is already used in the form name, then use an "X" at the end.

Layout of Excel form content is based directly on the paper form. This includes all instructions and notes except any paper-specific ones. For example, "Tick all that apply" should be replaced with "Please indicate with an 'X'..." and references to reply paid envelopes should be removed.

The text of notes, definitions and instructions should align under the first letter of text, not under the dotpoint. However, this preferred formatting should only be done where practical, and where time permits.

The bottom edge of response boxes should align with the text, or with the last line of text for questions with more than one line of text. In order to achieve this, you may have to use merged cells for the question text. For example:


Diagram 3.1
It is possible to merge a number of cells across rows and columns in order have enough space to fit the question without the need to increase row heights. Using tall row heights for questions makes it impossible to align response boxes with the question text, so this practice should be avoided where possible. Response boxes should be correctly aligned because it makes it easier for the respondent to identify the correct place of response. Diagram 3.2 shows an example of the use of merged cells with and without formatting. The merged cells are identified by the lack of grid lines:


Diagram 3.2
Forms are designed at a screen resolution of 800x600 to accommodate the lowest resolution in widespread use, and, allowing for the white space at the bottom of worksheets, so that they print off to closely resemble their paper equivalents.

BSCs have the option to include comments boxes at the end of each part (worksheet) to enable the respondent to document any problems or concerns at the time they occur. This is currently a feature of the Local Government Finance (LGF) Excel form.

For example:


Diagram 3.3

If comment boxes are used, then the BSC's business process and IT systems may be affected, and the BSC should be made aware of this so that they can plan to identify and deal with the extra information being collected.
Macros

There are two types of Macros used for Excel forms, the first are form formatting macros used by the form developers to construct the form, protect it etc. These should not be stored in the form.

The second type of Macro is meant for use by the respondent, and is stored within the form workbook. Note that Excel forms should not include macros unless there is a specific need that can only be achieved by using a macro. Such a macro is "Add Rows" in the IFAB Form 90 Excel workbook. These macros are developed by TA for ESG. Such macros should undergo extensive testing with providers and ABS processes (Secure Deposit Box). Note: care is required as some macros will not pass through our security systems and/or the provider's security.

Macros for use by the respondent and represented by a button in the worksheet should be positioned intuitively. For example "Add Row" should be positioned at the end of the last row and column, where the respondent would already be looking when the need for additional rows arises. Buttons should not appear at the top of columns where they can be confused with column headings. Diagram 3.4 shows an example of where a button should be placed in relation to the relevant response box:


Diagram 3.4
Worksheets and Sections

All Excel forms are divided into worksheets of varying length that represent the sections or parts of the paper form. The main use and features of the worksheets will be described below. Excel forms may use more parts and sections than the paper form equivalents to aid navigation and for convenience in assembling the form. It can be easier to have standard parts (Please read first, How to use this form, How to submit, Time taken) separate, so that they can be inserted as a sheet into new forms.

All forms should start with a standard 'How to use this form' worksheet about navigation and saving, followed by the 'Front of Form' and 'Please read this first' worksheets. For long forms, a 'Content' worksheet should be included after the 'Please read this first' sheet. This describes where to find the relevant sections, for example:


Diagram 3.5
Each Part or Section has an Excel 'tab' at the bottom of the screen that can be used to navigate between worksheets. Tabs should be labelled with the part headings, not the part numbers (or letters).

The exception to this rule is for a survey with a large number of worksheets, such as the Survey of International Investment (SII), where it is not practical to use the full part heading for every worksheet tab. The following example shows how the tabs should be labelled:


Diagram 3.6
This example shows how tabs are labelled when there are a large number of worksheets:


Diagram 3.7
If there are simply too many sheets to avoid some of them scrolling out of view horizontally, an additional instruction should be included in the 'How to use ..' section:


Diagram 3.8
Inside a given section/part/worksheet the content is displayed in one scrolling 'page', though in some cases this results in only a portion of the screen being used. Subsequently, references to paper form page numbers are removed and inter-page gaps and spacing adjusted to give an even, continuous, display.

Each worksheet ends with a note to the effect that it is the end of that worksheet. 'End of part' notifications should be placed in the bottom right hand corner of the 'page'. The natural navigation of the eye will make this area of the worksheet the last part viewed by the respondent. By putting the 'End of part' notification there, respondent eye movement is reduced. The 'End of part' notifications should also be meaningful by describing which part has ended. For example:


Diagram 3.9
Links should not be used in spreadsheets. This is because tabs are commonly used to navigate through the Excel form and links in the form do not have the functionality that respondents have come to expect of hyper links (for example, hitting 'backspace' will not take the respondent back to the last page visited).



Colour

Along with black and white, two shades of grey are used. When setting the colours in custom colour palettes, the correct colours are Grey (222) and Light Grey (244).
Back to top


Data Entry and Movement

Movement around the spreadsheet is possible using several keystroke shortcuts:
  • Tab (to next active cell),
  • Shift+Tab (previous active cell),
  • Control+PageDown (to next sheet),
  • Control+PageUp (previous sheet),
  • PageUp/Down keys,
  • arrow keys and
  • the mouse.

Movement and data entry in the spreadsheet are limited in two ways:
  1. 'inside the form' all cells where the provider should not enter data are coloured grey and are locked (data entry cells are white, as with paper forms). This includes the restriction that 'Total' cells cannot have data entered into them. They use formulas to reflect the cumulative totals of their contributing cells, and are 'greyed out' with default zeros; and
  2. the 'unused' areas of the spreadsheet 'outside the form' should be turned off to prevent inadvertent data entry. In addition, horizontal movement away from the form area is prevented by hiding all columns to the right of the active area to prevent users scrolling off to the right and losing the application. Because it is not necessary and allowing it to display could be confusing, the horizontal scroll bar is turned off. It is turned off in <Tools>, <options> then <view>.

The physical act of vertical scrolling cannot be prevented, however, the rows below the active cells can be locked and hidden, which prevents the appearance of activity 'outside the form'.

There should be no default zeros in any data entry fields. This is because a zero may indicate a response, where there really is none, which in turn contributes to the estimates, distorting the average number of genuine 'zero' responses.

Open (data entry) cells include the business name and ABN boxes so respondents can change them.

All response fields should accept alpha/numeric data. For example, respondents should be able to enter the word 'Nil' to indicate no response, where a numeric response may be expected.

Data entered into space intended for numeric responses should be formatted to be right justified.
'Total' Entry Cells

Unlike paper forms, 'total' and other similar data entry cells should not have a thicker border because testing has shown that users frequently misinterpret or confuse it with the focus/active cell indication.

Where total cells are used in paper forms, totalling in the Excel form should be automatic. Where appropriate, manual data entry in to total cells should be prevented. 'Total' cells will therefore appear with default zeros due to containing a formula.

Formulas should be written into total cells to decrease respondent burden and increase data quality. If summation is automated for one total, then this should be consistent throughout the form for all other totals. There are four types of totals:
  1. Cumulative total within a question
  2. Cumulative total over multiple questions
  3. Fixed total to indicate what the subparts should add to, that is, "100%"
  4. Fixed total that should be equal to an earlier question

Where the total refers to other parts of the form, this should be noted after "Total";
  • where the total sums over more than one question the note should state which questions sum to make the total. For example:


Diagram 3.10
  • where a total should be equal to an earlier question, a note can be added for example "Should equal Question xx";
  • where a total only includes figures from one question, a note is not required.

Greyed out cells for fixed totals should be visually different from greyed out cells for cumulative totals. In particular, cells that have a fixed total value for reinforcing how the question should be answered, for example "100 %", should be differentiated from those that cumulatively total. As shown in diagram 3.11:
  • Fixed totals have no cell borders and no note.
  • Cumulative (formula) totals that sum the respondent's inputs should have a cell border and a note, for example "Should add to 100%". For example:


Diagram 3.11
Text entry cells

Text and comments boxes present particular difficulties in spreadsheets with respect to wrapping, paragraph breaks and viewing during and after the cursor focus is in the cell. Data entered into cells intended for alpha responses should be formatted to be left justified and text in comments boxes should be forced to wrap (so that the text does not go past the right edge of the comments box).

Areas for text entry consist of a single row of horizontally merged cells. The row height should be adjusted to show enough space.
Cell Formats

Cell formats can help avoid incorrect responses by identifying what is expected in the cell. For example choosing text or numeric formats will automatically left or right justify the inputs. Percentage formats can be used to reinforce the need for responses to be expressed as a percentage, and numeric fields can be set to zero decimal places to force whole dollar amounts. This is achieved by:
  • Select the response cell(s) and choosing <Format>, <Cells> from the menu.
  • Choose the appropriate format, for example, where whole dollar responses are expected, select "Number" and change the "Decimal places" to zero, as shown in Diagram 3.12. By default the number formats show negative numbers with a negative sign and black text. This is the correct format for an Excel form and should not be changed.


Diagram 3.12
A numeric format still allows text entry, but will left justify instead of right. Therefore, respondents are still able to enter a 'nil' response if they wish.


Diagram 3.13.1

Diagram 3.13.2
Back to top


Dimensions, Margins and Borders

ABS Excel forms are designed to print out to the width of an A4 form, but absolute dimensions lose most of their meaning when using different screen and window sizes, fonts and resolutions.

Excel measures cell width in terms of standard font characters, as specified in Microsoft Excel Help (2003):

"The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font (standard font is: The default text font for worksheets. The standard font determines the default font for the Normal cell style (Microsoft Excel Help, 2003)."

The ABS Excel templates use a 'standard font' of Arial 10 point.

Column widths must vary from sheet to sheet to allow for a variety of layouts. There are a number of different column settings for different page layouts, these are partly automated through the formatting macros, and can be adjusted for the needs of the form.

The width of margins are relevant to printing only. Minimum margin requirements vary from printer to printer and can be altered in: <File>, <Page Setup> then <Margins>. The top and bottom margins are set to at least 1cm.

Cell borders are set to the finest solid black line for data entry cells, and the second finest for white borders.


Diagram 3.14
Radio Buttons and drop down menus

The use of radio buttons and drop down boxes in Excel forms is under consideration. Radio buttons may be used for mutually exclusive yes/no or choose one only questions. One advantage of using a radio button is that the round shape of the response spaces help to differentiate mutually exclusive type questions from 'tick all that apply' type questions. Drop down boxes do not work as well and should only be used sparingly for lists of response items. Long lists in drop down menus are problematic due to the need for scrolling, when using drop down menus the entire list should be visible when activated, i.e. without the need for scrolling.

Drop down menus share the same risk of ordering effects as printed lists, for example, respondents tend to answer in the top third of the list. If there is some natural order to the items in the list, this order should be used, otherwise the list should ordered alphabetically.
These tools should not be used unless thoroughly tested with providers.
Tagging/naming data items

In the future eforms will be required to have a 'named range' defined for each single entry field or column of entry fields. The 'named range' is given a unique label so that any individual entry field can be retrieved.

The names used for the 'named range' will be important in reducing the amount of concordance work done between exporting data from the eform and the eventual load process to the Input Data Warehouse (IDW). The Blaise system name should be matched to each data cell. The Collection Management Unit should be consulted when tagging an Excel form.

To name a cell(s) select and highlight the relevant cell(s). Go up into the name box located on the left side of the screen under the toolbar. As shown in Diagram 3.15, type a name for the cell(s) then press enter to save the name. For example:


Diagram 3.15
Alternatively you can open the 'Name' dialogue box by choosing <Insert>, <Name> then <Define> from the main menu as is shown in Diagram 3.16. Type a name in the "Names in workbook:" box, then go to "Refers to:" box, delete the reference that appears, if any, then using the mouse highlight the relevant cells. For example:


Diagram 3.16
Additional to cell tagging, each data sheet should be tagged with an embedded ID and both data sheets and the Front of Form sheet should have an ETL-PIMS metadata ID.



Previous PageNext Page