The question at the heart of this discussion is deceptively simple, yet it cuts to the core of how we should think about spreadsheet functions like ADDRESS. When you compare ADDRESS(1,1,1) and ADDRESS(1,1,4), the output difference is just a dollar sign. But that small character represents a fundamental choice about how your data behaves when you copy, move, or scale your formulas. The practical difference is not in the text string itself; it is in the intent behind the reference.
Let's be direct about what this means for your workflow. ADDRESS(1,1,1) returns $A$1, which is an absolute reference. It is the right tool when you are building a formula that must always point to a fixed cell, regardless of where it is copied. Think of a tax rate, a conversion factor, or a lookup table that should never shift. ADDRESS(1,1,4) returns A1, a relative reference. That is your choice when you want the formula to adapt as you fill it across rows or down columns, allowing the same logic to apply to new positions without manual editing.
The real insight here is that ADDRESS is not just a way to produce text; it is a way to construct references dynamically. When you combine it with INDIRECT, you can build formulas that respond to inputs, creating flexible dashboards or reports that update based on cell values. The difference between absolute and relative in this context is the difference between a fixed anchor and a moving target. If you need a formula that always references the first row of a dataset, you use the absolute form. If you need it to follow the current row, you use the relative form. That is not a subtle technicality; it is the difference between a formula that breaks when you drag it and one that works exactly as intended.
For users who feel overwhelmed by spreadsheet complexity, this distinction is a gateway to confidence. Once you understand that the dollar sign is not a decoration but a control mechanism, you stop fearing formula expansion. You can predict how your data will behave before you even press Enter. That is the kind of practical knowledge that transforms a frustrating session into a productive one. So the next time you type ADDRESS, ask yourself what you want to happen when your formula moves. The answer to that question is the only difference that matters.