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.
41 Responses
Ok, a remarkably simple but just awesome idea! Thanks.
This is a wonderful post!!! There have been so many times where I had to do separate graphs to show changes for the same product over time because I was measure two vastly different pieces (e.g. # of transactions and $ sales). I’ve needed this and didn’t even know it! Thank you!
I love your charting techniques posts!Â
How would you cope with values that are extremely variable before indexing? If the price of the commodity is sometimes very high and sometimes very low, the index won’t very accurately reflect later changes. Can you somehow homogenize the values to artificially deflate the variability?
Hi Bctyner…
Thanks for your love.
In such high volatility, you can use moving averages to plot the line. Assuming a value has cycle time of 7 days (for example: sport event ticket prices go up on weekends and go down on weekdays), you can calculate moving average of 7 days and then plot that to understand the true trend of your data.
For more on moving averages see – http://chandoo.org/wp/2009/04/28/calculate-moving-average/
Also, you can calculate standard deviation and show that as a data point on chart
Its not just an excel technique but a great analysis technique too. Thanks a lot for sharing ! Love your post ( as always 😉 )
Hi Chandoo,
  thanks for nice article. But I’m wondering, how to visualize or analyze data as follows: Let’s imagine we have forecast of 3 materials for week10 till week 20, actualised during 7 weeks (w1 ~ w7). Could you point me to some guideline, how to visualize biggest drops or increases and such a things? Like a forecast history?
I’m attaching example.
https://dl.dropbox.com/u/96356710/forecast.xlsx
Thanks for help.Â
Hi Exoskelet.. You are welcome.
If you have such data, a quick and easy method would be to calculate weekly fluctuation (change% with respect to previous week) and then apply conditional formatting to highlight very high or very low percentage changes. Like below:
Download example file
Great post – thanks for the great advice.
Awesome
Hi Chandoo
Downloaded the workbook ,  can you please explain how to relate the box to the data , Tried doing it was not successful
Regards
Srini
Ditto on Srini’s comment. The whole dang thing is cool, but I couldn’t figure out the link in the data box. Please comment on that technique.
Thanks
Hi Srini & Dean,
The linked data is called as picture link. Please see here to know how to use them:
http://chandoo.org/wp/2010/10/19/how-to-use-picture-links/
Another great post (but who’s surprised by that!).
I run eBusiness activities for a major, global manufacturing company. I need to record and display progress but to do so without revealing any company secrets. I use indices to show %age of total revenue processsed through eBusiness as well as absolute revenue numbers.
The starting point (=100) is the month I joined the company, so there’s no doubt about the value my team and I have added 🙂 Â
Hi,
Great article. I was wondering how you created the “View” page chart to show only columns A-J?Â
You can hide all unnecessary columns & rows by just selecting and hiding them. Please see here for instructions: http://chandoo.org/wp/2012/07/24/show-only-few-rows-columns-in-excel/
Thanks a lot appreciate your good work
Hi Chandoo,
i have a query please. Could you please tell me how i can create a secondary x axis. When i referred articles on the net, it mentioned that secondary x axis option is available in the ribbon under Axis. But i couldnt find it. Could you please help,
Regards
Deepa
right click on the series, you want to have on secondary plot, select format data series, right side of the window you will see secondary axis option..select that and you are done…!!
Hi Chandoo,
Another great post..thank you sharing.
I am not able to get the upward and downward arrows with the percentage in same cell, how do you do that??
Do reply.
Nishant
Hi, How would you handle a situation where the index date for one of the item which you index against has a value of 0 at time=0?
Hello, votre billet m’a été très agréable à lire. Je repasserais sur votre site internet pour lire souvent vos billets.
Thanks. great!how you put the summary below the chart pls?
I want to monitor data changes in open interest of stocks on daily basis.Will this indexed chart work in such situations too.Please reply ASAP.Regards.
Great post thanks.
Hi Chandoo, really like your website…
What if there is a decrease in the following year. How can I calculate the result if the value in the new year (2012 above) is zero?.
i.e.
2011: 2
2012: 0
-200%
Hi Chandoo,
thanks for this.
I need to have a sliding 0 index date so that I can amend the 0 index date on the fly.
How would I be best to approach this?
Best regards,
Mark
Dear Chandoo,
What if my categories are not in full value but in percentages, like Industrial Production, GDP growth rate, Inflation Rate? How to to the indexed chart of such categories?
I want to highlight the row cell with to dates in same row
This indexing and rebasing to 100 has been most useful; I have been able to take the aggregate performance of a small portfolio of investments and compare, or benchmark, this to a recognised global index. However, this portfolio is not static, additional sums are invested periodically (not necessarily regularly) so the original investment of say £100 is now £100 + additional amounts: my question is, how can I now compare my dynamic portfolio to the global index?
Best Excel tutorials I have ever come across. Your content is informative and easy to understand at the same time
im trying to create a chart where the buy price (x) is straight horizontal line across numerous dates on the bottom.
Than the current close of business price per day (Y) is in the same graph
So i can than see if the cost of item is higher than buy price per day.
I just cant seem to get the correct setup to work to show (x) in the graph as a separate continuous line.
Hi Richard…
See this:
Superb post – thanks so much Chandoo!
Mr. Chandoo, you are just great and a great teacher as far as excel is concerned.
I have been studying your post for a long and have derived immense benefits.
Thanks.
Thanks Uche… 🙂
Great post as usual! Could not find anything to create the same in Power BI. I’m trying to replicate it via Dax measure but I’m not getting the output I want. I’m using this formula below
Rebased Stock Indices =
VAR
Original_Value =
CALCULATE ( AVERAGE ( F_stockindices[Close]), FIRSTDATE ( ALLSELECTED (F_stockindices[Date] ) ) )
VAR
Current_Value = AVERAGE( F_stockindices[Close] )
RETURN
DIVIDE ( Current_Value, Original_Value )*100
It would be great if you could help with a video or a post! Many thanks anyway for your amazing work!