Spreadsheet Modelling – Assignment 3

Testing the Data Collection Spreadsheet [P7]


In order to ensure that an Excel form works as intended, testing is typically required. So that the testing would be as effective as possible, and would reliably target all aspects of the form, I decided to create a test plan, shown below.

  1. To begin, open the form workbook as it would be opened on a computer in the tourist information offices. In order that any users cannot tamper with any of the settings, such as the spreadsheet's password protection, most of the ribbon tabs should be hidden and remainder collapsed:
    1. Open the Excel settings window by clicking File > Options.
    2. In the window, click the Customise Ribbon button on the left, and then untick the all but the Home tab in the box on the right.
    3. Click OK before double clicking the Home tab label in the ribbon, which will make it collapse by default.

  2. Confirm that Excel has loaded the correct worksheet, and that the tabs for the other two worksheets do not show at the bottom of the window.

  3. Ensure than the cell selection is in the first input cell, E8. The date field above should already be filled in with the correct date using the TODAY() function.

  4. Fill in the form cell by cell. Using the Tab key in the upper left of the keyboard or the Return key, the form can be moved through easily. Because the cells in between the input fields are locked even to selection, Excel should only place the cursor in the fields requiring input; those in column E on rows 8, 10, 12, 14, 16, 18, 20 and 22.

  5. When typing a response into one of the form cells, Excel should ideally auto-complete using the lists of data provided by the reference worksheet. The is the case for the Country of Residence input cell but not the Comments cell, for instance. The auto-completion means that users can enter data that can be efficiently processed by the spreadsheet and used in statistics, while still entering the data in a perhaps more familiar way than the drop-down menu.

  6. If incorrect data is entered into the form, such as a country name without a capital letter at the beginning, the spreadsheet should throw an error and display a window to the user informing them to use the drop-down menus available in most of the cells. For some cells, the option to not change the data is presented, accounting for particular cases that may not otherwise be covered by the options given. The Gender input cell is among these.

  7. When the form is complete and the submit button pressed, there should be a short delay before a window appears confirming that the form's data has been successfully submitted to the database worksheet. If this does not appear, an error has occurred in the execution of the submission macro. This is an issue that must be attended to by a developer or possibly a member of staff, likely with the aid of the technical documentation.

In Practice

I tested the spreadsheet and followed the process outlined above. The only area in which the spreadsheet failed to pass was the auto-completion. I was under the impression that a cell with list-based Data Validation enabled would automatically have auto-completion enabled, but this seems not to be the case. There may be an option in the Data Validation window to enable this feature, otherwise it may just not be available. Was I to create the form and data submission system again, I would likely research into this in greater detail.

Spreadsheet Data Formats [P8]

So that the data processed in the original spreadsheet would be available and accessible to as many people and computers as possible, I exported both of the spreadsheets two common formats:

42.3-data.csv 42.3-data.html

The contents of the HTML export are also shown below.

Exporting to CSV

In order to export the data collected from visitors to the tourist information office each month, follow the steps below:

1. Firstly, the spreadsheet containing the data must be un-hidden. In order to allow worksheets to be hidden and un-hidden, navigate to the *Review* tab in the ribbon and click on *Protect Workbook*, which should already be highlighted. The password to allow this is simply `password`.
2. We must now un-hide the database sheet, by right-clicking on the *Form* worksheet tab in the bottom left, clicking *Unhide*, selecting the *Form Database* worksheet, and then clicking OK. The results should now be shown on screen.
3. To export the data, click *File* on the ribbon, and then *Save As*. In the dialogue box that appears, select *CSV (Comma delimited) (\*.csv)* in the *Save as type:* drop-down menu. Give the file a name and choose where to save it on your computer. Once this is complete, click the *Save* button in the bottom right.
4. Remove the protection on the database worksheet, by navigating to the Review tab once more and clicking *Unprotect Sheet* button. Again, the password is `password`. This allows us to remove the data in the spreadsheet.
5. Select the data columns by clicking and dragging the mouse right-wards from the column A label to the column I label. This selects all of the data provided by visitors, regardless of how far down the spreadsheet it it.
6. Right click anywhere in the selected area, and then click *Clear Contents* to remove all user-submitted information. Place the selection in cell A1 before proceeding.
7. Re-protect the spreadsheet by clicking the same button used to un-protect it previously, now labelled *Protect Sheet*, and un-tick the two selected tick boxes. Enter `password` in the password entry box, and click OK. At this point you will be prompted to repeat the password you entered.
8. Rename the worksheet to "Form Database" by double clicking the name in the lower left.
9. Right-click on the database spreadsheet label in the bottom left, and click *Hide*, and then click *Protect Workbook* in the *Review* tab. Enter `password` once more and confirm it when prompted.

Save the spreadsheet and exit. Send the CSV file generated by Excel to the database manager.

