Using Excel To Create A Class Database Tim Brosnannet

Summary

Using Excel to create a student database
Excel can be used to create spreadsheets or very simple databases

The difference between a spreadsheet and a database
A spreadsheet is used primarily to record numerical data and perform
calculations related to it whereas a database is used to store factual
information and to sort it and analyse it according to given criteria. For
example, you might use a spreadsheet to keep a record of examination
marks for a class whereas you would use a database to store the names,
dates of birth and telephone numbers of a class

Terminology
The term list as used in Excel refers to a series of worksheet rows that
contain related data, such as a set of contact names and phone numbers,
or a set of student marks or data from a survey

A list can be used as a database, in which rows are records and columns
are fields. The first row of the list has labels for the columns

Fields
Surname First Name Date of Birth Telephone SEN stage EAL – Y/N
Records Davies Beverly 30/12/1985 7 568 2365 4 N
Mosole Koko 19/08/1987 7 981 3957 1 Y
Davies Richard 24/12/1985 8 753 6857 N
Tew Ioan 17/04/1986 7 612 3831 Y
Cahill Grace 03/06/1987 7 905 7023 3 N
Evans Cathyrn 12/09/1986 8 335 6711 2 N
Tucker Sophie 01/02/1985 7 612 4035 N
Lists in Excel can be used for storing data that you might be tempted to
set-up a database for in a specialized database application such as
Access, or Information Workshop. The list below gives some pointers as
to when Excel is appropriate to use for managing lists

• Small amounts of data <500 records
• Little or no duplication of data. (For example you have don’t have to
enter the same address several times in lots of different records.)
• Data is only going to be used for simple reporting, eg. Mail-merging
data with Word, simple calculations, or looking up information

• Only one or two people are likely to be involved in inputting the data,
and only ever one person at a time

Sorting
When creating lists, especially long lists, it is useful to have the list
organized into a particular order eg lists of people are usually organized
alphabetically by surname

It would be time-consuming and impractical when adding items to a list to
have to find the correct place in the list and make a space for the new
item before being able to type it in. It is far better to enter the details
at the end of the list and then sort the whole list

Sorting also has to be carried out before using other useful Excel tools
such as sub-totals, which are described later

A quick sort can be done from an icon on the toolbar, and provides a
means to sort rows into ascending or descending order, based on the
contents of one column such as ‘surname’

How to sort a single column at a time:
1. Click in a cell in the column you would like to sort by

2. Click Sort Ascending/Descending

Ascending Descending
This method of sorting is usually used when sorting by one column only,
but it is possible to sort on more than one column by carrying out several
quick sorts one after another. The multiple sort method described later
(see Bigger Sorts) is however a more sensible approach when the sort
order depends on more than one column

Each time a list is sorted on the same worksheet, Microsoft Excel
"remembers" the previous order chosen (unless you specifically change
that order)

Example:
To sort a list by “Surname” and then by “First Name” using quick sorts -
quick sort by First Name
then quick sort by Surname
This will leave the list in order by “Surname” and then by “First Name”. ie
carry out the sorts in reverse order, the main item to be sorted is
"Surname" so that is sorted last

First click on a cell in First Then click on a cell in
Name then click sort Surname then click sort
ascending ascending
Bigger Sorts
This method is the best method to use when the sort order depends on
several columns e.g. if you wanted to sort a list of students you might
want to sort by "Surname" then "First Name"
For best results, the list you sort should have column labels ie. headings
at the top of each column

How to sort a list based on the contents of two or more columns:
1. Click in any cell within the list you want to sort

2. On the Data menu, click Sort

3. In the Sort by and Then by boxes, select the columns you
want to sort

Example: In the student records list above, to sort by Surname then by
First Name Fill in the boxes as shown:
Auto Filters
Sometimes when using lists it isn’t enough to have the items organized
into a particular order, there may be a requirement to look at a number of
items that can't be grouped together by a sort. In such cases filters
should be used. Filters allow the temporary hiding of items that do not
meet specified criteria

BEFORE FILTERING ANY LIST, MAKE SURE THERE ARE HEADINGS
ENTERED FOR EACH COLUMN

How to filter a list:
1. Click in a cell in the list you want to filter

2. On the Data menu, point to Filter, and then click AutoFilter. This
will turn the filters on for all the columns in the list

3. To display only the rows that contain a specific value, click the
arrow in the column that contains the data you want to select by

4. Click on the value being searched for

5. To apply an additional condition(s) based on a value in another
column, repeat steps 3 and 4 in the other column(s)

Example:
In the student list example, to show only those students with EAL follow
the instructions above and select Y as the value to click on in step 4

