In this lesson, I’ll demonstrate 3 effective examples of Creating a Dynamic Range with Multiple Columns Using **OFFSET **in Excel. These examples will enable you to solve the problem that led you here. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

**Contents**hide

## 📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## Overview of OFFSET Function

The **OFFSET** function can be simply explained as it is a tool for selecting a customized range by using commands to locate them. It follows the pattern given below.

**=OFFSET(reference, rows, columns, {height}, {width})**

The arguments are:

**reference** – The reference that you wish to use to calculate the offset.

**rows** – The number of rows, either up or down from which the range shall start. Positive means underneath the starting reference while negative means above.

**columns**– The number of rows, either left or right from which the range shall start.Positive means right of the starting reference while negative means left.

**{height}**–number of rows.

**{width}**–number of columns.

For instance, the formula **OFFSET(B2,2,3,4,2)** selects data of a **4**-row by a **2**-column range that is **2** rows below and **3** columns to the right of the cell **B2**.

We will be using **3 Functions** in the following paragraphs to calculate the **Average Number of People**, **The Total Income of a Family**, and **The Total Sale of a Store** respectively. Carefully go through the process of each example and practice on your own to understand better to offset dynamic range in multiple columns in Excel.

## Learn to Create Dynamic Range with Multiple Columns Using OFFSET in Excel with These 3 Examples

Say we have a dataset of some stores that have different data similar to our problem. We will use the **SUM, COUNT**, and **OFFSET** functions to determine these problems. Following these different examples, you’ll learn to use **OFFSET** dynamic range in multiple columns in Excel.

**Example 1**

### 1. Determining Average Population

Here we have a dataset of the number of people in a colony having 4 buildings each having 4 floors. We will be counting the total count of people from it using **AVERAGE**,** COUNT**, and **OFFSET** functions.

⬇️⬇️ **STEPS** ⬇️⬇️

- First, go to the formula bar and select the
**Name Manager**for defining the function.

- A new window will pop select
**New**from that window.

- Now another window pops up, however, type a name for the custom function. For instance, we are using
**People**and in the Refers to option copy the following formula.

**=OFFSET(‘People_Count’!$C$5,1,0,COUNT(‘People_Count’!$C$6:$C$9),COUNT( ‘People_Count’!$C$6:$F$6))**

🔨** Formula Breakdown**

👉 **COUNT(‘People_Count’!$C$6:$C$9)**

The First part of the command refers the column to select by the **OFFSET** function which is **C6:C9. **The **People_Count **part is the Sheet name in the whole formula.

👉 **COUNT( ‘People_Count’!$C$6:$F$6)**

This will refer to a selected row by the **OFFSET** function which is **C6:F6**

👉 **OFFSET(‘People_Count’!$C$5,1,0,COUNT(‘People_Count’!$C$6:$C$9),COUNT( ‘People_Count’!$C$6:$F$6))**

The **OFFSET **function selects the range to operate. **‘People_Count’!$C$5,1,0 **refers to where the range will start.

- Press
**OK**and you are done. - Now select a cell to determine the average count of people in a colony and type the following formula.

**=AVERAGE(People)**

- Press
**Enter**for results.

**Example 2**

### 2. Calculating Total Income

The dataset we have here is a Family’s Income chart for 4 months having 4 members. We will be counting the total Income of the family using **SUM, COUNT**, and **OFFSET** Functions.

⬇️⬇️ **STEPS** ⬇️⬇️

- Do as the
**Example 1**to define a function but use a different name we are using**Total_Income**and the formula for this example is given below.

**=OFFSET(‘Income of family’!$C$5,1,0,COUNT(‘Income of family’!$C$6:$C$9),COUNT(‘Income of family’!$C$6:$F$6))**

🔨** Formula Breakdown**

👉 **COUNT(‘Income of family’!$C$6:$C$9)**

The First part of the command refers to the column to be selected by the **OFFSET** function which is **C6:C9. **The **Income of family **part is the Sheet name in the whole formula.

👉 **COUNT(‘Income of family’!$C$6:$F$6)**

This will refer to a selected row by the **OFFSET** function which is **C6:F6**

👉 **OFFSET(‘Income of family’!$C$5,1,0,COUNT(‘Income of family’!$C$6:$C$9),COUNT(‘Income of family’!$C$6:$F$6))**

The **OFFSET **function selects the range to operate. **‘Income of family’!$C$5,1,0 **refers to where the range will begin.

- Select
**OK**and you are done. - Now
**select**a cell to determine the Total Income of the Family and type the following formula.

**=SUM(Total_Income)**

- Press
**Enter**for results.

**Example 3**

### 3. Obtaining Total Sale

Here we are working with a dataset that has the data of 4 months’ Sales of a Store of different products. We will be counting the total Sale of the store using **SUM, COUNT**, and **OFFSET** Functions.

⬇️⬇️ **STEPS** ⬇️⬇️

- Do as the
**Example 1**to define a function but use a different name we are using**Sale**and the formula for this example is given below.

**=OFFSET(‘OFFSET_Dynamic Range’!$C$5,1,0,COUNT(‘OFFSET_Dynamic Range’!$C$6:$C$11),COUNT(‘OFFSET_Dynamic Range’!$C$6:$F$6))**

🔨** Formula Breakdown**

👉 **COUNT(‘OFFSET_Dynamic Range’!$C$6:$C$11)**

The First part of the command refers to the column to be selected by the **OFFSET** function which is **C6:C11. **The **OFFSET_Dynamic Range **part is the Sheet name in the whole formula.

👉 **COUNT(‘OFFSET_Dynamic Range’!$C$6:$F$6)**

This will refer to select row by the **OFFSET** function which is **C6:F6**

👉 **OFFSET(‘OFFSET_Dynamic Range’!$C$5,1,0,COUNT(‘OFFSET_Dynamic Range’!$C$6:$C$11),COUNT(‘OFFSET_Dynamic Range’!$C$6:$F$6))**

The **OFFSET **function selects the range to operate, **OFFSET_Dynamic Range’!$C$5,1,0 **refers from where the range will start.

- Select
**OK**and you are done. - Now
**select**a cell to determine Total Monthly Sales and type the following formula.

**=SUM(Sale)**

- Press
**Enter**for results.

## 📄 Important Notes

`🖊️`

Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

`🖊️`

Use shortcuts to reduce time.

`🖊️`

Change the** formats **as you want them before applying a formula.

`🖊️`

While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

## 📝 Takeaways from This Article

`📌`

How to use **OFFSET** and **COUNT** functions.

`📌`

The perfect approach to dealing with huge datasets.

`📌`

Use of **SUM, Average **function.

`📌`

How to define a custom function using **Name Manager**.

**Conclusion**

I hope you were able to use the techniques I demonstrated in this Excel lesson to use the **OFFSET **function for dynamic range in multiple columns in Excel. We have stated 3 different Examples here. Decide deliberately on the approach that best addresses your circumstance. I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the **Excel to offset dynamic range multiple columns** Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks.

Again if you have any problem regarding Excel spit it in the comment box. The **Excelden **crew is always available to answer your questions. Keep educating yourself by reading. For more Excel-related articles please visit our website **Excelden.com**.