Alternatively, you may instead only proceed to step three, then close Excel without saving changes. Delete the Excel spreadsheet, and then re-download it from the file-server it was originally obtained from. This will give you a fresh copy, with workbook structure and password protection already configured as it should be.

10/11/2014SwedenPersonalFemale55-64Sea, in private vehicle3-6 months12000Although the circumstances which brought me here were not good, the landscapes and facilities in the UK have helped me greatly.
10/11/2014JapanBusinessMale35-44Air1-3 nights60My accomodation has been wonderful, and the afternoon I was able to spend sight-seeing was very interesting.
0/11/2014AndorraHolidayFemale25-34Air, on foot14-27 nights6000I have enjoyed my stay in London, and trips out into the countryside. Stone Henge was particularly impressive!
5/10/2014United KingdomPersonalMale16-24Channel Tunnel12+ months200000As one who has spend the vast majority of 16 years in the UK, I can honestly say it ain't all that bad. Bit damp sometimes, but oh well.

User Documentation [P9]


The following document details the process of submitting information about yourself and your visit to the United Kingdom using the form on the appropriate computers in our tourist information offices. This form is created using Microsoft Excel, and uses a macro to submit the data to a separate table. We can then collect the information people have submitted from around the country, and generate a wide variety of statistical information about tourism in the UK. The information you submit is anonymous, and we respect your right to leave any of the form fields blank if you feel the information is too personal.

Frequently Asked Questions

Guide for Form Completion

The form has been designed to be as simple to use as possible. A screenshot is shown below of the form as it should appear before being filled out.

The form without any data entered.
Image by myself; see the license.

Data Submission

The first field of the form, the Date field, should already be filled in with the current date. If this is not the case, please tell a member of staff. This cell does not require any input from you.

The second field requires you to enter a home country. The easiest way to do this without causing trouble is by clicking the downward arrow to the right of the cell when it is selected. This will show a list of nations available for selection. If your own is not shown, please select Rest of World, and tell a member of staff.

Continue the process of selecting the most appropriate option from the lists provided in each field. The last two fields, Cost of Visit (£) and Comments require you to instead type an answer. The images below may help to illustrate the process.

The process of selecting an age group from the list.
Image by myself; see the license.

Once you have completed the form with your information and are content with the data, press the submit button. The page may flicker for a short period of time, but a window will appear afterwards confirming that the data has been submitted if everything has worked. If this window does not appear, please tell a member of staff in the tourist office.

The message shown once the form has been successfully submitted, and the information recorded.
Image by myself; see the license.

If the window has appeared, as shown above, you may click OK. The form has been submitted, and you may leave the computer for the next person.


The User Documentation is also available by itself here.

Technical Documentation [M4]

Hardware and Software Requirements

In order to open the file and modify its contents, there a number of requirements which must be met both in terms of hardware and software.

Firstly, a capable computer is essential to efficiently developing the model. I recommend a computer with at least a 1 GHz processor, and ideally one with multiple cores or a higher clock speed. I recommend at least 1 GB of RAM, and at least 5 GB of free space on your hard-drive, meaning that there will be plenty of space for a Microsoft Office installation (which is required) and for the spreadsheet model to expand as more information is added. A monitor of a reasonable size can help greatly in improving one's workflow, and I recommend a monitor of no less than 1366 × 768 pixels, and ideally one of 1920 × 1080 pixels.

In terms of software, Windows or Mac OS X is required in order to run Excel. Alternatively Wine can be used to emulate a Windows environment in which Excel can be executed if you're using Linux. In order to open the spreadsheet, I strongly recommend Excel 2010 or newer, in order that all functions and formulae work as intended. Asides from the OS and Excel, no other software is required to open the spreadsheet.

The spreadsheet is saved as 42.3.xlsm. The majority of spreadsheets made in excel are saved as *.xlsx files, which is the default extension for Excel files. In order to enable the use of macros or any other VBA scripting in a spreadsheet, it must be saved in a separate format which allows this. In order to minimise the chances of data loss or corruption of the spreadsheet, I recommend making backup of the spreadsheet before and after making significant changes. This can be done simply by opening Window Explorer, and then copying and pasting the spreadsheet file. It's recommended to rename the new copy including the current date and time, so that you know exactly when the file was backed up, even if the file is accidentally modified. Continue to modify the original as you work, and keep making backups. If you make a severe mistake, delete the main file, and then copy/paste the most recent backup, before renaming to the original file name. You could also utilise revision control software such as Git or Mercurial to ease this process.

Colours and Type

I used a custom-designed theme for the two spreadsheets, which primarily involved the use of Segoe UI for the font, and a selection of colours for various different pieces of text. The largest text shown should be of 14 pt, in bold and in black. Secondary level headings should be of 11pt in company blue (RGB 33 62 92), and all lower-level headings should be the same size but in company red (RGB 117 5 1). All headings should be set in bold.


