MULTI-UNIT EXCEL FORM SPECIFIC STANDARDS

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

• Definition
• General Excel standards
• Standard components
• Wording and language
• Screen resolution
• Locking or protecting cells
• Layout
• Special features
• Examples

Definition

These Excel forms have a spreadsheet-like layout where the questions and responding units form a matrix. There are two different types, those with a horizontal layout where the questions are positioned in the columns across the spreadsheet (Diagram 1), and those with a vertical layout where the questions are in rows down the spreadsheet (Diagram 2).




Diagram 1. Horizontal Multi-unit layout



Diagram 2. Vertical Multi-unit layout

These forms have particular application to surveys that require multiple responses, for different business units, on the same form. Each cell in the spreadsheet potentially contains the response for an individual business unit. The forms are also sometimes used where the respondent wants to report on a by unit basis (for example by product code), but the ABS is interested in the aggregate.
General Excel standards

The general Excel standards for the use of colour, lines, fonts etc. still apply. For example, the answer space on the forms must be white, and all other parts shaded. Text should all be in Arial, except for office use only text, and of a suitable size to differentiate headings, questions and notes. Please refer to the relevant sections above for full details.
Standard components

Multi-unit forms should be consistent with general Excel and all other electronic forms. That is, 'Front of Form', 'How to Use', 'General instructions' and 'How to Submit' pages must be included in separate sheets. Some of the details on these pages may need to be adapted. For example, if pop-up comment boxes are used, an instruction for accessing them may be added into the 'How to use' tab and details on how to complete the form, using tables, etc. may need to go into the 'General instructions' tab. Refer to 'Common Standards to all Electronic Forms' for more detail on standard components.
Wording and language

The layout of Multi-Unit forms in rare cases prohibits the use of standard question wording and the provision of includes, excludes and notes on the same page. It is important that the main elements of the question are retained so that respondents can identify the question item correctly. For example, keeping key words such as 'employment' and 'income', which have widely perceived meaning, and not substituting words like 'number' or 'amount' or simply 'total .... ".

Standard question wording is tested to ensure that respondents understand the question and correctly respond. When using a summarised form without notes, includes and excludes, rigorous testing should be conducted to ensure the questions are still understood. If questions are being summarised or paraphrased, it is important to do this consistently across collections. That is, the Excel forms for similar collections which share standard question wording should be checked before embarking on the process of paraphrasing for a Multi-unit form. This is also relevant to other common questions used across different forms or surveys.
Screen resolution

Conventional forms are designed to fit within the width of a maximised 800x600 window on a 15" monitor running at 24m colours. This does not mean that ABS forms are saved at 800x600 resolution, only that the form is made small enough to be fully visible when opened at a lower resolution. However, for most Multi-Unit forms, the width of the body of the form will extend beyond the full width of an 800x600 screen, requiring scrolling to view the entire form. It is more important for these forms that any subsections of the form appear together, i.e. questions won't be split up when scrolling.
Locking or protecting cells

As with conventional Excel forms, any part of the sheet which is not a data entry point should be locked, to prevent inadvertent deletion of question text and headings. Unused parts of the spreadsheet should also be locked and hidden to prevent data entry outside the form and to avoid the respondent becoming lost.

To prevent the respondent from having to unnecessarily scroll up to view information in the column headings, or across to view row headings, these should be frozen . To freeze the column headings only, go to the column to the right of the last column (that is, the first column that will be hidden) and selecting the cell in the first row below the heading row. From the menu, choose <Window> then <Freeze Panes>. When the panes (rows and columns) are frozen an "Unfreeze" option becomes available in the "Window" menu which allows you to undo the freeze. To freeze the column headings and row headings select the cell in the row below the heading row and in the column next to the column containing the row headings then choose <Freeze Panes>.
Layout

Ideally, questions should be in one row (or column where the questions are in rows);

• Notes can be positioned in the row above (or preceding column);
• Includes, excludes, response categories and instructions can be positioned in the row below (or column right)

Cell widths can be varied for fitting in shorter and longer questions, however this affects the cell widths for answer boxes which should be consistent for questions where the same type and magnitude of answer is expected. One way to achieve this is to use merged cells for the question, while the answer box is contained within one cell. For example, in Diagram 5, the question text runs over two merged rows, while the answer box is contained in one row. Using every second column allows each answer box to be separated rather than appearing as a table format (as pictured in Diagrams 3 and 4).

In cases where the includes and excludes are quite large, these may be given in a pop-up Excel comment which appears when the mouse is hovered over the cell, as pictured in Diagram 4.


Diagram 3 - This example shows a case where the questions are split into sub-questions, note that the unit of response is given for each.


Diagram 4 - This example shows a case where there are categorical answers, and the Including items have been put into a comments box, as indicated by the red triangle in the corner. The comment is only visible when the mouse is covered over the cell.


Diagram 5 - This example shows the layout where the questions are in the rows, and the units in separate columns.
Special features

The respondent should receive a separate PDF of the paper form, along with the Excel form for their reference, because some features on the form may be different and they may wish to refer to this more familiar format while responding. The inclusion of the pdf should be explained in the covering email, that is, that it has been included for reference purposes only.

Specific instructions for how to complete the form should be added to the 'general instructions' tab. For e.g. in the Vineyards survey respondents are instructed:



Diagram 6 - This example shows specific instructions that can be provided for completing a multi-unit Excel form.
Examples

A simple example of a multi-unit Excel form:

Previous Page

Next Page