Excel formula articles

Excel formula articles

Average

Average function

How to average a column

How to average a column containing some text values

Are zeros counted in the AVERAGE function?

Are boolean values ignored in the AVERAGE function?

How to average absolute values

How to average across sheets

How to average by group

How to average by month

How to average blank as zero

How to average excluding high and low

How to exclude some cells from average

Average based on criteria

Average based on criteria - and or logic

Average ignore NA()

Average ignore 0

Running average

Weighted average

Weighted average based on percentages

Weighted average ignore blanks

Average ignore blanks

Average - ignore blanks and errors

Average - ignore blanks in non-contiguous cells

Average ignore empty cells

Average ignore 0 and blanks

Choose

CHOOSE function example

How to hardcode values in the CHOOSE function

CHOOSE function array

CHOOSE function alternative

CHOOSE function drop-down list

CHOOSE function with VLOOKUP

CHOOSE function return array

Combine/Merge/Consolidate/Join

Textjoin function

Group rows based on condition

Merge matching rows

Merge tables based on condition

Merge two columns ignore blanks

Merge two columns

Merge three columns into one list

Merge two data sets using the VLOOKUP function

Compare

Compare two columns and return shared values

Compare two columns and filter differences

Compare two columns and return differences

Compare two columns and return differences

Compare two columns and return differences

Compare three columns and filter shared values

Compare three columns and filter shared values

Compare two cell ranges and return shared values

Compare two cell ranges and return differences

Compare two data sets and filter differences

Compare two data sets and filter shared rows/records

Compare two data sets located in different worksheets

Create a list with most recent data available

Compare data sets and update with new values

Compare totals between two data sets and filter differences

Compare and highlight differences in price-lists

Concatenate/Join

Concatenate cell values in a row into a single cell manually

Concatenate cell values in a column into a single cell manually

Concatenate a multi-column and multi-row cell range manually

Join cell values using a delimiting character manually

Join cell values based on a condition

Join unique distinct values

Join unique distinct values based on drop-down list value

Join without TEXTJOIN

Join values returned from a lookup

Conditional Formatting

How to create conditional formatting

Highlight cell if equal to condition

Highlight row based on condition

Highlight cells in column based on a condition

Highlight cell if number is larger than adjacent number

Highlight unique distinct rows

Highlight empty cells

Highlight text values

Highlight numbers in close proximity to a given number

Highlight lookup values

Highlight records [AND logic]

Highlight records containing text strings (AND Logic)

Highlight records – multiple criteria [OR logic]

Highlight unique distinct values

Highlight unique values and unique distinct values in a cell range

Highlight unique distinct records

Highlight unique values in a filtered Excel table

Highlight duplicate values

Highlight duplicates in two columns

Highlight duplicate values in a cell range

Highlight duplicate rows/records

Highlight duplicates based on date

Highlight duplicates with same date, week or month

Highlight duplicate columns

Highlight smallest duplicate number

Highlight duplicates in a filtered Excel Table

Highlight shared values between two columns

Highlight differences between two columns

Highlight differences between two columns

Highlight rows/records not in both data sets

Highlight rows/records shared between two data sets

Highlight differences in pricelists

Highlight closest value

Highlight max and min values based on month

Highlight smallest duplicate value

Highlight weekends

Highlight dates based on day of week

Highlight dates based on date range

Highlight overlapping date ranges

Highlight records based on overlapping date ranges and a condition

Highlight current date

Highlight MAX and MIN value based on month

Highlight odd/even months

Highlight every other row

Highlight based on drop-down list

Highlight based on coordinates

Highlight opposite numbers

Highlight cells based on ranges

Dynamic formatting

Count

Count cells equal to a condition

Count cells larger/less than a criterion

Count cells containing a text string - partial match (wildcard)

Count cells with x characters

Count cells with text

Count empty cells

Count the number of times a string is repeated in a cell

Count how many times a string exists in a cell range (case insensitive)

Count how many times multiple strings are repeated in cell range

