Excel Formulas
Regular Expressions (Regex) Functions
Use Case:
1. REGEXTRACT:
If cell A1 contains "Contact: john.doe@example.com", the formula
=REGEXEXTRACT(A1, "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b") will
return "john.doe@example.com".
2. REGEXREPLACE:
3. REGEXTEST
Text Manipulation Functions
1.TEXT BEFORE:
2. TEXTAFTER:
3. TEXTSPLIT:
4. TRANSLATE:
5. DETECTLANGUAGE:
Array Manipulation Functions
1. VSTACK:
2. HSTACK:
3. TOROW:
4. TOCOL:
5. WRAPROWS:
Data Selection and Modification
1. TAKE:
2. DROP:
3. CHOOSEROWS:
4. CHOOSECOLS:
Data Aggregation and Analysis
1. GROUPBY:
2. PIVOTBY:
3. TRIMRANGE:
Use Case: Cleaning up datasets by eliminating unnecessary empty spaces.
Example:
If range A1:D10 has empty rows and columns at its borders,
=TRIMRANGE(A1:D10) returns the range without those empty rows and
columns.
Custom Function Creation
1. LET:
2. LAMBDA:
AI-Powered Functions
1. AI.PREDICT:
2. AI.SUMMARIZE:
Cloud and API-Driven Functions
1. CALLAPI:
Excel's DGET function and other D-related database functions (often
referred to as D-functions) are used to extract and manipulate data from a
structured dataset (or table) based on specific criteria.
1. DGET Function
Syntax:
DGET(database, field, criteria)
Explanation:
● database: The entire dataset or table, including headers.
● field: The column name (in quotes) or column index from which to
retrieve the value.
● criteria: A range specifying the condition(s) to apply.
Example:
If you have a table (A1:D10) containing employee details and want to get
the salary of an employee named "John":
=DGET(A1:D10, "Salary", F1:G2)
● Assuming F1:G2 contains criteria like:
● Name| John
This function retrieves John's salary from the database.
2. Other D-Related Functions in Excel
These functions work similarly to DGET, operating on a dataset using
criteria.
Function Description
DAVERAG Returns the average of a column based on given
E criteria.
DCOUNT Counts numeric entries in a column based on criteria.
DCOUNTA Counts non-empty cells (text or numbers) in a column
based on criteria.
DMAX Returns the maximum value in a column that meets the
criteria.
DMIN Returns the minimum value in a column that meets the
criteria.
DPRODUC Multiplies values in a column that meet the criteria.
T
DSTDEV Estimates the standard deviation based on a sample.
DSTDEVP Calculates the standard deviation for an entire
population.
DSUM Returns the sum of a column based on criteria.
DVAR Estimates the variance based on a sample.
DVARP Calculates the variance for an entire population.
Example Uses
1. Calculate total sales where Region = "West"
2. =DSUM(A1:D10, "Sales", F1:G2)
3. Find the highest salary for a specific department
4. =DMAX(A1:D10, "Salary", F1:G2)
5. Count employees in a department
6. =DCOUNT(A1:D10, "Employee ID", F1:G2)
When to Use D-Functions?
● When working with structured databases/tables.
● When needing dynamic calculations based on criteria.
● When requiring single-value results (like in DGET).
Would you like a hands-on example in an Excel file? I can create one for
you!
