Coalesce Function in Excel

COALESCE functions are commonly used in applications like mathematics, geometry, computer science, and programming. At their most basic, they return the first non-blank values in a given array. Excel doesn’t have an actual COALESCE function, but it’s possible to approximately replicate the performance of the function using one of Excel’s built-in formulas. With this in mind, let’s learn how to make a coalesce function in Microsoft Excel.

How to Make a Coalesce Function in Microsoft Excel

COALESCE as a specific function, as mentioned, doesn’t exist within Microsoft Excel. But it is possible to replicate, and we’ll learn how here. But before we do, it helps to understand what a coalesce function does, and how it’s useful.

A coalesce function is designed to return the first non-blank value in a given array. Arrays are simply the rows and columns that form a range within your data. For example, A1:A5 is an array. So is D25:AZ500.

A coalesce function has a variety of applications, as outlined above. Of course, for small datasets, it’s quite easy to pick out the first non-blank value. You can just visually scan your data and pick out the first cell with a value in it.

But imagine a data array with hundreds or thousands of blanks. Do you want to scroll and scroll until you find a non-blank value? Probably not. This is where a coalesce function does the work for you.

Unfortunately, however, Excel doesn’t have a COALESCE function, per se. Instead, you have to replicate it by building an array formula consisting of three separate functions.

This sounds daunting, but it’s actually quite simple to do. Let’s work through it step by step. To begin, we’ll be working with a list of values (and blank rows) here. They’re contained in the range A2:A7. Notice that the first non-blank value is in cell A4, and it’s 75. With the data entered, it’s time to begin building our formula.

To work properly, the formula needs to be an array formula. This means that there will be an extra step beyond the normal process for inputting formulas in Microsoft Excel. The formula itself, though, doesn’t change, so read on to learn how to complete the array formula.

Begin by clicking into a blank cell. This cell is where you’ll be inputting your array formula to replicate a coalesce function. It is also, therefore, where the first non-blank value in your range will be returned.

Our replica coalesce function is actually a combination of a trio of existing Excel functions: INDEX, MATCH, and ISBLANK. Individually, these work to return specific elements found in your array. When combined, as we’re about to do, they function identically to a standard coalesce function, although they operate with different names.

With your chosen cell selected, begin by typing an = sign. This is standard practice when you’re building a formula. It tells Excel that the contents you’re placing in the cell are a formula, and that the spreadsheet should return a value, rather than displaying the text contents of the formula.

Then, type INDEX(, followed by selecting the range you want to analyze. So far, your formula is:

=INDEX(A2:A7

From here, you’ll input the remaining two functions as follows:

,MATCH(FALSE,ISBLANK(A2:A7),))

Your complete formula is:

=INDEX(A2:A7,MATCH(FALSE,ISBLANK(A2:A7),))

If you’re using an older version of Excel, next press Control + Shift + Enter to make it an array formula. If you have Office 365 as a subscriber, you don’t need to do this and can skip this step.

Hit Enter on your keyboard, and Excel will return the first non-blank value: 75. 

As you can see, it’s easy to make a coalesce function in Excel! In fact, you can simply copy and paste the formula we just built into your spreadsheets. Simply change out the cell ranges in the formula to fit your datasets.