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 […]

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. […]

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 […]

Dynamic scoreboard

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 […]

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 […]

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 […]

Excel calendar [VBA]

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 […]

Auto populate a worksheet

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 […]

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 […]

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 […]

Fuzzy vlookup [Array formula]

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 […]

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 […]

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 […]

Extract duplicate records

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. […]

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 […]

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 […]

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 […]

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 […]

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 […]

Remove blank cells

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]