Leading the way in Microsoft Office Development

What is an Excel database ?

A database is a list of related data in which rows of information are called records and columns of information are called fields.

A database in Excel can be any size and should contain columns headings to identify the fields.

Databases can be used to help maintain, analyse and filter your data.

Your column headings will be used by Excel to indicate that this is a list of data ??

Organising your data as a small database will allow you to sort, filter and organise your data very quickly.

The following table is an example of a very simple database. Each column in your list represents a different field.

Each row represents a different record in the database and any new rows should be entered directly below the last record.

A worksheet can contain several database tables ?

Field names must be unique.

You should always leave at least one blank row and column between your table and any other data in the worksheet.

Excel is obviously limited to 65,536 rows and 256 columns. If you have a large amount of records it may be worth considering using Microsoft Access.

Worksheet Functions

There are 12 specific worksheet functions dedicated to working with large tables of data. These all begin with the letter "D" and are often referred to as the Dfunctions. They all take three arguments

Database - the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field - indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria - the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

You should be aware that you cannot use array formulas in place of your criteria.

The following example will not work

{=DSUM(Named_Range,3,{"Age","Name","26","Simon"})}

(Data > Form)

This dialog box can be used to capture data values for a list.

The corresponding data fields will be included that match those on the worksheet

You can add new records to the list, remove records and make changes to entries in the list all via a dialog box.

You can also use this dialog box to find items that meet certain criteria.

Using Subtotals

This allows you to quickly insert the SUBTOTAL() worksheet function combined with Outlines to help you summarise your information.

Things to Remember

The first row of your database table must contain column headings. These should be formatted differently so they stand out.

Do not use the same field name in more than one column of your table.

The advantage of using column names as opposed to column numbers when working with the worksheet functions means that columns can be inserted or deleted and the functions will still return the correct result.

Do not allow blank rows and/or columns in your database table.

Organising your data as a database will allow you to create specialised reports by using Pivot Tables. This is discussed in a separate section Pivot Tables.

Try to only use one database (or list) per worsheet.

Ensure the database table is always surrounded by at least one blank row and column.