# Excel test - top 3 things to study ?



## islandguy11

I work as an accountant and consider myself somewhere between novice and intermediate based on this:

http://www.xl-logic.com/modules.php?name=Content&pa=showpage&pid=1

I've got a job interview with an excel test.  What would you consider to be the top 3 things to study/review?  Pivot tables? Macros?

thanks..


----------



## texasalynn

welcome to the board and good luck

formulas
pivot tables


----------



## teylyn

Hi, 

go for pivot tables first. Also look at Sumif and Countif, and understand which works best in different scenarios, pivots or formulas. If you have time, explore Sumproduct, too.

You need time for macros. Start recording a few macros and study the difference and effects of absolute and relative cell differences.

good luck

teylyn


----------



## Matty

Ditto texasalynn's pleasantries, islandguy11.

Specifically, in terms of formulas, I would get familiar with:

Lookup formulas
If formulas
Math formulas (MOD, INT, etc.)

There is a good Workbook you can download from this site, that shows working examples of most of the formulas Excel has to offer:

http://www.xlfdic.com/

Though the site is down for the moment for some reason...

Good luck with the interview.

Matty


----------



## Blade Hunter

With regards to finance, I would say the VLOOKUP is one of the most useful formulas too.


----------



## Smitty

Welcome to the Board!

Have you asked how in-depth the test will be and if it's targeted toward any specific competency?


----------



## islandguy11

thanks for the welcome and advice everyone...

One question with macros:  would it be expected for an intermediate user to know VBA, or is the macro record tool sufficient?  i did some vba programming in the past, but it was quite some time ago and have not used it in daily work

Smitty:  i think the test will be targeted more towards analysis, and being able to summarize a large amount of data... perhaps in a format that could be used in a report.  It's not a programming job, so I'm not so sure about how much VBA is expected, if any.


----------



## Richard Schollar

I would make darn sure you know what a VLOOKUP formula is and, if you have time, understand how to use INDEX/MATCH instead of VLOOKUP.  Also SUMIF and COUNTIF.

I would also check out Pivot Tables and know how to create a chart from data (and maybe how to add a secondary axis - I've had that in an Excel test before!).


----------



## Blade Hunter

islandguy11 said:


> i did some vba programming in the past, but it was quite some time ago and have not used it in daily work.


 
If you were to say that to me in an interview, I would take it as a plus, it shows you can pick it up if you need to but are a little rusty.


----------



## xenou

I wouldn't expect anything truly advanced (by advanced meaning what counts as intermediate on this board).

Do expect a lot of questions about cell navigation, formatting, basic formulas, and a simple chart, pivot table, possibly a list.  Do not expect macros, advanced filter, external data sources - although I could be wrong.

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



		Code:
__


Format Cells - Number Formats, Date Formats, Alignment, Merge and Center, Borders/Underline
Sum, Average, Count, Round, IF, LEFT, RIGHT, MID, MATCH, VLOOKUP
Hide Columns, Rows
Tab Name, Color
Copy a Sheet
Share workbook
Protect Sheet
Name a cell
Subtotal
Validate an entry (must be number, must be date)
Sort
Filter
Split a window or freeze a pane
Page Setup - headers, footers, Rows to Repeat at Top/Left
Print Area, Page Breaks
Create a Simple Chart
Create a Simple Macro


----------



## islandguy11

Alexander Barnes said:


> 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

islandguy11 said:


> 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


----------