Count frequency of string in cell range (case sensitive)

Count cells equal to values in list

Count cells containing text from list

Count rows containing data

Count how many times a given string is repeated in a cell

Count overlapping time

Count cells containing text/number from list

Count unique distinct values based on a condition

Count cells based on background color programmatically

Count identical values if they are on the same row

Count overlapping days

Count and sort based on frequency row-wise

Count overlapping days in multiple date ranges (1)

Count overlapping days in multiple date ranges (2)

Count contiguous values

Count comma separated values

Count conditionally formatted cells

Count cell based on cell and font color

Count unique distinct values in a filtered table

Count the number of groups of each value

Count specific weekdays in date range (1)

Count specific weekdays in a date range(2)

Count cells between two values

Count entries based on date and time

Count unique distinct values based on criteria

Count unique distinct records between two dates and a condition

Count digits and ignore duplicates

Count cells based on date range

Count groups in a calendar

Count unique distinct values within same week, month or year

Count unique distinct values based on date

Count unique distinct numbers across worksheets 3D range

Count cells between two values

Count unique distinct months

Count unique distinct values in two columns

Count unique distinct values

Date calculations

Calculate days between dates

Calculate weeks between dates

Calculate months between dates

Calculate years between dates

Number of items in given date range

Number of workdays between two dates

Count specific weekdays between two dates

List items past date

Calculate overlapping date ranges

Find latest date based on condition

Calculate last date in a specific month

Match date in date range

Create date range

Create weekly date range

Add or subtract months and days

Date function example

How to make Excel order by date

Add days to a date

Subtract days to a date

Add months to a date

Last day of a given month

Calculate date ranges based on a schedule

Count overlapping days

Calculate date based on week number and day

Count overlapping days(1)

Count overlapping days(2)

Calculate missing dates between date ranges

Working with overlapping date ranges

Calculate overlapping ranges

Get overlapping date ranges

Get earliest and latest date from overlapping date ranges

Highlight overlapping date ranges

Find consecutive dates

Count overlapping days between date ranges

Prevent overlapping ranges

Find date range based on a date

Create date ranges

Calculate the number of weekdays in a date range

Calculate dates in a project based on an end date

Calculate week numbers based on a date range

Count weekdays between a start and end date

Calculate the number of entries between date & time ranges

Get overlapping date ranges

Highlight duplicate values with overlapping dates

Count groups in calendar

Get overlapping dates from overlapping date ranges

Get list of dates based on date ranges

Get a list of dates outside specified date ranges

List overlapping date ranges

List unique events for a given month

Highlight duplicates on the same date, week, or month

Filter duplicates on the same date, week, or month

Exctract unique distinct year - months from Excel dates

Match date in date rng

Find the latest date based on a condition

Get the most frequent or repeated value between two dates

Calculate the largest/smallest value based on a date range

Create a list of dates with blanks between quarters

Filter duplicate values and sort by the corresponding date

Create date range

Lookup and return the closest date

Extract unique distinct values based on a date range

Calculate machine utilization

Calculate a date based on specific weekday in a month

Duplicates

Extract a list of duplicates

Extract a list of duplicates from two columns combined

Extract a list of duplicates from three columns combined

Extract a list of alphabetically sorted duplicates from a column

Extract duplicate values sorted from A-Z based on a condition

Filter duplicate values using criteria

Extract duplicate values with exceptions

Label duplicate rows/records

Extract duplicate values within same date, week or month

Extract duplicates based on partial match condition

Filter duplicate values from a range that begins with string

Find min and max unique and duplicate numerical values

Filter duplicate values and sort by the corresponding date

Extract duplicates from a range

Create a list of duplicates where adjacent cell value meets a condition

Extract

Extract first word in cell value

Extract k-th word in cell value

Extract last word in cell value

Extract first three digits in value

Extract numbers in cell value

Extract unique digits

Extract numbers from a column

Extract name initials

Extract headers

Extract most frequent value between two dates

