A new view on your data: Pivot Tables

looking around cornersExcel’s pivot tables reorganize, categorize, and summarize your data dynamically however you want. Look at it one way, then in seconds, look at it another way. It has a lot of features, but the basics alone are powerful. Today I’ll show you how to use it on a simple example.

Let’s take the example from last time, a spending log.spending

 

 

Pivot table dialog box

Click anywhere in the data. Then select Data > PivotTable or Insert > Table > PivotTable, depending on your version of Excel. You’ll get a Create PivotTable dialog box. Click OK.

 

Blank Pivot Table Builder

Now you’ll get a new sheet with a blank table, marked only Column area, Row area, and Values area. You’ll also get the PivotTable Builder. This is where you tell Excel what you want for rows and columns and what you want in the body of the table.

 

Start by deciding what you want for Values. This is the data you’re going to total by categories, so it only makes sense if it’s numerical, something you can sum. Here, the only field name like that is Amount. Of course, that’s exactly what you want to look at, your spending. Drag the Amount field name into the lower right Values box. You will now see a tiny pivot table showing your total spending. You’d really like to break it down by category, so drag the Category field name into the Column Labels box. Now you have your total spending by category. Break that down by month by dragging the Month field name into the Row Labels box. (You may have to scroll in the Field name to find Month.) Here’s what the PivotTable Builder looks like when it’s filled in.

Completed Pivot Table Builder

Completed Pivot Table Builder

 

It looks complicated, but this just took a few steps. Now you can slice and dice your data any way you want. Drag Category out of the window and you immediately get a new table, summarized only by month. Drag Paid by into the Column Labels box to get your spending by month and payment method.

This feature does more, but these simple steps are all you need to slice and dice on the fly. I love Excel for its flexibility. You don’t need to pre-define reports the way you do in a database program. Try it on your data.

Final pivot table

Final pivot table

Dealing with data: Sort and Filter

 

alternativesHave you ever been overwhelmed with data? How can you organize it? How can you make sense of it all? I’ve tried a lot of databases, and I keep coming back to Excel. The main reason is that databases are designed to give you fixed reports, and the whole thing is structured to give you those reports. Well, when I have a lot of data, I’ll look at it from multiple views, each of which will trigger other questions. I can’t predict what I’ll want to look for next month or the month after that. Excel allows you to look into the data dynamically. The two major techniques for doing this are Sort/Filter and Pivot Tables. Let’s explore Sort and Filter. I’ll show you how to use Pivot Tables next time.

However you plan to look into the data, the first thing you need to do is organize your data into rows and columns. The rows represent the individual items and the columns are the categories and amounts. For example, for tracking your spending, each row will represent a payment and the columns might be the date, amount, payee, and any categories you might be interested in.

spendingIt also works best if your rows represent things at the lowest, most detailed level. For example, if you are tracking the costs, diagnoses, and insurance payments of your doctor visits, each row will represent one visit to one doctor. If you visit the same doctor twice, that’s two different rows. If you see two different doctors on the same day, that’s two rows. Then these Excel tools will let you look at things from a higher level if you want to, say, look at a single doctor or specialty.

It’s easy to turn on Sort and Filter. Click anywhere in your table and then find Sort and Filter. It might be called simply “Filter” in the Data menu, or may be an icon that looks like a funnel. Try Home > Editing > Sort & Filter > Filter. It depends on your Excel version, and it may appear multiple places. When you select it, little arrows appear at the top of the columns. Notice that Excel figures out how big your table is, even if you don’t formally make it a table.

filterNow when you click on that little arrow, it opens a window that allows you to sort or filter the whole table based on the values in that column. For example, if you open the drop-down on Category, deselect “(Select All)” and then click on Clothing, You’ll see just your clothing purchases.

sort filter dialogClick “Clear Filter” to put it all back. You can sort your purchases by amount, simply by opening the drop-down on Amount and clicking Descending (to put your biggest purchases on top). You can even use multiple sorts and filters at once. For example, see all your January Visa purchases, or list your entertainment purchases in order of cost. There are much more complex filtering options that you can explore.

Next month I’ll show you how to do basic Pivot Tables. I’m now going to a monthly blog, the second Monday of the month. Thanks for reading.

Which investment is better?

