Excel for Accounting: Getting Ready for Your First Job

excel for accountingYou’ve got an offer, and won’t start until next September. Is there anything you can do to get prepared for the first job? Honing your Excel skills is one idea.

The Expectation

First of all, if you mentioned that you are “pretty good” in Excel at the interview, and want to live up to your team’s standard, make sure you are familiar with the following functions before starting:

  • Pivot tables
  • Vlookups
  • Sumif
  • Filters
  • Conditional Formatting
  • Macros
  • Bonus: Index match

You probably won’t have to create them right off the start, but you should know how they work and how to modify existing ones.

How to Get Prepared

1. Practice with Everyday Examples

It is best to learn Excel with practical examples. For example, do you have a spreadsheet where you track your bank account, maybe with budgetary categories? If so, try to find ways to extract and use data.

Here are some exercise to work on:

  • Set up a formula to calculate expenses in a budget category in the current month (without having to reset the SUM range).
  • Set the budget column to turn red if it exceeds the budget by, say, 5% or more.
  • Create another sheet that pulls data from the bank records and uses pivot tables and charts to show expenses by category and by payee.
  • Create a macro so that when you export data from your bank’s website, the data is format to copy and paste into your bank spreadsheet.
  • If the bank doesn’t have your budget categories, use vlookup or index and match to create formula that looks for the last time this payee was paid and tells you what budget category it was.

2. Learn Excel for Accounting via Google

After playing around with different functions in your personal spreadsheet, you can move on to more challenging work. Try looking for an excel project to do, then figure out how to do it. There are lots of projects on the Internet, such as monthly budget and variance analysis templates.

If you get stuck, there are tons of Youtube tutorials on Excel for accounting. The following video is comprehensive but very long. You can easily google for shorter versions.

3. Learn on the Job — Remember the Prior Year Work Papers!

If you spend a couple of weeks working on Step 1 and 2, you are better than 95% of your class. Keep up with the great work by learning on the job.

Some seniors are helpful and friendly but others may not, so you can’t rely on them 100%. You can instead study the prior year work papers, with all the formula and data for your easy reference.

Another tip: If you feel like something is taking too long, there is definitely a faster way. Ask your bull-pen mate, or do a quick Google search and you will find something golden.

Do You Have Other Tips to Share?

For current accountants and auditors, what advice can you give to first-years? Would love to hear from you.

About the Author Stephanie

I am the author of How to Pass The CPA Exam (published by Wiley), and I also passed all 4 sections of the CPA Exam on my first try. Additionally, I have led webinars, such as for the Institute of Management Accountants, authored featured articles on websites like Going Concern and AccountingWeb, and I'm also the CFO for the charity New Sight. Finally, I have created other accounting certification websites to help mentor non-CPA candidates. I have already mentored thousands of CPA, CMA, CIA, EA, and CFA candidates, and I can help you too!

Leave a Comment: