Excel Database Tutorial
This tutorial has been designed for beginning to intermediate users of Excel. In Part 1, you will learn how to construct a simple Excel database and how to use a pivot table to create reports from the database.
The second part of the tutorial (coming soon) shows how to use VBA and Forms to automate the workbooks built in Part 1. By providing a friendly user interface, you are effectively building an application that allows even novice users to use your workbooks.
The tutorial places a lot of emphasis on developing workbooks with a common style. The adopted style has been developed over a number of years so that it makes good use of Excel's underlying features and provides a good platform for workbook development. This makes it easier to maintain a large number of very different workbooks. If you know, for instance, that every workbook you develop contains basic data in a sheet named "data", uses dynamic ranges that are documented in a sheet named "docs", and contains parameters for user controls in a sheet named "setup", then you can quickly make changes to your workbook even years afeter you first developed it.
In part, this tutorial explains my style of workbook development. Hope you enjoy it.
This tutorial is for use with the Excel workbooks provided. The finished tutorial excercise is Simple.xls. Download this now if you wish to skip the tutorial and use the sample database.
- A question of style
- The data list
- Lookup lists
- Reporting with Pivot Tables
- Determine what the form must do
- Adding Controls
- Event code
- Manipulating the Pivot Table through code
1) Setting Up the Workbook
a) A question of style
Default installations of Excel open new workbooks with 3 blank sheets (16 if you have Excel 95). I usually change the default settings (in Tools-Options) to open new workbooks with only one sheet. It is an easy thing to add new sheets when you need them, and I find it annoying to receive workbooks from people that contain 3 worksheets where one, two, or three might contain data, and the others are empty.
I strongly suggest that all worksheets in the workbook are renamed to indicate what they contain, and any empty sheets should be deleted. To rename a worksheet, double-click the sheet tab and type a new name.
In this tutorial, we will build some simple Excel databases. We will use four sheets, named ‘Data’, ‘Setup’, ‘Docs’, and ‘Report’. Hopefully it is fairly easy to guess what type of information we will put in each of these sheets, but I will spell it out here.
||Contains only raw data. One record for each row, different fields in each column, column headings in the first row. This is a simple Excel data list.
||Contains any information we will need to define workbook parameters and to limit data input.
||Contains a report based on the information contained in the Data sheet. In this case it will contain a summary report written as a pivot table.
In practice, we could have several report sheets, each with a meaningful name. eg
b) The data list
For the purposes of this tutorial, we will set up a simple workbook to track sales of wine for a small wine wholesaler.
Create a new workbook, rename "Sheet 1" to "Data" add column headings for the date of sale, name of the customer, number of cases sold, and product code for the goods sold. Enter some data so that the sheet looks like this;
Each customer name needs to be entered in exactly the same way every time. Otherwise, you would not be able to filter the list correctly, and you would be unable to add sales for each customer.
You can always copy customer names downs the list, but a better solution is to keep a standard list of customer names and to use cell validation techniques to add a customer to your data list.
First, insert a new sheet and rename it as "Setup". Then add a list of customers as shown below;
Define a dynamic range for the customer list. Dynamic ranges automatically expand as you add data to cells below the range. In this case we will use a COUNTA function to count the number of non-empty cells in column A, and the INDEX function to return the last non-emty cell in column A. Then the dynamic range can be written as "startcell in column:last non-empty cell in column", or A2:Index(A:A,last non-empty cell in column), or A2:INDEX(A:A,COUNTA(A:A)). We will need to use absolute addressing to lock the start and end cell of our range.
Make sure you are in the Setup sheet, use menu item Insert - Name - Define to show the following dialog, then enter "Customer" in the Names textbox and enter "=$A$2:INDEX($A:$A,COUNTA($A:$A))" in the Refers to textbox. Click the Add button - Excel helpfully adds sheet references for you. You can see this below.
Now, go back to the Data sheet, select the whole of column B by clcking on the column header, and use menu item Data - Validation to set up validation for every cell in column B. In the "Allow" drop-down select "List", and in the "Source" textbox enter "=Customer" which is the dynamic range you have just defined.