Sign in or start a free trial to avail of this feature.
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.
How Paste Formulas Works (00:10)
When you copy a formula and use paste formulas, the row and column numbers are automatically adjusted. For example, say the formula in cell F2 is =E2*D2. If you copy and paste this formula to cell F3, the formula in F3 will be =E3*D3. To see this in Excel, you can use the Show Formulas shortcut Alt, M, H.
Anchoring Cells (00:50)
Pasting formulas works well when you want the inputs to adjust automatically. However, if you want to hold a cell reference constant while pasting a formula, then you should anchor cells. For example, if we’re converting several months of revenue from dollars to euro, we want the exchange rate that we use to be constant. Anchoring stops a cell reference from changing when we copy and paste the formula.
When writing a formula, we anchor a cell using the F4 key. The anchoring is indicated by the $ symbol. There are three types of anchoring, as shown below:
- $A$1: The cell reference will be unchanged when pasting anywhere
- $A1: The cell reference will be unchanged when pasting horizontally
- A$1: The cell reference will be unchanged when pasting vertically
Naming Cells (03:10)
If you use a particular cell in a lot of calculations, it can get tiresome to type in the cell reference repeatedly. You can deal with this problem by naming cells. When you name a cell, you can use the name in formulas instead of the cell reference.
To name a cell, select it, then open the Name Manager with the shortcut Ctrl + F3. You can then create a name for the cell. As a convention, you should give cells names that are entirely in capital letters.
Naming cells has several advantages. You don’t need to worry about anchoring when you paste formulas. You make your formulas easy to read and understand. Names can also save time switching between tabs if your workbook has multiple sheets.
You can delete a name by navigating to the cell, opening the Name Manager, and pressing Alt, D to delete.
In the previous lesson, we learned about Excel's logical functions. In this lesson, we'll learn how to name and anchor cells in Excel. Naming and anchoring cells are two techniques you'll use in almost all datasets 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 role. 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 dataset, where I'll show anchoring cells in a simple example.
It turns out that our U.S. 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 this 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 moved to D30 because we have not anchored the cell in 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 press 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 the cell. Unanchor 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 a 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. Let's return to our exchange rate. And to name the cell, we'll press CTRL + F3 to bring up the Name Manager dialog box. Press Enter to create a new name.
Make sure to capitalize the name, which signifies a 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 not have to worry about anchoring cells.
Naming cells are a great time saver when you've workbooks 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 okay. 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.