Writing links correctly so you can copy them all over your model is one of the best ways of saving time in ExCel for investment banking. There are numerous functions and tricks you should use. Read on to get all of them.
Locking the right cells
The F4 shortcut is the most basic thing you need to know about linking. This decides what happens when you copy a link. Normally, the link will move with your chosen cell as you copy the formula from one place in the model to another.
If you link to a cell that is two rows to the left and two rows above the one you’re in, that’s going to be the case when you copy the formula to a new cell as well. It will always be two rows to the left and two rows above the one you’re in.
If you want to change that and lock the absolute position of a link, you can use F4. Pressing F4 once locks the cell completely. Pressing twice locks the row, and pressing three times locks the column.
This means that if you have locked the column to D, and copy the cell two columns to the right, it will still link to column D, but the rows will move.
Using lookup formulas
Usually, you will build a model where you can switch quickly between different inputs by selecting an alternative. You can, for example, have several price scenarios for a product your company is selling, or cost scenarios. This is where you want to be able to pick up information based on a change in a single cell.
Below is a list of the most common methods to use, along with the reason for choosing them.
Lookup function: This formula has an input value it wants to look up. It iterates through a row or a column until it finds this value, and then returns a value with similar coordinates from a chosen row or column.
Be warned that this formula uses a search algorithm which assumes the data are sorted in ascending order, and if they aren’t it can return the wrong result. Consider using a different formula that takes this into account.
If function: It returns a given value if a statement is true or wrong. For example, return 1 if the cell above is > another cell. This is very useful if you want to know if you are within a given time period for example.
SumIf function: This function sums cells where the statement you give it is true. This can, therefore, be used to choose data.
You can, for example, have one cell with the name of a cost scenario you have chosen, and ask the SumIf function to return a sum if the name of the data is equal to this. Meaning it will only return the chosen scenario.
Offset function: This is a quirky little thing. You give it a starting cell, and then a number of the rows and columns you want it to search away from that. It will move to the right and downwards.
Match function: It will take one cell as an input, and then look through a row or column to find the same value. Then it returns the position of this value to you. Sounds useless? Read on to see combinations of this formula with others.
Index function: This needs three inputs. First, it needs an area it will search in. This can be an entire worksheet. Then it needs the position within the row and the position within the column. It will return the value of the cell in that position.
Combining index and match functions: This is where ExCel starts to become more flexible. If you put a Match function into both the column and the row input of the Index formula, you can do a quite advanced search.
One example is if you are looking for the EBITDA for the year 2023. You can then ask it to go through all rows until it finds the cell that says EBITDA, and then walk through all the columns in that row until it finds the one with the headline 2023. Pretty good, huh?
Indirect function: Most people hate this function, but it has its uses. If you have a huge excel file with several sheets, and you need to pick up values from different sheets that are all in similar cells, you can use this. It takes a word as an input and translates that into a position in excel.
If you have a worksheet called OpexInput and want to find a number that is in cell F22, you can just input that into two cells and link Indirect to them. This means you can, for example, change the name of OpexInput, and get it to find cell F22 in OpexOutput as well.
This is where the combination comes into the picture. You link it to cells with different words, so the lookup formula changes.
PS. Always use the Indirect function at the end. If you make changes in a sheet it is referring to, it will not change the formula as you move cells around. This formula is also one of the reasons not to include spaces in the names of your tabs. This formula crashes if you use more than one word or weird symbols.
Naming the cells you use often
To name cells is helpful if there are a few cells you know you will be linking to all the time, or if you are using any of them in a macro. This can, for example, be annual inflation or an FX rate.
Names: F3 This is used if you want to manage all the named cells or ranges in your file. You can name either one single cell, or a group of cells. If you’re using a lookup formula that always looks up in the same area, you can name this entire area.
Choosing a name: Tab When you start inputting the name of a cell as you write, all your options will be shown. To choose one option, you need to press Tab. Enter will not work.
Rolling it out even quicker
Find: Ctrl+f If you are looking for a cell with a particular formula, try this to make excel look for you.
You can ask it to look both in the text in the formulas, or in the output the cell shows.
Find and replace: Ctrl+h This is very useful when copying sections from other ExCel models, or when rolling out parts to a larger model.
If all the cells refer to a certain worksheet, but you want it to refer to another one, you can just ask ExCel to replace this text, and the formulas will be changed.
This is also very useful if you forgot to use F4 when you started, and realize you need it later. If all the cells are linking to D2, D3, D4 etc, but you want them to link to $D$2, $D$3, $D$4: Ask ExCel to find D and replace with $D$. Solved!
What ExCel won’t help you with
Excel can do a lot, but not everything. Over the years, there have been a few formulas I have missed. You can easily create them as a Macro formula, but others may not trust your macros.
One is a formula to add cells with a certain interval. For example, sum every third cell below a start cell and iterate 20 times if you have 20 assets to model. The same goes for a SumProduct version of this formula as well.
More articles in the same series:
Over the next week, I will post more articles in the same series, helping you to more easily build a model.
- Basic ExCel shortcuts you need to learn when you start in investment banking
- How to quickly nail the formatting of your first EcXel model in banking
- Understanding how to link correctly and roll out big ExCel models quickly
- How to avoid the unnecessary pitfalls in your ExCel cash flow valuation
- Error checking in EcXel: Tools to discover your mistakes before it’s too late