I used a range of different formulae throughout the two spreadsheets. These primarily consisted of basic mathematical operations and interaction with functions, however.


A variety of functions were used on the two spreadsheets to achieve different goals, and some of they are listed below.


PivotTables offer a flexible way to show information, that can be easily changed by dragging sets of data around a simply interface. The tables generated allow for categorisation of data, and automatically generate totals at the bottom of sections. A PivotTable was used on the By Residency worksheet, to the right of the data, in order to show the total visits from different regions of the world in an interactive manner. All countries are listed, but grouped by geographical region, which makes finding a particular entry a lot easier. Different geographical regions can be collapsed using a small icon at the left side of each cell.

External Data Connections

In order to keep an up-to-date list of currency rates, the spreadsheet uses an external data connection. Every time the spreadsheet is opened, or on an regular basis if configured to do so, Excel will fetch data from the MSN Money API about the exchange rates between the US Dollar a number of other currencies from around the world. This means that the information shown in GBP, having been converted from USD, is always as accurate as it could be.

Data Validation

In order to ensure that the information users enter into the form is organised, Data Validation is used. This is how the in-cell drop-down menu is created; by being linked to a list of allowed values on the Reference worksheet (hidden, 42.2-form.xlsm). Data Validation restricts what people can enter in the form's fields; by ranges, conditionals, matches and more. In the form spreadsheet, for example, the user callow enter a value below zero for the Cost of Visit (£) field.


A macro was created in Excel to copy the information from the form to a separate database worksheet, and then clear the form for the next user. The macro was created initially by recording the copying and pasting of information, but was then heavily modified in the Visual Basic for Applications (VBA) editor in Excel. The allowed for better performance and improved stability.

The macro used on the form spreadsheet to copy data in the form to a separate database spreadsheet is shown below, with comments.

The Technical Documentation is also available by itself here.

Evaluation and User Feedback [D2]

In order to gauge how successful the model and feedback form spreadsheets were, suggestions and analysis was gathered from a classmate.

Spreadsheet Model

Feedback Form


I am happy with the theme that I developed throughout spreadsheet creation, and I feel it is effective. The typeface, Segoe UI, is clear and easily read at all sizes of text. It also has a dedicated bold version, which enables certain pieces text to stand out. The colouring used also means that the user can easily differentiate between different level headings. The colours I picked complement each other, and allow for a range of complementary colours to be used in graphs and charts.

I believe that the form in particular could use more clear labelling and information presented to the user when selecting each field of the form. This would mean that the less technology-familiar users visiting the tourist information office would still be able to comfortably complete the form. In it's current state, the form may discourage some from entering information, which would mean the form results would be skewed and would inaccurately represent the opinions of visitors.

I think that with minor adjustments, the spreadsheet could be used in a production environment, although I wouldn't recommend the use of Excel as a form-based data collection platform. Despite this, I do believe that the spreadsheets met the requirements outlined in the brief, and the model spreadsheet in particular.

The menu page, although visible on the Assignment 1 model spreadsheet, was not shown on the newer version of the spreadsheet due to my misunderstanding. If I were to create another model or develop the current version more, I would create a dedicated menu page, and include the functionality of the original one (such as VLOOKUP utility). By adding a clear menu page, the spreadsheet can be made a lot easier to navigate, and hyperlinks to other worksheets or cell references are all that are required to achieve this.

I believe the use of External Data Connections was successful, although I do feel data offered by MSN could be more concise. For instance, I would assume one could use a more capable finance API that would return only the values requested, which would be specified in the request address. This would reduce the time required to update the data, would make the process of using the data easier, and would greatly reduce the need for separate spreadsheet dedicated to the external connection. Despite it's possible inefficiency, however, the External Data Connection allowed for the accurate conversion between Euros and Pound Sterling.

I think that the functions and formulae I used were sufficient, and met their requirements. I feel like the use of VLOOKUP was also successful in extending upon the data already provided, which wasn't a documented requirement.

Conditional Formatting could have been used to greater effect had I picked more contrasting and saturated colours, which I decided against initially as they appeared to be excessively attention-grabbing. Other than the choice of colours, I believe the range of different conditional formatting rules available in Excel was used effectively.

Personally I feel that PivotTables have limited use, but that the use of them in the spreadsheet model showed one of their most useful features, asides from the flexibility in creation; the expand and collapse functionality on groups of data. This allows a great amount of data to be available on a spreadsheet, and without consuming a large number of cells initially. Without collapsing or hiding rows or columns, which can be cumbersome to reveal again, the PivotTable allows information to be consolidated and summarised by category.

Overall, I believe the spreadsheets were successful, but both could benefit from further development.