compare investmentsI have $10,000 to invest over the next six years. I have a chance to buy equipment that will save me $2000 a year for five years and after that I could sell it for $3000. Looks like $3000 ahead! But before I jump into this, I should compare it with other investment opportunities. Maybe I have a chance to invest it at 6% for the six years, and I figure the risks are comparable for the two choices.

Here’s a spreadsheet to look at investments like this. Fill in the interest rate you’re using for comparison and the amount you’re investing. Then fill in how much you will get each of years 1 through 6. (If you have to put in more money any year, enter that as a negative.) The cumulative column shows how much you’ll have total (positive in black or negative in red) at the end of each year, taking into account what you’ll give up by not investing the money.

IRR1The Cumulative column shows how much I’ll be ahead (black) or behind (red) relative to investing the money at 6%. I’m in the hole until Year 6, but I end up $765.45 ahead. All else being equal, this is a better than the 6% investment.

But how much better is it? Try various rates. Try 7%. The bottom line is still positive, so this is better than a 7% investment. Try 8%. Negative. Not as good. Now I can use Goal Seek to find the rate that gives comparable results. I tell Goal Seek to make the bottom line exactly zero by changing the rate. Here’s what I get.

IRR2

The answer is that this investment is exactly as good as investing the money at 7.6%.

Here’s another way I could use this spreadsheet and Goal Seek. I’m looking for a 10% return on my investment since I think it’s risky. How much should I offer to pay for the equipment?

IRR3

If I could buy the equipment for $9,275, I could make 10% on the investment.

The value of 7.6% that we got is called the internal rate of return, often abbreviated as IRR. This is a useful measure of how good the investment is. Take, for example, a similar investment that pays more each year, but has no salvage value at the end, as represented in this spreadsheet.

IRR4

The internal rate of return here is 7.9%, a better investment.

Now let’s compare it to a completely different type of investment. Suppose someone borrows $10,000 and in six years (without any interest payments) pays you $15,000. That’s more than you’d get back from the other investments. But if we find the internal rate of return, it’s only 7.0%. Money sooner is more valuable than money later.

IRR5

If you’re interested in how you’d build a spreadsheet like this, it’s the same techniques used in others—(1) create names for the inputs, (2) click on cells to get Excel to write a formula for you using only the current and preceding line and named inputs, and (3) AutoFill.

Take a look at the key formula for the cumulative in Year 1.

IRR6

You’ll get this if you click on the cells of interest while thinking through what you’d compute. Start with the cumulative you had the year before (the $10,000 in the cell just above, C5) and add in a year of lost interest on it (Rate*C5). On top of that, you get back the Amount in/out, so add in the $2000 in the cell to the left (B6). Click on the cells to build this formula rather than worrying about cell numbers. Once again, Excel formulas are easier to write than to read. You may notice that this formula is exactly the same as the one we used to look at investment growth. It works just fine even if some of the numbers are negative. This formula uses only the current and previous rows and named inputs, so we can AutoFill it down to complete the spreadsheet.

By the way, Excel has a function for calculating the internal rate of return directly, IRR. In this spreadsheet, =IRR(B5:B11) gives you the same 7.6% we got before.

Compound interest calculations without exponents or formulas

money growthIf I invest $200,000 now at 6%, how much will I have in 10 years? One of my students recently said that if she had a magic spreadsheet that could predict her financial future, this is one question she would ask it.

Some of you probably know that you can calculate this using the formula =200000*(1+.06)^10, but a lot of you have already run away at the mention of the word “formula” and the scary flashbacks to high school algebra it spawns. Come back! You can get Excel to do this for you without formulas, functions, exponents, or any of that. Just addition and multiplication. As a bonus, you’ll watch your future money grow year-by-year.

Here’s the compound interest spreadsheet. We start with the amount and rate as our inputs, named and colored blue to remind us that we can change them. Be sure to create names for them or this won’t work.* We don’t use the number of years as an input, since we’ll be looking year-by-year, and so can see the answer for any number of years we choose.

compoundNow make a year column and an amount column and fill in the first row, year 0 with a starting amount equal to the input of $200,000. Fill in the first column of sequential year numbers. (AutoFill can help here.)

