Custom Excel Functions With Examples
In this article I am going to cover important Custom Excel Functions you need to know. You will not regret after reading the entire article which is full of recent and most useful Excel functions available in Excel 360. Welcome.
So these are four new functions that were introduced recently in excel 365 also for excel for the web. If you have excel 365, you need to know these functions. And once you go through these, let me know which one is your favorite Excel function.
Here is the nice list of New Excel 365 functions.
#1. Sort (Sort-by) Excel function
With sort she gets dynamically sort your dataset based on a column. So let’s say I have name and treaties and yearly salary and I wouldn’t get everything sorted by yearly salary. While they have to do is type in the sort function my every is what I want to get back.
That this is officially and Custom Excel Functions table so let’s see i went to get everything back name and the date and yearly salary some going to select the full table. Next is my source index this is the column number we want to sort despite. If i completely ignore everything because these are optional arguments they’re all in square brackets.
If I close the bracket and press enter I sort everything automatically by the name here. And to sort order is ascending. That’s the default, the notice the formatting didn’t come with some gonna grab the formatting from here and apply to my range and if you’re planning to expand your range, you can drag the formatting down to a few cells below.
Then if I wanted to sort this by salary in descending order. I can just update my formula. But is my source index gonna become number three the source order remember default was sending. I wouldn’t go with descending, the last argument is whether he should source by column or by row.
Where our data is in rows so we’re gonna need falls and false is the default so I don’t need to mention anything. I just close the bracket here press enter and a half data sorted by salary In descending order.
All of this is dynamic if I add a new person here and let’s see this person earns 200,000 and everything updates automatically know in case you ever want a source a column by a column.
That’s not in your result you can use the sort by function. So let’s see you wanna get a source hit list of names based on salary but you don’t want to show entry date or salary in your and result. Here every is gonna be the column that you want to get back.
That’s just gonna be or name know by what column do we want to sort by yearly salary. How do we want to sort yearly salary by wanted in descending order? Close the bracket press enter you will name’s back without the other information number to the know.
#2 Unique excel function.
With the unique function. You dynamically get a distinct list of values so for example let’s say I wouldn’t get a distinct list for region here only have to do is typing “=unique (range)” that we can see its own here suppress tapped accepted and select our array.
The array in this case is a column because here I’m dealing with an excel tables its enough if a just select the column here and they get my structured referencing know if i close the bracket and press enter i already get a distinct list of values. You can do this for one or more column.
So let’s say you want it to grab the unique combination for division and region, this time your arrays gonna become division and region together, With Custom Excel Functions available here nothing is impossible.
Close bracket and press enter and we get a unique combination of the two, In the unique function you also have additional arguments that might come in handy. So for example let’s say you want it to grab the apps that only occur once in this dataset, that’s where the third argument exactly once comes into play.
My list here there is an argument in between and it’s called by column. This is whether college organizes my data or by rows. It wouldn’t return unique rows exactly once if it’s true he returns items that appear exactly once. The ways it returns every distinct eight him this is the default this is what we get here said this case I’m gonna go with true because let’s say I want to get the apps that only occur once.
And that’s my list. Just to compare let’s get the unique list of apps close bracket and press enter some of these apps occur more than once. But this gives me a distinct list of values whereas this one gives me the items that occurred exactly once.
Fun Fact
Come and read this interesting story of facts about Instagram. Finally Instagram Launches Its Best Creator Marketplace: An Invitation to all creators This will Increase the engagement of most viewers, welcome and keep reading.
#3 X Lock-Up Custom Excel Functions.
X look up is the new and improved version of V-look up it’s more flexible and it’s super easy to use so let’s see i want to select an app from here and i wouldn’t get the division from this list and the profit from this list. I can easily do that with x lookup.
My look up value is this one right here the look up array is the range were this values sitting in say sitting in this column here. Next is what do i want returned i want division returned so notice division is to the left-hand side of the apps column. That doesn’t matter.
X look up all they have to do is close bracket and press enter and I get game for hackrr. In case hackrr is missing. It will bring an error and this is where you can make use of the next argument in X-look up if it’s not found what do you wanna see.
In the table where X-lookup is looking and the result will be missing instead of error. And it automatically updated to my text year so you don’t have to put it inside the if error or if in a function just gonna put hacker back here to see the correct division let’s not get profit or go with x look up may look up value is here.
For the lookup array is wherever i have my app which was sitting right here and the return array is what a want returned and it only select this so notice with x look up what you want returned is the only call him you need to select.
You don’t have to select the entire range this makes it much easier to write the look up function normally. What you have to do is to close bracket and press enter and I get the profit for hackrr. All of this is of course they switch across i get this information updated automatically number for the filter.
#4 Filter excel function.
With the filter function you get to filter. The results based on any condition you want. Let’s see here and when a filter my results based on who earns over 90,000. Start off with filter first think I need is the array this is the range I want to get back so let’s
Then i want both name and yearly salary back then is include argument why when he includes every row where yearly salary is greater than ninety thousand they can’t just reference ninety thousand alone and need to compare a range with this number. That range is my yearly salary range.
I’ll send my list automatically updates now you might be wondering. Can we also sort this yes we can put the filter function inside the sort function?
Let’s see we went to sort by salary, that’s index number two and the sort order. Minus one for dissenting cause bracket and press enter and filtered results.
If anything changes in my list years off for simple James. The earning seventy thousand he’s going to pop up in my list.
#5 The sequence Custom Excel Functions.
Sequence is an interesting function it gives you number in the order that you want. So let’s say i want numbers ranging from 1 to 10 in each cell. All i have to do for rows is typing 10 close bracket press enter and i get 1 to 10 written here know if you look the other arguments we have.
Then the next procedure will be as follows.
Let’s see if I put a to here as may start and as may step one put it 3 close bracket. Press enter i get my start number from to it’s increasing by three it spreading to two columns. And ten rows know-how can you practically use this function in your reports.
That is all for today. I hope you have to get great functions that you needed. For more questions just comment on or keep on reading my Blog.
I hope this article Custom Excel Functions has helped you a lot.
