Using Excel as a Database

Frank Schneemann,  © 1990

 In this exercise we will use Excel to manipulate data in an excel spreadsheet designed as a database. This exercise is only intended to familiarize you with the basics of the Excel database function. Excel has many more features and is much more powerful than demonstrated in this tutorial. This exercise will get you started. Later, you can explore Excel further.
Creating the spreadsheet/database
  • Open Excel and start a new Excel spreadsheet
  • Enter the information just as you see it below

Sorting Data

  • Place the cursor in cell C7

  • Click the Sort Icon to sort alphabetically
    As you can see, it is simple to sort by any column in the database
  • When you have finished, print the sorted spreadsheet

 

Summarizing Data (Break Point)

 We now want to find out how much money we have earned in each of the cities in our Excel database.

To do this we must first sort the data.

  • Sort the database by City using the “Sorting Data” method outlined above.

  • Open the Data Menu and click Subtotals

  • Fill in the boxes as we have on the right
    At each change = City
    Use functions = Sum
    Add subtotal to: = Amount

 

 

Your sheet should look like the one below

  • Print your spreadsheet

  •  Experiment by clicking the 1 2 3 icons

 What effect did these icons have on the spreadsheet?
 Write the answer on the spreadsheet.

  •  When you are finished, open the Data Menu,

  • Click Subtotals,

  • Click Remove all

 

Filtering Data

  • Make sure you have removed the subtotals as explained above

  • Open the Data Menu and click Filter

  • Click Auto Filter
 

You should have little Drop Down Arrows next to each field name

      

  • Click the drop down arrow for the City field

  • Click Chula Vista from the list

The only data showing now should be records from Chula Vista

  • Print the spreadsheet
 

This assignment requires you to turn in the following printed exercise

  1. Printed copy of the basic database you created from the tutorial

  2. Printed copies of the database sorted, filtered and subtotaled.

 You did great