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.


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?


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.


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.


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.


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.

There’s a lot of data here, but how can I make sense of it?

notimeOnce in a while I find myself with a lot of really good information that I’d like to use. Great! Put it into Excel. Not always so easy, even if it’s already computerized. I ran across this recently in looking at Medicare payments for doctor visits. The mymedicare.gov site tells you everything about each of your doctor visits–dates, charges, payments, procedures. You can look at it, or you can download it as PDF or text. Not one of these gives you the one-row-per-doctor-visit format you’re used to seeing and working with in Excel. The text version seems to be the best, but here’s what it looks like if you copy it into Excel (plus a lot of blank lines that I’ve removed):

medicare text

One column, with keywords for what you’d like to become columns. Then below each claim are several “claim lines,” each with its own data, spanning several rows. This is good information, but it complicates things, because the number of claim lines varies, so you can’t just count off to find the next claim.

It’s those keywords that make it possible to work with this, because they’re in the same cell with the data. For example, to see all the dates you had a doctor visit, filter this on “Service Start Date”. Then copy that somewhere else. Filter on “Provider:” (don’t forget the “:”) to get a list of doctors that you can line up with the dates, and so on. Add column headers, do a global replace to get rid of the keywords, and you’ve got it.

I don’t usually need macros, but this is one of those tedious tasks that calls for a little automation. Here’s a data conversion spreadsheet, Medicare with macro. Yes, it’s all blank, but it includes a macro called Medicare. Put your Medicare text data in the first column of Sheet 1, run the macro, and you’ll get something that looks like this in Sheet 2. It also lists all the procedures in order.

Medicare converted

Now you can sort it by date, filter it by doctor, or use any of the other ways Excel can examine a bunch of information. Pivot tables, anyone? If you have data in a similar form from some other source you can edit the macro. Wouldn’t it be nice, though, if everybody gave us data already in Excel-friendly form?


What if you could visualize your financial future for the coming year?

Hiker woman looking forward in the snowy mountainHappy new year! This is a time for looking back and for looking ahead. How did you do financially this past year? Was it what you expected? What do you expect in the coming year, and the years ahead? If you could magically produce a spreadsheet that could help you look ahead, what would you ask it? Here are some of the types of questions you might want answered:

  • How can I get out of debt?
  • How much do I need to save to reach my goal?
  • Can I pay off my house faster?
  • Should I refinance my house?
  • How much money do I need to retire?
  • How much can I spend each year in retirement?

You can probably think of others. The good news is that you can easily build spreadsheets to explore questions like these. Of course, there are no certainties in financial planning. That’s why you may want to play “what if” and maybe do some Monte Carlo analysis. Best wishes for a new year filled with fun and no unpleasant surprises.

Volatility is not your friend

Businessman and declining shareWhen you can visualize your financial future using one of the spreadsheets I’ve included recently, it’s fun to put in some really high rate of return and watch your future self get rich. Before the great recession a few years ago, there were a lot of people touting 18% returns, and a lot more people running the numbers and thinking they were going to be gazillionaires. We all know how that turned out. Yes, if you could guarantee 18% year-after-year, go for it. But, if fact, you may get less, a lot less, maybe more, or the whole thing could go bust. That’s volatility (on top of the risk of losing your original investment). If you want to gamble, go to Vegas. But when you’re talking about your financial future, you want some certainty.

Volatility, the uncertainty of returns, hits you even in some relatively safe investments. Take the example of the man retiring with $2,000,000 invested at 4%. A few posts ago I built a simple retirement spending spreadsheet that showed he could spend $80,000 a year and not run out for 27 years, when he’s 92.Retirement til 92

But now if you throw in some modest volatility and do a Monte Carlo analysis of it with this spreadsheet, the certainty goes away. With a standard deviation (a common measure of volatility) of only 2.5%, there are no guarantees. retirement basic mc graphThis money will last those 27 years only with a probability somewhere between 75 and 80%. He’d need to cut his spending down to $70,000 a year to get near-certainty that it would last.

Try higher volatility (standard deviation) numbers to see how much harder it is to get near-certainty in your financial future.

Investment returns are uncertain. How much savings do I really need to retire?

money growthA couple posts back, I looked at how much you could spend each year if you retired with 2 million dollars invested at 4%. (Answer: About $80,000, increasing each year for inflation). But how do you know you can get 4% year-after-year for 30 or 40 years?

You don’t. Any investment has good years and not-so-good years, and in general, the greater the return, the higher the uncertainty. One way of measuring this volatility is the standard deviation. For example, a bond fund paying 4% per year had a standard deviation of 2.5%. That means about 68% of the time your return will be with 2.5 percentage points of the average 4%, or between 1.5% and 6.5%. Here’s a spreadsheet, retirement with volatility, that gives you a sample of what might happen to your investment over the years.

retirement variabilityWhat you see when you open the spreadsheet won’t be the same as this. The reason is that it’s using random draws to represent how well the fund will do that year. We can’t predict it, but we know what might happen, so for each year it randomly picks something within the realm of possibility. The Random number is selected between 0 and 100 percent, and the Growth is calculated based on it. Every time you change something in the spreadsheet you got a completely new list of random numbers and a new outcome. Try entering something, anything, in some unused cell to get another view of how it might turn out. There’s even a tiny chance that the money won’t last the full 25 years:

retirement bad variability

The Excel secret to making all this work, if you’re interested, is the RAND function. This also uses NORMINV to calculate the growth rates. This goes beyond the basic techniques used to build most of the spreadsheets here, but you can investigate these functions yourself.

If you’ve tried a few cases, you’re probably satisfied that this investment and withdrawal plan is sound. But what if the investment were more volatile? Try 8% or 10% standard deviation. Are you comfortable with that?