Improving Excel Skills

[BwoodBMW]

Originally Posted by BMW F22

What other skills would you prize more? Are you talking about analysis?

The critical thinking and analysis behind the model is a very big deal yes. BUT you also need to look at what your bosses do every day and start becoming better at those things as well. If it is PE/VC/Banking how well do you understand a stock purchase agreement and any other legal documentation involved? How well do you understand the 5,000 negotiating points that exist inside of every deal and what effect each would have on your outcome? How are your people skills? Do you know what it takes to sell whatever it is you do? How are your presentation skills? You get the point I'm sure. At the end of the day an excel model is a very small piece of the success or failure of your firm. Don't neglect all the other pieces.

[BMW F22]

I'm doing a simple vlookup and it's skipping over some of them and just doesn't work past a certain row. I did a spot check and the data is there. All the cells are formatted as numbers.

Help?

[M3 Number 86]

You just need to be creative in the modeling logic. Without that you will never be great at it. Once you know the how - I want this to reference that and this to do that and so on - you can start googling how to make this do that. I've learned a lot on the job but the bulk of the advanced skills I learned was to teach myself how to execute the logic. I'm one of those don't even use the mouse guys.

[M3 Number 86]

Originally Posted by ASAP

What would you like to know specifically?

I would say almost anything in excel (in a business setting) can be accomplished using Vlookups, SUMifs and Pivot Tables lol...

I kid you not. If you know those 3, almost anything can be done.

Index match is even better.

[M3 Number 86]

Originally Posted by catcher22

This is true. If you master the topics in MS Excel (Vlookups, if statements, and Pivot Tables), you pretty much have everything you will ever need with Excel in the business environment. Now, you may need to create crazy formulas that reference other tabs and perform calculations, but that's almost always just trial and error to get the formula correct (unless you already know the formula).

The only way I learned was on the job - I had a task, report, or data that I needed, and then began lots of trial and error, reading, and messing around with the data to see how it is output. I once spent 16+ hours straight on an excel spreadsheet getting the crazy formulas corrected. Something like 8AM-2AM at work....straight thru (it was year end)

If you need to do more than the three functions above, you need to learn how to use MS Access or a more sophisticated tool. Excel is a powerful tool but has its limits.

Indirect formula works to make it easy for models with a bajillion tabs lol.

[M3 Number 86]

Originally Posted by BMW F22

I'm doing a simple vlookup and it's skipping over some of them and just doesn't work past a certain row. I did a spot check and the data is there. All the cells are formatted as numbers.

Help?

how many columns is your lookup table??

[BayMoWe335]

"Crazy formulas" are usually just long, nested IF statements, etc.

The real key to Excel is being able to do the job in a concise formula. The best people I've seen in Excel have eloquent solutions to problems. The "formula gurus" always make things too complicated, increasing run times and calculations.

Long story short, long formulas usually are inefficient and the people who use them don't think outside the box well.

I'd focus on the latter. For example, having a formula with hard coated numbers = stupid (making long, complex formulas. Use tables and lookup references instead.

Besides, using Excel is for worker bees....ever notice how directors and VPs are shit at Excel to the point that can't even do a simple pivot table?

[BMW F22]

I just copied the data to a new sheet and it worked. Not sure why it was skipping lines. I did the exact same thing but on a new sheet and it worked.

[M3 Number 86]

Originally Posted by BayMoWe335

"Crazy formulas" are usually just long, nested IF statements, etc.

The real key to Excel is being able to do the job in a concise formula. The best people I've seen in Excel have eloquent solutions to problems. The "formula gurus" always make things too complicated, increasing run times and calculations.

Long story short, long formulas usually are inefficient and the people who use them don't think outside the box well.

I'd focus on the latter. For example, having a formula with hard coated numbers = stupid (making long, complex formulas. Use tables and lookup references instead.

Besides, using Excel is for worker bees....ever notice how directors and VPs are shit at Excel to the point that can't even do a simple pivot table?

Agreed. I would say you either do the whole function in one cell or create supplemental stuff on the backend to make the calculation simpler. Either way goes. Different models need different things.

VP's don't really know well because excel and or the amount of metadata wasn't as big back then and they aren't really going to be the ones putting the stuff together. The smart ones know though, how the data should be parsed out and ask if you have done that already.

One of our former VPs said when she first started the job 25 years ago people were using paper and pencil. WTF!!

[M3 Number 86]

Originally Posted by BMW F22

I just copied the data to a new sheet and it worked. Not sure why it was skipping lines. I did the exact same thing but on a new sheet and it worked.

sometimes excel gets foggy. I would start practicing index match rather than lookups.

[KingOfJericho]

Originally Posted by M3 Number 86

Index match is even better.

This. I use INDEX/MATCH dozens of times each day.

Another finance guy here...

I've taught myself quite a bit of excel VBA as well and the best method to learn is to do until you hit a roadblock then just google it. Almost every question I've ever had has been addressed on Mr Excel or Stack Overflow so I just thumb through until I find my solution.

I'm constantly amazed by what the program is capable of. I have monthly presentations that used to take me two days to update (~100 pgs). I took a few days when it was slow to macro most of it and I've managed to shave an entire workday from the updating process. Aside from the time saved, it has also eliminated all human error which used to be a constant issue.

I <3 Excel.

[BMW F22]

Originally Posted by M3 Number 86

sometimes excel gets foggy. I would start practicing index match rather than lookups.

Good suggestion. I've been brushing up on it but will try to do more.

[M3 Number 86]

Originally Posted by KingOfJericho

This. I use INDEX/MATCH dozens of times each day.

Another finance guy here...

