Excel is a life skill for any finance or accounting professional. I know many people in finance for whom MS Excel is at the core of everything they do. Probably if it was not for Excel, their job wouldn’t exist. In this article, I will highlight some of the most important excel skills you must know to succeed as a finance professional to do your job well. Even though how you use excel will largely vary based on your job requirements, these are the core skills required to become a power excel user.
VLOOKUP: This is my no. 1 most commonly used function in excel. You use this while looking up reference data in more than one fields. For example, looking up the current price of a stock in a portfolio spreadsheet by its symbol. Also learn the related function HLOOKUP.
INDEX and MATCH: These are also reference functions similar to VLOOKUP but more powerful and allow you to look up values in a table based off of other rows and columns. Unlike VLOOKUP, INDEX can be used on rows, columns, or both at the same time. INDEX and MATCH are used together to perform some really advanced lookups.
Conditional Logic: This involves building conditional logic using if conditions, and using logical operators such as AND and OR to build your formulas.
Finance Functions: You should learn to use inbuilt finance functions such as NPV, IRR, PV and other time value functions. An understanding of statistical and other excel functions is also helpful.
Array Formulas: Array formulas can perform multiple calculations on one or more of the items in an array.
Data Formatting: Learn all the tricks to format data, such as formatting numbers as currency, percentages, conditional formatting, date formats, and custom number formats. For example, a number 200000000 can be formatted as ‘$200 million’.
Keyboard Shortcuts: As a pro excel user, you should be able to work with Excel using keyboard shortcuts. Almost everything in excel can be done using the shortcuts.
Charts: You should learn to make effective charts with multiple types such as a combination of line and bar chart, stacked bar charts, and so on. While it is easy to feed data to charts, to make them presentable and beautiful can require some skill and you will need to learn all formatting techniques for that.
Building Financial Models: Building a financial model involves breaking up your spreadsheets into input sheets, processing sheets and output sheets. Typically, you will first have all your inputs, and from thereon everything will be driven by formulas.
Solver and Goal Seek: These are a useful set of what-if analysis tools. For example, you can use them to reverse calculate variables such Implied volatility in Option pricing.
Pivot Tables: These are super powerful and you should spend considerable time in learning to use them. Pivot tables can allow you to organize, sort, and filter data in a very flexible style.
VBA and Macros: You should also acquire a basic understanding of Macros and VBA programming in excel. Macros can be helpful to automate frequently repeated tasks.
Importing Data: You should be able to import data into excel from a CSV file or a tab delimited file and know how to import specific columns and segments of data.
You should also know how to use other little things such as relative vs absolute referencing, named ranges, etc.
Apart from learning to write formulas and calculating things in excel, the most important thing is to be able to keep your spreadsheet organized and present the results in a logical easy to understand manner. The excel sheets that you build may be for your own use or to be presented to others. In either case, your excel sheet should be presented in a clean and logical manner.