1. Navigating calculations with OFFSET and ranges
Welcome back to the second chapter of this course! Time for offsetting and dynamic ranges.
2. Navigating Excel cells
In Excel, there are two referencing styles.
Absolute, when we refer to concrete cells such as A1 or C1 or ranges A1 to C1
And
Relative referencing, where we describe the position of a cell in reference to another cell.
For example. Cell C1 is zero rows down and two columns right from cell A1.
3. When to use relative referencing?
Sometimes it’s not useful to refer to absolute cell references. Think about a scenario where there is new data added each week as a new row or a column.
Or suppose you would like to anchor your displayed data to dynamically selected ranges?
Finally, you may be dealing with badly formatted data, such as nested headers. Then relative referencing is your best friend.
4. OFFSET( ) in practice
The function that makes this all possible is OFFSET. It consists of three mandatory elements: reference, rows and columns and it returns the value of another cell which is relative to the reference value, based on rows and columns directions we give. Height and width are the last two, optional elements.
For example, we want to populate cell A1 with the value of cell G3, we need to reference cell A1 and indicate that the desired cell is 2 rows down and six columns right from the reference cell A1.
Hence, Offset, A1, 2 and 6.
5. OFFSET( ) in practice
We can also return a cell value which are up or to the left of the reference cell.
In this example the value of the desired cell G3 is 2 rows up and 2 columns left from the reference cell I5, so we use -2 and -2 again in the respective function items.
6. Wrapping OFFSET( ) in other functions
We can also use OFFSET to aggregate values, so to return sums, averages and so on based on offsetted cell references.
In this example, we used offset as ingredients of sum function, to sum up the cells which are in an array distanced between 1 row down and 4 cells right to 2 rows down and 6 cells right from the reference cell A1.
7. Going dynamic!
But that’s not all! Remember the practical application of anchoring data relative to a (dynamically) selected data points?
Suppose we allow the end-user to select the starting week via a drop-down menu.
Using offset, we can populate a final table with the desired data, in this example based on the selected week.
8. Going dynamic!
We can combine the offset function with the match function.
To find the sales of cell E2, so Week 2 (as selected in cell B2), we need to offset the starting point, for example cell A5 with 1 row down and a certain amount of columns.
We will go down by 1 row. To find the columns, we will use the Match function, which will give us the exact position of week 2 in Weeks named range.
9. Going dynamic... with charts!
From here the possibilities are plenty. Imagine combining the dynamic selections with an interactive visualization. By using a drop down menu on the left and offset and match functions, you decide which weeks are displayed in the table to the right and, consequently, what data is visualized in the chart! Efficient and effective!
10. Let's practice!
Now, let's apply this in practice.