gotcha. well, welcome to the world of padding mr. gates wallet. its awesome.
luke77 Wrote: ------------------------------------------------------- > Numi, I don’t think I’m at the firm/group you > think I’m in - almost all the analysts in our > group use excel heavily. I’m not sure why they > didn’t give us much excel training - maybe they > figure, like kkent said, that the best way to > learn is on the job because we’ll be doing it > every waking moment? Our training was pretty wide > - they told us at the outset that they wanted to > give s a broad background, and that they pride > themselves on having the best training on the > street - although I guess all banks probably say > that. The training has been very impressive, > though… > > nolabird I’d rather not say where I went to school > on a public message board; I’ve already posted > probably too much identifying information… > > BTW, for anyone who has taken the 7 and 63, which > one is harder? We took the 7 this week and I was > nervous about passing so I studied alot and ended > up with an 88, I was wondering how much I need to > prepare for the 63? i guess the degree of excel use is relative, but i felt pretty certain you were in ECM/DCM at one of the banks in downtown nyc. maybe i’m wrong though.
No, that’s correct - but all of our groups use excel heavily except maybe two or three (there are over 20). When you’re doing derivatives structuring or something similar, pretty much the entire job revolves around the model, and many are built from scratch.
So in reference to my original question, does anyone have an answer? Thanks.
Very easy: Search help and learn: Pivot Table Hlookup Vlookup Index Match Lookup Then go to functions and learn every one
Check out Microsoft’s website for Webcasts/Labs. I have used webcasts in the past and found them very useful. You can also read textbooks on “VBA” programming from the local library.
Never use the mouse. Just think everytime you reach for it you are showing signs of weakness. There are only a couple of things I don’t know how to do in Excel without using the mouse, and they annoy me. Contrary to the above advice try to avoid any function containing LOOKUP and OFFSET. Looking slick in Excel isn’t knowing all the functions it is knowing the basics and knowing how to find out the functions i.e. Help. Learning simple things like Ctrl / shift + arrow keys, F2, F5 will help loads and obviously Ctrl + C / V / X etc., but if you don’t know that already…
CTRL R is your friend!
Uhh, I’m going to have to disagree. Lookup and Offset are very helpful for complicated cash flow models, but useful in a small minority of instances. I do agree that you should minimize your use of the mouse, although there are a number of routine things in Excel where not using your mouse is just silly. Use the mouse when it’s the fastest.
OFFSET is unauditable and hence shouldn’t be used (especially in large models). My former company had a complete ban on its use in place! One redeeming feature is that you can put OFFSETs and imbedded OFFSETs into F5 which is a very neat trick. LOOKUP is less bad, but I only see it in bad models and hence I associate it with bad practice. It requires you to repeat the first column / row everytime you use it whereas using INDEX, MATCH and a namerange ensures consistency. I can’t think of many things that are quicker using the mouse. My old boss used to go around removing the mouses from new recruits - harsh, but they learnt quickly. kkent Wrote: ------------------------------------------------------- > Uhh, I’m going to have to disagree. Lookup and > Offset are very helpful for complicated cash flow > models, but useful in a small minority of > instances. > > I do agree that you should minimize your use of > the mouse, although there are a number of routine > things in Excel where not using your mouse is just > silly. Use the mouse when it’s the fastest.
completely agree about not using a mouse, that’s the best way to learn and become proficient in Excel
Geesh, Big Bean, I don’t know what your firm does, but my former employer that taught us the Excel tricks couldn’t function without OFFSET or LOOKUP. They built extensive (and I mean EXTENSIVE) bond pricing models that required every kind of information possible. How the heck would you look up (LOOKUP) information in tables more efficiently than LOOKUP in a manner that allows for maximum flexibility within the model? We had absolutely zero problem using vlookup and hlookup. It works wonderfully. I rarely used OFFSET because I never did models that complicated, but I saw it successfully used many dozens of times in highly complicated models. There are plenty of things in Excel that are quicker with a mouse. For example, most things on the toolbar (which you can customize) can be done as fast, if not faster, than not using the mouse. I agree that Excel is fastest without a mouse, but best practice is to use the method that is quickest. 90% of the time, not using a mouse is quickest.
kkent, almost all things on the toolbar, with a few exceptions have a shortcut key for them, for instance CTRl+1 will get you into all formatting functions. Also, I agree that ‘vlookup’ and ‘hlookup’ functions are useful, and especially more appropriate rather than using direct links, but there are other methods available to lookup values, for instance ‘indirect’ and ‘address’ are a powerfull combo, syntax is =indirect(address(…)) Also agree with TheBigBen that ‘offset’ is very hard to audit, however the function has some nice uses, but many prefer to model without it.
volkov, yes, ctrl 1 brings you to the formatting functions. But why do that to change your font when you can easily point and click? Why change colors that way when you can point and click? That’s a lot of unnecessary tabs just to avoid the mouse. As much as I hated my firm, I totally agreed with them that avoiding the mouse just to avoid the mouse was foolish–you avoid the mouse when it’s quickest. 90% of the time, the mouse is slower, I agree. But there are plenty of things that make sense with the mouse. Again, I don’t use OFFSET (it’s above my head), but the people getting paid big bucks to model bond issues sure as heck used it. I’ll defer to them about its use.
I am not trying to argue that one has to avoid using the mouse at all cost. However, for someone learning Excel avoiding it may be necessary, so he can learn quciker and become more proficient. You need to become comfortable with all shortcuts or at least the ones, you’ll be using on a daily basis, and there is no better way of learning them than practise.
I think his point with the lookups is that it is more robust to use a combo of INDEX and MATCH functions than a v or h lookup. In my experience this is true, especially for huge models. Lookup functions can become unstable and slow if there is a ton of data. I still use them for fairly simple models though, as they are a little easier.
Big Nodge and volkovv, thank you for expertly arguing my corner! Though, volkovv, whilst INDIRECT is amazingly powerful it is also unauditable so perhaps not one for the beginners. Many very clever people use OFFSET. I have seen models with 5 rows of calculations in one cell only using OFFSET and it does what they want. At least one of these people deliberately wrote his model so that no one else would understand it - a kind of unemployment policy. The problem is that the cells it references have no dependents which makes it extremely hard for any one else to follow and hence is considered bad practice. That doesn’t mean that it doesn’t work nor that clever people will start caring about those that follow. And now the geeky stuff which is irrelevant to this discussion (learning by avoiding the mouse was my advice for this thread) but might help someone… Changing font - it is best to change style, that way when your client says “I don’t like this bit in bold” you simply change the style defintion. In a couple of seconds the model is changed to your clients style. Changing style can be done by Alt + ’ and then either the letter of the style or the arrow keys. So in the blink of an eye you have everything presented as you wish. For colours and actual changes in fonts there are basically two ways I know. Half of all the people I know have personal macro workbooks automatically opened in the background of Excel. This allows you to define any shortcut you like. Many people use this to say colour something yellow, or conditionally format something etc. It may seem petty to have a whole macro devoted to colouring yellow, but it saves time in the long run. The second way takes a little practice and involves either Alt + Tab, Ctr + Tab, Tab combo (depending on your icons), or Ctrl + Shift + P and Tab combos, or Alt + ’ and Tab combos. Initially this way is slow, but when you see someone colour something this way you can’t fail but be impressed - assuming of course you’re an Excel geek like me!