11. Ampersand: Startup and investor excel model training

Intro to Excel model training- Ampersand

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 instalments, 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 “&” Ampersand?

& or ampersand sticks stuff together.  There are some more fringe uses of & in advanced formulas, but for the most part you will use it to make dynamic sentances.

Here’s an example from the staff sheet of our fundraising models, where we populate the costs per month of staff for different expense categories.

This serves two purposes:

  1. You can quickly populate similar sentances (if you are lazy). You write the format once and then you drag it down and you are done.
  2. You can make them dynamic so if somone, or you, changes the categories they will automatically update. So basically you can be wrong and change stuff a lot and you don’t need to worry.

ampersand populate names.gif

Another example would be if you have financial statements, say your Balance Sheet. For your actual year you may illustrate the number of shares outstanding. This may change regularly, so throwing in a little automated sentance can save you some time.
If you look closely you can see for the date that we’ve thrown in a text function and embedded some formatting between the [” “]. We could do the same thing to format the shares. The reason you have to format the date is it will return as a date code: 43221
No one know what the f 43221 means, so you need to turn that into something useful. You can save time on the shares, but not the date.
2017-07-24_13-38-36.gif

How do I do these things you speak of?

It’s pretty simple. You want to stick Rock and Roll together. Well you need excel glue, right? & is glue to make Rock & Roll.

= rock and roll results in this #NAME?

You need to use quotes [” “] around any text block.

Formulas are happy to stick together, but you may need to format them as above.

So:

=”Rock”&”Roll” sort of works… but it just looks like RockRoll. Which could be a deli for giants (shit joke, sorry).

=”Rock”&” & “&”Roll” This is what you have to do. Note there are spaces included after and before the & which we want to see (not use as glue)! Also to include & in the formula you have to put them between quotes.

TBH, I only learnt how to do all this by failing. I don’t know off the top of my head how to format everything. I test stuff or quickly google. So don’t stress about learning everything off by heart. Just know that this is possible.

Example

The excel example sheet will help you to learn this with an example.

ampersand.png

To get the training model, subscribe to the series on the right.

Get in the game

Free tools and resources like this shipped to you as they happen.

We won't send you spam. Unsubscribe at any time. Powered by Seva

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.