## How to use XLOOKUP in Excel to replace existing LOOKUP functions.

In September 2019, Microsoft introduced a new function to help speed up the way that data lookups are performed. As of February 2020, the XLOOKUP in Excel function is still in testing; however, upon release, it will change the way …

## INDEX / MATCH – How does it work?

Up until the release of the XLOOKUP function, there has been significant debate in the Excel community around which function is better – VLOOKUP, or INDEX / MATCH. But, what is INDEX / MATCH? This is a combination of 2 …

## Quarterly Report – Using SUMIFS()

In this post, along with the next few posts, I will be discussing a hypothetical case where data has been provided and your job (as the analyst) will be to create a quarterly report detailing sales of 2018 new vehicles …

## Creating a Basic Loan Amortization Schedule Without Code

An amortization schedule contains details with respect to a loan, including the following: The principal amount of the loan. The interest on the loan (typically reflected in annual terms). The length of the loan (typically reflected in years; except in …

## Using PMT() to calculate installments

One of the most commonly used financial functions is the payment function – the PMT() function. This formula replicates the mathematical equation below: Instead of using the mathematical equation, the Excel formula uses a much simpler list of arguments. =PMT(interest …

## SUBTOTAL() vs. SUM()

As an accountant, one of the frequent tasks that needs to be accomplished is the summing up of results; this can be anything from summing up ledger balances to a full income statement or balance sheet. We normally use a …