Dynamic Range with Multiple Columns Using OFFSET in Excel

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.

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.

`๐`ย  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.

(Visited 45 times, 1 visits today)

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts