COLUMN function
Excel » Functions » Lookup and reference » COLUMN function »
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})) […]
Get date ranges from a schedule
The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based […]
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 […]
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 […]
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 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 […]
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. […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 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 […]
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 […]
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 […]
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 […]
Unique distinct records sorted based on count or frequency
This article demonstrates how to sort a table based on count meaning the formula counts each record and returns a […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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. […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]
Search values distributed horizontally and return corresponding value
Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]
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 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.
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 […]
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, […]
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 […]
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 […]
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 […]
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 […]
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 […]
Extract records between two dates
Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]
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 […]
How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? (See pic below) Answer: It depends on how the emails are […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]