The magic happens in cell B6. If you can tell Excel how to do this using only the previous row and the inputs, you can use it to fill the whole thing. Start with =, then think about how you would figure this manually. The new amount would include the money you had the previous year, so click on the $200,000 just above. But there will be more, so type “+”. You’ll also have the interest earned, which is 6% of the $200,000 from the year before. You multiply to take percentages, so click on the 6%, type * for multiply and click on the $200,000 just above. That’s it. When you enter it, Excel gives you a formula, =B5+Rate*B5, but all you did was think “I’ll have the $200,000 from before plus the interest, 6% of the $200,000 from before.” You did it with no formulas, no cell identifiers.

Now you can tell Excel, “do the same thing” for the rest of the table. Click on the cell B6 that you just entered, grab the lower right corner and drag down to AutoFill. Take it down to 10 years or more. You see that after 10 years you’ll have $358,170 (the same answer you get from the formula, by the way).

I love this technique since you don’t have to memorize any formulas, but just think about what’s changing. I hate memorizing! Even better, you can use this same technique for more complicated problems, such as if you’re withdrawing money each year. Seeing it year-by-year helps you understand what’s going on.

*By the way, if you’re wondering why naming cells should make any difference in how the spreadsheet works, it’s because any named cells automatically become absolute references. That means that when you AutoFill, it always picks up the right number, and not one below it.

Have you used this technique for anything else? Let us know.

If you could instantly visualize your financial future…

crystalball…what would you want to see? I recently asked this of an Excel workshop I was teaching. There was one stipulation—nothing speculative, like, “Will I get a raise next year?” or “Will the Dow hit 20,000?” They had just seen an example of saving for a child’s college education, showing progress year-by-year for each plan they tried.

Here are some of the questions they came up with.

  • If I invest $200,000 now at 6%, how much will I have in 10 years?
  • If I pay an extra $100 a month to principal, how soon will I pay off the loan?
  • How much am I spending and for what?
  • How can I save to make a down payment on a house in two years?
  • What profit percentage do I have to make to earn $3000 per month on the jewelry I make?
  • If I make $2000 a month at a 45% commission, how much will I have at year-end?
  • Can I afford to buy a more expensive house, even though I’m retiring in a year?

This was a little two-hour workshop, in which I taught them five basic skills:

  • Play “what-if” to see how your plans or assumptions change the answer
  • Get Excel to write simple formulas for you
  • Name input cells to make the formulas play nice
  • AutoFill formulas to build a sophisticated model from one or two simple formulas
  • Use Goal Seek to work the problem backwards

I asked the question because I wanted to see what other skills they would need to learn to help them answer the financial questions that interested them. I was surprised that the five skills were all they needed! In fact, all of these can all be done using only addition, subtraction, and multiplication.

Some of the questions were even simpler. Tracking your spending can be done just by entering your expenditures and their categories in a spreadsheet. You can then use sort and filter to see which categories are costing you the most. Yes, it takes discipline, but the techniques are easy. One simple formula can keep track of your balance.

In future posts, I’ll show you how to answer these questions.

Now, my question to you is, what would you ask? Do you have a financial question that you think can’t be answered using the simple techniques? Please leave your most challenging question as a comment.

What nine things should everyone know how to do with a spreadsheet?

Businessman holding a laptopMacworld recently ran an article, “Nine things everyone should know how to do with a spreadsheet,” by Rob Griffiths. Here’s the list:

  1. Format numbers
  2. Merge cells
  3. Use functions
  4. Distinguish between relative and absolute references
  5. Name cell references
  6. Extract data from ranges
  7. Perform logical tests
  8. Mix text and formula results
  9. Use conditional formatting

I thought about my own top nine before I read the article, or even looked at the list. My focus is on visualizing your financial future and exploring your options. Even so, I was surprised how different my list was. I’ve used everything on Griffith’s list and certainly found it all valuable, but here’s my list for those wishing to explore their financial future.

 

  1. Play what-if

This is barely a skill. Griffiths is assuming that you know how to do this. It’s just entering a number in a cell. The key is to see what that does to the answer. This hardly seems worth mentioning, except that so many people seem to think that all you do with Excel is to dump a bunch of numbers into it and produce a static chart or slide. Explore! See what drives the outcome. See how to get where you want to be.

  1. Write formulas

This is also implicit in Griffith’s list. Even if the word “formula” brings up scary images of high school algebra, everyone should be able to get Excel to write a formula for them by typing “=” and clicking on the cells you want to use in the calculation. It’s amazing what you can do without ever entering a cell reference (if you name your inputs). Learn how to build more sophisticated formulas and you’ll be unstoppable.

  1. Name cells

