Taking Excel out on a Date

Why on earth would I write a post entirely about using dates in Excel? Simply because Excel is not it's usual solid self when dealing with these vixens. Many of you, I'm sure have typed in something like "6/20/2008" into a cell and had no trouble. But did you know that Excel took your little date and turned it into the non-sensical number "38157"? Huh? Better yet, try typing "38157" into a cell and then formatting it into a date. How do you feel about Excel's handling of dates now?

For reasons beyond my comprehension, Excel uses "serial numbers" as its system for delivering dates. So long as these numbers are formatted as dates, everything is fine. But let's say you want to play around with dates a little bit, how do you deal with these serial numbers?

I would divide Date-related functions into two primary buckets: Date Construction and Date Extraction.

The primary Date Construction function is aptly named DATE(year, month, day). As you can see, it takes three arguments: a year, a month, and a day. So, going with 6/20, we would type in DATE(2008,6,20), which will return our desired date. In sum, the DATE function will construct a serial number for the date that corresponds to the year, month and day you feed it.

The three Date Extraction formulas are YEAR(serial number), MONTH(serial number), DAY(serial number). These formulas extract information from a serial number. Keeping with our example:
1: YEAR(38157) = 2008
2: MONTH(38157) = 6
3: DAY(38157) = 20

Okay, that's great, now why do we care? Well, since you ask, there are myriad applications for Date functions. In the linked worksheet, we will look at two, both related to budget applications. The first uses date extraction formulas, which will come in handy when we cover the SUMIF formula. The second uses date construction formulas, and includes my favorite way to automatically determine the last day of the month using a formula that is not related to "30 days has September, April, June and November."

Download the Dates worksheet here. If you have trouble with the formulas on the Date Worksheet tab, I put the answers on the "Date Answers" tab.

Note: This worksheet introduces the concept of Nesting Functions (a function as an argument of another function). In date-related examples, this means starting with one serial number and using both extraction and construction formulas at the same time to create a new serial number. So if we started with 6/20/2008 in cell a1 and wanted to create a formula to give us 7/20/2008 in cell b1, we would put the following nested formula in b1:

=DATE(YEAR(a1),MONTH(a1)+1,DAY(a1)).

Excel will first calculate the internal functions, so:

YEAR(a1) = 2008

MONTH(a1) = 6

DAY(a1) = 20

Which leaves us with:

=DATE(2008,6+1,20)

=DATE(2008,7,20)

and voila! we get 7/20/2008.

Another important note for this: If you put in a zero for the "day" argument in the DATE function, it will return the last day of the previous month. So, if you wrote: =DATE(2008,2,0), the function would return "1/31/2008". This trick comes in handy when you want to create a list of dates that correspond to the last, rather than the first day of the month.

Grade School Cont'd: More Arithmetic

