A lot of people view Excel as a tool that’s only good for business applications, but when you see the simple and useful formulas below, you’ll see that Excel can also help you solve real life problems at home.
Whether you’re shopping for a new car loan, want to figure out which mutual fund investment is best for you, or if you’re just trying to make sense out of your bank account, Excel is a powerful tool that can help.
The 15 formulas below were carefully selected for both simplicity and for how powerful they are with helping regular people figure out complex issues. If you’re a teacher trying to sort out student grades, a parent trying to get a handle on your household spending, or a young couple shopping for your first home loan, the formulas below can help.
Shopping for a new home and confused by all the mortgage lingo? Looking for a new car and getting confused by the car loan terms the sales person keeps throwing at you?
Have no fear. Before you start shopping, do your research with Excel by your side!
Whenever you’re comparing any loan terms and want to quickly figure out your actual monthly payment given different variation in terms, take advantage of the powerful (and simple) PMT formula.
Here’s what you need to use this formula:
Here’s a cool way to quickly compare a variety of loans to see what your payments will look like; just create an Excel sheet that lists every potential loan and all available information about them. Then, create a “Payments” column and use the PMT formula.
Just grab the lower right corner of the PMT cell you just created, and drag it down so it calculates the payment total for all of the loan terms listed in the sheet.
As you can see, when you can compare loan terms side-by-side, some stark realities show forth. For example, for a car loan of $19,500, if you take the time to show around and get a loan that’s just a few interest points lower, it could save you a lot of money each month, and thousands of dollars over the life of the loan.
(A very big thank you to Mark Jones (@redtexture on Twitter) who pointed out that for PMT and FV formulas, you’ve got to be very careful about using the same period – in this case using monthly payments requires dividing the interest term by 12 months)
@rdube Egregious errors: formula PMT & FV calculates percentage per month, not per year. Interest rate off by 12x. https://t.co/wrC5eXFzNw
— Mark Jones (@redtexture) November 26, 2015
This is why our readers are so great – thanks for helping with this fix Mark!
The next formula comes in handy when you are looking to invest some money into something like a CD (Certificate of Deposit), and you want to know what the CD will be worth at the end of the term.
Here’s what you need to know to use the FV formula:
So let’s compare several bank CDs using the terms that you know from the information the banks have given you. In the example below, let’s say you have a $20,000 inheritance to invest in a CD.
As you can see, interest rates are again represented in decimal format (take the interest rate the bank gave you and divide by 100), and payments are zero, because CD’s are typically based on a starting value and a future value paid out. Here’s what the comparison looks like when you use the FV formula for every CD you’re considering.
Without a doubt, the higher interest CD over a longer period of time pays out handsomely more. The only drawback of course is that you can’t touch any of your money for three whole years. For a lot of people, that’s just a little too long, but that’s the nature of investing!
Most banks these days give you the ability to download nearly a year’s worth of bank transactions to a format like CSV. This is a perfect format to analyze your spending using Excel, but sometimes the data you receive from banks is very disorganized.
Using logical functions is a great way to spot overspending.
Ideally, the bank either automatically categorizes your spending, or you’ve set up your account so that things are placed into spending categories. For example, any restaurants we go to get labeled with the “DiningOut” label.
This makes it easy to use a logical function to identify whenever we’ve gone out to eat and spent over $20.
To do this, just create a logical function in a new column, looking for any value where the category column is “DiningOut” and the transaction column is larger than -$20 (the comparison below shows “<“, less than, because the values in column C are all negative. Here’s what that looks like:
Using IF and AND together in one formula looks tricky, but if you think about it, it makes perfect sense. The IF statement will output the dollar amount (C2) if the AND statement is true, or FALSE if it isn’t. The AND statement checks whether the category is “DiningOut” and the transaction is greater than $20.
There you have it! Without having to manually sift through all of those transactions, you now know exactly those times when you’ve overspent in a certain category. As usual, Excel formulas turn out to be a massive time-saver.
Lists are a big part of everyday life. If you’re managing a household, you’re using lists constantly.
Maybe you’re on the Parent-Teacher Committee and you’ve been sent a long list of community donations. It’s up to you to figure out who in the community donated to a specific school project, and how much. Or maybe you run a family business and you do payroll for the half-dozen employees you employ, and have to summarize their individual expense charges.
Whatever the list is, Excel can help you quickly organize and sort values. Let’s take the PTC example. Here’s a list of donations from community members.
If you want to see how many times a person’s name shows up in the list, you can combine the COUNT formula with an IF formula. So, first create a column to check if the person is Michelle or not. If it is, make the cell a 1. If it isn’t, make it a space ” “.
Next, create another column that counts how many times you’ve found Michelle Johnson in the list.
This gives you the count of every place in Column E where there’s a 1 rather than a blank.
So, this is the simplest way to do this kind of thing, but it does require two steps.
If you don’t mind using a slightly more advanced function, you might consider using one of the many combined “IF” functions like SUMIF, COUNTIF, or AVERAGEIF. These allow you to only do the function (COUNT, SUM or AVERAGE) if the logical condition is true. Here’s how it works using the above example.
This formula looks at column A, which contains all of the donor names, and if the cell within the range matches the criteria in quotes, then it counts up by one. This gives you a count of all of the times the donor name equals “Michelle Johnson” in a single step.
It’s much faster than using two columns, but for some people it may be a little too complex – so use the approach that works best for your situation.
Another function that you can use creatively sometimes is the LEN function. This function tells you how many characters are in a string of text.
One interesting way to use this in the example above would be to highlight donors who donated over $1,000 by counting the number of digits in the donation column.
Then, if you want to, you can even format another column based on the value in column E; the result of the LEN function.
To do this, you need to highlight all of the cells in the Donation column, select the Home tab in the menu, and click on Conditional Formatting in the toolbar. Then select Use a formula to determine which cells to format.
Set the range under Format values where this formula is true: to the column/range where all of your LEN function outputs are displayed.
In this example, if you make the condition “>3″, then anything over $1,000 will receive the special formatting. Don’t forget to click the Format… button and choose what kind of special formatting you want for these.
Also, a quick note. You’ll notice my range is defined as “$E2:$E11″, not “$E$2:$E$11″. For some reason when you select the range, it defaults to the former, which won’t work. You need to use relative addressing as shown in the picture above. Then, your conditional formatting will work based on the condition of the second range.
Sometimes, when you download information from businesses – whether it’s your bank, your health insurance company, or anywhere else you do business, the format of the incoming data doesn’t always match what you need it to be.
For example, let’s say that in the exported data from your bank, you’re given the date in the standard format.
If you want to add a new column of your own with your own that’s prefaced by the year and includes the Payee information (for your own sorting purposes), extracting pieces of information from a column is really easy.
You can pull the year out of the text in that column using the RIGHT function.
The function above is telling Excel to take the text in column D and extract the four characters from the right side. The CONCATENATE function pieces together those four digits, with the Payee text from the next column.
Keep in mind that if you do want to extract text from a date, you will need to convert it to text format (instead of date) using the “=TEXT(D2,”mm/dd/yyyy”)” function. Then you can use the RIGHT function to pull out the year.
CONCATENATE really comes in handy when you have text from a bunch of different columns that you want to piece together into one long string. For example, you could concatenate a person’s first name, last name, street address, town, and state into a field that contains the person’s entire address in one single column.
One last fun function is one you may use if you have to do something like pick some names out of a hat for a Christmas party. Put that hat and those scraps of paper away and instead pull out your laptop and launch Excel!
Using the function RANDBETWEEN, you can have Excel randomly select a number between a range of numbers you specify.
The two values you need to use are the lowest and highest numbers, which should be at the ends of the range of numbers you’ve applied to each person’s name.
Once you hit the Enter key, the function will randomly select one of the numbers within the range.
It’s about as random and tamper-proof as you can possibly get. So instead of picking a number from a hat, pick a number from Excel instead!
As you can see, Excel isn’t just for data-analysis gurus and business professionals. Anyone can benefit from the many functions that you’ll find tucked away in Excel.
Has this article given you any creative ideas of how you can use Excel to solve problems in your own life? Have you ever used some of these Excel functions before in creative ways? Share your own ideas and stories in the comments section below, I’d love to hear them!
Image credit: Goodluz via Shutterstock.com
Until Dawn Guide - How to Find all the Twins Clues
The Talos Principle Walkthrough
How to open the Destiny Vault of Glass for PlayStation and Xbox
7 Days to Die Wiki – Everything you need to know about the game .
Here's How To Find Pickman's Blade In Fallout 4