13. Dollar signs: Make formulas connect in specific ways Startup and investor excel model training

Intro to Excel model training- Dollar signs

This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.

This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.

To get the excel example for this and all the other installments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.

You can join up to the training here.

What’s the point of the Dollar Signs?

It’s impossible to do anything cool without the might dolla dolla bill and I mean $.

When you put a $ in a cell reference, it allows or prevents that reference to move (or not) around columns, rows, or both.

Specifically, there is absolute or relative referencing.

I know this sounds nerdy. And the only way to really explain it is to show you examples.

Let’s say you want to calculate tax on revenue. You always have 20% tax.

Without $ this is what happens:

See that it doesn’t link right? You want a double $ to make your reference absolute. This is totally relative

So what does this need to look like?

You see in cell C6 you want the reference to be $C$2 and C5.

$C$2 = Doesn’t move!

C5 = shakes it like a polaroid picture

That’s the basic use case, but there are times that you want things to move or stick in both rows and columns. The example in today’s class is going to teach you how to do just that.

We’re going to learn how to make a matrix with formulas. This could also be done with a data table, but that’s another class, and frankly, data tables can be annoying as you have to load them and that takes up performance (if you have too many they are a nightmare)

How do I do these things you speak of?

A cell consists of a column and a row. So for C2

C = Column

2 = Row

To stick things to their column or row you slam a $ before the reference.

So to make the above stick we do this:

$C = Stuck Column

$2 = Stuck Row

But a cell reference is both, so they need to be together:

$C2 = Stuck Column and free Row

C$2 = Stuck Row and free Column

$C$2 = Stuck everything

Now I do this a thousand times a day (when I’m getting my model on). It’s REALLY important.

It’s not totally intuitive. What I do is think

“What do I want to stick? Row or column. Ok, the formula has to go across and not down, so I stick the row. That’s the second part of the cell reference. So $2.”

I literally think this everytime I do it as it needs to be right or nothing will work!

Yes, of course, you can hack it to save time and then test to see if things are working right, but that’s a terrible use of your time and you might test it wrong. I spend a little more time thinking through making the formulas as I do it, since I don’t want to do manual testing. I want to bust out another 100k formula.

So think what you want to do and what you want to restrict. Then restrict the column, row or both. And you need to do this for every reference.

Now, there are times you don’t have to add a $, even though you could. If you are building a formula in only one row and won’t copy the formulas down to more rows, you may not care about restricting a row reference. It saves you a few seconds editing, but means you can’t reuse the structure. So just do what you need to to get things done fast. But remember there is a tradeoff of flexibility and speed sometimes.

Key learning!!!!

If you are going to do this a number of times, manually clicking and editing is totally bogan!

You HAVE to use shortcuts to be a boss.

The way you do this is:

• Mac: CMD+4
• PC: F4

Everytime you press the shortcut it will cycle through the options.

1. $C$2 = $ all
2. C$2 = $ the Row
3. $C2 = $ the column
4. C2 = back to no $

So I know if I want to $ the column, I’m going to slam F2 or CMD-4 3 times in a row. I’ve done this a lot, so to me it’s autopilot.

If I mess up and I’ve $ the column, I know I only need to shortcut once to get to totally relative formula.

This may sound inane, but trust me, if you get into modeling you’ll want to think like this to save time and work efficiently.

Pro tip:

You can press

• Mac: CMD+2
• PC: F2

This will open up the formula bar and you can press left to the cell reference you want and start tapping your $ shortcut 😉

Example

The excel example sheet will help you to learn this as an example, by making a matrix.

To get the training model, subscribe to the series below.