In this course, you will get hands-on instruction of advanced Excel 2013 functions. You’ll learn to use PowerPivot to build databases and data models. We’ll show you how to perform different types of scenario and simulation analysis and you’ll have an opportunity to practice these skills by leveraging some of Excel's built in tools including, solver, data tables, scenario manager and goal seek. In the second half of the course, will cover how to visualize data, tell a story and explore data by reviewing core principles of data visualization and dashboarding. You’ll use Excel to build complex graphs and Power View reports and then start to combine them into dynamic dashboards. Note: Learners will need PowerPivot to complete some of the exercises. Please use MS Excel 2013 version. If you have other MS Excel versions or a MAC you might not be able to complete all assignments. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.
A lot of people view Excel as a tool that’s only good for business applications3 Crazy Excel Formulas That Do Amazing Things3 Crazy Excel Formulas That Do Amazing ThingsThe power of Microsoft Excel lies in its formulas. Let me show you the wonders you can do with formulas and conditional formatting in three useful examples.Read More, 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 loanThe 6 Best Online Tools For Car BuyersThe 6 Best Online Tools For Car BuyersBuying a new car is a big decision. The Internet is the best way to research new cars – and also the best was to calculate the costs associated with them. Let’s have a look...Read More, 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 issuesDoing Your Taxes? 5 Excel Formulas You Must KnowDoing Your Taxes? 5 Excel Formulas You Must KnowIt's two days before your taxes are due and you don't want to pay another late filing fee. This is the time to leverage the power of Excel to get everything in order.Read More. 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!
PMT – Payment
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:
- the percentage interest rate of the loan
- the term of the loan (how many payments?)
- the starting principle of the loan
- future value, if for some reason the loan will be considered paid off before it reaches zero (optional)
- type of loan – 0 if payments due at the end of each month, or 1 if they’re due at the beginning (optional)
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 termsPersonal Loans Online -- How Peer-to-Peer Lending Is Changing the GamePersonal Loans Online -- How Peer-to-Peer Lending Is Changing the GameA number of services on the market right now allow lenders to provide credit, and for borrowers to gain access to funds without dealing with a bank. They're shaking up the finance game.Read More 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)
This is why our readers are so great – thanks for helping with this fix Mark!
FV – Future Value
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!
Logical Functions (IF or AND)
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.
Making Sense of Lists
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 chargesManage Your Budget & Expenses Using Google Budgeting ToolsManage Your Budget & Expenses Using Google Budgeting ToolsRead More.
COUNT and COUNTIF
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 formulaMini Excel Tutorial: Using Advanced Counting and Adding Functions in ExcelMini Excel Tutorial: Using Advanced Counting and Adding Functions in ExcelCounting and adding formulas may appear mundane compared to more advanced Excel formulas. But they can help you save a lot of time when you need to collect information about the data in your spreadsheet.Read More. 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 textSaving Time with Text Operations in ExcelSaving Time with Text Operations in ExcelExcel can do magic with numbers and it can handle characters equally well. This manual demonstrates how to analyze, convert, replace, and edit text within spreadsheets. These basics will allow you to perform complex transformations.Read More.
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.
Organizing Bank and Financial Downloads
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.
RIGHT, LEFT, TEXT, and CONCATENATE
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.
Picking Random Names from a Hat
One last fun function is one you may use if you have to do something like pick some names out of a hatHow to Get a Free Raffle Ticket Template for Microsoft WordHow to Get a Free Raffle Ticket Template for Microsoft WordFundraising is a lot of work. Let us take some of it off of your shoulders and show you how to find templates and customize free raffle tickets. All you need is a word processor.Read More 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!
Using Excel for Everyday Problems
As you can see, Excel isn’t just for data-analysis gurusHow to Become a Data ScientistHow to Become a Data ScientistData science has gone from a newly coined term in 2007 to being one of the most sought-after disciplines today. But what does a data scientist do? And how can you break into the field?Read More and business professionals. Anyone can benefit from the many functions that you’ll find tucked away in ExcelFind Anything in Your Excel Spreadsheet with Lookup FunctionsFind Anything in Your Excel Spreadsheet with Lookup FunctionsIn a giant Excel spreadsheet, CTRL+F will only get you so far. Be clever and let formulas do the hard work. Lookup formulas save time and are easy to apply.Read More.
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
5 Useful Resume Sites for Preparing a CV That Gets Read in 20184 Tips for Doing Real Research on Android