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.

Fax a Duplex Document

Ever get confused trying to fax a document that has two sides from the district Konica photocopiers?  Well, here’s how to fax a document that is printed on both sides of the paper (duplex). Follow the steps below.

 

Place originals in the feeder.

 

Press the “Fax/Scan” button

 

Press the “Simplex/Duplex” button

 

Press “2-Sided” button

Press OK

 

You will now be back at the Scan Settings screen

Press OK on the Scan Settings screen

 

Proceed with your fax as normal.

 

 

 

 

Field Trip

Available from the Google Play store for Android devices, this free app will show you, based on your location, information about historical places you are close to as well as places where you can shop or eat.

You can download or find out more information here:

https://play.google.com/store/apps/details?id=com.nianticproject.scout&feature=nav_result#?t=W251bGwsMSwyLDNd

Nexus 7 Android Tablet

The Nexus 7 Android Tablet has a 7 inch screen and runs the latest version of Android 4.2.1 (known as Jelly Bean). There are 3 different models of the tablet. The models are A 16 GB Nexus 7, 32 GB Nexus 7, and a 32 GB nexus 7 with Mobile Data.

The 16 GB and 32 GB Nexus 7 tablets are WiFi only, meaning that in order to download an app to the tablet or browse the internet a WiFi connection is needed.

The 32 GB Nexus 7 Tablet with Mobile data has the ability to connect to AT&T ‘s cell service with a mobile data plan.

There are several downsides to the Nexus 7 Tablet. First, it has no rear facing camera and no expandable storage. The tablet has a front facing camera for use with Google Plus hangouts or skype, but no camera to take pictures with. This is not a big issue since most people have a cell phone with a camera in it or own a camera. Also, it can be awkward to take pictures with a tablet.

Expandable storage is an issue if you plan on storing large files like movies or pictures on the device. There is no slot to insert a microSD card.

The 16 GB Nexus 7 is $199 and can be purchased at: https://play.google.com/store/devices/details?id=nexus_7_16gb

The 32 GB Nexus 7 is $249 and can be purchased at: https://play.google.com/store/devices/details?id=nexus_7_32gb

The 32 GB Nexus 7 with Mobile Data is $299 and can be purchased at: https://play.google.com/store/devices/details?id=nexus_7_32gb_hspa_att

These tablets may also be purchased at most major retailers and box stores.

 

 

 

How to Take a Screen Shot of Your Computer Screen

Pictures are worth a thousand words. Often it is easier to send a picture than describing what you see on your computer. You can easily take a picture of what you see by using the computer’s print screen function. To use this, press the Print Screen button on your keyboard.

Then open Microsoft Paint (by clicking on: Start Menu, All Programs, Accessories, Paint)

After you have pasted your image into paint, click the menu button then save as a jpeg file.

Save the file on your desktop so you can easily find it then attach it to an email, work order or print it.

 

Everyone Wants a Grant

It’s true!  I don’t believe there is a question I am asked more then, “Do you know of any grants I/we can apply for?”  The short answer there is yes.  You can find grants for anything you want depending on the amount of work you wish to put in and the time frame that you want to receive your grant benefits.  The longer answer is that grants are not always worth it.  Some grants will require lots of writing, data collection and, sometimes insidiously, that you follow their initiatives.  Some grants will require data collection before, during and after the grant’s active period, as well as some analysis of that data.  Not doing this work, or not doing it correctly, results in having to payback the amount of the grant.  There is a reason why grant writing is a career path itself.  There are secret expectations and language used when organizing and writing grants.  In short, no one gives you anything free; you will work for what you get.

Are you scared away from grants yet?  No?  Good!  In this series on grants and grant writing you will find answers to questions like where can we find grants to apply for, how do you organize and write a grant, why do “they” word their questions so oddly, and  do I really need to do everything they are asking?  The only way to win at grant writing is to own the process.

Let’s start with this question: “do I need a grant to achieve my goal?”  This may sound a bit silly, but lots of times what you think you need may already exist within the district somewhere.  So before you start putting in long hours on a grant, here are the people you should check with first:

  • Other Teachers or Teams
  • Your Principal
  • your CTLs
  • The IT Director or department
  • The Curriculum Council
  • The Superintendent

If none of these people can help you, then it may be time to start a grant.  Here are some questions to ask yourself to know if finding and writing a grant is worth the undertaking:

  • Do I have a concrete idea of what I want?  This includes
    • A basic plan for data collection
    • A basic plan for implementation
    • A basic timeline for the above and for the life of the program/project
    • A basic budget
    • A basic hierarchy of people included in the program/project
  • Have I checked with–and gotten the approval of –my administrators?
  • Do I have 10-100 hours to put into writing and maintaining a grant?
    • While this is not even close to a solid statistic, start with an average of 2 hours for every 500 dollars of grant money.  Go up from there just to estimate the time needed.
  • Will I be reimbursed for my time in writing and organizing the grant?

There are lots more questions to ask yourself and your colleagues before the organizing and writing process begins, but most importantly is this question to sincerely ask yourself: is it worth it to me?  That alone may be all the answer you need.