Griffiths has this one on his list as a means to keep formulas easier to read. That’s nice, but it’s on my list for a very different reason. If you name your inputs they automatically become absolute references and you don’t have to bother with all the dollar signs. This makes writing formulas to AutoFill a cinch. Also, I’m lazy, so I prefer to use Create Names, which uses the input labels. By the way, Griffiths looked at how to do everything on his list in Apple’s Numbers as well as Google’s Sheets. Numbers doesn’t support naming cells. Too bad. I’m a Mac user, but that’s a show-stopper for me.

  1. AutoFill formulas

The best part of writing formulas is being able to replicate them to fill an entire spreadsheet. Construct simple formulas that build on each other, AutoFill them down the page and get complex results. The secret to doing this is to name your inputs to make them play nice and then click on cells in the same of previous row to create a formula. Grab the corner and pull it down to fill in everything else. This is the technique that makes students’ jaws drop in my classes. Some big Excel reference books don’t even mention it.

  1. Use Goal Seek

Work the problem backwards. Your projected savings won’t get you to your goal? Tell Goal Seek to find the amount you need to save to get there.

  1. Use functions

This definitely belongs on any list, and it’s in both of ours. Whatever you want to do—statistics, finance, text handling, and more—Excel has a function for you. Griffiths mentions IF, HLOOKUP, and VLOOKUP. The IF function is especially powerful. It lets you make what happens on the spreadsheet respond to whatever is happening anywhere else on the spreadsheet. If you’re doing loan payments, check out PMT, IPMT, and PPMT.

  1. Create charts

You don’t need to know all the kooky chart options, but sometimes a simple chart helps you see what’s going on, year-by-year.

  1. Sort and filter

In most of the problems I deal with, you don’t have a lot of data (eg. How can I save enough for an $80,000 college education in 18 years?). But sometimes you’re keeping track of a lot of information and you need to make sense of it. Make a table of it, and then use the tabs at the top of each column to reorder or focus it all, to figure out what you have and what matters.

  1. Build data tables

This is a somewhat advanced, somewhat confusing technique, but it’s great for sensitivity analysis. What really matters to the answer? You can also use it to turn any spreadsheet into a Monte Carlo model for risk assessment.

 

 

This is clearly a very subjective list. What are your nine favorite Excel techniques?

What’s with the dollar signs in Excel formulas?

dollar signEver noticed cell identifiers with dollar signs in them? Things like $A$1. Sometimes they’re in formulas. Sometimes you select a cell for a dialog box and it shows you something with dollar signs. What’s that all about?

You might try the formulas =A1, =$A1, =A$1, and =$A$1 and find they all give you the same answer, the contents of cell A1. Where you see the difference is when you copy or AutoFill the formula. Excel is smart enough to adjust your formula to its new location, a very convenient feature. But sometimes you don’t want it to do that. That’s what the $ does. It tells Excel “Don’t change the following letter or number when you copy this formula.” So, for example, $A1 will always keep the A, but the 1 may change when copied somewhere else.

Let’s take an example. Here’s a spreadsheet, dollar signs, that calculates total cost of some items, including sales tax.

dollar signs spreadsheet

Everything in columns C through F starting in row 4 is calculated using a formula. In fact, formulas were entered only in row 4, and the rest were copied down the columns using AutoFill. The formula in D4 is =B4+C4. Add the price and the tax to get the total. No dollar signs, so when it gets copied to the other rows it will change accordingly. In D5, for example, the formula is =B5+C5, which is just what you want. This is what Excel does normally, and usually it gives you exactly what you want and saves you a ton of work.

Now look at C4. The formula is =B4*B1, multiply the price by the tax rate. That works here, but it we try to copy it down the column, we’ll get garbage (try it), since it will try to use B2, B3, and so on, instead of B1. We want to make sure we always use the tax rate that is in B1, so we write it as $B$1, and the formula that works for copying is =B4*$B$1.

Check out the formula in E4, that multiplies the total cost of one item by the quantity in E3. It’s =E$3*$D4. See if you can figure out why this works not only when you copy it down the column, but also when you copy it across to column F.

By the way, the references that change (without the $) are called relative references and the ones that don’t change (with the $) are called absolute references. Knowing when to use them will really help you create some big, useful spreadsheets from just a few formulas.