Excel is an invaluable source of financial data analysis. Every day, an uncountable amount of financial data is analyzed by financial experts. These data helps investors to improve investment portfolios and dig deeper into the financial market.
Below are top 13 Most Important Excel Skills for finance and accounting!
1. 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.
2. Finance Function
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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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’.
9. 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.
10. Conditional Logic
This involves building conditional logic using if conditions, and using logical operators such as AND and OR to build your formulas.
11. Array Formulas
Array formulas can perform multiple calculations on one or more of the items in an array.
12. 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.
13. 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.
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.