Thought you graduated? Well, you're almost there, but let's roll through some more arithmetic before you move on to bigger and badder things like conditionals. You know how to add numbers in Excel (if you're thinking: "I do?", then check out this post), now it's time to subtract, multiply, divide, and raise to powers.

In the addition post, we explored in excruciating detail the differences between using an Operator and a Function to accomplish your task. In this installment, we'll do the same for the other arithmetic activities in a less excruciating fashion. Download "Arithmetic" right here to start exploring.

Here is your Operators starting five:
+ Addition
- Subtraction
* Multiplication
/ Division
^ Raise to Power

Here is your Function starting four:
SUM Addition
PRODUCT Multiplication
QUOTIENT Division
POWER Raise to Power

If you've read any other posts, you'd probably assume I'm just going to talk about how awesome Functions are, and how they should be used in all cases to solve arithmetic problems. You would then be surprised to learn that in some cases Operators are actually better than Functions (even though it breaks my hear). If you take a look at the Arithmetic sheet, you'll see which functions are good, and which one is absolute garbage.

Before we close up shop on this brief post, here is a broad run-down of how arithmetic works in Excel, for those of you who haven't heard the term "Order of Operations" in more than twenty years. Order of Operations is PEMDAS, Please Excuse My Dear Aunt Sally, or whatever mnemonic you favor to remember that it goes: Parentheses, Exponents, Multiplication, Division, Addition Subtraction. Excel adheres to this rule and all of the other fun rules/properties you don't remember (associative, commutative, um . . .). Por Ejemplo:

(2+3)*4 = 20
2+3*4=14
(2+3)^2*4=100 (had to verify that one)

Things can get confusing with parentheses, because in addition to being used for mathematical operations, they also are involved in functions. So if you did some hybrid function, such as:

=SUM((2+3)^2,(3+4)*2)

Excel will solve it like this:

=SUM(5^2,7*2)
=SUM(25,14)
=39

Make sense? If not, play around in the worksheet and try out some different things. Remember, instead of using hard-coded numbers like I did here, do it using references as I do in the worksheet. Also, if the ideas aren't sticking, I recommend going back to the addition post, which really lays out the concepts underpinning this somewhat cursory explanation of arithmetic functions in Excel.

Way Back to School: Simple Addition Excel Style

Welcome back to grade school. Unfortunately, you will receive no virtual gold star stickers for good performance, but on the bright side, you will not have to sit in timeout or miss recess if you don't do your homework. You are old enough and motivated enough to do your homework for the sake of self-betterment, right? RIGHT?

Before getting too far along, let's download the "Addition" worksheet here.

With that we move on to adding numbers together. Excel provides two basic ways of adding number together: the operator '+', and the function SUM. I'm assuming that you, the reader, know how to add two numbers together (if not, no recess for you), thus I am going to focus on how to transfer your knowledge to the spreadsheet.

Operators:
Using an operator is the most intuitive way to perform arithmetic functions. You basically write it as you see it:

Excel: = 2 + 5
Your Brain's version: "what is 2 plus 5?"

Okay, so maybe it's a bit different. The '=' sign always goes in the front (even though the formula in your head is "2 plus 5 equals 7"), I cannot stress this point enough. Without the '=', Excel assumes you are just writing "2 + 5" for the fun of it. And trust me, you are not writing "2 + 5" for the fun of it. This form is intuitive because it's almost exactly as you would type it into a simple calculator (which they didn't tell you about until middle school). Again, with the difference that the '=' goes at the beginning, rather than the end.

Functions:
Okay, this is not quite as intuitive a translation, but is more along the lines of how you do things in Excel's language. Let's try the same example:

Excel: =SUM(2,5)
Your brain's version: What is the sum of the following numbers: 2 and 5?

This version is more efficient than the previous translation when dealing with a Series of numbers. A Series is a list of numbers or references, separated by commas. The SUM function can take a Series as its Argument. An Argument is the information you feed into a function, that allows it to come up with a solution. You can also call Arguments "Parameters", which is probably more accurate, but also more scary, so I will stick with the word Argument. For example, if you just wrote SUM(), Excel would answer with a blank stare, because it does not know the numbers you want to sum up. However, if you type =SUM(2,5), Excel knows that you want to add 2 and 5 together, and smiles at you. The SUM function takes a variable number of Arguments, depending on how many numbers you want to add together. Most of the Arithmetic functions work this way, but most of the more complicated functions take a fixed number of Arguments. This may not make too much sense right now, but don't worry about it, it will become clear over time what exactly we mean by an Argument.

In addition to a Series, SUM can also take a Range as an argument. A Range is a continuous group of references. These are examples of Ranges:

1. A1:A4 (includes cells A1, A2, A3, A4)
2. A1:C3 (includes cells A1, A2, A3, B1, B2, B3, C1, C2, C3)

Notes about Ranges:
1. Always Rectangular: The first reference defines the top left corner of the range, the colon signals that you are creating a range, and the second reference defines the bottom right corner. Excel takes this information and creates the associated rectangle.

2. Can use Absolute or Relative References: For a review of how these references work, check out this post. This is only important if you intend to fill your function or copy and paste it (which I always encourage). $A$1:$A$4 will always refer back to those cells, no matter where you fill or copy and paste the function. This is not true of A1:A4, which is a relative range, and will move around if you start filling or copying and pasting your function.

For example, if you put =SUM(A1:A4) in cell A5, and then fill it down to cell A6, you will get a different answer, because Excel translates your function to =SUM(A2:A5) in A6, which will be larger than =SUM(A1:A4). In contrast =SUM($A$1:$A$4) will always take the sum of those four numbers, no matter where you put it.

You can also use any sort of combination of relative and absolute references mentioned in the aforementioned post.

Back to SUM
The biggest advantage of SUM over '+' is that SUM can take a Range as an Argument. So if you want to add all the numbers in Column A from Row 1 to Row 100, instead of typing each number, which would be spectacularly awful, you can simply do =SUM(A1:A100) and let your buddy Excel do the rest. Don't you wish you knew how to use it in third grade?

In most circumstances, you will want to Sum a Range. I encourage you to do your own scratchwork on the worksheet I provide above, just to get used to doing it. Because that's what you would have had to do in grade school.

Turbocharge your VLOOKUP with MATCH

Did you look at the VLOOKUP post and think, "Well, that's nice, but something about that col_index_number argument seems a bit crappy?" If so, you are DEFINITELY ready for the Jock curriculum. If this thought didn't cross your mind, that is perfectly reasonable; please read on.

Because Excel data tables are two dimensional (rows by columns), lookup functions are also two dimensional. VLOOKUP is dynamic in the vertical (rows) dimension - based on the lookup_value, the VLOOKUP will return different answers. As you change the value in the reference cell, the answer will change.

But what if you want to make it dynamic horizontally? The simple VLOOKUP is static horizontally, based on the col_index_number argument, which takes a simple number. As discussed, this number tells the function how many columns to the right the information you are seeking is located. What if instead of counting columns on your data table each time you write a VLOOKUP, you would rather let Excel do the work? This is where the MATCH function comes in handy.

The MATCH function finds the relative position of a value within a range. The reason it works so well with the VLOOKUP is that MATCH returns a simple number, which is exactly what VLOOKUP wants for the col_index_number argument. Here is what the function looks like:

MATCH(lookup_value, lookup_array, match_type)

For the lookup_value, you feed a reference for the value that you're looking to match, just as with VLOOKUP. The lookup_array is just like table_array in VLOOKUP, but instead of a large rectangle, the range will be only one row tall. The match_type argument is similar to the range_lookup in VLOOKUP, except instead of "false", we use the number zero for an exact match (the reason for this will make at least a little bit of sense in a later post when we deal with approximate matches, but for now, let's look for exact matches).

Tips:

1. The trickiest part of using MATCH with VLOOKUP is the lookup_array argument. The col_index_number will tell the VLOOKUP how many columns to the right to look relative to the first column. MATCH returns the number that represents how many columns to the right the lookup_value is, relative to the range you create. In order for this to work, the first column in both ranges must be the same. If your VLOOKUP table_array starts in column C, your MATCH range needs to start in column C, or else your results will be horrendously wrong.

2. I take it back, the trickiest part of using MATCH with VLOOKUP is getting your relative and absolute references working correctly. Because you are going to fill the function both down and across, things are going to get a bit messy. For review on using absolute and relative references, check out this post.

Let's kick that electricity analysis up a notch and take a look at some trends over time. Download VLOOKUP with MATCH here.

A Note about Levels:

Excel for Poets wants to make sure your knowledge-building quest is taking place in a reasonable order. Because I don't create posts in any rational order, I've tried to aid this process by tagging posts based on four "experience" or "degree of difficulty" levels. Here is my attempt to rationalize their meanings:

1. Beginner: You read the Welcome post and said, "yes, that's me, this is going to be awesome" (or, "yes, that's me, this blog is lame, but may actually come in handy"). You've probably encountered Excel, but you also probably struggled through making a chart for school and may not know how to add two cells together. The Beginner posts focus mainly on foundational concepts, such as filling and references that will be assumed knowledge in later posts. In addition to this, rudimentary functions will be addressed, but nothing fundamentally more complicated than the SUM function.

2. Intermediate: You either graduated the Beginner course or came in knowing the basics. You probably have some knowledge of Excel, but it probably isn't well-organized. By this I mean that you encountered a specific problem and either learned or figured out a specific solution without necessarily understanding the underlying logic of it. For people who consider themselves "intermediates" I would recommend taking a quick look at the beginner posts, just in case anything slipped through the cracks. In terms of content, the Intermediate lessons will probably be the most useful as a reference source. I will go through a large number of functions and demonstrate how to use them, and hopefully in realistic situations where they will be useful. The limit is that each function will be used in a plain vanilla, straightforward way.

3. Excel Jock (or just "Jock"): You know the functions, and came not to learn the basics, but to up your already formidable game. Maybe you came just to point out that there may be a better way to do something than the way I did it. In that case, I challenge you to a duel (and invite you to make a mocking, scathing comment). The Jock posts will broadly cover optimized spreadsheet design and accomplishing the goal of truly separating your 'hard-coded' or downloaded data from the badass calculation/presentation engine you've created. The name of the game will be flexibility in both data aggregation and location through aggressive use of conditionals, power lookups, conditional sums, and maybe dynamic ranges (haven't decided if this is Jock level or . . .

4. All-Madden: BOOM! You're Jocky enough to get voted to the All-Star team. The All-Madden posts will predominately explore the use of Array formulas to establish complete and utter supremacy over your data. In addition to arrays, there will be some arbitrary level at which a Jock formula might become a little too monstrous and move into the All-Madden category. Things such as embedded dynamic ranges and multiple OFFSETS might qualify. I also want to make it clear that I will not be covering any VBA or macro stuff, and all the functions will work in either Excel 2007 or 2003. There will also be scant coverage of graphs/charts and pivot tables (even though they're totally awesome). We'll see how it goes.

