Excel test - top 3 things to study ?

[islandguy11]

Definitely know if you'll be tested on XL2003 or XL2007

i know it will be excel 2007.

are the differences from 2003 (what i use) large enough to warrant buying 2007 or installing the free trial?

 

[DonkeyOte]

My two pennies...and on a similar theme to the others:

Pivot Tables IMO should be the first thing anyone learns -- if nothing else it teaches you the value of laying out your data correctly in the first instance whilst not requiring a great in depth knowledge of XL formulae.

All of the following:

VLOOKUP (& HLOOKUP), INDEX, MATCH

The latter two in particular.

With any formula try to make a point of knowing which is best when and moreover why ... ie why would you opt for one over the other in a given scenario.

Demonstrating an understanding of the mechanics of a function is one thing but understanding why it's the optimum approach is another ball game altogether and in the world of big financial models of immense value... I'm still learning myself but having any/some grasp is better than none

In Finance knowing how to work with Dates is of obvious importance -- learn the basic functions but also make a point of knowing the functions available to you via the Analysis ToolPak -- the likes of EDATE, NETWORKDAYS, WORKDAY, EOMONTH etc... can save you a lot of time and head scratching but obviously try to have an alternate approach available should the ToolPak not be a viable option.

Again related to big models -- try to get to grips with Volatile functions - these can have a serious impact on performance -- examples of Volatiles being things such as: INDIRECT,OFFSET,TODAY(),NOW() etc... I've seen lots of BIG models with lots of volatiles... they're slow to calculate for a reason

And if you still have the will to live... Arrays & Sumproduct ... though often where you require lots of Sumproducts a Pivot Table is invariably your better option

 

[xenou]

Yes -- absolutely. You won't know how to find the menus for simple tasks you can do in your sleep in XL2003 - imagine not being able to show you can save a file with a new name. That would ruin your day, I guess. Shortcut keys and hot keys (Alt + key) will work still...if you can't find it on the 2007 ribbon.

 

[Peter_SSs]

i know it will be excel 2007.

are the differences from 2003 (what i use) large enough to warrant buying 2007 or installing the free trial?

I'm strongly agreeing with Alexander. If you know Excel 2003 fairly well, you will know much of what excel 2007 can do. However, in 2007 most people initially have a terrible time finding what they know is there somewhere. So you need to get familiar with the interface if you are going to be tested on it.

Good luck!!

 

[Greg Truby]

Like Peter, I too strongly agree with Alexander. If you have not had a chance to get to know XL2007 yet and the test will be done in 2007 you *must* figure out a way to get some practice time on 2007. I am a very, *very* strong keyboarder. I can do just about anything in XL2003 without even touching the mouse. And since the keyboard shortcuts still work, I can do pretty anything in 2007. But not everything. It's a bit of a shock when you first open 2007 and you cannot figure out how to print, save or open a file unless you invoke the keyboard shortcuts.

And to Smitty's point, you would be well served to see if they can tell you a bit about the focus. Excel is so darned big and all encompassing that I would wager that -- with a lot of effort -- I could come up with a test where even Smitty would miss 5 of 10 questions and I'm sure Smitty could do the same to me. Even Bill himself will tell you that even after interacting with thousands of users in his seminars and writing over a dozen Excel books he still learns something new from the crowd at almost every seminar he gives. Anything you can do to try and get a bead on the focus will help a lot.

 

[riaz]

As you said you are an accountant, if the job is in accountancy, then a look at the financial functions might not go amiss.

Good luck, and come back and share the good news with us.

 

[Jon von der Heyden]

I think the financial functions really depend on what sort of an accountant you are. I for one have very litte opporuntity to use the financial worksheet functions.

I'm really just echoing others but in my view Pivot Tables are an absolute must! I wish most of the accountants I work with knew pivot tables - they would avoid so many unnecessary errors. Pivot Tables can deliver so much more than most people realise, although largely provided that the data is correctly laid out.

Debra's site has some great tutorials on pivot tables: http://www.contextures.com