5. Naming and Anchoring Cells
Writing many formulas in Excel can get tedious and time-consuming. Naming and anchoring cells are two techniques that you can use to make the process faster and easier.
ALT + M, H: Show formulas
F2: Enter formula in selected cell
F4: Cycle through anchoring cells when inside cell formula
CTRL + F3: Open name manager dialog box
- $A$1: Cell does not move at all when pasting.
- $A1: Cell does not move when pasting horizontally
- A$1: Cell does not move when pasting vertically
- Allow you to refer to a specific cell with a name (e.g. INTEREST_RATE)
- Will appear in the autocomplete, just press TAB to complete
- Make your formulas much more readable
- Need to be deleted if you're moving the location of the named value
Naming and anchoring cells are two techniques you'll use in almost all data sets and models when copying and pasting formulas.
In an earlier lesson, you might remember that we first wrote a formula in the cell F2 to calculate the Order Dollar Amount.
We then copied this cell and then we extended it to the remaining cells with the command Alt + E S F.
Let's examine how this paste works by using the show formulas shortcut Alt + M H.
As we move down the column, you can see that the pasted formula changes the inputs to correspond to the new row.
So D2 multiplied by E2 becomes D5 multiplied by E5, And so on right down to D24 multiplied by E24.
Alt + E S F, which pastes formulas, works great in this case, but sometimes we'd like to hold a number constant while pasting a formula, and this is when we anchor cells.
Let's return to our values again with Alt + M H and move below the data set where I'll show anchoring cells in a simple example.
It turns out that our US Company is actually a subsidiary of a European holding company that would like our monthly revenues converted back into Euro.
We'll be able to do this using the exchange rate in the yellow cell.
Let's start off by calculating the Euro Revenue for January, by taking the Dollar Amount and dividing by the Exchange Rate.
We can then copy the cell and paste it across February and March.
And unfortunately, we get a divide by zero error.
When we press F2 on the February calculation, we can see that the Exchange Rate cell has move to D30, because we have not anchored the cell and the formula.
Let's delete our two calculations, and go back into the first calculation by pressing F2.
When we get to C30, we can pres F4 which adds two dollar signs, one before the letter and one before the number.
This anchors the cell so it won't be moved when we paste formulas.
If we press F4 again, it shows only a dollar sign next to the number, which means the cell is only anchored within a column.
If we press it one more time, the cell will now only be anchored across rows.
This setting or the double dollar sign setting will work for us in this case.
So let's copy this formula, and Alt + E S F for February and March.
When we now look at the formula bar, we can see how the cell C30 stays constant for each cell.
Let's do another anchoring cells example, but this time for columns.
Again, we take the First Value and divide it by the Exchange Rate.
We then press F4 to anchor this cell.
I'll anchor with the double dollar sign this time.
We''ll then copy the formula and Alt + E S F for February and March.
In this scenario, I could have also anchored with the dollar sign just in front of the number if I wanted to.
As you can see, anchoring is pretty straightforward and very useful.
Sometimes however, it can get tiresome typing in the cell reference for commonly used constants such as Exchange Rates or Tax Rates.
This is where naming cells comes in use.
Lets return to our Exchange Rate, and to name the cell we'll press Ctrl + F3 to bring up the name manager dialogue box.
Press Enter to create a new name.
Make sure to capitalize the name, which signifies the constant, and then press OK to complete.
We can now use this new name in our formulas.
Let's go back to our initial example, and type in equals dollar revenue divided by USD.
Our constant appears, type Tab, and then press Enter.
We can then copy and paste this formula, and I'll have to worry about anchoring cells.
Naming cells are a great time saver when you've worked with multiple sheets, and they can save you a lot of time switching between Tabs.
They also make your formulas much easier to read.
To delete a name, navigate to the cell, press Ctrl + F3 and then Alt + D to delete.
And if you're sure, press Enter for OK and then Escape to leave the Name Manager.
We can now see that the name has been removed from the Exchange Rate and the Cell Number has been returned.
I'll just press Ctrl + Z to undo and return the cell name, so that my formulas work again.
To get some practice at anchoring cells, I've left an exercise at the bottom of this sheet with multiple Exchange Rates for you to try.
You should be able to fill all of the blue cells with just one copy paste action.
As always, I'll have the answer in the after file that you can access below this video.