Menu
Shortcut for Long Models
Financial Modeling - 14 comments
Have you created models which run into 20 – 30 years? You might have noticed that navigating to the last year (the last column) is probably the most boring part (and also the most time consuming part). Excel does provide you a shortcut (Ctrl + end), but that hardly works!
It’s been a while since we spoke and in this tutorial, I would like to make up for our lack of interaction by introducing a clever trick to cut down your time and effort in creating such models.
Where would you see the use of such a technique?
In most of the financial projections that we create for Project Finance, Project Management (Especially for long gestation projects), month on month projections, navigating to the last year/ month in such a sheet is a slow process. Typically you would have 100s of years/ months and you have the following choices with you:
· If you use Shift + Right key, you can easily take a quick nap by the time you reach the right cell.
· If you use Ctrl + Shift + Right, Excel will take you to the end. If you are planning to come back with Shift + Left Key, I suggest you have a comfortable pillow to sleep!
· I earlier used to resort to Ctrl + End shortcut key, but that does not work if your sheet has end characters placed at random places in your sheet.
The basic techniques do not work here!
Create a Guiding Row
The trick is to use a combination of Excel shortcuts and use the modeling process more intelligently. The first step is a manual process and can take the usual time – Creating a guiding row.
For example, in my model, I have created a row for Construction counter flag. For this row, I typically just write the formula and use Shift + Right key to navigate to the end of the model and use one of the following:
· Ctrl + R (Copy to the full row)
· F2 (to Edit), followed by Ctrl + Enter (Please note that it is not Ctrl + Shift + Enter)
· Copy (Ctrl + C) in the beginning and then press Enter (I avoid using Ctrl V to make sure that my clipboard is always empty)
Ctrl + C: Copy, Shift + Right to Navigate to End
Enter to Paste
Use the Guiding Row to Create the Model now
Once we have the guiding row ready, we can use a combination of the excel shortcuts that we already know of. Let me show you the sequence:
1. Copy the formula
2. Navigate to the guiding row
3. Use Ctrl + Right Arrow to navigate to the end of the guiding row
4. Go Down one row (From the guiding row)
5. Use Ctrl + Shift + Left key to select and reach the beginning
6. Press Enter (or Ctrl + R Key) to fill all the cells
7. Final Shortcut Usage
Another way to tackle the problem
Just like in this case we are using a row as a guiding row, I also use a guiding column to navigate quickly to the last column. What I would do is simple – Put a cross after the last column and then use Shift + Ctrl + Right key to navigate to the end.
I will speak about this trick in another tutorial!
Which Shortcuts do you use for your long models?
Shortcuts are cool! They help you concentrate on the modeling process rather than waste time fiddling with the Excel. Which shortcuts do you use in your long models? Share and learn!
Templates to download
I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template and check, if the information you recorded, matches mine or not! 🙂
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com
Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Learn Any Area of Excel using these 80 Links | Formula Forensics 006. Palindromes » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
14 Responses to “Shortcut for Long Models”
When building models running into many years/months, I find the last column of data in the time line and then hide all the columns after this one. Navigation is then very easy. Ctrl + R takes me to the last column of the row series. Ctrl + L takes me back either to the first column of the series in the row or if the row is blank back to the last column with an entry. Copying a formula across the time range is very simple and saves lots of time, which would be otherwise lost on moving around the spreadsheet and making sure that the cursor is where it should be!!!
I use Shift + End + Arrow to easily navigate. It's lightning fast, and works well, so long as you don't have blank cells along the row/column you're trying to get to the end of.
I work as a performance analyst for BP and have very large spreadsheets for lots of different reasons. One technique I regularly employ is to freeze row A1 and insert some hyperlinks to other sections of the workbook.
I often use these shortcuts, and when I use them while others look on they become lost rather quickly. I usually have to back up a few steps and slow everything way down.
I also skip the copy/paste step, and use CTRL+R & CTRL+D (the letters, not the arrows) to fill formulas to the right and down respectively. You don't even have to change selections. If you have one formula built out that has the relative and absolute references set correctly, make your full selection, and press CTRL+R & CTRL+D, your full selection will have the formulas filled in. My only lament is that Excel doesn't have a Fill Up and Fill Left shortcut. Perhaps I will build them into my personal VBA add-in...
Hi Chandoo,
I'm surprise to see your tutorial on "Shortcut for Long Models", as I have been using this technique for very long time, I never thought this is so important topic to talk,, but your post gives a meaningful insight to this subject.
This technique really save lots and lots of times of who use excel more frequently
Thank you,,
Tadeusz's method is by far the best. Suggest you could do a whole blog post about hiding unused rows and columns in order to speed up model development and reduce the chance of data being entered in ranges that should remain blank. Bravo Tadeusz!
I have a similar wide spreadsheet covering 36 monthly data sets. My solution is to freeze Columns A-C.
Column A is a list of hyperlinks to each quarter: So cell A2 is "FY 2010 Q1"; A3 is "FY 2010 Q2", A4 is "FY 2010 Q3", etc. I used to be annual, then semi-annual, but management wanted it quarterly.
Column B is blank to offset the table
Column C of course contains the data categories
Hope this helps!
Thank you for this guidance.
Within long tables like this I would create some hyperlinks with named cells. This works for my use perfectly.
In a long spreadsheet I use at work, I use the headings frozen at the top/left as the guide row/column. I prefer keyboard over mouse but I find mouse quicker to get to frozen guide row/column because the data in the spreadsheet is non contiguous. As I already have my hand on the mouse after clicking the guide row/column, I then use double clicking a cell boundary to navigate.
I find the following easier than all posts above:
1. In Options, turn on Lotus 123 Navigation. Lotus always had a more logicical and intuitive use of the keyboard than Excel for navigating around spreadsheets, where End+arrow, End+Home, and Ctrl+Home are the quickest ways to the extremeties of your model
2. Create a "fence" in the column immediately to the right of the last period by entering a vertical bar in the cell (copied from row 1 to the very bottom of my model, with a similar bottom fence using \= from column A to the RHS.) These become my "rat-runs" or boundary fences which I can quickly run along or use at end-barriers
3. To copy from LHS to RHS: Ctrl+C to copy source range, Shift+End+arrow to go to the RHS whilst simultaneously selecting the paste range, Enter.
Colin, this method breaks down as soon as you insert new rows or columns in your model, i.e., you need to then go and mend your fences. Try Tadeusz's suggestion of hiding rows and columns beyond your model range and see how easy it is.
@Tardeusz: Thanks for sharing the tip. I think it is good. But I dont personally like that methodology. It changes the structure of the full sheet (From col 40 to the end). It can significantly reduce the performance of excel. Typically I have found that changing the background color of the full sheet/ hiding lots of rows/ columns deteriorates the performance.
@Ken: Most of the times you would not create models in a strictly linear fashion. I have not found ctrl + shift + end to be that useful for me.
@Gereth: Thats again a good idea. But if you are creating models with multiple rows running to the last year, it might not be that useful.
@ahamed: Thanks
@Sachin: Thanks for sharing the tip. I have not been using hyperlinks in my models. Let me try using it.
@Colin: Thanks. I also use this technique quite often and will probably write a post on the same.
@paramdeep: Not that I am pushing my approach - I am always open to new methods particularly if they improve my productivity. Building financial models that involve hundreds of lines of formulas, where consistency demands that only one formula can be entered across the calculation area of each row, I have found that hiding the columns at the end of the used area of the spreadsheet allows me to work very efficiently. Nevertheless, I was most intrigued by your comment that this method can significantly reduce the performance of excel. Would you care to expand on this?
thanks !
a useful tip