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**.