Kick your spreadsheet skills up a notch by taking this crash course in how to use Excel's database functions.
By Chris Dunn
Do you support Excel users who are afraid to try anything but what they know? Let’s face it, we all like to stay in our comfort zone—I’ve worked with people who use their calculator to add two numbers together, and then type the sum in their Excel spreadsheet. Getting users to try something new can be daunting, but once they see the productivity gains, it becomes a much easier sell.
A common occurrence in many companies is the unwieldy spreadsheet—often created in a hurry without much thought to how the data would someday be used. When that day comes, and your users need to summarize the data, they will stick with what they know without seeking the best solution. The database functions are good examples of tools that many users fear, but that can be great time-savers.
A single-variable scenario
To demonstrate how the database functions work, we’ll use the simple spreadsheet with one column of data shown in Figure A. Suppose it’s time to cut Bob’s commission check, so you need to know his total sales for this period. There are a couple of approaches you could use, including the Data Filter tool. In addition, you could sort the list, which might work in this example, but that approach can really be a pain for much larger spreadsheets.
|We’ll demonstrate how to use Excel’s database functions to extract information from this range.|
What I like to use here is the database function DSUM. The function call takes the form:
DSUM (database, field, criteria)
Our database in this simple example is the range A1:B10, and the field we’re summing is “Weekly Sales.” The criteria parameter requires just a little more work. To set up the criteria, we enter the column title in cell A14 and the name of the salesperson in cell A15, so that our criteria range is A14:A15, as shown in Figure B.
|To set up a criteria range, you simply copy the appropriate column title to a new location and type the appropriate entry below it.|
To calculate the sum of Bob’s sales as they appear in this sample database, just enter into another cell the function:
=DSUM(A1:B10, “Weekly Sales”, A14:A15)
If the Excel sheets in your shop are suffering from GIGO (“garbage in, garbage out”), check out “Ensure accurate data entry in Excel by using Data Validation to create drop-down lists.” To learn how to use a pivot table to summarize your data, read “How to create a pivot table in Excel 97.”
A multi-variable scenario (with wildcards!)
Now let’s kick this exercise up a notch. Suppose you have a spreadsheet like the one shown in Figure C that contains daily and weekly figures. You want to calculate some summary data (average, min, max, and so on) by week.
|You can use database functions to summarize the information in this range.|
With just two weeks of data, averaging the results is a snap. But what if you have a year’s worth of data? Using the average function would mean using a function in the form AVERAGE (E7, E13, E19…E313), and that's no good—it’s too long to type and too tedious to build the formula by clicking on the appropriate ranges.
It’s time to use another database function, DAVERAGE. This time our solution will be a bit more elegant. Using the sample sheet from Figure C, we’ll name our data range (A1:E13) "Database." (To do so, select that range, open the Insert menu, select Name, and then choose Define. Type the name and click OK.)
Next, create a two-row table with the same column headings, as shown in Figure D, and name this range "Criteria." Then, enter the values you are looking for in your Criteria table. In this case, we want to define two conditions:
- We entered “>2” in the Printers column, indicating that we only want to count records where the value in the Printers column is greater than 2.
- We entered "Week*" in the Day column, indicating that it doesn’t matter in which week. Note: Excel allows the use of wildcards here, so my database functions will be performed on all rows containing “Week” in the Day column.
|We copied the column labels from our original data range to set up this two-row criteria range.|
At this point, we’ve created a database and set up the criteria range that Excel will use to query that database. All that remains is to write the formulas, which are quite simple. For example, to find the average weekly monitor shipments, use the function:
=DAVERAGE (Database, "Monitors", Criteria)
To find the lowest weekly shipment of keyboards, use:
=DMIN (Database, “Keyboards”, Criteria)
Of course, you can change the values in my criteria table to refine your queries. Let’s say you want to know how many times you shipped more than two printers on a Wednesday. (I don’t know why; just play along). To do so, just change the criteria as shown in Figure E, and then enter the formula:
=DCOUNT (Database, “Printers”, Criteria)
In this case, the function returns the value 1, since the database shows that we shipped only one printer the first Wednesday and three printers on the second Wednesday.
|We’ll use this criteria range in a database function that counts the number of times we shipped more than two printers on a Wednesday.|
Only the beginning
Excel offers a dozen or so database functions in all, and we’ve just scratched the surface of what you can do with them. I encourage you to try some simple examples on your own, and you’ll soon be a master of the database functions.
Chris Dunn is director of technology at MediaVenture, a newspaper-focused marketing and technology agency based in Louisville, KY.To comment on this tip, or to share your experiences with Excel’s database functions, please post a comment below or drop us a note.