Filter function - Excel 365

Filter values based on a condition - case sensitive

Filter values if not equal to

Filter values if smaller than

Filter values if larger than

Filter values if contains

Filter values if not contain

Filter values that begin with

Filter values that end with

Filter values based on a condition sorted from A to Z

Filter values based on criteria

Filter values based on a condition per column - AND logic

Filter values based on a condition per column - OR logic

Filter rows/records

Extract all rows from a range that meet criteria in one column

Match two criteria and return multiple records

Extract records where all criteria match if not empty

Extract all rows that contain a value between this and that

Extract records between two dates

Filter records based on a date range and a text string

Search for a text string in a data set and return multiple records (partial match)

Extract records containing negative numbers

Filter rows containing digits

Filtered values

Sum and include manually hidden values

Sum and exclude manually hidden values

Sum filtered data

Count the number of cells containing numbers from filtered data

Count non empty cells from filtered data

Largest number from filtered data

Smallest number from filtered data

Average based on filtered data

Extract filtered values - Excel 365

Filterxml

How to extract XML attribute

How to import XML data

Put XML data in different columns based on XML tags

Split strings in cell - any a delimiting character

Split search value using delimiter and search for each substring

Hyperlink

Create a link to a cell

Create a link to a cell range

Create a link to another worksheet

Create a link to a workbook

Create a link to a named range

Create a link to an Excel Table

Create a link to a file

Create a link to a webpage

Create a link based on cell value

If cell

If cell equals value from list

If cell contains partial text

If cell contains partial text - hardcoded asterisks

If cell contains text then return value

If cell contains text then add text in another cell

If cell contains text then sum

If cell contains text add 1

If cell has value

If cell contains any text

If cell contains multiple values

If cells equal to list value

If cell contains text from list

If cell contains text

Index Match

Index Match - lookup value and return corresponding value on the same row

Index Match - multiple criteria

Index and Match - partial text using multiple conditions - Excel 365

Index Match - partial match multiple columns

Index and Match return multiple results

Index Match - get last value

Index and Match – multiple criteria and multiple results

Index Match - case sensitive match

Index - Rearrange cells to a single column

Small function – Index Match

Lookup multiple values across columns and return a single value

Looking up data in a cross reference table - 2D lookup

Logic

Use IF + COUNTIF to evaluate multiple conditions

IF function with AND function – multiple conditions

How to shorten nested IF functions

Lookups

Lookup value and return corresponding value on the same row

Get the latest revision

Get last value in column

Get last value based on a condition in an unsorted list

Lookup with any number of criteria

Find entry based on conditions

Lookup - two conditions in two columns

Two-dimensional lookup in a cross-reference table

Get closest value

Match

Match two columns

Match two columns case sensitive

Match two columns partial match

Match a range value containing both text and numerical characters

Match cell that ends with the condition

Match cell that begins with the condition

Match cell that contains a given string

Match cell that begins with a specific string and ends with another string - any number of characters in between

Match cell that begins with string and ends with another string - a single character in between

Case sensitive match

Extract headers

Max

How to use the MAX function

Find and return the highest number and corresponding date based on a condition

Max based on column

Get the largest number based on a condition

Get the largest number based on multiple conditions

Max function returns 0 (zero)

Max function for text

Get the most repeated value

MAX function ignore #N/A error

MAX function second highest

MAX function second-highest number no duplicates

MAX function second-highest number based on a condition

MAX function Conditional formatting

Min

MIN function example

MIN function ignore 0 (zero)

MIN function returns 0 (zero)

MIN function not working

MIN function ignore errors

MIN function ignore negative numbers

MIN function ignore text

MIN function ignore #NA error

Find closest value

MIN based on column

Get the smallest number excluding zeros

Create a link to the largest/smallest number

How to highlight the largest and smallest value

Get the cell address of the largest/smallest number

Get the smallest number larger than a given number

Get the column header name whose column contains the largest/smallest value

Numbers in sum

Find numbers in sum

