From Vertical to Horizontal: Transposing your Lists

vtoh1I remember in college receiving a set of data going down a column. However, I wanted to present it going across the row. How could I ever do this?! I will tell you how. I started from top and one by one copied and pasted each cell. If you are doing this then STOP THE MADNESS!!! Here is how you can quickly transpose data.

First, select the cells you want to transpose and copy it. Now select the cell where you want to transpose the data. On the Home tab, you will see a Paste button. Click on the down arrow to see more paste options. At the bottom of the the drowdown, you will see a Paste Special. Click on that.

vtoh2

A form like the one below will show up. Check the Transpose box, click OK, and BAM! You just transposed your first data set.

vtoh3

vtoh4

As always, join us in the following weeks as we take you on another fun-filled Excel adventure!

Why Your VLOOKUP Is Not Working

vlookup1The two most common frustrations with the VLOOKUP formula is either the lookup spits out the wrong value or you get the dreaded #VALUE. Even seasoned Excel experts can get stumped by this. However, 9.5 times out of 10 the reason your VLOOKUP formula is not working is due to one simple word: FALSE. Let me explain.

Let’s say you have the set of data below with the VLOOKUP formula in cell E2 to look up Mary.

vlookup2

Two things should stick out to you. First, the formula (as most people are concerned) is 100% correct. Second, the result of the lookup should be 34, not 68.

VLOOKUP actually works in a different way than most people think. The formula will find the closest (which very often does not seem that way) match to your lookup criteria. This can produce some whacky results. For instance, the above example picked Billy as the closest match to Mary. One fix for this is to sort your data from A to Z. This will usually fix your problem. If you are too lazy to sort or if that is not an option, all you have to do is modify your formula as follows:

=VLOOKUP(D2,A1:B7,2,FALSE)

When you input the FALSE into the formula, you are telling Excel that you want the VLOOKUP to only lookup values that are exactly the same as your criteria.

Use FALSE on all of your VLOOKUP formulas from now on. I promise it will alleviate you from needless stress and incorrect data. Share this knowledge with your follow co-workers as the greatest Christmas present one can give.

How to Fix Errors: Remove #N/A and other Errors from your Spreadsheet

fixerrors1 If you have eaten food in the last 3 days then chances are you have come across the annoying #N/A or #DIV/0! error in Excel.

Although these errors can be useful to detect possible errors, for the most part they cause spreadsheets to look messy and raise unnecessary questions from end users. Excel has created a very easy way to get around this error. Forget 3 steps, this will only take 1!

Let’s say the formula A1/B1 you copied down Column C has some pesky zeros in Column B which inevitably causes the dreaded #DIV/0!. To fix this, try copying and pasting the following formula instead:

=IFERROR(A1/B1, 0)

fixerrors2

fixerrors3

This formula is saying the following: If A1/B1 causes any type of error, put a 0 there instead of an error message, else, put the result of A1/B1.

You are not limited to replacing the error with a number. You could also put text:

=IFERROR(A1/B1, “You cannot divide by zero!”)

You can use IFERROR for any formula. For instance, here is how you would use it for a VLOOKUP:

=IFERROR(VLOOKUP($A$1:$B$8, 5, 2), 0)

Take a few minutes this week and apply the IFERROR to your formulas!

Get your mother something special next Sunday by giving her a clean-looking spreadsheet. You are bound to be hailed as the greatest son/daughter in the family.

Date Formulas Part II: EDATE() & EOMONTH()

calendarThis is part II of amazing date formulas where we’ll learn about EDATE() and EOMONTH().

EDATE(): This formula returns a date that is a certain number of months before or after a specific date. This is very useful for when you need to calculate a date that falls on the same day a certain number of months in the future or past. In this example, I have an investment that compounds on the 14th day every three months. In cell C9 is the beginning date that we will increment by 3 months.

1

Put the formula =EDATE(B9,C5). This is telling Excel to start at 1/14/14 and increment by 3 months. You can now drag the formula down as many rows as needed.

2

=EOMONTH(): This formula returns the last day of the month. This formula can be used in a similar way as the formula above (except it returns the final day of the month). We’ll use the same example as above, except we’ll use the EOMONTH() function. In cell B9, we’ll put “0” in for Months which returns the current dates end of month.

3

Now in cell B10, we’ll put a similar formula to the EDATE() formula above. This will return the end of month every 3 months. We can then drag this down.

4

Enjoy these amazing date functions and never manually type a date again!

Date Formulas Part I: TODAY() & DAYS()

date1Date formulas can become your best friends, if you just take the time to get to know them. The next couple of posts will be dedicated to ‘various Excel date functions and how to use them to optimize your spreadsheet (and maybe even win the heart of your next date).

 

=TODAY(): Returns the current date. To use this, simply type =TODAY(), hit Enter and Excel will spit out the current date. I use this quite a bit, especially when I’m too lazy to type out the actual date.
date2

=DAYS(): Returns the number of days between two dates. The formula’s syntax is =DAYS([end date], [start date]). For this formula, you must use a cell reference or a date formula. You cannot type “9/1/15” in the formula.

Here is how you use the formula. Let’s say your birthday is in cell C2 and today’s date is in cell C3. In cell C4 type =DAYS(C2,C3) and hit enter.

date3

Or better yet, we just learned how to use the TODAY() formula. You could modify the formula to say =DAYS(C2,TODAY()).

date4

Named Ranges

range1

Have you ever looked at a formula and thought, “What in the world does this mean! What items make up this crazy formula?” Or have you ever found your self constantly linking back to a cell and thought, there has to be a better way? If so, you are not alone. That is why named ranges exist! After you use named ranges, you’ll wonder how you ever got through life before.

Microsoft sums up perfectly why you should use named ranges: they make your “formulas much easier to understand and maintain”. Here is how they work…

By default, each cell has a name that corresponds with the column letter and the cell number. To see the cell’s name, look at the top left hand corner of your spreadsheet.

ranges2

You can change the name of that particular cell to any name you’d like (although you should be somewhat descriptive). In this case, I’ll rename the cell “Age”. To change the name, click in the area where the name appears, type the name and hit Enter.ranges3

Now whenever you refer to that cell, you will see “Age” appear in the formula bar instead of the cell reference.

ranges4

This also means that you can easily refer back to the cell by typing simply typing the name of range. No more going back and forth between spreadsheets to refer to cells!