Dec 2 / Ruairi O'Donnellan

Using Excel for Finance: 5 Essential Functions (with Videos)

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
New call-to-action

Pivot Tables

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:
Write your awesome label here.

Conditional Formatting

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:
Write your awesome label here.

Conditional Statements (IF Statements)

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:
Write your awesome label here.

Data Validation

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:
Write your awesome label here.

CHOOSE Function

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:
Write your awesome label here.
If you would like to build your Excel skills for finance using an online, on-demand, accredited course, click here.
New call-to-action