Excel is a great tool for analyzing and presenting data. You can create fancy and complex formulas to make it look nice and even create fancy reports. Like I posted in my last post a Doctor used it* to create a nice patient tracking system.
I create a lot of spreadsheets in my work as a developer everyday. I am not using it for business purposes, but more for analyzing data, sometimes presenting data in a fancier reports for users to see. This involves lot of formulas used in coloring, highlighting data.
If you are like me, you will type the formula once and copy and paste that on all cells. Here is an example:
Now, if you look at the formulas behind those cells (using Ctrl + Backtick – see here), you will see what I mean:
I typed the formula into D2, then I just copied and pasted the formula into D3, D4. Then Summed it up in D5.
This is fine for regular calculations where you have each line calculates based on the data in the same row.
What if you have a common cell that needs to be used in all calculations? Consider the below example, where the transactions are in a different currency and you want to calculate in USD. Instead of hard coding the Currency exchange rate, you put that in a Cell, so you can use it in various lines or even sheets.
Let’s look at the formulas behind (again by pressing control + back tick(`)):
What happened? I did the same thing like I did before, enter formula in C2 and copied and pasted into C3, C5. But the values there are 0. Why?
Hmm.. Now I see the problem. My conversion rate is in Cell B8. I correctly used this in my formula on the first line (C2). When I copied and pasted in to other cells, Excel naturally shifted the row numbers. Thus, my second value in the expression, in C3, C5, became B9, B11… which happen to be empty, thus the result = 0.
How do we avoid this? I typically go back and correct the formula, but that tends to be a tedious task, if you have a lot of such formulas on your spreadsheet. This is where anchoring comes in handy:
In Excel you can anchor a cell used in a formula by adding $ to it. Here is the modified formula and pasted values in other cells:
See that? When I added $ to B8 ($B$8), it anchored it. When I copied and pasted into other cells (C3, C5) it maintained that. And that did the job:
Voila! Oh yeah, to add anchor to a cell, you can type $ sign each time, or simply press F4 when you type a cell reference into a formula, as mentioned here. I love it when there is already a solution for a problem you run into. Don’t you?
Before closing, here are some terminologies:
Anchoring a cell reference gives you Absolute Reference – same reference is used anywhere on the spreadsheet. If you don’t anchor it, it gives you a Relative Reference – relative to the cell you are in. These are typically valid when used in a formula.
(*) I know the Doctor used Google Sheet there, but for practical purposes it works the same way Excel does.