COLUMNS function

Excel » Functions » Lookup and reference » COLUMNS function »

How to copy every n-th row from a list

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The […]

Extract unique distinct records from two data sets

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]

Rearrange values in a cell range to a single column

How to use the COLUMNS function

The COLUMNS function allows you to calculate the number of columns in a cell range. Table of Contents COLUMNS function […]

If cell contains text from list

This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

Distribute values across numerical ranges

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

Rearrange values using formulas

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]

How to extract rows containing digits [Formula]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

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

How to color chart bars based on their values

(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]

Dynamic scoreboard

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

Sort by multiple columns

This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

Identify rows of overlapping records

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

Locate lookup values in an Excel table [HYPERLINK]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

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

Use a drop down list to search and return multiple values

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Search each column for a string each and return multiple records – OR logic

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Create a drop down calendar

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

Filter shared records from two tables

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

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

VLOOKUP of three columns to pull a single record

Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Answer: Array formula in […]

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

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.

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

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

Lookup with criteria and return records.

Perform multiple partial matches and return records – AND logic

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Highlight duplicate values in a cell range

The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]

Highlight unique values and unique distinct values in a cell range

The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]

Search for a text string in a data set and return multiple records

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Partial match based on two conditions

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Create a date range [Formula]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Split values equally into groups

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Partial match with two conditions and return multiple results

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

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