Getting Our Hands Dirty with Absolute and Relative References

Fill it Down!

Now that we've gone through some of the basics of what kind of information Excel can handle (if you don't know what I'm talking about, then check out this post), let's dive into making your sheets sing. I talk a lot - and will continue to, despite all your protests - about how the beauty of Excel is its elegance and flexibility. Even though it may not be as appealing as a gymnast with fine, discerning taste, a well-constructed sheet can help you save time and look good while doing it.

What I generally mean by flexibility and elegance is that a single function can be quite general and do exactly what you want it to do in a variety of situations. The task of 'Filling' is central to this ability. 'Fill Down' is one of the most important and basic tasks you can do on a spreadsheet. The idea is that you write a formula in one cell, and then instead of writing it over and over again (or copying and pasting it, which can also work), you can just drag it down as far as you want and it will perform its calculations.

For example, let's say you have two columns of numbers that you want to multiply together in a third column. You can do that very easily by performing the following:




In the first cell, I typed the formula to multiply 11 by 4. After hitting Return to finish the formula, I used the 'fill handle' (the little cross in the bottom right corner) to drag the formula down so that it would perform the same calculation for the whole range. In addition to filling down, you can also use the fill handle to fill to the right (very common), left (unusual), and up (highly unusual).

Absolute vs. Relative References

