Nashid
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.
📁 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.
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.
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.
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.
