This article is an ultimate guide about the crucial database functions in Excel. These functions will not only ease your task but also let you execute more smarter and logical functions for your databases in Excel.
Before you begin, you might want to learn to create a database in Excel.
Click here to learn to create a database in Microsoft Excel.
If you already have a readymade database or have read the article above, then let’s get started with learning about some interesting database functions that you SHOULD care about when dealing with databases in Excel.
Database Functions in Excel
Every database function allows you to execute a specific action or mathematic function on a database based on given criteria. You will learn how, by the end of this tutorial.
1. DMIN function
The DMIN function returns the smallest number from a column in the database based on a given condition. Let us learn this formula with a real example.
Here is a database containing data of different types of dresses, the material used in them, sizes, colors, and the total pieces available in stock.
Our goal is to find out the minimum stock value of Kurtis made of cotton. Note that this includes cotton Kurtis in small, medium, and large sizes and all the available colors in this type.
Here’s how the DMIN formula can help us out.
- Copy and paste the headers of your database on a blank beside it.
- Create a criteria table for your database to specify your conditions in it.
- Now in a blank cell, type the DMIN formula.
- Type DMIN(
- Select your entire database on the database argument.
- Put a comma and click on the column header whose values you want to find the minimum from. Here, Total piece.
- Put a comma and select the entire criteria table you created on the criteria argument.
- Hit ENTER.
You can see that we have found the cotton Kurti with the lowest stock in the database.
2. DMAX function
The DMAX function is the opposite of the DMIN function and helps us find out the highest value from a column in a database in Excel. The steps are similar to the DMIN function.
We can use the DMAX function if we want to find out a specific item with the highest stock based on a given condition.
- Copy and paste the headers of your database on a blank beside it.
- Create a criteria table for your database to specify your conditions in it.
- Now in a blank cell, type the DMAX formula.
- Type DMAX(
- Select your entire database on the database argument.
- Put a comma and click on the column header whose values you want to find the minimum from. Here, Total piece.
- Put a comma and select the entire criteria table when asked for the criteria argument.
- Hit ENTER.
Note that the DMIN and DMAX functions are advanced versions of the MIN and MAX formulas. They allow us to find out the lowest or highest values based on a condition we specify.
3. DCOUNT function
The DCOUNT function returns the count of the number of cells with numbers in them in the database based on a given condition. How to find out the total number of leggings that are less than 50 in stock? The DCOUNT formula is the perfect solution.
Here’s how the DCOUNT formula can help us.
- Copy and paste the headers from your database for which you want to specify conditions.
- Specify the conditions under the headers.
- Now in a blank cell, type the DCOUNT formula.
- Type DCOUNT(
- Select the entire database with headers on the database argument.
- Put a comma and click on the column header whose values you want to find the minimum from. Here, Total piece.
- Put a comma and select the entire criteria table when asked for the criteria argument.
- Hit ENTER.
You can see that we have three leggings with less than 50 as stock value.
Similarly, the DCOUNTA function counts the number of non-blank cells from a column in a database based on a condition you specify.
4. DSUM function
The DSUM function adds the values in a database based on a given condition. If we want to add the total stock values of tops in the database with less than 100 as the stock value, we can use the DSUM function.
Follow the steps below to use the DSUM function.
- Copy and paste the headers from your database for which you want to specify conditions.
- Specify the conditions under the headers.
- Now in a blank cell, type the DSUM formula.
- Type DSUM(
- Select the entire database with headers on the database argument.
- Put a comma and click on the column header whose values you want to find the minimum from. Here, Total piece.
- Put a comma and select the entire criteria table when asked for the criteria argument.
- Hit ENTER.
You can see that we have found the total stock values of tops with less than 100 as stock value.
5. DAVERAGE function
The DAVERAGE function returns the average of a column in a database based on a given condition. Let’s understand this with a different example.
Here is a list of stationery items, their unit prices, total quantities, and total prices.
Our goal is to find out the average total price of books more than ₹55 per unit.
Here’s how to use the DAVERAGE function.
- Copy and paste the headers from your database for which you want to specify conditions.
- Specify the conditions under the headers.
- Now in a blank cell, type the DAVERAGE formula.
- Type DAVERAGE(
- Select the entire database with headers on the database argument.
- Put a comma and click on the column header whose values you want to find the minimum from. Here, Total.
- Put a comma and select the entire criteria table when asked for the criteria argument.
- Hit ENTER.
You can see that we have found out the average total price of the books greater than ₹55 per unit.
Conclusion
This article was a detailed guide about the crucial database functions you need to know about when dealing with databases in Microsoft Excel. Stay tuned for more exciting tutorials like this only on QuickExcel!