There is an inherent ambiguity to the idea of filling. When you drag that formula down, how does Excel know that you don't want to multiply 11 by 4 in all of the cells? Maybe you do want to do that. Maybe you want to multiply each of the cells on the right by 11 (the first cell on the left). This is where the distinction between absolute and relative references comes in, so listen up.

By default, Excel assumes you want to use relative references. This means that Excel interprets the formula I put in the third column above as this, "Two cells to the left times one cell to the left." This way, as I fill down, the interpretation remains the same, but the values themselves change. If I used absolute references, Excel would think, "B3 times C3". In this case, as I fill down, the interpretation and the values remain the same, because no matter where it goes, B3 times C3 equals the same amount. If I changed the value in one of those two cells, it would change the product in every cell I filled in.

You can also use combinations, such as "B3 times one cell to the left". As you fill down, this would multiply each cell in the right column by the top value in the left column. If you changed a value in the right column, it would only affect only one product; however, if you changed the value in cell B3, it would change every product.

Now that we've gone through what it means, here's what it looks like in Excel:

Relative reference: B3
Absolute reference: $B$3

It's that simple, dolla dolla bills. You can type them in by hand, or after you type in B3 you can hit the f4 key on your keyboard, which is probably a quicker way to do it.

Last Step in this Mammoth Post: Splitting the Cell

Each reference, rather than being a whole unit, is actually the combination of two smaller units, the Row Reference and the Column Reference. As you may have guessed, you can hold one of them Absolute and make the other Relative. Here's the list of the four reference combinations:

1. Relative Row, Relative Column (default): B3
2. Relative Row, Absolute Column: $B3
3. Absolute Row, Relative Column: B$3
4. Absolute Row, Absolute Column: $B$3

In general, the Relative/Absolute nature of the Row reference will determine how the formula will behave when you fill it up or down and the Relative/Absolute nature of the column reference will determine how it behaves when you fill left or right. The Absolute and Relative references sheet will allow you to get your hands dirty with this knowledge and try a sick challenge set.

Next up for the beginners, we deal with our first real functions. Who's pumped?

VLOOKUP: the gateway function

Welcome to the intermediate graduation course. Learning VLOOKUP is like having your Excel Bar or Bat Mitzvah - you are supposed to be an adult, but in fact, you are only 13. Perhaps a more apt analogy is to Marijuana: the gateway drug. Because once you really get high on VLOOKUP for the first time, you are in serious danger of seeking that high from more and more potent functions. I am honored to be your Excel drug dealer.

It is hard to explain in words why the VLOOKUP is so crucial, but I will try. The function is used to find specific information (rather than summarize a large amount of data) within a table with unique indentifiers. Nevermind, I'm going to stop trying because that sounded really lame.

This is what it looks like:

=VLOOKUP(lookup_value,table_array,column_index_number, range_lookup)

Where:

1. lookup_value is the value that you want to match in the first column of the table_array. Your lookup_value should generally be a reference, and Excel will assume it is a reference unless you surround it with " "s. So VLOOKUP(texas, . .. ) will not work, but VLOOKUP("texas" . . ) will work, provided that Texas is a value that is in the far left column in the table_array.

2. table_array is the range in which your data is found. It must be a continuous rectangle and can be as big as you want, but the lookup_value must be located in the column to the far left.

3. column_index_number is the number of columns from the left that contains your data. So if your range is $b$2:$f$50 and column E has the information you're looking for, the column index number will be 4.

4. range_lookup should always be set to FALSE, which means that you are looking for an exact match. If you fail to do this, there is a high likelihood that you will get an unexpected result.

Specifically, the function looks for an identified value (lookup_value) in the first (farthest left) column of a defined table (table_array), and then returns the value a specified number of columns to the right (column_index_number). Okay, it's much cooler than it sounds.

The biggest advantage of VLOOKUP (and lookup functions in general) over using simple references (such as =g3) is that it is highly flexible. This flexibility derives from the ability to use a reference to define the lookup value. This means that you can change the value in the reference cell, and the VLOOKUP will automatically bring back the corresponding value, and it doesn't even matter what order the actual data is in. You can also copy and paste the formula (as long as you get the relative and absolute references right) all over the place and it will work.

Tips:

1. VLOOKUP will very rarely give you a wrong answer, but it will give you a frustrating #N/A error if it cannot find the lookup value in the first column of the table_array.

2. VLOOKUP will often give you an unexpected answer (but not an error) if you don't remember to put 'false' as your fourth argument, SO REMEMBER TO DO IT.

3. Sometimes #N/A errors occur because you either filled down or pasted a VLOOKUP where the table_array isn't absolute ($b$1:$c$5). If you forget to make the table_array absolute, the references will change as you fill down, making it likely that a lookup_value you are seeking is no longer in the table_array range. If you have a PC, the f4 key will cycle through the different absolute/relative combinations to save some time. If not, you can manually type the $s where they belong.

4. If there are two or more identical values in the first column of the table_array, a VLOOKUP will only return a value for the row that is closest to the top. This is why the VLOOKUP is only really useful for data that has a unique identifier. A VLOOKUP cannot take the sum of values that have the same identifier (for this, you would want a SUMIF function).

5. VLOOKUP is not case sensitive.

In the sample file below, I've provided a table of Electricity Generation by state for 11 years, between 1995 and 2006. Each year has its own column, and the states are listed in the rows. As it is, the data is essentially useless, and it is very difficult to make any conclusions. But you can make sense of this all using VLOOKUP. So go on and download that puppy and do the data a favor.

Download VLOOKUP Lesson

What is a reference?

Excelforpoets is adamant about using references whenever possible. The word will come up over and over again, relentlessly clearing your brain of any possible negative notions you might have had concerning the word "reference". Without references, Excel is no better than a fancy calculator (and trust me, Excel is far superior to a fancy calculator). Even though this may be one of the most basic Excel concepts, it is often passed over as assumed knowledge. Excelforpoets has only one assumption: you have come here to up your game. What follows is a list of the different types of information you can put into a cell.

1. String: a string is anything that is not a number or a reference. Most strings are just plain text. A string can also be anything that you surround with " "s. The frequent use (and important nature) of " "s in Excel creates explanatory ambiguity, so as a convention, '25' or 'A3' will indicate that you should use no quotes when actually typing it into a cell, and "25" or "A3" will indicate that you should use double quotes when typing it into a cell. Glad that's out of the way.

2. Number: you know what a number is. You can perform all sorts of operations with numbers. An important thing to remember is that as soon as you add anything to a cell that is not a number, it will no long act like a number, but will become a string. For instance, you can't multiply '25 lives' by '50 lives' because they are both strings, but you can multiply '25' by '50' because they are numbers.

3. Special Numbers: Sometimes Excel acts cute and thinks it knows what you're thinking. One of these situations is when you put what Excel believes is a date in a cell, such as '4/20'. I'll talk more about the bewildering things Excel does to dates in a later post, but for now, just be aware that sometimes Excel will do strange things if you put in dates.

4. Booleans: TRUE and FALSE hold a special place in Excel's heart and are not considered mere strings (unless you type "true" or "false"). Despite Excel's protests, we are going to ignore them until we explore the IF function, and conditional arrays later on.

5. References: The beauty of Excel is in reference text. The spreadsheet is set up with numbered rows going down and lettered columns going across. At the intersection of each row and column is a cell, which is labeled with that letter and number, such as A4 and B30. If you type 'Donkey' in cell A3 and '=A3' in cell A4, you will end up with 'Donkey' showing twice. In this way, the reference represents the data that is in the cell. In order for text to act as a reference, you must put an '=' in front of it, which signals that the following is a function (in this case the most simple function). If you don't put the '=' in front, the text in A4 will not read "Donkey", but "A3". Going forward, I will refer to the "reference cell," which means the cell that has the data you want to reference in a formula (in the 'Donkey' case, A3 is the reference cell and A4 is the destination cell). To create a reference in Excel, you do not need to type 'A3' in the destination cell, but can instead type '=' in the destination cell and then click on the reference cell - Excel will know what you mean.

Welcome!

After years of liberal arts study where you were encouraged to think creatively and express your wildest opinions (so long as you could support them), you entered the "real world" and had a crappy spreadsheet set out in front of you by someone who probably doesn't have your impressive academic pedigree.

That day sucks.

I aim to make that day suck less by teaching you the foundational lessons of excel that will save you time/frustration and impress your superiors who resent your perceived snootiness. Even if you don't use Excel at work, I promise that the lessons will help you create an awesome budget model for yourself, and who doesn't want to have a sweet budget?

Excel is a language that has its own unique syntax and capabilities, but unless you can understand it, you won't be able to unlock its potential. If you can understand the basic concepts and speak the language well enough that the "Help" articles are actually useful and enlightening, then I have achieved my goal and you are ready to rock.

The posts that follow will target beginners, experts, and everyone in between. Each one will be tagged with some indication as to how difficult the concepts are, and whether there are "prereqs."


Good luck and godspeed.