Most Important Excel Skills for Finance
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.