Database Functions in Excel
Database Functions in Excel
Excel boasts a vast library of functions, but don’t be overwhelmed! We’ll explore the most essential functions grouped into intuitive categories. Here is the list of Excel Formulas and Functions. We have included Excel functions, Description, Syntax. Explained with Example Formulas.
Database Functions
Bridge the Gap Between Excel and External Databases. These advanced tools allow you to retrieve, manipulate, and aggregate data directly from database tables, streamlining data integration and analysis for power users.
- Retrieve, manipulate, and aggregate data directly from database tables.
- Requires a basic understanding of database structures and functionalities.
Function | Description | Syntax and Formula |
DAVERAGE | DAVERAGE is an Excel function that calculates the average of selected database entries that meet certain criteria. | Syntax: DAVERAGE(database, field, criteria) Formula: To calculate the average of the prices of all products in the database with the category of “Fruit”, the following formula can be used: DAVERAGE(A2:E7,5,”Fruit”) |
DCOUNT | DCOUNT: The DCOUNT function counts the cells that contain numbers in a database. | Syntax: DCOUNT(database, field, criteria)Database: This is 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.Field: This is the column in the database that you want to count the numbers in.Criteria: This is an optional range of cells that contains criteria. Formula: DCOUNT(A1:D10,”Age”,A1:C10)This example counts the number of cells in the Age column (column 4) of the database range A1:D10 that meet the criteria in the range A1:C10. |
DCOUNTA | DCOUNTA is an Excel function that counts the number of cells in a database that contain numbers and meets a given set of criteria. | Syntax: DCOUNTA(database, field, criteria) Formula: DCOUNTA(A1:C10, 1, “>10”)This example counts the number of cells in the range A1:C10 in the first field (field 1) that contain a number greater than 10. |
DGET | The DGET function is used to extract a single value from a specified database record (row) that matches the given criteria. | Syntax: DGET(database, field, criteria) Formula: DGET(A1:D10, “Age”, “Name”=”John”)This example will return the age of the person named John from the database range A1:D10. |
DMAX | DMAX is an Excel function that returns the maximum value from a specified database column that meets a given criteria. | Syntax: DMAX(database, field, criteria) Formula: DMAX(A1:B10, “Price”, “Product = Shoes”)This example would return the maximum price from the range A1:B10 where the product is equal to “Shoes”. |
DMIN | The DMIN function returns the smallest number in a set of values from a database. | Syntax: DMIN(database, field, criteria) Formula: DMIN(A2:C10,”Price”, “Category=Clothing”) |
DPRODUCT | The DPRODUCT function multiplies the corresponding values in two or more arrays and returns the sum of the products. | Syntax: DPRODUCT(array1, array2, [array3], …) Formula: DPRODUCT(A1:A3,B1:B3)This example multiplies the values in cells A1, A2 and A3 with the corresponding values in cells B1, B2 and B3, and returns the sum of the products. |
DSTDEV | DSTDEV is an Excel statistical function that calculates the standard deviation of a population based on a sample of numbers. | Syntax: DSTDEV(number1, [number2], …) Formula: DSTDEV(2, 4, 6, 8, 10)Result: 2.82842712474619 |
DSTDEVP | DSTDEVP is a statistical function in Microsoft Excel that returns the standard deviation of a population based on a sample of data. | Syntax: DSTDEVP(database, field, criteria) Formula: To find the standard deviation of the population of people living in a certain city, the following formula could be used: =DSTDEVP(A1:A100, “population”, “city”) |
DSUM | DSUM is a function in Microsoft Excel that allows users to sum up values in a list that meet certain criteria. | Syntax: DSUM(database, field, criteria) Formula: DSUM(A1:C10, “Price”, “Category=Clothing”)This example will sum up all of the values in the “Price” column of the range A1:C10 that have the category “Clothing”. |
DVAR | The DVAR function in Excel calculates the variance of a sample set of data. | Syntax: DVAR(database, field, criteria) Formula: To calculate the variance of the amount column in the table below, where the date is greater than or equal to 1/1/2020:DVAR(A2:C10, “Amount”, “Date>=1/1/2020”) |
DVARP | The DVARP function in Excel is used to calculate the variance of a population based on a sample of data. | Syntax: DVARP(number1,[number2],…) Formula: DVARP(2,4,6,8,10)This example would return 4. |
You May Like to Browers More


