Forms

Introduction
The traditional way of displaying data in Excel is, of course, in worksheet format.
With very large datasets, this format can problematic trying to see all the data for a row or
overwhelming trying to view one row in the midst of all the others.
Perhaps future Excel versions will add to the Ribbon capabilities to add forms to your Excel
sheets, but we’ll add the Form button ourselves – to the Quick Access Toolbar.

Adding a Forms Button

1. Open the [Link] file, found in Canvas.
2. Click on the [Customize Quick Access Toolbar] button – the down arrow on
the right of the bar (shown circled in red below).

3. Choose More Commands... then set Choose commands from: to Commands

Not in the Ribbon (shown circled in red below):
 4. Scroll down and then select Form... then click the [Add>>] button in the middle of
the window.
5. Click [OK].

An extra button has been added to the toolbar (it looks like a form).
This is permanent on your own computer – if you’re working on a ‘temporary’ computer, (i.e. a
campus computer or one you’ve borrowed), this would be permanent on that computer.

Form View
6. Click on cell B4.
7. Click on the new Form button that you added to the Quick Access Toolbar.
Note*** You can also do this using the keyboard by clicking [Alt-D] and then pressing the
[o] key.
A form window appears – it uses the data headers from the top row of the worksheet
and contains the data from row 2.
There is a scrollbar in the center that you can use to move up and down the rows. You can also
move through the records by clicking on the [Find Next] and [Find Previous] buttons. As
well, you can now use the form to add new records.

Adding New Data

8. Add a new worksheet to the file by clicking the plus sign at the bottom of the
sheet (Sheet1). (You’ll have to close the form first).
9. In cell A1 type x.
10. In cell B1 type x again
11. Without leaving cell B1, click on the Insert tab on the Ribbon and click the
[Symbol] button, then choose Symbol.
12. From the Symbol window, change the Subset option (shows up when Font is set to
(normal text) to Latin–1 Supplement then choose the superscript 2 symbol
(bottom row, 2nd from the left).
[Link] Insert, then click Close.
14. Click in cell C1 and repeat steps 10-12, choosing superscript 3 instead of 2. (You can
also do this by clicking on the arrow in the bottom right of the Font option on the
Home tab and choosing superscript before typing the 2 or the 3).
[Link] in cell C2 and type the formula =a2*b2.
[Link] in cell B2 and type the formula =a2*a2.
[Link] on the Form button – if you get an error, you are not using Excel 2016!
[Link] 1 in the box for an x value, then press [Enter].
19. Repeat step 18 to enter further values from 2 up to 10 then click [Close] to close the
form – note that you didn’t have to enter the calculations again or drag them down,
they carry through automatically with a form.
[Link] (highlight) row numbers 3 to 11 and [Delete] the values.
[Link] in cell D1 and type y.
[Link] in cell E1 and type x*y.
[Link] in cell E2 and type the formula =a2*d2.
[Link] on the Form button.
Now you have 2 data values to add – the first one shows up in the form with the 1 x value and
a blank y value.
[Link] the ‘1’ value for x, press [Tab] to go to the y value box and enter ‘3’.
[Link] [Enter].
[Link] 2 for x, press [Tab], enter 4 for y and press [Enter].
28. Repeat step 27, incrementing the x and y values by 1 each, until you’ve entered
values of 10 for x and 12 for y.
Note** If you accidentally press [Enter] before you enter your y value, press the up arrow or
[Shift Enter] to move back to the previous row.

To delete a row of data:

29. Click on [Find Previous] to move to the data with an x value of 4.

30. Click on the [Delete] button to the right of the form scrollbar – a dialog box appears to
confirm the deletion.

31. Confirm the deletion by clicking [OK].

32. Click [Close] to close the form.

Filtering the Data

If you only want to look at certain rows you can set up a filter on a form:
1. Click on the Form button to display the form.
2. Now, click on the [Criteria] button to the right of the form scrollbar.
3. Use [Tab] or click with the mouse to move to the data you want to filter by – for example,
choose the y value.
4. Type in the value defining the filter criteria – for example, type 9.
5. Click on the [Form] button to the right of the form scrollbar.
6. Use the [Find Previous] and [Find Next] buttons to jump up and down through the rows

Note: the scrollbar arrows operate as before and move through all the rows
More than one criterion can also be set on a form, if needed, by clicking the [Criteria] button
again. The original criteria remains and a new one can be added.
We’ve done our criteria by definite numbers, but with numeric data, defining the criteria as an
exact match is usually not very helpful, since each row often has a unique value. In these
cases, criteria such as greater than or less than can be set by using greater than or less
than before the number value. Try this with your y value!

Note that greater than and less than can also be used for text (alphabetical) data.
Other criteria which can be used include less than or equal to (<=), not equal to (<>), text
beginning with letter c (c*), text including letter x (*x*).
Only very simple criteria can be set when filtering using a form. You cannot set criteria to select
one of two or more alternative values or a range between two values.
Further, you cannot calculate a value for the filter, so you cannot use a cell reference or any of
the functions.
Finally, when editing the criteria, note the [Clear] and [Restore] buttons, which clear any
previous criteria set and which restores the criteria if accidentally deleted.

Microsoft Query
Introduction
Microsoft Query lets you link to data held in a database (such as Access) and extract a
subset of the data to a sheet in Excel. By providing a value in a cell or picking one from a
drop-down list, the matching data is automatically passed back from the database to
Excel.

Importing Data from Access into a New

Query
Data can be imported from a database into Excel via the Import External Data command.
You can also import data stored in another Excel file.
1. From Canvas, in the Course Documents area, download and save [Link] to
your Windows desktop.
2. Add a sheet (Sheet2) to your file.
3. Move to the Data tab then click on [From Other Sources] using the Get External Data
pulldown.
4. Select From Microsoft Query
5. The Choose Data Source dialog box appears - select MS Access Database* then
click [OK]
6. Locate the [Link] file you saved, then click OK.

The Query Wizard now starts up:

 Select the students table and click on the + to the left of the table name to display the
fields - move across by hitting the > button) all the fields except photograph - click on
[Next>]
 The next area allows you to set up a filter (none is required here) - click on [Next>]
 The next area lets you up any sorting required (again none is required here) - click on
