Need to create a project schedule? Try our Gantt Chart Template!

Excel Formulas and Functions

Without formulas, a spreadsheet is just a nice way to organize data. Formulas provide the power and automation that make spreadsheets come alive. At Vertex42, we love using Excel formulas to create awesome templates, so we have devoted this part of the blog to describing our favorites.

Custom LAMBDA Functions in Excel

LAMBDA Library Thumbnail

Introducing the LAMBDA Library – A collection of fully documented new functions for Excel that you can use right now, including array and matrix manipulation, interpolation, eigenvalues and a newton-raphson solver.

Dynamic Arrays in Excel

Dynamic Arrays in Excel

Learn need-to-know information about Dynamic Arrays, the spill behavior, and many of the new functions such as SEQUENCE, VSTACK, FILTER, TOCOL, etc.

Array Formula Examples – Simple to Advanced

Excel Array Formula Examples

In Excel, an array formula allows you to do some powerful calculations that you might not be able to do any other way. Learn how to use both simple and advanced array formulas, including nested IF formulas, sequential number arrays, and matrices.

Text Manipulation Formulas in Excel – The Ultimate Guide

Text Formulas in Excel

This is the ultimate guide to using Text Formulas in Excel such as SUBSTITUTE, LEN, CHAR, CONCAT, TRIM, LEFT, RIGHT, MID, FIND, SEARCH and others. The examples start simple and then build up to more advanced formulas including cleaning up data, splitting text into columns, and converting strings to arrays.

SUMIF and COUNTIF in Excel

SUMIFS and COUNTIFS – Power Functions

SUMIF, SUMIFS, COUNTIF and COUNTIFS are very powerful functions for data analysis. The hardest part is learning how to define the criteria for different types of comparisons. This article shows examples of all the different criteria types, including matching blank and non-blank values.

Volatile Functions – What’s the Big Deal?

Volatile Functions in Excel

A Volatile Function such as RAND, TODAY, OFFSET, or INDIRECT is recalculated every time Excel recalculates. That may be a problem, or it might not be. This article explains why volatile functions may slow performance and also explains why I love them so much.

Dynamic Named Ranges in Excel

Dynamic Named Range Formulas in Excel

A Dynamic Named Range is an awesome way to reference customizable lists. This article explains how to use OFFSET, INDEX, INDIRECT, CHOOSE, and IF to create dynamic ranges. It also explains formulas for finding the position of the last value in a range.

VLOOKUP and INDEX-MATCH Examples in Excel

VLOOKUP and INDEX-MATCH Examples in Excel

Learn how to use both VLOOKUP and INDEX-MATCH and other powerful formulas to do lookups in Excel, from simple to advanced formulas, including multiple criteria and case-sensitive lookups and to get the last value in a range.

Use DATEDIF to Calculate Age in Excel

DATEDIF Formula to Calculate Age in Excel

Learn how to Calculate Age in Excel using DATEDIF and other Excel formulas. Calculate age in years or a combination of years, months and days. Learn an accurate work-around for the “md” option.