STOP Writing Multiple Formulas When One Will Do

Most Excel users aren't aware they can use curly braces inside functions like date, XLOOKUP, sort, and more to unlock behavior and advanced tricks. I'm going to show you a handful of examples you can use immediately. As always, you can get the example file from the link in the video description. Curly braces define an array constant. Think of it like a mini list that lives inside your formula. And many Excel functions can accept arrays as arguments, even if most users only ever feed them a single value. In Excel 365, you'll often see the results spill automatically. The curly braces are just the input list. Now, one quick note inside curly braces, semicolons separate rows and commas separate columns. So, semicolons create a vertical list and commas create a horizontal list. Okay, let's look at how we can use this inside a function. The date function usually [snorts] takes a single month number. Here I want the quarter start dates for 2026. So the year is 2026. Then instead of a single month number, I'm going to enter an array of month numbers inside curly braces. So I want the start of each quarter, which is month 1, month 4, month 7, month 10, and close curly brace. And then the day, I want the first of each quarter. Close parenthesis on date. And because I used semicolons, the dates spill out. Similarly, I can use commas to spill the quarter dates across the columns. So again, 2026 for the year. This time we want 1, 4, 7, 10. Close the curly brace on the months for the first day. Close parenthesis. And I get a horizontal array. So just by changing separators, you control the shape of your output, which is perfect for building reporting periods without typing a list of months on the sheet. Let's say I want to filter this list to only return HR and IT employees. I can do that easily with the filter function, but I only want the name and salary columns returned. And I can do that with the choose function. Choose wants to know the index number of the column I want returned. Well, remember I want two columns returned. So, I'll enter them as an array. One, two. Close the curly brace. And then all I need to do is tell it where is column one. Well, that's the name column. And column 2 is the salary. Close choose. So, that's my array, comma, and then I'm going to alt enter down onto the next row where I provide the logical tests for filter to know which rows to return. The first logical test goes inside parenthesis. I want where the department equals HR close parenthesis on that one plus which is for or if the department equals it close parenthesis on my second logical test. I can leave the if empty argument and close filter. Press enter. And now I have just the employee names and salaries for the HR and IT departments. Keep in mind that you could also use this choose trick to rearrange the order of the columns just by simply changing which column you select for column one and 2. So you can give that a try for homework. Here I want to create a mini summary table with the IT and HR department's employee count. So I can use vstack to create the table. The first row of the table will be the headers. I can pass them to vstack in an array. The first header is it followed by HR. Close my curly brace. That's my first array, comma. And then for the count, I'm going to alt enter down onto the next row. And we'll use count if to count where the department equals it and HR. And here I can use the same array as the criteria list. So where they equal it and HR. Close curly brace. Close count if. Close vstack. Press enter. And there we have our table with count if returning both counts in one hit. Most people know XLOOKUP can return multiple columns. But what they don't realize is the if not found argument can also return multiple columns with our curly brace trick. Here I want to look up the employee in cell I8. We can do that with XOOKUP. So we're going to look up Jane. Where are we looking her up? In this list here. and I want to return the department and salary which are in these columns. Now, if it's not found, [clears throat] instead of just returning some generic text for both columns, I can return a specific message for each column by placing them in an array. So, the first column is the department. If it's not found, I'm going to enter the text unknown department. The second column is the salary, and here I'll return the text unknown salary. Close the curly brace on my if not found arguments. Close XLOOKUP. Press enter. Now

obviously, Jane is found, so we don't get an error. But let's look up someone not in the list. And now I get a tailored error for each column. If this video has made you realize there's a whole other level to Excel formulas, that's exactly what I cover in my advanced Excel formulas course. We're also running 20% off all courses until February 26. So, if you want to take this further, I put the link in the video description and pin comment. Here's a powerful one. Sort can do multi-level sorting if you feed it a list of sort keys. For example, let's say I want to sort this data by the item, then the total. In the sort index, I can pass it an array. I want to sort item, which is the second column, followed by total, which is the fifth column. Close the curly brace. And then in the sort order, I want to sort it in descending order. Tab to insert that. Close sort. Press enter. And now I have my data sorted by item followed by the total amount in descending order. Alternatively, let's say you wanted to sort it alphabetically by item followed by the total in descending order. You could also pass an array into the sort order. So we want the item sorted in ascending order and then the total sorted in descending order. Close the curly braces. Press enter. And now we have it alphabetically in ascending order by item followed by the total amount in descending order. Here I have a text string with multiple delimiters. We can see forward slashes after the city, spaces after the postcodes, and parentheses around the percentages and finally commas between each set. We can use text split to specify multiple delimiters and split the text in one go. The text is here. The delimiters we're going to put in curly braces. Each delimiter needs to be in double quotes. So, comma, close double quotes. And then each delimiter is separated by a comma. So, comma open double quotes. Then it's forward slash close double quotes, comma. The next delimiter is opening parenthesis close double quotes. And that's it for the column delimiters. So, I'm going to close the curly brace, comma. Then I want to split it into rows at the closing parenthesis. So there's just one delimiter here. I don't need curly braces. Comma. And then I want to ignore empty. That's true. Close parenthesis on text split. So with curly braces, we can write one formula that takes a messy blob of text and turns it into structured columns. Now you know you can use curly braces to pass multiple values into a function in one go. You'll start noticing how many everyday Excel functions can do far more than you realized. If you want to keep building on this, I've got a video on 15 Excel functions. Every professional should know that's the perfect next step. I'll see you there.