Recommend an Advanced Excel course

Onearmedbandit

Guys,

I want to do a night course in advanced microsoft excel specifically something around business modelling if possible. Does anyone have any ideas of good course currently on?

Thanks

j.olsen

You can do one of the ECDL advanced courses in Excel.I have done same and found it to bee very useful.

It covers Vlook,Hlook,scenerio manager so on.In order for you to receive your cert in the exam based course,you will have to complete the entry level first.Yoy can still do the advanced level, sit the exam without receiving the cert.

nordydan

As an Financial IT Developer who uses very advanced Excel (VBA, forms) etc every day, I would recommend looking for a course that involves at least learning some basic VBA (not the run/record macro on the fromt end). Especially if you are doing modelling.

If you work for a large institution, I would also recommend learining the in-built MS Query (and VBA-SQL code structure with or without the XLODBC add-in).

to actually learn the business and design principles of financial modelling (which to be honest is what is most important once you actually start building models and working in the area) these guys are great but not the cheapest http://www.fi-mech.com/

Onearmedbandit

Thanks for your responses.

I suppose im looking for a course that teaches VLookup H Lookup and a bit of VBA. The modelling part can come afterwards, any ideas?

I'd split it up and learn advanced excel first and vba second. Advanced Excel is about formulas, where to find things on menus, shortcuts and general how to do things. VBA is a programming language. I'm not sure about courses. If you have a level of dedication I would advise just to get a book with a cd with examples and exercises. That's the best way to learn - by doing. The buillt in help in excel from 2003 on is actually quite good and has lots of examples on formulas.

For VBA the best book is
Excel Power Programming with VBA (by John Walkenbach)

For actual advanced excel there's really only about 10 to 20 formulas that you need to know how to use properly:-

sumif - way more useful than vlookup or hlookup for where you want to get figures
sum array formulas e.g =SUM((C12:C20="jan") * (D12 : D20="dub") *E12:E20) to get dub sales in jan with the figures in col e - need to press ctrl+shift+enter at the end of entering an array formula of course
vlookup
hlookup
match
index
choose

if - and nested if's e.g. an if within an if e.g. if(condition,if(condition,true,false),false)
if(and(
if(or(
offset

count
countif
dcount

stdev
dstdev

abs
round
mod

npv
xnpv
irr
xirr
ipmt
ppmt

Sleepy

You could look at the Microsoft Certication:

http://www.microsoft.com/learning/en/us/certification/mbc.aspx#tab2

fontanalis

LostInDaHack wrote: »

I'd split it up and learn advanced excel first and vba second. Advanced Excel is about formulas, where to find things on menus, shortcuts and general how to do things. VBA is a programming language. I'm not sure about courses. If you have a level of dedication I would advise just to get a book with a cd with examples and exercises. That's the best way to learn - by doing. The buillt in help in excel from 2003 on is actually quite good and has lots of examples on formulas.

For VBA the best book is
Excel Power Programming with VBA (by John Walkenbach)

For actual advanced excel there's really only about 10 to 20 formulas that you need to know how to use properly:-

sumif - way more useful than vlookup or hlookup for where you want to get figures
sum array formulas e.g =SUM((C12:C20="jan") * (D12 : D20="dub") *E12:E20) to get dub sales in jan with the figures in col e - need to press ctrl+shift+enter at the end of entering an array formula of course
vlookup
hlookup
match
index
choose

if - and nested if's e.g. an if within an if e.g. if(condition,if(condition,true,false),false)
if(and(
if(or(
offset

count
countif
dcount

stdev
dstdev

abs
round
mod

npv
xnpv
irr
xirr
ipmt
ppmt

SUmif is a great one, much better than subtotal imho. Can you elaborate on arrays.
A good excel course would be very beneficial, once you get into the logic behind the formulas alot of other things become easier.

this page will give you an insight into array formulas.
basically allows you to check multiple criteria (sumif only lets you do one).
be careful though - array formulas can really slow down spreadsheets...

http://www.cpearson.com/excel/ArrayFormulas.aspx