'COLUMN function' category
Convert column number to column letter
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Use the following formula to convert a column number to a column letter: =LEFT(ADDRESS(1, B3, 4), MATCH(B3, {1; 27; 703})) […]
Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
Rearrange values in a cell range to a single column
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
How to use the COLUMN function
The COLUMN function returns the column number of the top-left cell of a cell reference. If the argument is not […]
The COLUMN function returns the column number of the top-left cell of a cell reference. If the argument is not […]
INDEX MATCH with multiple criteria
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
Reverse two-way lookups
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]
Count identical values if they are on the same row
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]
Sort values by corresponding text arranged in a column
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
Sort items by adjacent number in every other value
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
Sort based on frequency row-wise
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
How to group items by quarter using formulas
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
Merge tables based on a condition
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]
This article demonstrates techniques on how to merge or combine two data sets using a condition. The top left data […]
Wildcard lookups and include or exclude criteria
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
Lookup with any number of criteria
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
How to use the Scroll Bar
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
VLOOKUP and return multiple values across columns
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]
Search related table based on a date and date range
I will in this article demonstrate how to search a table for a date based on a condition and then […]
I will in this article demonstrate how to search a table for a date based on a condition and then […]
Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
Extract table headers based on a condition
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
This article demonstrates an array formula that returns the table header based on a condition. For example, in cell C8 […]
Unique distinct records sorted based on count or frequency
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]
Auto populate a worksheet
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
Filter unique distinct values, sorted and blanks removed from a range
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]
Filter unique distinct records with a condition
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]
Filter unique distinct records case sensitive
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
Bill reminder in excel
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
Filter records based on a date range and a text string
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
Fuzzy VLOOKUP
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Text to columns: Split words in a cell
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Merge matching rows
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Question: I'm using excel 2003. This is my problem.Sheet 1 COL A contains fruits, col B to H contains there […]
Count digits and ignore duplicates
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Question: I have a question that I can’t seem to find an answer to: I want to make a full […]
Compare tables: Filter records occurring only in one table
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
Extract duplicate records
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]
Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
True round-robin tournament
Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]
Mark G asks in Create a random playlist in excel: Can this example be modified to create a true round-robin […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
Create numbers based on numerical ranges
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]
Schedule recurring expenses in a calendar in excel (Personal Finance)
Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]
Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]
List all unique distinct rows in a given month
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Extract records containing negative numbers
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]
Filter duplicate values in a range using “contain” condition
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
Extract unique distinct text values containing string in a range
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]
Filter duplicate values from a range that begins with string
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
Extract unique distinct values from cell range that begins with string
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
Search values distributed horizontally and return corresponding value
Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]
Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]
Create a quartely date range
I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]
I will demonstrate three different methods to build quarterly date ranges in this article. The two first methods have a […]
Create a monthly date range
I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they […]
I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
This post explains how to lookup a value and return multiple values. No array formula required.
Filter unique values from a cell range
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]
Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Filter common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Filter values occurring in range 1 but not in range 2
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
Search for a cell value in a dataset
Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]
Question: How do I identify rows that have a specific cell value in an Excel defined table? Array formula in […]
How to quickly find the maximum or minimum value [Formula]
This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]
This article demonstrates formulas that will return the largest and smallest numbers, the corresponding column headers, the cell addresses, and […]
Extract all rows that contain a value between this and that
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
Extract records between two dates
This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]
This article presents methods for filtering rows in a dataset based on a start and end date. The image above […]
Extract unique distinct records based on a criterion
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]
How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Remove blank cells
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values