Unlock Hidden Formula Power with Curly Braces

Hi ,

Most Excel users are not aware that curly braces can be used inside functions like DATE, XLOOKUP, and SORT to unlock behaviour that is otherwise hidden. These are not tricks or edge cases. They are built into how Excel evaluates formulas.

Just take the DATE function which most think returns a single date; you can see below I've used it to return the quarter start dates for 2026, and I'll cover how and why it works in more detail shortly:

In this article, you will learn how curly braces work, why they matter, and how to use them in practical formulas you can apply immediately.

What Are Curly Braces in Excel?

Curly braces define what Excel calls an array constant. You can think of an array constant as a small list of values that lives directly inside a formula.

For example:

This is a horizontal array of three values:

In Excel 365, formulas that return arrays will usually spill automatically into neighbouring cells. The curly braces are simply defining the input values.

There is one important rule to remember when working with array constants.

- Semicolons separate rows.
- Commas separate columns.

This means:

creates a vertical list:

While:

creates a horizontal list:

With that foundation in place, let's look at how curly braces work inside real Excel functions.

Using Curly Braces With the DATE Function

The DATE function normally takes a single year, month and day

Most users expect it to return a single date.

However, if you pass an array of months using curly braces, DATE will return multiple dates.

For example, to generate quarter start dates for 2026:

Because semicolons are used, the results spill vertically. This instantly produces a list of quarterly start dates:

If you want the same dates to spill horizontally instead, use commas:

By changing nothing but the separators, you control the shape of the output:

This technique is perfect for building reporting periods or schedules without typing month lists onto the worksheet.

Filtering Specific Columns Using FILTER and CHOOSE

Suppose you have a table containing employee names, departments and salaries, and you want to return only HR and IT employees. That part is easy with the FILTER function.

But what if you also want to control which columns are returned, or even rearrange them, without creating helper columns?

This is where the CHOOSE function and curly braces come in.

Here is the full formula:

And here's the result:

Let's break this down.

The CHOOSE function is used to build a virtual table. By passing {1,2} as the index argument, you are telling Excel that you want two columns returned.

Next, you specify which columns those are. In this case, column C contains names and column E contains salaries. The department column in D is skipped entirely.

Finally, FILTER is used to return only rows where the department is HR or IT. The plus sign acts as OR logic.

This pattern is extremely powerful. You can use CHOOSE not only to select columns, but also to reorder them before they are filtered.

Creating a Mini Summary Table With VSTACK

Curly braces are also useful for building small summary tables without PivotTables or helper ranges.

For example, we can use VSTACK with COUNTIF to count how many employees are in IT and HR and display the result as a table:

And here is the result:

The first array constant: {"IT","HR"}, defines the labels. The COUNTIF function then accepts the same array constant as its criteria and returns both counts in one calculation.

VSTACK combines the labels and counts into a clean two-row table.

This is a simple but very practical reporting technique.

Using Curly Braces in XLOOKUP for Smarter Fallback Results

Most users know that the XLOOKUP function can return multiple columns. What many people do not realise is that the if_not_found argument can also return multiple values when you use curly braces.

Here is an example:

This formula looks up an employee name in cell I8. If the name is found, it returns the department and salary.

If the name is not found, instead of returning a generic error or message, it returns a tailored fallback message for each column.

This results in much cleaner, more professional output, especially in reports or dashboards.

Multi-Level Sorting With SORT and Curly Braces

The SORT function can perform multi-level sorting if you pass a list of sort keys using curly braces.

For example:

Here's the result:

Here, {2,5} tells Excel to sort first by the second column and then by the fifth column. The -1 specifies descending order.

This allows you to perform complex sorts without SORTBY or helper columns, simply by passing an array of sort keys.

Cleaning Messy Text With TEXTSPLIT and Multiple Delimiters

The TEXTSPLIT function becomes far more powerful when combined with curly braces.

If you have a text string that contains multiple delimiters such as commas, slashes and parentheses, you can split it in one go.

For example:

Here it is applied to a practical example:

In this formula:

With a single formula, a messy block of text is transformed into structured columns.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub