Excel - Database Tools

From the Tools menu, Customize submenu, Options tab, select all options except “large icons.”

 

This assignment will teach how to use data tools to manage information stored in tables. You’ll learn about data sorting; creating forms to add, delete, and change data; and how to use search criteria to generate reports.

 

Data used http://www.cob.sjsu.edu/BUS91L/Textbook/DeliveryForm.xls

Do not open the file from the web. Save the file on your desktop. Then open the file.

 

Background

Excel has built-in database capabilities that are suitable for small and simple tasks. Consider using Access, or something even more powerful, for sophisticated, large, or complex projects. 

 

A data table consists of one row of labels, called field names or headers, and several rows of data. The headers row is always the first row, above the rows of data. The rows of data should not contain any blank cells. The information stored in each column should be consistent, (labels, dates, or numbers) not a mixture of types.

 

 When creating your data table, keep it separated from other areas in the worksheet by a blank row and a blank column. This helps Excel determine which cells are included in the database table. You can select any cell in the data table to work with the entire set of data; you do not have to select the entire table.

 

Tables are sorted based on criteria (headings, and /or formulas) which you specify. To sort a data table using one criteria, click in a cell in that column, then click on the A-Z (ascending order) icon, or the Z-A (descending order) icon. Warning: Make sure you click on a cell within the table, not on the column letter above the worksheet. Clicking on the column letter will sort items in that column, but won’t sort the other columns in the table. To sort using multiple criteria (last name, first name, for example) you will use the data menu.

 

Data can be maintained using Data Forms, or by editing directly in the cells.

.

Reports can be generated using search criteria based on headers. More complex search criteria can be created, using mathematical and logic operations to narrow a search.

 

 

Tasks:

 

Practice working with existing tables

 

Sort the County Table

When you use the A-Z or Z-A icons to sort data in a table, you have to start by selecting a column. You can click on any cell in a column that contains data. The entire table will be sorted, based on the order of the data in the column you selected.

  1. Select the Lists tab.
  2. Click on cell B8.  Click on the A-Z icon to sort the table based on sales tax rates.
  3. Click on cell C8. Click on the Z-A icon to sort the table based on delivery charges.
  4. Click on cell A8. From the Data menu, select Sort. Set the sort options to sort by Delivery (ascending) and by Tax Rate (ascending) with the header row button selected. Click Ok.
  5. Click on cell A8. Click on the A-Z icon to put the data back into alphabetical order by county.

Using Data Forms

Forms are a user-friendly method of maintaining a data table.

 

Add a Record to the Menu Table

  1. Click on cell A21.
  2. From the Data menu, select Form.
  3. Click on the New button.
  4. In the first box, type Pie and press Tab.  In the second box, enter 2.95 and press Tab.
  5. Press Enter.  Pie is now included in the table.
  6. In the first box type Cake and press Tab. In the second box type 3.50 and press Tab.
  7. Click on the Close button.
  8. Pie and Cake are now included in the table.

 

Remove a Record from the Menu Table

  1. Click on cell A16.
  2. From the Data menu, select Form.
  3. Use the scroll bar (if necessary) in the form to find “Pie.”
  4. Click the delete button. Click OK to delete the record.
  5. Notice how the row containing cake moved up. Tables will automatically adjust when you delete an item. What happens if something else was in that same row in the worksheet? Excel will not delete the information outside of the data table.
  6. Cake should now be in the top box of the form. Delete Cake.
  7. Close the Data Form. 

Change a Record in the County Table

 

  1. Click on cell A5.
  2. From the Data menu, select Form.
  3. Use the scroll bar in the form to find Monterey County.
  4. Click in the Delivery Charge box.
  5. Type 20 and press Enter.
  6. Scroll down (if necessary) and find San Francisco.
  7. Click in the Travel Time box. Type 70.
  8. Click the Close button.

Naming Ranges

Sometimes a formula or Macro will refer to a range of cells. But what happens if that area changes, for example, the cells in a data table which change as you add and subtract records? Excel automatically adjusts for this problem in formulas by renaming the cell borders of the range. Unfortunately, it does not adjust your Macros. There is a way to solve this problem. Excel lets you give a name to a range of cells, instead of using cell references. The name can be used in a Macro. Excel will automatically adjust the cells included in the named area as records are added or deleted, so Macros using the range name will function perfectly. Describing the range in English may also be helpful to the users of your worksheet.

 

Create a Named Range

 

  1. Highlight cells A17:B25 on the Lists Worksheet.
  2. In the name box, type the word Menu. The name box is located just above the worksheet, on the left hand side, over the letter A.
  3. In cell B28 enter the formula =SUM(Menu).

 

Creating Customized Reports

Create a filtered list

  1. Click on the tab of the SalesData worksheet.
  2. Click on any cell in the data range (A4:H13).
  3. In the Data Menu, Select Filter and AutoFilter.  Pull-down arrows will appear next to the column headings.
  4. Click on cell B4 and select the pull-down arrow. Select San Mateo from the list. Your display will show the two orders from San Mateo County.
  5. Click on cell B4 and select the pull-down arrow. Select Santa Clara from the list. Your display will show the three orders from Santa Clara County.
  6. Use Print preview to view the report.
  7. Click on cell B4 and select the pull-down arrow. Select All from the list. Your display will change to show all of the orders.
  8. Click anywhere in the data range. From the Data Menu, select Filter. Turn off the Autofilter.

You can create more complex sorting criteria using the Custom Autofilter on the Data menu.

Adding subtotals.

  1. Click in column B and sort the data in the table in alphabetical order by County, using the A-Z icon.
  2. Click in any cell in the data range. From the Data Menu, select Subtotals. A dialog box opens.
  3. In the dialog box, “At each change in” select county.
  4. Skip the “Use Function” and” Add subtotal to” boxes. These boxes give you additional ways to customize the report.
  5. Of the three checkboxes, select only “Summary below data.” Click OK.
  6. Your report will now include subtotals. Use Print Preview to view the report.
  7. Click in the range. From the Data menu select Subtotals. In the dialog box, click on “Remove All.”
  8. Close the file without saving it.