15 Excel Functions Every Professional Should Know in 2026

Excel has released a ton of new functions in the past couple of years, and if you haven't kept up, you're probably working a lot harder than you need to. In this video, I'll walk you through the best modern Excel functions with practical examples that will transform the way you work in 2026. I'll demonstrate one or two examples for each function. But as always, you can download the workbook with all the examples and links to more advanced examples. First up, the group by function. It lets you group and summarize data using a single formula. Think of it as creating a pivot table style summary like this but with formulas that update automatically whenever your data changes. Now this is a simple example but we can also group by multiple columns. So let's take a look at that. Group by the first argument is the column or columns you want to group by. I want to group by country and product and I want to include the headers. My data is in an Excel table. So we get the table structured references. The second argument is the data you want to add up, average or count. For me, that's the sales and again including the headings. The third argument is the function you want to use for the calculation. And you can choose from any of these in the list or write your own custom lambda. I want to sum them. So I'm going to tab to insert sum. Then you can specify if your data includes headers and whether you want to display them. My data includes them and I want to show them. So that's three. You can also control how grand totals are displayed, how your data is sorted, apply filters, and so on. But I can stop there. Close parenthesis. And there's my pivot table created with the group by function. The beauty of group by is that it's completely dynamic. If you change your source data, let's make this drastically different. You can see it feeds straight through to my group by formula. Next, we have the pivot by function, which takes the idea of group by to the next level, incorporating the ability to also shape the summary across columns, just like a pivot table. We'll use the pivot by function to recreate this pivot table that summarizes the sales in this table. You can see it's summarized by segment and country in the row labels and products across the columns. I'll place it over here equals pivot by. Now, I've already applied some conditional formatting in these cells. So, you'll see that apply when I enter the formula. The first argument of pivot by is the field you want to group your rows by. Well, I want multiple fields. I want segment and country. And again, I'm going to include the column headers. The second argument is the field you want to pivot across the columns. I want products and again include the column header. The third argument is the data you want to summarize, which is sales. And the fourth argument is the function you want to apply. And like group by we can choose from all of these or write our own custom lambda. I'm just going to sum them. So tab to insert sum. Again I have headers and I want to show them. So that's three. I can also add subtotals and grand totals and that's two. And like group by. There are further optional arguments for sorting and filtering etc. But this is enough to recreate the pivot table on the left. So I'm going to close parenthesis there. Press enter. And there's my pivot table recreated with the formula. And like I said, I've got some conditional formatting that's automatically applied of bold format to the headers and borders at each subtotal. One of the best features of pivot tables are slices. They're just a really intuitive way to quickly filter your data. And with a clever trick, you can also link group by and pivot by to slices. And I cover that trick in the extended video. So be sure to download the file and check it out. Now, let's look at what I call Excel's modern array toolbox. A set of lightweight, ultra flexible functions that let you reshape, trim, and reorganize your data in seconds. The first function got the Excel fans on Reddit super excited because it simplifies and streamlines dynamic formulas. With the trim range function or its equivalent shorthand, the dot operator, we no longer need to use the volatile offset function to reference dynamic ranges and formulas like this that waste valuable resources calculating empty cells can be avoided. Let me show you here. I'm going to use XLOOKUP and then in the lookup array argument, I'll use trim range to trim any trailing rows in the lookup array. So, we're looking at the products and we're allowing for more entries underneath the current data. And trim range only has one mandatory argument. That's the range you want to trim. So, I'm going to close parenthesis there. Then, I'm going to look up the products here and return the categories here. Close parenthesis. Press enter. And notice XLOOKUP now stops calculating at row 39, ignoring the empty rows. And if I add some new data, which I've got over here, let me copy it and paste it in. You can see it automatically expands to include it. So with trim range, I've got a dynamic formula with no redundant calculations. Now with this formula, we've only used one argument in trim range, and that's the range argument, which means I can actually simplify it

