Tech Help
	Back

Excel Basics

• Excel is a primarily a spreadsheet program
• The file you create in Excel is called a workbook
• A workbook is made up of several worksheets, which are the pages that look like an accountant's ledger sheets
• The "boxes" on the spreadsheet are called cells, and are identified by their corresponding letter and number (e.g. A1, G8)

Setting up the database table

Decide which categories you want to have (e.g. Name, Address, Birth Date). Categories in a database are called fields. Each row of information is called a record. A database in Excel is referred to as a list. To enter the fields, click in cell A1 and type in the name of the field. Continue across the spreadsheet until all your field names are entered. (You can move from one cell to another by clicking the mouse in the next cell, using the Tab key to move horizontally and the Enter key to move vertically, or by using the arrow keys.) It is best to store information in its smallest logical parts, for example, have a First Name field and a Last Name field instead of entering the whole name as a field. It's a good idea to format the field names in bold type to distinguish them from the data.

Entering the data

There are several ways to enter data. One way is to type the information in directly into the table, in the same way you entered the field names. Another way is to use a Excel's Data Form function. To access the Data Form, click on any cell that contains a field name. Click the Data menu, then click Form. Click the New button, then type each field entry into the appropriate text box. (You can move from one text box to another by using the Tab key to move to the next box, and the Shift + Tab keys to move to move to the previous box.) Click on Close when you are finished entering data. Data can be entered in any order as Excel's Sort function will sort the records for you

Another way to enter data is by importing information from another programs, such as Access and SDS. This can be done from within the other programs by saving the file as an Excel (.xls) file. These files can be opened by double clicking on the icon, or from within Excel by using the File / Open menu options.

Sorting the Data

Once data is entered, it can be sorted using Excel's Sort function. This function will sort data either alphabetically or numerically, and in ascending or descending order. To sort your records into a new order, click on any active cell in your database. Then, click the Data menu, then click Sort. The Sort dialogue box will appear. In the Sort By box of the window, click on the column on which you want to sort. Then choose Ascending or Descending. If you would like to sort by a second or third field, choose those field names in the Then By boxes. Be sure to click the Header Row button under My List Has so that your field names (Header Row) don't get sorted in with your data. Click OK.

Filtering the Data

Filtering allows you to work with a subset of data in a list. A filtered list displays only the rows that meet the criteria that is specified, and hides all other rows. To filter your data, click on any active cell. Click the Data menu, click Filtering, then click AutoFilter. An arrow appears alongside each field name. You select the records you require by clicking on the arrow of the appropriate field, and select the item that records must match to be included in the list. To return to having all records displayed, click the Data menu, click Filtering, then click AutoFilter. This will turn off the filtering option.

Editing the Data

You can use the Data Form to change data in an Excel database. To access the Data Form, click on any cell that contains a field name. Click the Data menu, then click Form. Scroll to find the record you want to edit. Make the changes in the appropriate text box. You can also edit data by double-clicking in the cell you want to edit. After you make the changes, click the Enter key so that your changes will be accepted.

Adjusting the Column Width and Row Height

To change the width of a column, click on the line between the column labels (letter names). The cursor will turn into a double-headed arrow with a vertical bar through the center of it. Dragging the line between column labels to the left or right makes the column width smaller or larger. To change the height of a row, click on the line between two numeric labels and drag the line up or down.

Highlighting a Column or Row

To highlight a column, click somewhere in the gray box that contains the letter above the column you want to highlight. This will highlight the entire column. To highlight a row, click on the gray box that contains the row number. This will highlight the entire row.

To Add a Column or Row

To add a column, highlight the column next to the right of the one you would like to insert. Click the Insert menu, then click Columns. This will insert a column to the left of the highlighted column. To add a row, highlight the row below the one you would like to insert. Click the Insert menu, then click Rows. This will add a row above the highlighted row.

Deleting a Column or Row

To delete a column or row, highlight the column or row you want to delete. Click the Edit menu, then click Delete, or click on the Delete key on your keyboard.

Copying and Pasting a Column or Row

To copy data from one column or row to another, highlight the column or row, click on the Edit menu, then click Copy. Highlight the column or row you would like the data in, click on the Edit menu, then click Paste.

Importing an Excel database into an Access Database

Open or create the Access database that will receive the Excel list. Click on the File menu, click on Get External Data, then click on Import. The Import dialogue box will appear. Find the Excel file you want to import. Be sure to choose Excel Spreadsheet (*.xls) in the Files of Type field at the bottom of the window. The Import Spreadsheet Wizard will walk you the import process.

In the first screen of the Import Spreadsheet Wizard, choose the worksheet you want to import, then click Next. In the next screen, check the Select the First Row Contains Column Headings box so that Access can use the correct field names. Click Next. The next window allows you to specify whether you want to import the data into a new table or into an existing one. Click Next. The next screen allows you to designate a field to be used as a primary key. Click the No Primary Key box, then click Next. The last window allows you to give your new Access table a name. Type in the name, then click Finish. You can now access your database by double-clicking the icon next to the table name that you chose.