Excel test - top 3 things to study ?

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
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
 
With regards to finance, I would say the VLOOKUP is one of the most useful formulas too.
 
Welcome to the Board!

Have you asked how in-depth the test will be and if it's targeted toward any specific competency?
 
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.
 
Last edited:
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!).
 
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.
 
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
 

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top