Excel is the one universal tool used by finance professionals worldwide – from interns to C-suite, Excel skills are a must.
Online Course: Excel for Finance
This post runs through five Excel features commonly used in finance:
- Pivot Tables
- Conditional Formatting
- Conditional Statements
- Data Validation
- The CHOOSE Function
Many financial professionals spend a long time putting together tables that summarize and aggregate data.
Typically, these tables draw information from different columns and rows within the spreadsheet.
However, as the underlying information changes on a daily, weekly, or monthly basis many hours can be spent updating the spreadsheet.
This video explains how to set up a pivot table:
Conditional formatting is a helpful tool in Excel that can help make spreadsheets more user-friendly.
Data can be color-coded to highlight or separate parts of it, making it easier to identify important information.
Spreadsheets can be made responsive to changes in underlying data, minimizing the need for human intervention.
This video explains how to set up conditional formatting in Excel:
Conditional statements are effective tools widely used in financial models to facilitate flexibility.
Often in finance, the spreadsheet that you are creating shows outputs that are dependent on inputs that are likely to change.
There are several variations, but the basic building block conditional statements are:
1. IF statements
2. OR statements
3. AND statements
The video below walks you through the use of IF statements in Excel:
Excel data validation allows you to control the type of data that is entered into your worksheet.
For example, it allows you to limit data entries to a dropdown list selection and to restrict specific data entries, such as dates or integers, to a predetermined range.
This video shows how a drop-down list can be constructed and how data validation can be added:
Financial professionals who use Excel often find themselves in situations where they need to collate information from different sources into one overall summary.
Performing this exercise manually may take a great deal of time. Consequently, it’s better to use Excel functions to automate the process.
Given a specified number of cells, the CHOOSE function returns the value of nth entry where "n" corresponds to some predefined reference number.
So, if we have specified a list of 10 cells (adjacent or nonadjacent) and we have defined our reference number to be 7, then Excel returns the value of the seventh cell listed in our list of 10 cells.
This video shows how you can use the CHOOSE function as part of a valuation model:
If you would like to build your Excel skills for finance using an online, on-demand, accredited course,
click here.