[Next>]
 Choose Return Data to Microsoft Excel to simply paste in the data (you will be looking
at View data or edit query in Microsoft Query later) click on [Finish]
 Finally, state where and how you would like your imported data to appear – choose a
Table and cell A1 in the existing worksheet - click on [OK].

The data appears in Excel as a table (alternatively, you could have chosen a Pivot Table or Pivot
Chart). Filters are automatically provided in the cells on the header row; these also give you
sorting options.
Editing the Query
You can now make any amendments required by editing the query:

7. Right click inside the table and choose Table then Edit Query... - the Query
Wizard again appears.
8. Click on [Next>]
9. Set up a filter - click on Tutor in Column to filter then use the list arrows to
first select equals then choose Dixon (you can type the name in the box) - click on
[Next>]
10. Set up a sort on LastName in ascending order - click on [Next>]
11. In the final step of the Query Wizard choose [View data or edit query in
Microsoft Query] then click on [Finish]

The results of the query are shown in the bottom half of the Microsoft Query Window.
The query would be of greater use if it were turned into a parameter query.
To do this, you have to change the current filter Value into a question surrounded by square
brackets.
12. In the Value: row, replace ‘Dixon’ with [Which Tutor?] (including the brackets!)
13. Close the Microsoft Query window by clicking on the red close window button
(the outer window if you have two). Don’t save the query if it asks you.
14. A dialog box appears - enter a parameter value (Dixon) then click [OK].
The results appear in the spreadsheet table.
To refresh the data or obtain a different set (as a result of supplying a different parameter):
15. Click on the [Refresh] button on the External Table Data area of the Table
Tools Design tab and choose Refresh.
16. The parameter box appears. Type in another tutor (Evans) – click [OK].

The Refresh button has additional capabilities:

17. Click on the arrow below the [Refresh] button and choose Connection Properties...
18. Note the Refresh control settings – here you can ask for the data to be refreshed
each time the file is opened, or every n minutes (to reflect any changes which have
been made to the database)

The Connection Properties dialog box also has a Definition tab – the settings are quite
complex here.
Note the links to [Edit Query...] and [Parameters...] at the bottom.
We’ll look at parameters next:
19. Click [Cancel] to close the Connection Properties dialog box
20. Click in cell L1 and type the letter x and hit Enter.
21. Now, right click inside the table and choose Table then Parameters.
22. Turn on the Get the value from the following cell: option and set the cell to
=Sheet2!$L$1. Also turn on Refresh automatically when cell value changes
 23. Click [OK] to close the Parameters dialog box.
24. Now type the name of a tutor (Fox) into cell L1 and press [Enter] - the query results
change to reflect the new value

Using a Drop-Down Menu to Supply a

Value
To add more dynamics, you can use a drop-down menu to supply the value for the
query parameter.

25. Move back to cell L1 then, on the Data tab click on the [Data Validation] button
pulldown and select [Data Validation...] – the Data Validation dialog box appears.
26. On the Settings tab, set Allow: to List
27. Now in Source: type the names of the Tutors (here, type just some of them:
Fox, Evans, Foot, Morris, Peters) then click [OK]
28. Test out the drop-down menu (Please note that the drop down is the outside arrow –
the instead is the filter) by choosing different tutors from the list in L1
29. Choose Peters as the drop down tutor.

Click on the File tab, save the file to your computer, then answer the forms assessment
based on your file and/or comprehension of the lecture.