Would you like to learn most popular Excel tips and shortcuts?
100 Excel & Charting Tips, Tricks and Resources for you.
These 100 tips & resources are organized in to the areas,
- Keyboard Shortcuts
- Formulas
- Using Excel to do more
- Charting
- Excel Books for everyone
- Excel Blogs & Resources
All these tips are bite sized and easy to read, digest and implement. The focus is on improving your productivity and making your day better. Wherever possible, I have included links to relevant articles on this site so that you can read and learn more.
If you like this post, I encourage you to signup for my free email newsletter.
Ok, on to the tips now…
25 Very Useful Keyboard Shortcuts
1. To format any selected object, press ctrl+1
2. To insert current date, press ctrl+;
3. To insert current time, press ctrl+shift+;
4. To repeat last action, press F4
5. To edit a cell comment, press shift + F2
6. To autosum selected cells, press alt + =
7. To see the suggest drop-down in a cell, press alt + down arrow
8. To enter multiple lines in a cell, press alt+enter
9. To insert a new sheet, press shift + F11
10. To edit active cell, press F2 (places cursor in the end)
11. To hide current row, press ctrl+9
12. To hide current column, press ctrl+0
13. To unhide rows in selected range, press ctrl+shift+9
14. To unhide columns in selected range, press ctrl+shift+0
15. To recalculate formulas, press F9
16. To select data in current region, press ctrl+shift+8
17. To see formulas in the worksheet, press ctrl+`
18. While editing formulas to change the reference type from absolute to relative vice versa, press F4
19. To format a number as currency, press ctrl+shift+4 (ctrl+$)
20. To apply outline border around selected cells, press ctrl+shift+7
21. To open the macros dialog box, press alt+F8
22. To copy value from above cell, press ctrl+’
23. To format current cell with comma formats, press ctrl+shift+1
24. To go to the next worksheet, press ctrl+shift+pg down
25. To go to the previous worksheet, press ctrl+shift+pg up
20 Situations and How to Solve them using Excel Formulas
26. To get the first name of a person, use =left(name,find(” “,name)-1)
27. To calculate mortgage payments, use =PMT(interest-rate,number-of-payments,how-much-loan)
28. To get nth largest number in a range, use =large(range,n)… Get Full Tip
29. To get nth smallest number in a range, use = small(range,n)… Get Full Tip
30. To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… Get Full Tip
31. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,””))… Get Full Tip
32. To count positive values in a range, use =countif(range,”>0″)… Get Full Tip
33. To calculate weighted average, use SUMPRODUCT() function
34. To remove unnecessary spaces, use =trim(text)
35. To format a number as SSN using formulas, use =text(ssn-text,”000-00-0000″)… Get Full Tip
36. To find age of a person based on DOB, use =TEXT((NOW()-birth_date)&””,”yy “”years”” m “”months”” dd “”days”””), output will be like 27 years 7 months 29 days
37. To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas… Get Full Tip
38. To get proper fraction from a number (for eg 1/3 from 6/18), use =text(fraction, “?/?”)
39. To get partial matches in vlookup, use * operator like this: =vlookup(“abc*”,lookup_range,return_column)
40. To simulate averageif() in earlier versions of excel, use =sumif(range, criteria)/countif(range, criteria)
41. To debug your formulas, select the portions of formula and press F9 to see the result of that portion… Get Full Tip
42. To get the file extension from a file name, use =right(filename,3) (doesn’t work for files that have weird extensions like .docx, .htaccess etc.)
43. To quickly insert an in cell micro-chart, use REPT() function… Get Full Tip
44. COUNT() only counts number of cells with numbers in them, if you want to count number of cells with anything in them, use COUNTA()
45. Using named ranges in formulas saves you a lot of time. To define one, just select some cells, and go to menu > insert > named ranges > define
15 Excel Tips on Improving Productivity Using Excel
46. To format a number as SSN, use the custom format code “000-00-0000″… Get Full Tip
47. To format a phone number, use the custom format code “000-000-0000″… Get Full Tip
48. To show values after decimal point only when number is less than one, use [<1]_($#,##0.00_);_($#,##0_) as formatting code… Get Full Tip
49. To remove grid lines from excel worksheet, go to menu > tools > options > and un-check grid lines option. (Excel 2007: office button > excel option > advanced)… Get Full Tip
50. To hide a worksheet, go to menu > format > sheet > hide… Get Full Tip
51. To align multiple objects, like charts, drawings, pictures use drawing toolbar > align and select alignment option… Get Full Tip
52. To freeze rows on top, select the a row and use menu > window > freeze panes… Get Full Tip
53. To disable annoying formula errors, go to menu > tools > options > error checking tab and disable errors you don’t want to see… Get Full Tip
54. To change the shape of cell comments from rectangle to some other symbol, select the comment, go to drawing tool bar and change the shape from there… Get Full Tip
55. To transpose a range of cells, copy the cells, go to empty area, and press alt+e+s+e… Get Full Tip
56. To save data filter settings so that you can reuse them again, use custom views… Get Full Tip
57. To select all formulas, press CTRL+G, select “special” and check “formulas”
58. To select all constants, press CTRL+G, select “special” and check “constants”
59. To clear formats from a range, select menu > edit > clear > “formats”
60. To move a chart and align it with cells, hold down ALT key while moving the chart
9 Charting Tips for Everyone
61. To create an instant micro-chart from your data, use sparklines… Get Full Tip
62. Understand data to ink ratio to reduce chart junk, using even a pixel more of ink than what is needed can reduce your chart’s effectiveness
63. Combine two different types of charts when one is not enough, to use, add another series of data to your sheet and then right click on it and change the chart type… Get Full Tip
64. To reverse the order of items in a bar / column chart, just click on y-axis, press ctrl+1, and check “categories in reverse order” and “x-axis crosses at maximum category” options
65. To change the marker symbol or bubble in a chart to your own favorite shape, just draw any shape in worksheet using drawing toolbar, then copy it by pressing ctrl+c, now go to the chart and select markers (or bubbles) and press ctrl+v
66. To create partially overlapped column / bar charts, just use overlap and gap settings in the format data series area. A overlap of 100 will completely overlap one series on another, while 0 separates them completely.… Get Full Tip
67. To increase the contrast of your chart, just remove grayish background color that excel adds to the chart (in versions excel 2003 and prior)
68. To save yourself some trouble, always try to avoid charts like – 3D area charts (unstacked), radar charts, 3D Lines, 3D Columns with multiple series of data, Donut charts with more than 2 series of data… Get Full Tip
69. To improve comparison, replace your radar charts with tables… Get Full Tip
6 simple steps for better chart formats
70. Remove any vertical grid-lines
71. Change horizontal grid-line color from black to a very light shade of gray
72. Adjust chart series colors to get better contrast
73. Adjust font scaling (for versions excel 2003 and prior)
74. Add data labels and remove any axis (axis labels) if needed
75. Remove chart background colors
5 Excel books for everyone
76. Excel 2016 Bible by John Walkenbach
77. Excel 2016 Power Programming by John Walkenbach
78. Excel 2016 All in one for dummies by Greg Harvey
79. Microsoft Excel Data Analysis and Business Modelling by Wayne Winston
80. M is for Data Monkey by Ken Puls
PS: Links to Amazon, affiliate code used
20 Excellent Resources and Blogs for getting latest Excel Tips & Charting Ideas
81. PTS Blog
82. Andrew’s Excel Tips
83. Microsoft Power BI Blog
84. Contextures
85. Junk Charts
86. Daily Dose of Excel
87. Digital Inspiration
88. Life Hacker
89. Jorge’s Charts Blog
90. Data Chant
91. Excelarator BI
92. Guy in a Cube
93. More information per pixel
94. Newton Excel Bach
95. Presentation Zen
96. Visual Business Intelligence by Stephen Few
97. Spreadsheet Journalism
98. Allen Wyatt’s Excel Tips
99. Excel Guru
100. Chandoo.org on YouTube
Join the celebrations, share your tips & ideas
I encourage you to share your Excel Tips & Charting Suggestions through comments section. We have a vibrant reader community here and we love to learn from each other.
If you like this post, I encourage you to signup for my free Email Newsletter
Post updated on 20-May-2018
15 Responses to “Modeling Interest During Construction (IDC) – Excel Project Finance”
Thanks again for a very helpful post.
I had a similar problem when trying to model a balance sheet and profit and loss projection. The problem was that interest expense (in P&L) was dependent on a cash shortfall (in BS) which had to be funded. The cash shortfall depended on how much interest was paid, so the mutual dependency made a circular reference.
I addressed it with a macro that calculated interest outside of the P&L, then pasted the calculated amount into the P&L as a value. The model was out of balance, but by repeating the pasting and calculating loop the imbalance reduced to zero. It was a bit messy, and had to be repeated every time a line changed - but it worked.
If I have to do it again I'll read this article again first and see if it can be done more elegantly.
Hi,
The use of a circular reference can be avoided in this case. Just make use of the geometric sum to calculate the interest required. I’ll walk through the example from the spreadsheet.
First calculate the cash needed each year without the interest expense. So you year 1 you need 55 Mn, year 2 105 Mn, and 190 Mn for year 3. The total amount to borrow for year 1 is then (50 Mn)/(1-interest_rate) = (50)/(1-0.1). For years 2 and subsequent the amount borrowed is the cash needed in that year plus the interest_rate times the amount already borrowed. For year 2 (105 + interest_rate * sum(previous debt raised))/(1-interest_rate)=(105+0.1*61.1)/(1-0.1).
This process avoids the need for a circular reference, and makes the calculation more stable.
Thanks,
Tristan
The question is for the year 1 in your case, the amount works out to 45 mn. However in the year 2 you have applied the loan amount as 61.1 mn.
Am I missing something ! Please help !
very helpful information!!!
using circular references and to make model more stable we can use combination of "IF" and "ISERROR" functions. i.e
=if(iserror(formula1),"",(formula1))
this formula will return blank value if there is any error otherwise give the result required.
I usually use this in my models and it makes them very stable......
🙂 🙂 🙂
@Terry: Thats right. Exactly same problem is seen in Interest - Cash cycle in P&L and Cash Flow statement as well. In our trainings on financial modeling in excel, we demonstrate using both the circular loops as well as the macros to take care of this problem. Circular loops have their own pitfalls. If the model enters into a state of error, the error percolates!
@Tristan: Thanks for pointing out. I agree with you that if circular loops can be avoided, they should be avoided.
@Yogesh: This is one way of avoiding the problem. Although circular loops have another problem that they make your sheet slower. Each time, there is a change in the sheet, all the calculations are redone. So if they can be avoided, they should be avoided.
Please note that this was an example (a large one indeed) and I didn't have space to speak about the pitfalls of this approach! I just wanted to illustrate an approach and am glad that some of you found it useful!
I think while posting, there is an error in the images! The last image should be flipped with the one that is posted in step VII!
I think you can try the following simple solution given by Microsoft itself to make the circular works:
Windows: Excel Options -> Formulas -> Put a tick on "Enable iterative calculation"
Mac: Excel -> Preference -> Calculation ->Put a tick on "Limit iteration"
You can change the maximum number of calculation iterations as well as the maximum changes which iteration stops for goal seeking or for resolving circular references based on the number you type in the maximum change box.
Thank you.
Hey All
I heard that we can take care of the circularity with the help of macro for IDC. Can anybody help on the steps to construct the macro for the same.
Regards
Vinay
Hi Vinay,
If you look closely, you are essentially copying the values from the interest calculation to the IDC in project cost.
Basically you can record a macro, that takes the values from interest and pastes special the values in IDC row in project cost.
Then you can run that recorded code in a for loop.
Hope this helps.
Thanks Param for reply.
But before calculating interest, i need to provide for Upfront Equity and Equity, which are essentially part of total project cost. Hence, i need to put in Upfront Equity and Equity to calculate the IDC which is again hitting the total project cost.
Bit of confused on how to remove this circular reference.
Regards
Vinay
Wow, this was a brilliantly simple post. I was looking online for a while before I found this page. Never seen this been explained so beautifully yet so crisply before. Thanks for saving my ass at work! (i'm relatively new to finance + modeling)
I'm not sure why but this web site is loading very slow for
me. Is anyone else having this issue or is it a problem on my end?
I'll check back later and see if the problem still exists.
[…] Project Finance Modeling using Excel – Part 1 & Part 2 […]
I have been reading your blog since my college days. Today, I'm writing just to say thanks.
We have calculated Financial Rate of return of a hydropower projects, and the observer has raised an observation regarding Total Project cost with IDC Rs. 8616.01 million (PKR) and with-out IDC 8352.46 million (PKR). How does the Financial nalysis be calculated on the basis of with-out IDC Or With IDC?????
Please helpf. if possible to spare some time.