3: Structuring and Referencing Data for Analysis within Excel
3.1 Review cell reference styles and types
3: Structuring and Referencing Data for Analysis within Excel
3.1 Review cell reference styles and types - Video Tutorials & Practice Problems
Video duration:
4m
Play a video:
<v Speaker>Although you should be able to follow</v> the course content with either style, you can easily revert to your preferred cell reference style using the following menu option. Go under File, options, formulas, and then look for the R1C1 reference style option under the working with formula section. When developing Excel based solutions, quite often you'll be copying, moving or filling formulas to other cells within Excel. Irrespective of your preferred cell reference style, you will thus be required to incorporate relative or absolute cell references within your formulas. Excel cell references are relative by default, which means when you copy a formula from a cell to another cell, the row or column references including within it will be updated relative to the formula cell. Now in this example, if we wanted to sum the values in column one and column two, for each row in these columns, we can use the sum function, and then select the values in a row, and then press enter. We can then select the formula we just typed in, and copy that into another cell in the same column. Or we can use the fill handle to copy down the formula into other cells in the same column. Now if we use the Ctrl + Tilde shortcut, we can see that Excel has automatically updated the row references in each formula that was copied down the sum column. This is made possible by the relative cell reference capability within Excel. Now carrying on with the same example, if we copy the formula from a cell in the sum column to another cell on the same row of the AltSum column, using the Ctrl + Tilde keyboard shortcut again, we see that Excel has automatically updated the column reference from K to N in the new formula. This again is made possible by the default relative cell reference functionality within Excel. However, in some cases, this behavior might not be desirable. For example, if we had to sum the values for column one and column two, into the sum column, and the values from column one and alternate column two into the alternate sum column, we would want that our reference for column K is not updated as we move it into the alternate sum column. To achieve this, we use the absolute cell referencing capability within Excel. When writing formulas with absolute cell references, we lock a part off or the entire cell reference to a specific row, column or cell. We achieve this by using the dollar sign prefix for the part of the reference that we do not want to change. So in our case, that would be the column K reference in the original formula. Now adding the dollar sign prefix, pressing Enter, and then copying the formula down into other cells, we can see that the row references in these formulas have been updated as desired. However, the column K reference has stayed the same. Now copying the same formula into the alternate sum column, and then using the Ctrl + Tilde the shortcut, we can now see that the column K reference has not been updated as was done previously. We would expect to see the same result when copying this formula into other cells of the alternate sum column. So to summarize, we typically use a combination of relative and absolute cell reference types to author formulas that we expect to copy across multiple cells, while maintaining a reference to a specific cell, a specific row or a specific column. This allows us to optimize our design process and expedite model development by writing fewer formulas that can update reliably, when copied, moved or filled across the expected data structures within our models.