using the new trim referator. And I'll show you how to use that here. Again, we'll xook up the products allowing for growth. And after the colon, all I need to do is add a dot to tell Excel to trim any trailing empty cells. Our lookup array is again the products and the return array are the categories. Close parenthesis. And there I have the same result in a simpler formula. By the way, if you want to trim leading blank rows as well, you can add a dot before the colon. I don't have any, so I didn't need it in this example. Now, you might say, if I put my data in a table, I don't need trim range. And that's true. But dynamic array formulas that spill results like this one can't be used in tables. So, for example, if I convert this data here to a table, we get a spill error. And that's where trim range and the dot operator are super helpful. Of course, this is a small example, but imagine this over thousands of rows. Next, talle, which reshapes any range into a single column. For example, I can use two colon and take this list of suburbs split across multiple columns. Notice it's got some blank cells. So in the next argument, I can choose one to ignore them. And I want each column stacked on top of one another. So I'm going to choose true here to scan by column. And that's it. Now I have a column containing my suburb list with no blanks. It's too easy. Similarly, two row does the same thing but horizontally. So I'll select my data. I want to ignore blanks and I'm going to again scan by column and press enter. And now we get the data from the first column followed by second column. Two col and two row are perfect for flattening tables, building validation lists or preparing data for other functions. Next, choose col which returns only the columns you specify. For example, here I can choose coals. I want all the data here including the postcodes this time. Then I just tell it which columns I want to return. I want to return the last column. So that could be five or I can also use minus one. And then I want the first column as well in that order. Press enter. And you can see I've got the last column followed by the first column. On the other hand, choose rows returns only the rows you want. So let's select the data here. I want data from the third row followed by data from the second row. And there we have the data rearranged in the order I specified. Super easy. Choose calls and choose rows makes it easy to reorder data or extract just the parts of an array or range you need. If you're watching this and thinking, I wish I could write these formulas confidently and understand how they fit together, then my advanced Excel formulas course is the perfect next step. It takes you from writing long messy formulas to building clean flexible solutions using dynamic arrays. Let lambda and the modern functions analysts rely on today. The links in the description and pin comment if you want to join. Next, take which keeps the first or last set of rows or columns. For example, here I can use take to reference the data including the postcodes. But I just want the postcodes from that data. So that's the first row. Press enter and it returns one row of data from the top of the array. Or maybe I just want the last row. Selecting the data again. I could enter three here or I can use minus one for the last row. But I also only want the first three columns. So now I have the first three columns of the last row. Similarly, [snorts] I can use dropped to remove rows or columns from the beginning or end of an array. So I'll reference these postcodes and suburbs here. The next argument is the rows that I want to drop. I don't need the postcodes. So let's enter one to drop the first row. And now I just get the suburbs. Or I can drop the last row. Let's select the data again. I could type three here or minus one. And let's say I also want to drop the first two columns. That's two. So now I have the first two rows of the last three columns. Together they give you precise control over your output. Great for excluding headers or footers from your data that you want to work with in other functions. Wrap rows reshapes a single list into multiple rows. Here I can use wrap rows to reference this list of postcodes and wrap them into two columns. Now because I have an odd number of postcodes, I can display empty cells with a blank. So that's what the pad with arguments for. And you can see that last empty cell is displaying a blank. On the other hand with wrap coals again I'll reference the list of postcodes here. I can use it to wrap the columns down three rows and again empty cells display as a blank. Notice they both return the same number of rows and columns but the order of the results is different. Wrap rows goes rowby row

whereas wrap col goes column by column. The expand function increases the size of a range to a fixed number of rows or columns. For example, we can use expand to take this list of suburbs and rearrange it over four rows with three columns with any blank cells padded with TBA. It ensures your spilled arrays always keep a consistent shape, which is helpful when passing arrays to other functions. Next, we have the scan function, which has a complicated technical definition. But don't let that put you off because it's actually super easy to use for calculating running totals in a single formula that automatically expand. For example, I've got a list of sales here that I need a running total for. I'm going to use scan and the initial value or my starting point is zero. The array are my sales. Now, I want to allow for more sales to be added. And this is where the trim referator is super handy. I can add a dot in there to trim these trailing empty rows. And then the function here, I can write a custom lambda or I can specify the function that I want, which is simply a sum. Close parenthesis. And the formula spills a running total. And I can add new data. Let's just copy this row down. And it automatically expands because I use the trim referator after the colon. Now, because scan returns every intermediate result in the sequence, it's perfect for analysis, trend tracking, and building helper arrays for more advanced formulas. Next, we can use the image function to insert pictures directly into cells using a formula, enabling you to make your spreadsheets more visual without manually inserting, resizing, or positioning anything. First argument for image is the source, and it's a URL that you wrap in double quotes. The next argument allows you to add alt text or alternative text which helps with accessibility and clarity. The third argument controls the sizing behavior, whether the image fits inside the cell, fills the cell, maintains its original size, or is customized. I want it a custom size, which means I can enter the height and width arguments here. So, I want it 42 pixels x 56. Close parenthesis, and it returns the United States flag. And because it's inside a cell, I can use the alignment tools just like you would with text. Image is handy for adding product thumbnails, status icons, profile photos, or any kind of visual cue directly inside cells. And finally, we have the Copilot function. The newest and most transformative addition to Excel. Copilot brings AI directly into your formulas, allowing Excel to analyze your data, generate insights, and return results that go far beyond traditional calculations. For example, here I have a list of addresses with the details all jumbled up. I can use copilot to extract the zip code with a simple prompt. The prompt goes inside double quotes and then the context is the list containing my postcodes. Close parenthesis. Press enter. And just like that, it's returned the postcodes. Normally, this type of text extraction would be a nightmare because there's no consistent pattern, but it's super easy for Copilot. What if we want to rearrange the addresses so they're correctly formatted? Let's again use co-pilot. This time the prompt is rearrange the addresses so they're correctly formatted and again show it where the addresses are. Press enter. Thinks about it for a moment. And just like that, the layout is fixed. The copilot function is ideal for generative and exploratory tasks like classifying transactions, explaining variances, writing summaries, and generating examples. It is not designed for performing calculations. Though the things AI can do in Excel now are genuinely incredible and the copilot functions only the beginning. Excel's new co-pilot agent mode is the next level of automation. And in this video I show you how it can plan a workflow, read your workbook, build multiple sheets, write formulas, create a dashboard, and document every decision it makes, all from a single prompt. If you're excited about the future of Excel and you want to learn how to use AI and your own expertise together, this video is the perfect next step. I'll see you there.