[Anonymous]

So I have an interview next week. The job listing said that excel skills are a must and “will be tested”

What are the top 5 or 10 excel skills you think are most critical to know?

I’m pretty comfortable with excel, but not sure I use many of its advanced features terribly often. I usually look up formulas I’m not familiar with. Tell me which advanced skills you think are most used in the accounting field.

[MBA MST CPA]

Three that I can think of are Macros, Pivot Tables and VLookup. Although Vlookup is pretty simple, I have used it a lot working in an accounting function.

REG - 11/5/2012 - 80
BEC - 1/17/2013 - 76
AUD - 2/22/2013 - 70 4/5/2013 - 75
FAR - 5/28/2013 - 75 - Done!!!

Question: What do you call someone who only gets 75's and 76's on the four parts of the CPA exam?

Answer: A CPA

[SeattleAccountant]

Give priority to reviewing 1. VLookups, 2. Pivot tables

and only then, review anything else like filtering, sorting, protecting, count, absolute references, conditional formatting, basic cell formatting, IF function, hyperlinks, SUM, etc.

I did get tested on Macros but on a very basic level that does not require much of the knowledge.

Becker Class of Jan - Aug 2013: FARB DONE!!!!
CPA license pending 🙂

[smalltimeauditor]

I think the most critical excel skills are knowing shortcuts, formatting skills (think financial statements), and be able to filter/sort any PBC you receive from clients.

I've never seen that type of wording in a job listing before. Interesting.

B - Passed
A - Passed
R - Passed
F - Passed

[Anonymous]

Like others have said VLookup and Pivots come to mind instantly. Basic if statements are important as well.

I show people all the time how to do things like cntrl+shift+down to highlight a set of data to copy, using cntrl+down to move to the the next break in the data you are looking at, cntrl+home to go to the A1 cell. Some of these hot keys might be worth looking at.

[MCLKT]

Macro is pretty advanced, probably won't be tested in an interview. But VERY valuable. Probably #1 in value.

Agree MBAMST- Pivot Tables and Vlookups are a must.

Concatenate

If this, then that formulas =if()

Converting txt, csv files correctly using fixed, delimited, and specific characters

Should know how to:

Sort/filter by: amount, text, and formatting

Apply conditional formatting

Use formulas for: Sum if, Absolute

=trim()

=left / right/ middle/

Also maybe paste special? Comments, Value only, multiply

A:[73]97 F:[74]85 R:86 B:[74]82
*NINJA 10 Pt. COMBO & Yaeger*

[Anonymous]

Ohh ya MLCKT good ones! Esp paste special, trim, and text to value. Who knows if they will test this stuff in a standardized formula but all of that is so relevant.

The company I work for has a standardized test for all of the office suite and the excel test is less formula driven and more navigation based. How to sort, insert data, cut/copy/paste data or tabs, format cells (wrap text, currency w 2 decimals and $ sign), etc. If I had to guess I would bet this is how they will test you.

[Anonymous]

I dont want to hijack the thread but I am curious what you all are using macros for in your day to day work. I have never used macros before because I don't know a need for them at this point – I also don't know VBA which is probably my biggest hurdle to effectively using them.

[Anonymous]

Thanks everyone, this is helpful. I'm not as bad off as I thought, but will need to hone up on a few areas.

[Tncincy]

Wow, sounds like an excel refresher course would be good…I am a little rusty in excel. Most of the job descriptions I've read want you to have excel experience but I didn't know that they would do testing. As I think about it, this is a good question.

It begins with a 75
Been here too long as a cheerleader....ready to pass

[MCLKT]

I work in industry.

We have written Macros for just about anything to improve efficiency and decrease the risk of user error.

Pretty much anytime we can upload a file instead of using data entry or posting directly, we will. If it is cyclical, we write a macro.

Our AP department uses them for coding massive invoices, like health insurance.

Macros for the payroll files we receive from outsourced payroll firm.

Hmmm… we have macros to take data from our operations department to automatically create pretty workbooks with individual spreadsheets for each team commissions and revenue. And one to sort all data by separate entities and create files that can be easily uploaded to create receivables and also to upload to outside custodians websites for processing. Lots of macros.

A:[73]97 F:[74]85 R:86 B:[74]82
*NINJA 10 Pt. COMBO & Yaeger*

[Anonymous]

MCLKT I work in industry as well. It sounds like we do a lot of what you all do with macros through Khalix for Excel. We have tons of templates set up that pull in info based on an updated set of month/quarter/year inputs, just run it and have pretty reports ready to go – it is a beautiful thing.

[Anonymous]

An interviewer recently asked me about double-if statements (like an if statement within an if statement). So maybe look into that a little.

[ParanoidAndroid]

Bill Jelen's books and DVDs are great for mastering excel

CPA for life...

[kmac]

I really like the go to/special/visible cells only function (copy/paste function)

pivot tables, vlookups, sum ifs, the short cut keys cntl end up/down

[MCLKT]

I'd like to second the importance of protecting cells and setting security password for access to the file. 🙂

A:[73]97 F:[74]85 R:86 B:[74]82
*NINJA 10 Pt. COMBO & Yaeger*

[Author]

Replies