You should then have a list with the following records (only those which
show students with EAL):
When clicking on the filter arrow at the top of the column a drop down
menu will appear. This will show the list of all values available for that
column, plus the options shown in the table below
To Click
Display all rows All
Display all rows that fall within the Top 10
upper or lower limits you specify, either
by item or percentage; for example, the
amounts within the top 10 percent of
sales
Apply two criteria values within the Custom
current column, or use comparison
operators other than AND, the default
operator. (See More About Filters)
Display only rows that contain a blank Blanks
cell in the column

Display only rows that contain a value in NonBlanks
the column
Filters can be carried out on the contents of one or more columns at a
time, but the order that filters are applied is important

Once a row is hidden by a filter(s), it will not be reappear in subsequent
filters until the filter that hid it is removed. This means that when
applying a second or third filter to a list, the only filter values available
are the values that are still visible in the filtered list

For example, if you perform a filter in the SEN Stage column to find all
non-blanks after you have filtered for EAL students, you will only be
filtering through 2 records : Koko Masole and Ioan Tew, so your result
will only give you the record for Koko Masole

More About Filters
It is possible to filter by two values in the same column, relationships
such as AND, OR, BETWEEN, IS GREATER THAN etc

How to filter by more than one value in the same column:
1. Follow the steps 1-3 above
2. In step 4, instead of clicking on a particular value choose CUSTOM

3. Enter the appropriate selection criteria and click OK

Example:
To find everyone on stages 1-3 of the SEN register, follow the
instructions above, and in step 3 fill in the selection criteria as shown
below

You should then have a list just showing the records for Koko Masole,
Grace Cahill and Cathryn Evans

Data input forms
Rather than typing the items for a list directly into a spreadsheet, it is
possible to create a simple data-input form to use instead

As well as providing an input method data-input forms also allow you to
flick through the items of a list one at a time, and to search for
particular entries

How to create a data-input form:
1. Type in the labels (field names) at the top of each column. (If a list
already exists you can skip this step)
2. For a new list click into any one of
the heading cells and on the Data
menu click on Forms… OR for an
adding to an existing list click into
any cell within the list and on the
Data menu click on Forms

3. Enter the data in each of the fields

4. Once you have finished entering the
data click on CLOSE

5. To use the form again you must click
onto any cell containing data or a
label, then on the Data menu click on
Forms…
Data Protection
Schools of course keep records of student information. You need to be
aware that the information you hold about students is sensitive and
confidential. The principles of the data protection act are outlined at
http://www.dataprotection.gov.uk/principl.htm and more detailed information
is available from the rest of this web site

Possible to create a simple data-input form to use instead. As well as providing an input method data-input forms also allow you to flick through the items of a list one at a time, and to search …

Download Now

Documemt Updated

Popular Download

• › Understanding Poetry 5th Grade Introduction To Writing Poetry
• › Understanding Your Request For Information Letter
• › Us Small Business Administration Counseling Information Form
• › Uploading Tests Or Test Pools In Blackboard Using Excel
• › Using Google Slides To Create Flyers Antioch University
• › University Services The Advising Ecosystem At Cmu
• › Understanding Responsive Email Design Vision6
• › University Services Coursecatalogwebcmuedu
• › User Guide For Work Order Control Rma Depot Repair
• › Using The Templates For Word 2003 Manitobaca
• › Uscis Form I 9 University Of Central Arkansas
• › Us Army Marketing And Engagement
• › Using Microsoft Teams To Manage Care Requests
• › Using Semantic Technologies To Manage A Data Lake Data
• › Udx Shopping Using Punch Out Catalogs
• › Udx Shopping Using Hosted Catalogs University Of Delaware
• › Using The New Nursing Practice Assessment Document
• › Us Small Business Economic Profile Small Business
• › User Instructions For Authorized Personnel Virginia
• › Uscis Form I 9

Frequently Asked Questions

How to create an excel database?

Creating an Excel Database 1 Make sure you have all the required columns and name each heading properly. 2 Once the headers of the data table are clear, we can easily start entering the data just below the respective column headings. 3 As I said, each column is called Fields in the database. ... 4 Fill all the data carefully. ... More items...

How to create a visualisation table in excel?

Every column is called Field in the Excel database. The column headings are called Field Names. Once you are done with inputting the data, it should be converted into a better visualisation table. Select cells A2 to F12 from the spreadsheet. Go to the Home tab. Select Format as Table drop-down menu. You can choose a table layout of your own.

How do you use data tools in excel?

The basic format for storing data in an Excel database is a table. Once a table has been created, Excel's data tools can be used to search, sort, and filter records in the database to find specific information. To follow along with this tutorial, enter the data as it is shown in the image above.

How to convert data into a data model table in excel?

Now let’s convert your data into a data model table! Click inside any cell with data (avoid blank rows), and from the menu toolbar, go to Insert tab > Table. All the rows and columns with your data will be selected. We don’t want the title to be included in the table, so we have to manually highlight the table without the title.