Need a checklist for testing Excel files

[deepfriedcheese]

We have several people that create Excel-based forms for use by our field personnel. The quality of these is uneven at best. I feel that we could improve the reliability and consistency of our forms if we had a more rigorous testing process before forms go live. Right now the testing process is informal and allows the uneven quality of our forms to go unchecked.

Does anyone have a resource they use to check if things function properly? I'm thinking of a checklist with just some basic questions for the tester like:

• Did you press every button visible to the user?
• Did what was supposed to happen occur?
• Was it clear from the instructions what the button would do?
• Did you enter text in every field available to the user?
• Was that text properly formatted?
• Did the text word-wrap when necessary?

Not hard stuff, but I'd rather not create it from scratch lest my own limitations prevent it from truly increasing our quality.

 

[dandungan]

Were you able to make any improvements?

I'm curious if the people who make the forms share your concerns.

Who has the authority to implement your quality process?
How would determine if the instructions were clear?
Would it be possible to get suggestions from the group of forms creators?

 

[deepfriedcheese]

I wasn't able to locate anything using Google, so I sat down and brainstormed my own list of questions. I've sent it to a couple of developers and received positive feedback, but no changes or additions to my suggestions.

I have the implicit authority to implement the changes as the department head will support my recommendation.

Making sure they are clear is the hard part. Here is what I have after about 15 minutes with a blank sheet of paper. Some of these are pretty vague, but at least they might jog a memory. Any suggestions are appreciated.


Protection
Was protection set during testing equivalent to how it should be set for distribution?
Is no more protection used than is necessary?
Indicate which protections were set:
Locked cells
Format cells
Format columns and rows
Insert or delete columns and rows
Are the macros locked from viewing if they contain the corporate password?

Instructions
Cover when the form is applicable?
Cover function of macros?
Cover basic use of form?
Links to other forms that may be required to supplement or replace this form?

Formatting
Did you enter something in every field to test the formatting?
Merged cells avoided where unnecessary?
Text cells are wrap or shrink-to-fit as appropriate?
Row height adjusts automatically for wrapped cells?
Look and feel of form is organized and professional?
Entry fields are color coded?
Does conditional formatting work?

Formulas
Formulas do not result in errors?
ISERROR formula used to avoid displaying errors before form is completed?
Formulas are calculating as the instructions and purpose of the form intend?

Macros
Macro functions are intuitive when overall form design and control labels are considered?
Macros do not produce errors or incorrect results, regardless of the order in which they are run?

Printing
All sheets print all relevant information without adjustment of print area?
Items that should be hidden in printed version are not printed?
Headers and footers of all tabs have been updated with version date?

Reset for distribution
Protection set as indicated above?
Column and row headings hidden when appropriate?
Sheets that should be hidden are?
Columns and rows with hidden data or calculations are hidden?

 

[dandungan]

It might be useful to develop design standards and testing to see if the forms meet the design standards.

I searched the web for "excel form design standard" and found:


Excel Form Design Standards
1530.0 - ABS Forms Design Standards Manual, 2010


table design best practices
Excel Table Design Best Practices » Bacon Bits:

hth

 

[SydneyGeek]

If you expect all controls to be filled out, make that a requirement.
So when a user hits the Submit button or equivalent, run a check for Null controls and don't submit the record if any are found. You could also pop up a message saying which controls require data entry, and possibly take the user to that control.
Where users have a defined set of possible responses, give them a drop-down list. Makes the data entry much more consistent.

Denis