Macros, and Pivot Tables, and V LookUps – Oh My!
Being the smartest accountant is good, but it is not going to necessarily make you stick out at your job. It is important to have other skills that complement your accounting knowledge and your everyday tasks. It is about going above and beyond. If you want to double your salary you have to be worth it. This is how you get promoted. The more you can contribute to the whole accounting team, the better you will look. Two things that every accounting department is looking for is making tasks more efficient and standardization. When it comes to closing the books at month or yearend things are hectic and there is lots of work to be done. Minimizing the amount of work that needs to be done is something that everyone at all levels of the accounting department can appreciate. Contributing to the efficiency of the department can be a great way to stand out. When everyone has their own way of doing things or uses different templates, it can make things confusing. It can also cause more work when documents need to be converted. Anytime you can standardize a process you have another opportunity to stand out.
One skill that I believe covers both of these is Advanced Excel. Everyone includes Excel as a skill they are knowledgeable about on their resume. However, being able to do basic math and highlight cells is not enough. This is the absolute minimum. To really make use of Excel, you want to be able to do much more than the minimum. You want to become the Excel guru that everyone looks to for help. Some specific things to get good at:
- -Conditional Formatting
- -Drop down lists
- -Pivot Tables
- -V/H Lookups
Macros will help automate repetitive tasks to save time. Learning to create Macros will take dedication and time because you will have to get familiar with VBA (Visual Basic Applications). This is the code that makes up Excel. Some broad examples of things you can use macros for: consolidate multiple worksheets/books into one, formatting all worksheets/books to be the same, sending emails, and many more. A more specific example of a macro for accountants uploading journal entries into an ERP via Excel format, if you receive journal entries from multiple accountants and want to do one big upload instead of multiple uploads you could create a macro to consolidate all the journal entries into one large upload. This eliminates the multiple files you need to save and upload.
Another more specific example is for auditors testing accounts receivable or account receivable clerks needing to send out a notice to several customers. You could create a macro to email multiple customers at one time with the click of a button instead of creating multiple emails one after another. The emails can be standardized in wording, but contain personalized touches.
Functions are essential more advanced formulas than just the typical addition and subtraction. The basics will use IF statements to manipulate data into information that is usable. For example if your manager asks you to get totals per customer you may use a “SUMIF” formula to get the information. This could save the time it takes to manually add up each line or even filter each customer.
Conditional Formatting is one way to help standardize the way a report looks. It can be set up so that numbers and words change based on criteria. For example if you wanted to highlight all the losses on a report it can be set up show losses in red.
Drop down lists allow only a certain range of values to be selected. This helps with standardization and eliminating typos from skewing data. For instance if you were summing up amounts by city and you happened to hundreds of entries for Louisville, but on a couple entries you spelled the city, “Lousiville”, you could be short in your total summary. The drop down list would allow you to only be able to select the correct spelling.
Pivot Tables are one of the best items you can use to manipulate data. You can take a table of information and dissect it to find totals by lots of different criteria. It is the “SUMIF” formula on steroids. Once the Pivot Table is set up correct it is very easy to use and read. If you are dealing with lots of data this is a tool that will certainly help you.
V/H Lookups are used to look up information. It looks at a one set of criteria and returns information based on a match. For example, if you had hundreds of transactions and wanted to instantly look up one piece of a transaction you could use a VLOOKUP to search the data and return the information without having to scroll through the whole data set. This is very helpful and is almost a must for someone with large amounts of data.
Google can be your best friend when it comes to working within Excel. Almost anything you want to do can be found in some Excel Forum. There are tons of free tutorials online. One site I highly recommend is using the forums on Mr Excel.