Static and Dynamic References
After we get basic formulas such as Sum, Average, Count down, learning how to rapidly create sheets with these formulas will require a better understanding of how the static and dynamic references work.
When people tell me they understand and know how to use Excel, I start off with a basic question to test their knowledge.
"What does the $ when used in a formula mean? For example I type in Cell B2 the formula =$A2"
Invariably I get answers ranging from it makes the cell display currency or simply that they do not know.
The Proper use of a $ is for speed when making sheets with multiple formulas. It creates a static Column and or Row reference. Lets assume that I have column B2's formula set to ="$A2". No matter where I copied my formula it will always look at column letter A. This also works with auto fill capabilities in Excel.
The Static declaration can be placed before the column, row, or both. Each has it's benefits. When I want a static range for a lookup, I will write something like $A$2:$G$273. Now when I copy that cell, it will always try to look up from that range.
A very common usage can be to have a list of dates in Column A, Sales amount in Column B.
You can now get a running total of sales by using "=Sum($B$1:$B1)". I place the Static reference in front of column B so that I can Move my formula anywhere as needed and it will still give me a summary of the sales in Column B. Now as I fill my formula from C1 and down it will give me a sum from B1 through the current row.
Likewise Imagine I had Dates in A, Sales Unit Quantity in B, Sales Revenue in C, Hours Worked in D.
I want to get a running total of all of these items per day quickly. In Cell E1, a very similar formula "=Sum(B$1:B1)" Can be used. Now when I copy the formula both right and down I will get running totals of each column instead of just column B.
Learning when to use a Static reference vs a Dynamic reference will dramatically reduce the time it takes to build large and more complex workbooks!
For information on other Excel topics, check out the Excel Tag to the right or look at the overview I have planned on My Blog Here
