Spreadsheets are ideal for storing and processing lists and Excel has some special features to assist you with these tasks. A list is effectively a database, in particular a flat file database or table.
You can use a spreadsheet to keep lists of anything you like and you can store as much information as the size of the sheet will allow. A spreadsheet list is particularly useful if you need to store numerical information such as the cost of items because Excel easily lets you perform calculations on them.
Excel also includes many functions designed specifically for databases. Many data storage problems require a more sophisticated approach so you should not think that a spreadsheet is the answer to every situation.
As a first example, consider a list of GCSE results. In Excel we place data for each pupil on a row and we use the columns to store separate items of data such as name, form, subject, etc.
In database terminology each row holds a record of pupil data and each column holds a field within the record. We place the names of the fields in the first row of the worksheet.
This list/database could be used to:
As a second example, this is a list of names and addresses of potential employers:
This list/database could be used in conjunction with a word processor to generate a mail merge of the names and addresses with a standard letter.
We can view this data one record at a time through the Form option on the Data menu.
Using the form you can scroll through the data file one record at a time, add and delete records or field data and search for records which meet particular criteria.
To set the criteria for finding a record proceed as follows:
• Click the Criteria button on the form - a blank form appears
• Enter the criteria as data such as 'MU' in the House field
• Click on the Find Next and Find Prev buttons to scroll through the records
which meet the criterion.
You can use this technique on any field but you have to write in the complete contents of the cell in the exact same format for a match to be made - 'Abbot, Stephen B.', for example, not 'Abbot, S.', or any other variant.
A Cell Comment can be attached to any cell on a worksheet. A cell comment is like a 'post it' sticker attached to the cell which can be either permanently displayed or shown only when the cursor is over the cell. It can store more characters than a cell and can be stretched into a size and shape which makes it easier to read large amounts of text - like a miniature word processor. To add a comment to a cell point at it, click the right mouse button and choose Insert Comment from the pop-up menu. Under Tools/Options/View you will find three options for displaying comments:
• None - comments are permanently hidden, useful if you want to avoid the
distraction of comments;
• Comment indicator only - a small red mark is displayed in the top right corner
of cells which have comments; the comment appears when the cursor rests over the
cell. Use the right mouse button and the pop-up menu to show and hide comments.
• Comment and indicator - the comment is displayed permanently.
To print comments:
• choose File/Page Set up and select the Sheet tab
• choose one of the options from the drop down list for comments - None, At end
of sheet or As displayed on sheet.
If you have created a database for others to use, or if you want to avoid mistakes when using your own database you can set up various checks to reduce the risk of entering incorrect data. This is known as 'validation'.
Prior to selecting Validation from the Data menu you should select one or more cells to which you want to apply the validation rules. With validation you can:
• Set cells to accept a particular type of data;
• Set cells to accept data within a certain range;
• Show a message to tell the user what to do which is displayed when the user
clicks on the cell;
• Show an error message if the user enters incorrect data
Another database function provided by Excel is that of sorting. You may wish to sort the data on fields such as House or Form or to add a new record at the end of a file and then sort it to put the record in the correct place. To sort a datafile proceed as follows:
• Select all of the data items you want to sort
• Select Sort from the Data menu;
• Click on the drop down list for the first sort field - choose, for instance,
House or Tutor;
• Click the Ascending button to put the 'smallest' data items at the top of the
list or the Descending button for the reverse;
• Repeat this for the second and third sort fields as required - for example, to
sort by Name within House;
• Check the appropriate button for Header row or no Header row.
It is particularly important to select all the data before performing a sort operation, otherwise some of the fields will be moved from one row to another but others will not. Thus row 2 might hold four data items from record 2 but the other items, having been sorted there, may be from record 257.
The criteria for a search are also known as a 'filter' - you filter out the unwanted records leaving only those you do want. Excel provides two levels of filtering, Autofilter and Advanced filter.
Autofilter provides a simple way to search for records by placing a drop down list at the top of each column; you click on the drop down list and select the item you are searching for. Excel scans each column and makes an entry in the list for every unique item found. In the case of a column containing names there will be an entry in the drop down list for every unique name in the list so it is not particularly useful for this type of field. It is far more useful where a field has only a limited number of entries, as in the case of 'House', where there are eight, or Tutor, where there are fifty or so.
To use the Autofilter proceed as follows:
• Select any cell inside the database area
• Select Filter/AutoFilter from the Data menu
• Click on one of the triangles inserted in the header row across the database
• Click on one of the data items listed for that field
Excel removes all records which do not meet the search criterion. To restore the records you just removed select Filter/Show All from the Data menu.
The Advanced Filter allows you to search the database using more advanced criteria such as multiple conditions using AND or OR to link them, and 'wildcard' symbols such as '*' and '?' (‘*’ means any group of characters while ‘?’ means any single character). The Advanced Filter requires you to enter the criteria for the search in a separate area of the worksheet, above the data, to the right of the data or underneath them. To create an area of empty rows above your data select Row 1 (click on the '1' panel) and select Insert/Row. You enter the field name as it appears at the head of the column and the data item you are searching for in the cell underneath.
The first example uses two fields in separate columns to specify two criteria linked by AND. It also uses the '*' wildcard character to specify a '5' followed by any other character. This search will find all pupils who are in House 'A' AND in any Form which begins with '5'.
The second example shows two criteria linked by OR. This search will find all
pupils whose Tutor is either 'RLT' OR 'JA'.
For further search criteria see the Advanced Filters section in Excel Help.
To set up an Advanced Filter proceed as follows
The first set of criteria perform an AND operation, the filter searches for pupils who are in form 5W AND had 9 GCSE entries:
The second set of criteria perform an OR operation, the filter searches for pupils who are in form 5W OR 5C:
Use Data/Filter/Show All to restore the database.
A report is a summary of information in a database in a structured format. A report may include every piece of data in a database or it may consist of a more restricted set of data produced by a filter. A report will almost certainly have headings and may be grouped in some way so that records may be seen in a logical way, for example pupils could be grouped by form and by gender and postcode.
To print the same rows at the top of each page of a report use File/Page Setup. Under the Sheet tab there are options to specify the 'rows to repeat at top' and 'columns to repeat at left'.
This can be done by adding a subtotal for each row - in Data/Subtotals there is an option to add a page break between groups.
Alternatively you can add a manual page break after any row by clicking on a cell in column A in a row and choosing Insert/Page Break (or select a row and insert the page break).
Choose File/Page Setup and use the Scaling section on the Page tab to scale the page size or fit it to a set number of pages.
You may want to take different parts of a workbook and integrate them into a single page report. To do this first prepare a worksheet with a suitable heading. Next select the cells on one of your worksheets and copy them to the clipboard. Back on the report page hold down Shift and choose Paste Picture Link from the Edit menu (this option only appears when you hold down the shift key). This will create a live picture link to the cells in the other worksheet, rather like an Excel table in Word. Repeat for other worksheets. Add lines or borders to cells to separate the different parts of the report. This technique works with different column widths on different worksheets.
Sorting and filtering can be wrapped up in a macro. Record a macro to sort the data in a particular way or to perform a filter that you think will be used often. The macro can be run from a keyboard combination or from a graphical device such as a shape or text box.
A macro to find pupils in a form with a specific number of GCSE entries was created using Tools/Macro/Record New Macro:
This uses the criteria illustrated above, 5W and 9 stored in cells AB9 and AC9. This macro can be assigned to a graphic button created from a simple rectangle or, with a little more work, to a command button from the toolbox.
For more detail on macros see here.
GCSE work here.
An introductory exercise for this topic can be found here.
For GCSE or A Level a pupil might devise a questionnaire to collect data from people on a particular topic. The questions and answers might be put in an Excel spreadsheet and converted to a database or pivot table for further analysis.
See here.
Use the Back Button to Return to the previous page