Sounds like another obvious thing, or does it? Dexterity with the keyboard is the great differentiator between the amateur and the bad a$.
Adding and removing rows/columns and then even hiding them is a master skill, but you probably think it’s for n00bs, so why bother?
No. Trust me, you’ll save so much time. EVERY SINGLE template I have made I used this technique multiple times.
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 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 excel hide columns and rows?
The faster you get around excel, the faster you are back in the pub 😉
This is wax on, wax off stuff Mr Miyagi would have taught.
Firstly you need to be able to move around and select a row/column and then select all those to the right or below of them. That’s the first skill.
Next, by hiding rows and columns you restrict where the selected cell can wonder to… Why does this matter?
Well, I make 3-year models. which have 36 columns of forecasts? The last column is always AR. When I’m modeling I hide all columns after AR (AS+) so that when I copy cells right, they stop at month 36 and now 1 million.
This blows the model up with calculations, or worse leaves a bunch of data which blows up your file size, making calculations sllllllooooooooow. Restricting my operating environment saves me a bunch of time.
Finally, it looks better. Well, a little. When an investor looks at my model, they know exactly what to look at and don’t have to wonder where the model on a sheet ends.
Trust me, it took me years to figure out all this.
How does hiding rows and columns work?
The details are in the sheet. It’s just a series of keyboard shortcuts. .
Useful hints
- If your model blows up to 10mb, then it’s because data is down a row or column. Unhide the rows and columns, delete all the ‘blank’ data and your file size is back to normal
- There is a plugin tool which can tell you which sheet the blow up is in, but we’re not getting into plugins for quite a while
- Don’t worry about getting frustrated about selecting row instead of a column. I forget sometimes. It becomes muscle memory if you keep practicing!
- Always check file size in case you created a data leak!
Example
The excel example sheet will help you to learn this with an example.
Go from this:
To this:
To get the training model, subscribe to the series below.