Modelers across the globe are beginning to recognise financial modeling as a profession.

Despite strong support for professional standards many modeling teams do not implement them rigorously and a suprising number of teams do not have consistent quality control systems in place.

Key conclusions from "2019 Financial Modeling Profession Survey":

• 84% of modelers agree that financial modeling is a dynamic and innovative profession.
• Errors in models remain common. 47% of respondents reported errors that were not picked up by internal review processes.
• Most financial modelers are self-trained and rarely attend formal financial modeling training courses.

Introducing XLOOKUP, a replacement for VLOOKUP and HLOOKUP.

The main benefits of XLOOKUP:

• Can find the last match!
• Can look to the left!
• Defaults to an exact match (unlike VLOOKUP which defaults to True for the 4th argument).
• Defaults to not support wildcards, but you can explicitly allow wildcards if you want them.
• Has all the speed improvements released to VLOOKUP in 2018.
• No longer relies on Column Number, so it won't break if someone inserts a column in the middle of the lookup table.
• Performance improvement because you are only specifying two columns instead of the whole lookup table.
• XLOOKUP returns a range instead of VLOOKUP returning a value.

One of the cardinal rules of Excel is don't key-in a value into a formula if that value could change.

Tracking the value down could be problematic if you need to change the value. Tables can be the solution to avoiding keyed-in values.

Often when building even a simple financial model, unit conversion is necessary.

For example, you sell 437,911 widgets at £12.50 each. However, in your output summary sheet for senior management, do you really wish to show that the revenue earned is £5,473,888 or is it more likely that you will display this figure as £5.5m?

Most modellers will modify their number with a calculation such as =437911*12.50/1000000. But using this approach poses the danger of making a mistake in your additions by adding £ and £millions.

By using a custom number format, rather than modifying values, you will never again add or subtract numbers of different orders of magnitude.

This article shows over 20 different ways that you can use Conditional Formatting, starting with some basics and presenting some advanced techniques as well.

Many of the examples are templates that you can download and experiment with.

This short article shows you how to average the top n values in a set of data.

The technique uses the LARGE function to select the required values, which are then evaluated by the AVERAGE function.

In this tutorial, you will find a detailed description of all the trendline options available in Excel and when to use them.

You will also learn how to display a trendline equation in a chart and find the slope of a trendline.

Topics:

• Excel trendline types.
• Excel trendline equations and formulas.
• Excel trendline equation is wrong - reasons and fixes.

In more advanced Excel formulas, you might run into the double negative operation (--).

The double negative (sometimes called the even more nerdy "double unary") coerces TRUE or FALSE values to their numeric equivalents, 1 and 0. It's used in formulas where numbers are needed for a particular math operation.

A double negative is not the only way to get ones and zeros from logicals. You can also add or subtract zero, multiply by one, or use the inscrutably named N function.