Excel Database Functions Explained
This guide will provide a summary of the built in Excel Database Functions, with descriptions of each individual function, and examples.
We will cover:
- What are database functions in Excel?
- The syntax
- What functions are available?
- Examples
What are Database Functions in Excel?
Database functions in Excel allow for a series of basic calculations to be performed against a dataset that is similar in format to a more traditional database – a structured series of rows and columns.
Compared to a typical excel calculation formula such as SUM or AVERAGE, these database function formulas are a bit more complicated, but allow for greater control over the data we are working with.
The Syntax
As mentioned, the syntax for the database functions is a bit more complicated than a traditional calculation.
For example, if we had a small group of values running down across some cells and wanted to calculate the average, we might use the below formula:
=AVERAGE(A1:A5)
This would simply take the values in the 5 cells down from A1 to A5, and return the average.
Database functions as a formula require three separate arguments that are required by Excel. The syntax is as follows:
=DAVERAGE(database, field, criteria)
Let’s break this down:
Database: The database is the entire set of data that we want to work with. If we have a set of data starting in cell A1 (including headers) that is 5 columns wide and 10 rows high the ‘database’ argument would be A1:E10. It does not matter which column at this stage we are specifically looking to analyse, we are simply letting Excel know where all of the data sits.
Field: This is where we input the column of data that we are looking to work with. For this step we don’t need to select the entire column worth of data, but simply the header. If the third column of our previous example has test scores that we are looking to calculate the average of, with a header of ‘Test Scores’, we would simply enter C1.
Criteria: Finally, we let Excel know exactly which records we are looking to work with. In a lot of cases we would simply use the entire column (in this example C2:C10), but we can be selective – for example using C2:C6 to just work with the first 5 rows of data, and completely ignore C7 onwards in our calculations.
The syntax would then look as follows with the above example:
=DAVERAGE(A1:A10,C1,C2:C10)
Available Database Functions
The available Excel Database Functions are summarised as follows:
DSUM:
Returns the sum of all selected cells within the database
DAVERAGE:
Returns the average value for the requested cells
DCOUNT:
Returns the total number of cells out of those requested that contain numerical values
DCOUNTA:
Returns the total number of cells out of the requested that are not blank (numerical or otherwise)
DGET:
Returns a single record from the database that matches a specific entered criteria
DMAX:
Returns the highest value out of the selected cells
DMIN:
Returns the lowest value out of the selected cells
DPRODUCT:
Calculates and returns the product of the values out of the selected cells
DSTDEV:
Calculates the standard deviation from a sample of the data sitting within the selected cells
DSTDEVP:
Calculates the standard deviation from an entire dataset based on the selected cells
DVAR:
Calculates the variance of values from a sample of the data sitting within the selected cells
DVARP:
Calculates the variance of values from a sample of the data sitting within the selected cells
Database Function Example
Let’s look at a quick example of a couple of these functions in action. Let’s say we have a database that includes a group of students with the following columns: name, age, test score:
Using this dataset there are ways that the Database Functions can quickly provide us with some analysis.
If we wanted to easily see what the average test score is across the group we would use the following formula:
=DAVERAGE(B3:D12,D3,D4:D12)
This would result in the below output, highlighting the average test score of 70.56:
We know that there should be 9 students who took the test, but what if we wanted to quickly see how many we have test scores for? The DCOUNT function easily provides this information:
=DCOUNT(B3:D12,D3,D4:D12)
As expected, this returns a value of 9:
In this case it probably would just be easier to manually count them, or just highlight the cells and see what the count is in the bottom corner, however when working with larger dataset where we can’t simply look over the data then these database functions come in very handy.
This sums up our guide on Excel Database Functions.
For more tips and tutorials on using Excels built in functions be sure to check out our Excel Tips page
