Database basics in Excel

What is a database and why would I use it?

You may already have a database and not even realize it.   You could have a list of your students in a spreadsheet or a CSV (Comma Separated Value) file that someone sent you and you have no idea what to do with it.

Databases are great for organizing your data for use in a variety of applications.   A database in Excel might be a list of OAA scores, students or student and parent contact information.   If entered properly, Excel can use this information to create charts, sort, merge with Word documents and more.

Just having data in Excel does not truly represent a database.  In order to use the database outside of Excel and get the most out of it, you must enter it a certain way.  Take a look at the following table.

Example 1

Rick Sterling Network Specialist 419-996-3413
123 anywhere st, lima ohio rsterling@limacityschools.org
Jamie Hyneman Mythbuster 555-555-5555
911 Blown up Ln, San Francisco, CA

In Example 1, although you are storing it as an Excel spreadsheet, you might as well be using Word.  It is essentially a document for reading.  Excel does not look at this as a database because of the following:

  1. There are no column headers. (Not required but it does make sorting easier)
  2. There are spaces between records.
  3. Records span multiple lines and each column holds multiple types of information.
In Example 1, the data is grouped together but only a human can distinguish where one record ends and the next begins.  As I like to say, computers are dumb.  They do exactly what we tell them to do.  If we were to try to sort the above table by Column A we would get this:

Example 2

123 anywhere st, lima ohio rsterling@limacityschools.org
911 blown up Ln, San Francisco, CA
Jamie Hyneman Mythbuster 555-555-5555
Rick Sterling Network Specialist 419-996-3413

Our data just got scrambled!   Argh!

We don’t want that to happen so let’s start over. Let’s look at a very basic table and then rearrange and expand our data to meet our needs.

In a database, each record contains the same details or fields that define each record.  For example, a single record might consist of the following information:

Example 3

Rick Sterling Network Specialist 419-996-3413

Although technically that is a record of data, depending on what we want to do, it may not be the best way to store the information. (More on that later)

Our record consists of three fields: Name, Title, and Phone.   In Excel, one row equals one record of data.  Each column represents one “field” or property of that record.  If we have more records to add, we go to the next empty line and put in the data.

Example 4

Rick Sterling Network Specialist 419-996-3413
Jamie Hyneman Mythbuster 555-555-5555

Great!  Now we have a database!   What more could we ask for?   How about some column headers?

Example 5

NAME TITLE PHONE
Rick Sterling Network Specialist 419-996-3413
Jamie Hyneman Mythbuster 555-555-5555

Much better! Now we can give our data to someone else and they can understand what each column is meant to hold. It is also important to note that when using column headers or field names, try to use all captial letters and no spaces. This is how Excel knows that you are using column headers and not just adding another record to the top of your data.

Okay, so we’re all Excel experts so let’s quickly sort the data by last name.  We click in our table and then click Data, Sort.

What do you mean Excel can’t sort by last name? The last names are clearly in my database!

As you may have guessed, in order to sort by an element of data you have to store that data element in it’s own field. Let’s revise our table.

Example 6

FIRSTNAME LASTNAME TITLE PHONE
Rick Sterling Network Specialist 419-996-3413
Jamie Hyneman Mythbuster 555-555-5555

That’s better! Now we can sort by last name. Why didn’t I split up the phone number into area code and number? There are not many times that I would ever need to sort by number or just area code but you could if you really wanted to.

Now let’s say we have added about 300 records to our database and we wanted to send a paper invitation to each of these people.  Darn it all!   I forgot to add addresses!  Let’s add some columns.

Example 7

FIRSTNAME LASTNAME TITLE PHONE HOUSENUM STREET CITY STATE ZIP
Rick Sterling Network Specialist 419-996-3413 123 MAIN ST LIMA OH 45801
Jamie Hyneman Mythbuster 555-555-5555 911 BLOWNUP LN SAN FRANCISCO CA 90028

A few short hours later and half asleep, we’re finally finished!  (remember there are 300 rows of data in our scenario)  We are now ready to use our database for whatever comes our way.  Or are we?

Finally, sorting in Excel, would look like this:

Notice the checkmark beside “My data has headers”?

Summary

Excel can be a great place to start working with data.   But your data must be stored following these rules:

  • One record per row.
  • One field for each column that you want to be able to sort by.
  • No empty columns between fields and no empty spaces between rows.
  • Column headers (optional but makes sorting easier)

Upcoming articles in this category:

Mail merge in Microsoft Word with an Excel table

Merging data from another database

Managing multiple types of data without redundancy

When your database exceeds the capabilities of Excel

(Your suggestions here – add to comments)

 

 

Technical Note for nerds:  This article is meant to be a beginner’s guide to database concepts using Excel,  While Excel may not be best suited for database operations, it is often the first exposure to working with data in a school enviornment.

Leave a Reply