We can get a complete set of **relative column numbers in a range** with an **array formula** that is based on the **COLUMN function. **The steps below will walk through the process.

*Figure 1: Result of Relative Column Numbers in a Range:*

**General Formula **

**{=COLUMN(range)-COLUMN(firstcell)+1}**

**Formula**

**=COLUMN(A3:E3)-COLUMN(A3)+1**

**Setting up the Data**

We will determine the relative column number for **range A3:E3 **based on the content of **figure 2**

* Figure 2: Setting up the Data*

**Get Relative Column Numbers in a Range**

- We will highlight the range from
**Cell A3 to E3** - We will input the formula below inside the range

**{=COLUMN(A3:E3)-COLUMN(A3)+1}**

* Figure 3: How to Get Relative Column Numbers in a Range*

**Because this is an array formula, we will press CTRL+SHIFT+ENTER to get the result**

*Figure 4: Result of Relative Column Numbers in a Range*

**Explanation**

In this formula, the first **Column function** creates an array of 5 numbers like this: **{2,3,4,5,6}.** The second **Column function** creates an array that has only one item: **{2}. **This is subtracted from the first array and results in **{0,1,2,3,4}**. **1** is added to the result to produce: **{1,2,3,4,5}.**

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment