Most people dream in color. Some dream in black and white. I dream in spreadsheets…
I use spreadsheets all the time. Why do something by hand that a computer can do quicker, more accurately and allow you to vary the inputs to see the effect on the results?
Here are a few gems—some functions, some operators and some formatting—that can make your spreadsheets hum.
NOW Puts in the current date and/or time.
YEARFRAC Calculates the number of years between two dates.
IF If a condition you establish is true, Excel makes one calculation; if it’s false, it makes a different calculation. Perfect for determining, say, the capital gains tax on a stock sale: if the holding period is more than a year, multiply by the long-term capital gains rate; if it’s under a year, multiply by the short-term rate.
COUNTIF It counts up a group of cells, but only includes those cells that meet the condition(s) you set. So let’s say you have a column of donors, and next to it a column indicating if you’ve contacted them recently. COUNTIF can quickly count up how many donors still need to been reached.
VLOOKUP Looks up a value on a table for a given input. For example, for any given income level, Excel could look up the income tax rate.
XIRR Calculates the compounded annual rate of return for an investment. It uses the date and amount of each cash flow: the initial investment amount, any additional payments into or out of the investment, and the final value.
ROUND Rounds an answer up or down to the number of desired digits.
SUMPRODUCT Multiplies entries in two columns and then adds up the answers. Great for calculating weighted average returns, where one column has the individual investment returns, and the other columns shows the percentage that each investment is of the total account.
Operators (in formulas)
^ “Raise to the power.” Let’s say you wanted to square whatever’s in cell H3, you’d write: =H3^2. By the way, the square root of whatever’s in H3 would be: =H3^(1/2).
& Joins the text in different cells. So, if cell A1 has “super” in it and cell A2 has “girl” in it, then in cell A3 you could write “=A1&A2” to get “supergirl.” Great for merging, say, first name and last name.
>= Greater than or equal to (<= would be just the opposite).
$ Freezes a cell you reference in a formula. Without the $, when you copy or drag the formula, the cell reference changes, too.
Gridlines: Eliminate them (View tab; uncheck Gridlines). Then put in just the lines you want using Borders. Much cleaner.
Conditional: The value of a cell will determine its font, text color, background color, etc. Great for easily differentiating results.
Labeled number: Example: instead of just showing the number “12,” you can add some clarity and have the number show as, say, “12 pounds.” But this is not text; you can still do other things to the 12 (like add or multiply). In Format, choose Custom and, in this example, type: ## “pounds” (where ## displays the 2-digit number and the text you choose is in quotes).
My dad would always say: Excel in everything you do. I got close, Dad—I do everything in Excel…
Robert A. “Rocky” Mills is a Financial Advisor with the Global Wealth Management Division of Morgan Stanley in Westlake Village. The information contained in this article is not a solicitation to purchase or sell investments. Any information presented is general in nature and not intended to provide individually tailored investment advice. The views expressed herein are those of the author and may not necessarily reflect the views of Morgan Stanley Smith Barney LLC, Member SIPC, or its affiliates.