Numbers closest to sum

Numbers in sum using Solver

OR logic

Working with boolean values

OR function - multiple conditions

OR function array

OR function with COUNTIF

OR function with IF

OR function contains text

Overlapping ranges

Identify overlapping date ranges

Find overlapping dates in a list

Use median function to calculate overlapping ranges

Sum overlapping units between two ranges

How to sum overlapping time

Sum overlapping days across date ranges(1)

Sum overlapping days across date ranges(2)

Find empty dates in a set of date ranges

Working with overlapping date ranges

Find earliest and latest overlapping dates in a set of date ranges based on a condition

Prevent users from entering overlapping date and time ranges

Filter overlapping date ranges

Highlight records based on overlapping date ranges and a condition

Partial match

If cell contains partial text

If cell contains partial text - hardcoded asterisks

If cell contains text then return value

If cell contains text then add text in another cell

If cell contains text then sum

If cell contains text add 1

If cell contains any text

If cell contains multiple values

If cell contains text from list

If cell contains text

Distribute values across numerical ranges

Search values (partial match) and return value with the highest level

Lookup value based on two conditions – the second condition is the corresponding value based on its position in a given list

Wildcard lookups (partial match) and include or exclude criteria

Partial match and return multiple adjacent values

Partial match and return multiple records - OR logic

Partial match - VLOOKUP

Filter based on multiple partial matches

Perform multiple partial matches and return records - AND logic

Partial match based on two conditions

Strings in column

Contains string - column

Partial match two conditions

Partial match two conditions - Excel 365

Three conditions return multiple records – OR logic

Records

Unique distinct records sorted based on count or frequency

Remove common records between two data sets

How to sort a data set in a custom order

Highlight records [AND logic]

Compare tables: Filter records occurring only in one table

Sequence

Repeating sequence

Repeated numbers in a sequence

Blanks in a sequence of numbers

Format numbers in sequence with leading zeros

Sequence of dates based on a start date

Sequence of months

Sequence of weeks

Sequence of quarters

Sequence of letters

Sequence of cells

Small

SMALL - Ignore errors

SMALL - with multiple cell ranges

Small - Index Match

SMALL - ignore 0 (zeros)

SMALL with conditions

SMALL function for text

SMALL with duplicates

SMALL function with duplicates (Excel 2016)

SMALL function with duplicates (previous Excel versions)

SMALL function with duplicates (Excel 365)

SMALL function with duplicates based on a condition

Sort by - Excel 365

How to sort from A to Z

How to sort from Z to A

How to sort from smallest to largest

How to sort from largest to smallest

How to sort by multiple columns

Sort by another column

How to sort by the first letter/digit

How to sort by the first letter - Example 2

How to sort by the first digit - Example 3

How to sort by absolute value

How to sort by the last word in a cell

How to sort by the first name

How to sort by the last name

How to sort by custom list

How to sort by month

Sort by word length

Sort in random order

Sort by week number

Sort by quarter

Sort based on row count

Sort values

Sort a column from A to Z

Sort two columns from A-Z

Sort a range from A to Z

Sort unique distinct values based on frequency

Sort based on frequency and criteria

Sort rng - frequency

Sort count freq. condi.

Sort two columns

Sort adj. txt horiz. (1)

Sort table custom

Lookup/Sort custom

Sort random order

Sort based on length

Sort unique A-Z

Sort unique A-Z blanks

Sort cell delimiter [VBA]

Sort Excel table [VBA]

Sort based on proximity

Sort dates - criteria

Sum

Add numbers in a column and return a total

Add numbers in an array

Sum specific cells

Sum numbers from multiple cell ranges

Sum a column with text

Sum boolean values

Sum numbers based on a condition/label/item/category

Sum multiple conditions

Sum only visible cells

Sum a filtered column

Sum the entire column

Sum across worksheets

Sum absolute values

Sum values greater than smaller than

Sum values below/above 0 (zero)

Limit SUM function

Sum a column and ignore errors

