Home » Knowledge and Tools » Excel Fundamentals

Excel Fundamentals

Our Excel fundamentals category focuses on the ‘must knows’ of Microsoft Excel. The articles and tutorials you find here will teach you everything from how to use Excel shortcuts to work more efficiently and quicker, how to calculate IRR and NPV, use range names and LOOKUP functions, dynamic chats or custom number formats to how VBA can make your life as a financial modeller easier. Become an expert in your field!

Jun 14, 2013

SMART Scenario Manager Tutorial

By Tim Heng

The Scenario Manager is one of the most powerful tools to be implemented into a model. The intention of the Scenario Manager is to combine numerous sensitivities into a single scenario, and derive key outputs relating to each scenario simultaneously. … read more


Feb 12, 2013

Range names in Excel

By Rickard Warnelid

Range names are a useful tool in Excel that allows you to assign a name to a single cell or a range of cells. This tutorial describes how to create range names including the dynamic range and how to work … read more


Aug 27, 2012

Dynamic charts – the why, the what and the how

By Bing Chien Quek

Why use a dynamic chart? Charts and/or graphs can be very useful in visually displaying information and statistics to tell the users a story. A dynamic chart within your summary makes it more interactive by having several inputs that can … read more


Oct 25, 2011

Benefits of using Excel shortcuts: get your shortcut sheet

By Rickard Warnelid

Corality’s Excel shortcut sheet is one of our free materials we’re proud to share, as part of our passion to spread the best practice modelling message. Download your copy of Corality’s Excel shortcut sheet. Benefits of using Excel shortcuts In … read more


Sep 14, 2011

Custom number formats – formatting decimals

By Rickard Warnelid

Customizing number formats can be very useful in helping you control the appearance of numbers, dates, and text in cells. This is the second tutorial in our series, which focuses on the presentation of numbers in Excel. Digit placeholders and … read more


Sep 07, 2011

Custom number formats – formatting text

By Rickard Warnelid

Customizing number formats can be very useful in helping you control the appearance of numbers, dates, and text in cells. Overview This series of tutorials assumes that you have a basic understanding of what number formats are, and how to … read more


Aug 24, 2011

How to use SUMPRODUCT

By Rickard Warnelid

The SUMPRODUCT formula allows you to write compact formulae. However, it can also result in a lack of transparency, and increases the risk of introducing errors into your spreadsheets. One useful application of the SUMPRODUCT formula is to allow you … read more


Aug 17, 2011

Excel offset function increases modelling risk

By Bing Chien Quek

In this tutorial, we look at the important uses of the OFFSET function, e.g. scenario-based modelling. However, we also examine modelling risk and some alternatives to the OFFSET function. Offset in Excel The Excel OFFSET function returns a reference to … read more


May 18, 2011

Excel 2007 Styles Shortcut Tool

By Rickard Warnelid

The Styles shortcut (Alt + ‘) is a popular shortcut for many Excel users. To their frustration and grief this shortcut was removed from Microsoft Excel 2007 and 2010. Download the 2007 Styles Excel Shortcut Tool We have the cure! … read more


Feb 10, 2011

An iterative approach to calculating the Internal Rate of Return (IRR)

By Bing Chien Quek

One function in Microsoft Excel that calculates the IRR is the IRR function. One of the disadvantages of the IRR function is that it assumes an annual cashflow and most financial model these days are generally monthly or quarterly. This … read more


Oct 27, 2009

Calculate IRR in Excel

By Nick Crawley

The Internal Rate of Return (“IRR”) is a common source of error in a financial model. This document covers how to calculate an IRR in Excel and assumes that the reader is already familiar with the mathematical concept of the … read more


Aug 02, 2009

Tutorial: Formula to calculate NPV in Excel

By Nick Crawley

This tutorial covers the formula for calculation of the Net Present Value (“NPV”) in Excel. It assumes the reader is familiar with the concept of net present values and concentrates on how to calculate an NPV in Excel and the … read more


May 26, 2009

Excel Data Tables in any sheet

By Rickard Warnelid

Excel data tables are amazingly powerful tools in scenario and sensitivity analysis. In this tutorial, we would like to demonstrate how to create an offsheet data table in any location in your workbook. Input cell reference is not valid If … read more


Mar 16, 2009

Data Validation in Excel

By Rickard Warnelid

Data validation is a feature available in Excel that helps you to control the information that is entered in the worksheets. This tutorial describes ways to use the data validation feature and examples to illustrate how to implement it. Data … read more


Mar 09, 2009

Array formulas in Excel

By Rickard Warnelid

In Excel you can perform some powerful and useful operations using array formulas. This Tutorial together with the examples in the workbook introduces basic array and application of more advanced formulas. Introduction to Excel array formulas An array formula is … read more


Feb 26, 2009

Excel data tables

By Nick Crawley

In Excel, you can create data tables to see how different input values affect the result of a formula without having to re-type or copy the formula for each input value. This adds a new dimension to Excel that is … read more


Feb 25, 2009

Goal Seek function in Excel

By Rickard Warnelid

Goal Seek is a very powerful tool in Excel for finding break-even points or to perform tailored what-if analysis. When you know the desired result of a formula but not the input value the formula needs to determine the result, … read more


Jan 16, 2009

Lookup instead of VLOOKUP and HLOOKUP

By Rickard Warnelid

One of the most useful functions available in Excel is the Lookup functions. This allows you to take any value entered, find it in a data range, then return a value or information from that same data range without having … read more


Oct 20, 2008

Top 10 Excel functions to avoid in project finance models

By Nick Crawley

When building project finance models, one of the key Best Practice methods adhered to is that models are transparent. This is done by using simple formulas that are laid out in a logical manner. Simple models will ensure that the … read more


Sep 25, 2008

Excel Keyboard Shortcuts – Basic

By Nick Crawley

Using the keyboard shortcuts in Excel drastically increases efficiency and allows you to remain focussed on the task at hand – if you use Excel regularly it is essential that you utilise this functionality. This topic makes it a little … read more


Sep 25, 2008

Project Finance Modelling top ten mistakes

By Nick Crawley

Financial models in project finance are often plagued with a wide variety of problems. The vast majority of these can be attributed to a lack of simplicity, a failure to clearly segregate different elements of the model, and the absence … read more


Sep 24, 2008

Dynamic Cross-Cursor in Excel – VBA

By Nick Crawley

This article outlines a neat trick in how to code a very professional looking dynamic cursor in any table in excel using a short VBA macro and conditional formatting. The following presentation trick can be achieved in just minutes using … read more


Related Content

Courses
  • Financial modelling training promotion: 20 percent off Corality’s London courses
  • Graduate Training Programme
  • Advanced acquisition structures and merger modelling
  • Essential Project Finance Debt Modelling
Tutorials
  • SMART Scenario Manager Tutorial
  • Improved Financial Modelling Methodology – April Fools 2013
  • Offset Function in Excel
  • ADSCR (Annual DSCR) in financial modelling
Blogs
  • Coraligator’s complete the Fitness First Corporate Triathlon in Sydney
  • Corality to present at EuSpRIG 2014
  • What is a financial modelling ecosystem?
  • CORALITY PRESENTS A SESSION AT THE MAJOR ROAD PROJECTS CONFERENCE IN SYDNEY

 

You must be logged in to view the Tutorial