I've taught myself quite a bit of excel VBA as well and the best method to learn is to do until you hit a roadblock then just google it. Almost every question I've ever had has been addressed on Mr Excel or Stack Overflow so I just thumb through until I find my solution.

I'm constantly amazed by what the program is capable of. I have monthly presentations that used to take me two days to update (~100 pgs). I took a few days when it was slow to macro most of it and I've managed to shave an entire workday from the updating process. Aside from the time saved, it has also eliminated all human error which used to be a constant issue.

I <3 Excel.

Yes! The best thing that comes with excel skills plus business knowledge is the ability to execute faster.

Who is the VP going to ask to put together a complex analysis - someone who can do it quickly and accurately then spot check or someone who does it manually and then needs to check over their work manually for a while.

That's how I've been able to succeed.

To all, finance is such a broad term what do you guys do?

[M3 Number 86]

Originally Posted by BMW F22

Good suggestion. I've been brushing up on it but will try to do more.

It's awesome because you can do lookups left and right, up and down, to find a cell in the intersection of a table and starting in any column or row of the same table.

Are you by chance the person who left work to pursue some fun for a bit?

[Verbiage]

Go Skills is a nice well-rounded database for tutoring/simulations.

[BMW F22]

Originally Posted by M3 Number 86

It's awesome because you can do lookups left and right, up and down, to find a cell in the intersection of a table and starting in any column or row of the same table.

Are you by chance the person who left work to pursue some fun for a bit?

Yeah, that was me. Was gone for about 2-3 months. Started a new job with a nice increase in Jan. Was a Financial Analyst and now I'm doing data/sales analytics.

Always keeping an eye out for even better opportunities though.

[m630]

Originally Posted by BayMoWe335

Besides, using Excel is for worker bees....ever notice how directors and VPs are shit at Excel to the point that can't even do a simple pivot table?

...this may be true, but us Drs had to start somewhere to get where we may be today, so to those looking to grow, excel is an ok skill to have. It is true i rarely need my vba skills much anymore, but from time to time I still do my own proofs or revalidations in certain situations and still can use vlooks with th best of 'em. But in enterprise situations, excel is not a preferred solution, in fact it's not used much as itsso inefficient, same goes for access which is a nono. If you are forward thinking in this space (finance and big data business intelligence) learn how to use etl tools along with reporting engines like business objects, tableau and the like, that's how u can unlock the power of the data

[M3 Number 86]

Originally Posted by BMW F22

Yeah, that was me. Was gone for about 2-3 months. Started a new job with a nice increase in Jan. Was a Financial Analyst and now I'm doing data/sales analytics.

Always keeping an eye out for even better opportunities though.

Cool always good to see ppl succeed.

I did market intelligence for about a year and it did take my excel skills above what you typically need in an fp&a role. So it's helped me build addition skills that my competition in my field doesn't have. So I've been able to land a new job at a start up like company basically building a finance dept from scratch. I start next week and it should be fun!

[BMW F22]

Originally Posted by M3 Number 86

Cool always good to see ppl succeed.

I did market intelligence for about a year and it did take my excel skills above what you typically need in an fp&a role. So it's helped me build addition skills that my competition in my field doesn't have. So I've been able to land a new job at a start up like company basically building a finance dept from scratch. I start next week and it should be fun!

Very nice- congrats!!

Lately I've been thinking in doing Sales Ops, Sales, or some role that deals with people more on a regular basis. Extrovert so the interaction gives me energy. Lol

[M3 Number 86]

Originally Posted by m630

...this may be true, but us Drs had to start somewhere to get where we may be today, so to those looking to grow, excel is an ok skill to have. It is true i rarely need my vba skills much anymore, but from time to time I still do my own proofs or revalidations in certain situations and still can use vlooks with th best of 'em. But in enterprise situations, excel is not a preferred solution, in fact it's not used much as itsso inefficient, same goes for access which is a nono. If you are forward thinking in this space (finance and big data business intelligence) learn how to use etl tools along with reporting engines like business objects, tableau and the like, that's how u can unlock the power of the data

But interface type reporting tools are static in terms of what you want to look up. Don't they need to be coded on the backend? That takes time and resources.

In excel modeling you can do a data dump and cut it however you want. In my field half the work is of ad hoc nature. There is no system that spit out what you want. No two analysis are the same.

You have to take the data and manipulate it. That's the beauty of excel.

Can you tell systems to alter assumptions to provide pro forma estimates quickly or to manually tweak a number here and there to alter the outcome?

Different tools do different things better.

[M3 Number 86]

Originally Posted by m630

...this may be true, but us Drs had to start somewhere to get where we may be today, so to those looking to grow, excel is an ok skill to have. It is true i rarely need my vba skills much anymore, but from time to time I still do my own proofs or revalidations in certain situations and still can use vlooks with th best of 'em. But in enterprise situations, excel is not a preferred solution, in fact it's not used much as itsso inefficient, same goes for access which is a nono. If you are forward thinking in this space (finance and big data business intelligence) learn how to use etl tools along with reporting engines like business objects, tableau and the like, that's how u can unlock the power of the data

But interface type reporting tools are static in terms of what you want to look up. Don't they need to be coded on the backend? That takes time and resources.

In excel modeling you can do a data dump and cut it however you want. In my field half the work is of ad hoc nature. There is no system that spit out what you want. No two analysis are the same.

You have to take the data and manipulate it. That's the beauty of excel.

[M3 Number 86]

Originally Posted by BMW F22

Very nice- congrats!!

Lately I've been thinking in doing Sales Ops, Sales, or some role that deals with people more on a regular basis. Extrovert so the interaction gives me energy. Lol

Yea interacting is fun. On the other hand I find it hard when you work with others esp if they dumb.