Sum values by date

Sum values by week

Sum values by month

Sum values by year

Sum between two dates

How to sum a cell range

Sum unique distinct no

Sum nums betw. dates

How to create running totals

Advanced totals

Sum numerical ranges

Sum based on criteria

Running totals - date

Sum - OR AND logic

Sum - dates & condition

Sum unique numbers

Sum only visible cells

Sumifs function

SUMIFS function greater than

SUMIFS function not equal

SUMIFS function multiple criteria

SUMIFS function or

SUMIFS Function example using partial match

How to use logical operators in the SUMIFS function

How do I sum values between n-th weekday this month and n-th weekday next month?

Sumproduct function

How to do tiered calculations

Sumproduct function and nested IF functions

Sumproduct and multiple conditions

How to use the SUMPRODUCT function

How to use a logical expression in the SUMPRODUCT function

How to use multiple conditions in the SUMPRODUCT function

How to use OR logic in the SUMPRODUCT function

SUMPRODUCT if not blank

SUMPRODUCT returns nothing (blank)

SUMPRODUCT if not NA

SUMPRODUCT if not error

SUMPRODUCT and the IF function

How to simplify IF functions in the SUMPRODUCT function

SUMPRODUCT if greater than 0 (zero)

SUMPRODUCT if between two dates

SUMPRODUCT if cell contains text

SUMPRODUCT if or

SUMPRODUCT - weighted average based on percentages

SUMPRODUCT - weighted average

Text string manipulation

Replace part of formula

Identify characters

Time

Hours between time ranges

How to AVERAGE time

Overlapping time ranges

Calculate total cost based on different rates per hour

Calculate total cost based on different rates per hour (smaller formula)

Calculate total cost based on different rates per hour across days

Count overlapping time

Unique distinct records

Extract unique distinct records from two data sets

Sort based on freq.

Filter unique distinct records using criteria

Count uniq. dist. [2010]

Unique dist. rec. criteria

Records - condition(2)

Records - condition(1)

Filter unique distinct records

Count unique distinct records

Count unique distinct records

Extract duplicate records

Unique distinct values

Uniq. dist. - condition

Adjacent contains string

Adjacent has text

Contains string

Based on criteria

Sorted frequency

Sorted alphabetically

Sorted & no blanks - rng

From a cell range

Sorted A-Z from range

Contains string in rng

Begins with criterion

Based on a date range

Year - month from dates

Based on date criteria

Extract filtered table

Extract & sum condition

Sum uniq. dist. rec.

Assign randomly unique

Sums, return unique

Sorted, no blanks - rng

Unique records

Duplicate columns

Unique values

Unique strings [UDF]

Sort digits in rng

Based on date criteria

Sort unique A-Z

Min unique number

Sort based on date

Vlookup

How to use the VLOOKUP function

Merge two data sets using the VLOOKUP function

How to use the VLOOKUP function with a cell reference specified in a cell

What if the lookup value is not in the leftmost column

Using multiple conditions in VLOOKUP

Use VLOOKUP to calculate discounts, commissions, tariffs, charges, shipping costs, packaging expenses or bonuses

Lookup value and if in range return multiple values

Lookup value and return value if in range

VLOOKUP of three columns to pull a single record

Choose between two data sets to VLOOKUP

Vlookup and return multiple values

Vlookup and return multiple values

5 easy ways to VLOOKUP and return multiple values

Lookup based on a date range and a condition return multiple values

Use VLOOKUP and return multiple values sorted from A to Z

Unique distinct list sorted alphabetically based on a condition

Vlookup with 2 or more lookup criteria and return multiple matches

Vlookup a cell range and return multiple values

Search for a cell value in an entire dataset and return multiple rows/records

VLOOKUP and return multiple values across columns

Vlookup with multiple matches returns a different value

Search values distributed horizontally and return corresponding value

Use a drop down list to search and return multiple values

Vlookup across multiple sheets

Comments Off on Excel formula articles

Comments are closed.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form