3. Paste links and transpose data: Startup and investor excel model training

You gots the data in a column, you wants it along a row, how d’ya do that? Simples!
What about automatically linking a tonne of cells to eachother? That would be cool too right? Let’s find out how now, brown cow!

Intro to excel model training

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 direct 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 paste links and transpose data?

There are just a tonne of times you do this.
Let’s start with transposing, I mainly ‘transpose with formulas’ using a plugin when I am making cohorts. That’s super duper useful. Otherwise, maybe you have weird shaped data and you want to fit it a different way? You’ll know when you want to, but great you know how too now!
Here is an example where you can transpose the months to a column for a cohort:

Screen Shot 2017-07-11 at 12.26.40 AM 2.30.46 PM.png

Next, paste links. Great stuff. Whenever you have a series of calculations to calculate things, things this comes in useful. Take a look at the 50Folds SaaS model. In almost everysheet, I can’t be bothered to replicate the data fields, so I just paste links (making sure to $ them). Then anytime I change the title all the rows change just right. Super handy 😉

Screen Shot 2017-07-11 at 12.21.54 AM 2.30.46 PM.png

How does paste links and transpose data work?

Paste links

It’s best to use the sheet and actually do this.
On PC, select the data then ctrl/cmd+C, select where you want to paste, and then press ALT+e+s+l then press ALT+L, or on a Mac, CMD+Option+V, then L and enter.

Screen Shot 2017-07-11 at 12.29.12 AM 2.30.46 PM.png

Transpose data

On PC, select the data then ctrl/cmd+C, select where you want to paste, and then press ALT+e+s+l then press ALT+E, or on a Mac, CMD+Option+V, then E and enter.

Screen Shot 2017-07-11 at 12.29.19 AM 2.30.46 PM.png

Useful hints

  • Watch out that you have the space to paste the data, otherwise you copy over your existing data with your paste
  • Make more than enough space so no headaches by adding more rows/columns as you need (remember the last class! Told you it was useful)

Example

The excel example sheet will help you to learn this with an example.
Move the data here:

Screen Shot 2017-07-11 at 12.35.33 AM 2.30.46 PM.png

To transpose and link here:

Screen Shot 2017-07-11 at 12.35.41 AM 2.30.46 PM.png

Which will look like this:

Screen Shot 2017-07-11 at 12.35.49 AM 2.30.46 PM.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 ConvertKit

Leave a Reply

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