- 4 weeks ago
5 awesome Excel functions you’ve probably never heard of can make formulas faster, cleaner, and far easier to manage. This tutorial walks through uncommon Excel functions that are especially useful when you need to calculate loan payments, simplify complex formulas, organize nested logic, or work with dates and links more efficiently.
You’ll see how PMT can calculate loan payments based on interest rate, term, and loan amount, plus how to adjust it for monthly payments. The lesson also shows Formula Text for turning a formula into a readable string, LET for naming intermediate calculations and building more organized formulas, HYPERLINK for creating cleaner clickable links, and EOMONTH for finding the end of a month with forward or backward offsets.
If you’re learning Excel for data analysis, finance, reporting, or everyday spreadsheet work, these functions are a great way to expand your toolkit. They’re especially helpful when debugging long formulas, building reusable logic, or handling subscription dates and loan schedules.
Created for viewers searching for Excel functions tutorial, uncommon Excel formulas, PMT function, LET function, Formula Text, HYPERLINK in Excel, and EOMONTH examples. Great for Excel for beginners, intermediate spreadsheet users, financial modeling, and formula troubleshooting.
You’ll see how PMT can calculate loan payments based on interest rate, term, and loan amount, plus how to adjust it for monthly payments. The lesson also shows Formula Text for turning a formula into a readable string, LET for naming intermediate calculations and building more organized formulas, HYPERLINK for creating cleaner clickable links, and EOMONTH for finding the end of a month with forward or backward offsets.
If you’re learning Excel for data analysis, finance, reporting, or everyday spreadsheet work, these functions are a great way to expand your toolkit. They’re especially helpful when debugging long formulas, building reusable logic, or handling subscription dates and loan schedules.
Created for viewers searching for Excel functions tutorial, uncommon Excel formulas, PMT function, LET function, Formula Text, HYPERLINK in Excel, and EOMONTH examples. Great for Excel for beginners, intermediate spreadsheet users, financial modeling, and formula troubleshooting.
Category
📚
LearningTranscript
00:00What's going on, everybody?
00:01Welcome back to another video.
00:02Today, we're gonna be taking a look
00:03at some Excel functions
00:04that you most likely have never heard of.
00:12Now, there are so many functions in Excel,
00:14I don't expect almost anybody to know all of them.
00:16That would be pretty unrealistic,
00:17but there are some in there
00:19that I recently have learned about
00:21or I've been using that I know most people don't know about.
00:24And so I wanna share those with you
00:25because I think some of these are really, really interesting.
00:28So let's not waste any time.
00:29Let's jump right onto my screen and take a look.
00:31Let's jump right into it with a function
00:34that I recently learned about
00:35when I was working with one of my consulting clients.
00:37They showed me this function and I was like,
00:39I've never seen that function before,
00:41but it makes sense because it's specifically geared
00:43toward the finance sector, which I have never been in.
00:46I've mostly been in healthcare and IT.
00:48And so this PMT function basically calculates
00:52the payment for a loan based on a constant payment
00:55and constant interest rate, which is what we have right here.
00:58Here we have some people who have taken out loans.
01:00They have an interest rate and then they have their term,
01:04how long, how many years they have to kind of pay that off.
01:08So we're gonna come in here, we're gonna say PMT.
01:11This is gonna calculate the payment for a loan
01:13based off of the things I just mentioned,
01:15constant payments and constant interest rate.
01:17We're gonna open up our parentheses.
01:19The rate is a 5% interest rate.
01:23The NPER is the total number of payment periods.
01:26And that's gonna be this number right here, the five.
01:28And then for the very last one, we have the PV.
01:32That's the present value or the total loan amount.
01:35And so that's gonna be this one right over here.
01:38Now, if we just close this and we run it,
01:40it's going to work,
01:42but this is not gonna be the monthly payment.
01:44This is going to be the yearly payment.
01:47If we want the monthly payment,
01:48as it says right here in this part,
01:51we just need to divide the C2 by 12.
01:54And then we need to multiply the D2,
01:57that's this number right here, by 12 as well.
02:00So we're gonna say times 12.
02:02And then we'll run this.
02:03So this is gonna be this person's, for this loan ID,
02:07this is gonna be this person's $471 amount.
02:10And we can drag this down and you can see,
02:12these are the monthly payments that they'll need to make.
02:16Now, right now you can see it's in red.
02:18That's because it's a negative number.
02:20That's how much they're spending.
02:21But if you wanna make it positive,
02:22you can come in here to this B2 and say it's minus B2.
02:26And it turns it into a positive number.
02:28So this is how much in a positive number,
02:31like if we did absolute,
02:33that this person is going to pay monthly.
02:35And I just found this one fascinating.
02:36I had never used this one before.
02:38And so I thought you guys might enjoy this as well.
02:41Pretty interesting, mainly used for, like I said, loans,
02:44where you know the interest rate and the term in years.
02:48It could also be the term in months,
02:49and then you just wouldn't need to do
02:51some of the alterations that we had made right in here.
02:53So a really, really interesting one.
02:56Let's go to the next one.
02:57And this one I found really useful
03:00when I'm debugging really long, complex functions.
03:05Formula text basically takes your really complicated text.
03:08And if we click in here,
03:09this is a pretty complicated text.
03:11We're doing, we're using by row.
03:13We're using a Lambda function.
03:16We're using XLOOKUP to then come down here and multiply it.
03:19And we have all these different anchored cell references.
03:22It's pretty tough.
03:23And so if I wanna come in here and I wanna read all this,
03:25it can be a little bit challenging.
03:27So what we can do is we can come over here,
03:28and we can say a formula text.
03:31It returns a formula as a string,
03:34which can be really helpful
03:35because sometimes when you start messing around
03:39with all this, you accidentally change something
03:41or you get rid of something and whoops,
03:44then you just, you know, you destroy the whole thing.
03:46And so this can be really helpful
03:47because now we can see this entire thing in just a string.
03:51And we can come in here and we can look around.
03:53We can even, if we wanted to paste it as a value,
03:57so then we can come in here and actually change it up.
03:59We can say, okay, maybe I don't need to take this.
04:02I get rid of this and I can make changes to it
04:04and I won't necessarily be breaking
04:06or messing with the actual formula.
04:09And so I think this one is really, really useful.
04:11One that I don't think a lot of people
04:13have seen or used before.
04:16Let's go on to our next function.
04:18That's gonna be the let function.
04:21Now the let function is actually not used
04:23for something as simple as we're about to do typically,
04:25but it can be used to kind of organize
04:28and make it a little bit easier to read
04:30when you're doing complex formulas.
04:32So as you get more advanced in Excel
04:34and you're using a lot more complex functions,
04:36you can use this let function
04:37and it'll allow you to kind of join together
04:40multiple calculations and kind of an easier way to read.
04:43And so let's go ahead and try it right now.
04:46We're gonna say it's equal to,
04:47then we're gonna say let.
04:49And let's just read this real quick.
04:51It says assigns calculation results to names,
04:54useful for storing intermediate calculations and values
04:57by defining names inside a formula.
05:00These names only apply within the scope of the let function.
05:04So if we assign certain names,
05:05it's not gonna affect other things outside of this function,
05:08it's just going to occur within it.
05:10Now, when we put our parentheses here,
05:11you can see we have name one, name value one,
05:14calculation or name two, name value two.
05:16And this can be a little bit confusing.
05:18So let's just start writing it out.
05:20And then we'll see exactly what this is.
05:22Now, what we wanna do is we wanna say, okay,
05:25if they have sales that are less than 6,000,
05:29we're gonna give them a 10% bonus.
05:32But if they have sales that are between 6,000 and 8,000,
05:35we wanna give them a 15% bonus.
05:37And if it's higher than $8,000,
05:40we'd give them like a 20% bonus.
05:41And then after we do that, we wanna take this number
05:45and multiply it times that bonus rate.
05:48And so there's multiple steps
05:50that we're gonna be doing in here.
05:51And the let function allows us to kind of programmatically
05:53select what we want in a very logical, formulated way.
05:57And it feels nice to write, I'll just say that right away.
06:00So let's say we wanna do, we'll take these sales.
06:03And that's gonna be this right here.
06:05So we're gonna just name that.
06:08Now, if I say Alt, Enter,
06:11it's going to kind of organize this for me.
06:14So I'm gonna say Alt, Enter.
06:15And typically you'd even come in here
06:17and you do like some spaces or something.
06:19You don't have to do this, but it looks nice, right?
06:22If you do it right.
06:23So we have our sales C2 here.
06:25We have that specified.
06:26The next thing we have to do is we're going to define
06:30and then create our calculation.
06:31So we're gonna define our name for this.
06:34So this is gonna be called our bonus rate.
06:37I need to spell that right.
06:39But now we're gonna do a comma.
06:41And next we're gonna have our name value two,
06:44calculation or name three.
06:46This is where we're gonna add our calculation.
06:49So we're gonna say if, and we'll do sales.
06:54Now, this isn't this sales.
06:55This is this sales right here,
06:57because we're naming it and then we're using it.
06:59So let's actually change this
07:00so that it's easy to note the difference.
07:03We're gonna say a current sales.
07:06Do current sales, just like that.
07:08So now we're going to take this.
07:10I'm gonna paste that right here.
07:11So we have our current sales.
07:12If the current sales are less than, let's say, 6,000,
07:17then if that's true, what do we wanna do?
07:20We wanna give them a 0.1.
07:23That's a 10% bonus.
07:24So we wanna give them a 10% bonus.
07:26If it's false, we're gonna do a nested if function.
07:29So we're gonna check for another condition
07:31and see if it's met.
07:32So this time we're gonna say,
07:34if the current underscore sales is,
07:38let's do less than or equal to 7,500.
07:43If it is 7,500 or below,
07:46which is gonna be these two right here,
07:48then we're gonna give them a 15% bonus.
07:50They made more sales, they deserve more bonus.
07:52So we're gonna do a comma,
07:53and we're gonna say, if that is true, then 0.15.
07:57And if it's false, now we'll actually do a false one.
07:59We'll do 0.2.
08:00It'll be 20% or 0.2, just to make it more easily readable.
08:06Now we need to close this parentheses
08:08to make sure we close off this if statement.
08:11And we can end it with a comma.
08:13Now we're gonna go down again.
08:14And so you're starting to see,
08:16we're kind of creating this internal logic
08:19all within this let function.
08:20We've defined our current sales.
08:22We're using our current sales in this formula.
08:26And now we're gonna do another calculation.
08:29So now we'll call this one our bonus right here.
08:31So we're gonna say bonus.
08:33And what is the bonus gonna actually be?
08:35The bonus is going to be the current sales,
08:38right here, times, then we're gonna do their bonus rate,
08:43because now we've defined it.
08:44So we're gonna come right here.
08:46We're gonna specify the bonus rate.
08:48And then lastly, we just need to specify
08:51the very last piece, which is, this is the bonus.
08:55This is our output.
08:56And so now we're going to run this.
08:58And now you can see here is our bonus.
09:00And it all works well.
09:02Now for this example,
09:03I would actually say this is somewhat simple.
09:05But as you get really complex,
09:08this can come in a lot of handy,
09:10because you can define different parameters.
09:12And then you can use them,
09:13or maybe you want to call them variables.
09:15That's more common with programming.
09:17But this is kind of like a little program
09:20that you're writing within this let function.
09:22And so this allows you to take a lot of control
09:24with naming your variable, using your variable,
09:27creating multiple calculations,
09:29all within one function,
09:30which is really powerful as you get more advanced,
09:33like I was saying.
09:34So this one to me is really, really neat.
09:36One that I think is pretty underutilized,
09:39but is a really neat function within Excel.
09:42Let's go on to our next one.
09:43And here we have hyperlink.
09:45Now if you've ever worked with any type of link within Excel,
09:49it shows up just like this.
09:51And then you go and you click on it,
09:52it's going to take you to the location.
09:55But sometimes you're going to be passing this off
09:56to a client,
09:57and maybe you don't want it to actually say that.
09:59And so what you can do is you can say is equal to,
10:01and you'll type in,
10:02I need to spell this right,
10:03you'll type in hyperlink.
10:05It's going to say creates a shortcut or jump
10:07that opens a document stored on your hard drive,
10:09server, or the internet.
10:11So hyperlink could be anywhere.
10:13It could be even in your,
10:13you know, like it said, your hard drive or server.
10:16Well, you're going to open this up,
10:17and you're going to specify the link location.
10:20So you're going to specify this A1,
10:22and then you're going to do a comma,
10:24and you can name it whatever you'd like.
10:26So for this one, I'm going to say,
10:28just click, and I need to name it within parentheses,
10:31but I'm going to say click here.
10:33And I'll close the parentheses.
10:35And now these will take you to the exact same place,
10:37but this one is a lot more user friendly.
10:39And so when I'm handing things off to a client
10:42that, you know, may or may not be super Excel,
10:45you know, friendly or really know what they're doing,
10:48I might make it even more user friendly
10:50by doing something like this.
10:52And so they can go and they know to click here,
10:54I can leave some custom message for them.
10:55Our very last function that we're going to take a look at
10:58is this one right here.
11:00It's EO month, which stands for end of month.
11:03And this one is actually really, really useful.
11:06I mean, you can think of,
11:07let's say you have a subscription business,
11:09and within this subscription business,
11:11you say when someone pays this month,
11:13they get till the end of that month
11:15for their current payment,
11:17or they get to the end of next month,
11:19or whatever it is.
11:21And so we're going to just look at the start date,
11:23but then we'll also take into account
11:24something called an offset.
11:26And so let's come in here.
11:28We're going to say EO month.
11:30This is going to return the serial number
11:32of the last day of the month
11:34before or after a specified number of months.
11:39Let's go ahead and open this parentheses.
11:40We have our start date,
11:42and then we have our months.
11:44So right here, we need to specify
11:45how many months we're going to do.
11:47And that's this months to offset.
11:50So once we pass these through,
11:51you'll see that we are not offsetting by any months.
11:55It's just within this current month.
11:56So the end of this month is going to be 131 of 2024.
12:01If we drag this down, we're going to offset it by one month.
12:06So we're going to say, okay, the start date is 315 of 2024,
12:11but we need to move forward one month
12:13and look at the end of that month.
12:15So this will be 430 of 2024.
12:19We can also go backward.
12:21So this is 620 of 2024.
12:23We can look at the end of the month,
12:25looking back one month.
12:26We can even look ahead six months in the future.
12:29So six months ahead of this is going to be 430 of 2025.
12:33And this is, you know, even in the future of where I am now.
12:36And this is just really useful.
12:38There are some specific use cases, like I was saying,
12:40maybe subscriptions where you're going to want to be able
12:42to look ahead or maybe even backward in some cases
12:46to see when the last day of the month is
12:49to maybe know when to bill them
12:51or when to look for a refund or whatever it is.
12:54Maybe they're signing up for a six month subscription.
12:56So you want to know when the last day they're going to have access to that service is.
13:00And so again, this one, I think, is one that not a lot of people have used or know about.
13:05But there are a lot of times where people will try to recreate this EO month function
13:10themselves and it's quite complicated.
13:12And then they realize, oh, there's already something built in for that.
13:15I just have never seen it.
13:16And so this is another really, really cool function.
13:19I think a lot of people don't know about.
13:21And that is all the functions that we're going to look at in this lesson.
13:24A lot of really, really cool stuff that Excel can do and knows how to do
13:29that you may have been trying to recreate yourself or just have never heard of it.
13:33So it's pretty neat.
13:34If you like this video, be sure to like and subscribe.
13:36I really appreciate it.
13:38If you haven't already, I have a full course on analystbuilder.com for Excel,
13:42as well as SQL and Python and Pandas and a bunch of other stuff as well.
13:46So be sure to go check that out.
13:47But with that being said, I hope you enjoyed this video
13:49and I will see you in the next one.