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 Page
 Print All
| ||||
MAIN FEATURES OF EXCEL FORMS
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 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:
Movement and data entry in the spreadsheet are limited in two ways:
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:
Where the total refers to other parts of the form, this should be noted after "Total";
Diagram 3.10
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:
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:
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.
|
||||