Goal Seek: Finding the Perfect Number

goal1Have you ever wondered, “I wonder what X needs to be in order for Y to be 50%?” Good news! Excel thought of that as well! If you know what a certain results needs to be (i.e. profit margin needs to be 35%) but don’t know what input value (i.e. Sales) will get you there, then use Goal Seek to find it for you. It sure beats trying to guessing 100 iterations in order get there yourself. Here is how it works.

You will notice on my income statement below that my profit margin (Net Income/Sales) is 26%.

goal2

I now want to know what sales need to be in order for my profit margin to be 35% (holding everything else constant). On the Data tab, you will see What-If Analysis. Click on that and select Goal seek.

goal3

A box will now appear. In our example we are going to Set Cell C12 (our profit margin), To Value 35%, By Changing Cell C3 (our sales).

goal4

Note that the cell you choose for By changing cell needs to be a hard coded value (no formulas). After I press OK, I see that I need to have sales of $623,049 to get to a profit margin of 35%. Pretty neat!

After you show your boss Goal Seek, you are sure to get a promotion, pay raise, and have all of your hopes and dreams fulfilled. Stay tuned for our next Excel tip!

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!

Extreme Filters & Sorting

filters1Most people know how to use the basic Excel filter. However, do you know how to “extreme filter and sort”? Let’s take your knowledge one step further and show you some neat functions of filters you probably did not know about.

1) Sort by Color: Let’s say your boss goes through a list and highlights all the people he wants to meet with in the next week. What is the best way to sort through a list of over 500 employees? Easy, just filter it by color! First create the column filters.filters2

Next, click on the column filter arrow, find the Filter by Color option, and select the color you wish to filter. If your boss selected multiple colors, you can also select the Sort by Color option on that same drop down menu.

filters3

2) Custom Sort: Most of your friends probably only know how to use the simple Sort function. However, that tool is very limited. The Custom Sort tool is much more powerful. You will find it on the Home ribbon – Editing – Sort & Filter – Custom Sort

filters4

To get started, highlight the table you would like to sort. Make sure you include the headings. Next, select Custom Sort on the Home ribbon. A box like this will appear.

filter5

The Sort By drop down will allow you to select which heading or column you want to sort. You can then select what you want to Sort On (usually just Values) and the Order you want it. For this example, We want to sort by ID Number from Smallest to Largest.

filters6

You can even add multiple sorting criteria. You do this by clicking on the Add Level button. Now I can first have it sort by ID Number from Smallest to Largest and within that, sort by Last Name from A to Z. That is pretty neat, huh?!

filters7

Now you can sort and filter like no one at work can! This is sure to save you time, organize your data more efficiently, and, most importantly, impress your co-workers. Stay tuned for our next Excel tip!

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.

Group, Don’t Hide, Your Data

Group1Most Excel users know (for better or worse) how to hide rows and columns. However, many times this is a brutish way to hide or organize your data, especially if you want to see that data again. This is where the power of grouping comes in.

Basically, grouping allows you to quickly hide/unhide/organize your data, especially large sets of data. Let me explain by using an example. Let’s say you have the following financial statement.

group2

On this financial statement, you only want to see Total Revenue, Total Expenses, and Net Income. To group (hide), first select the rows you want to group. In this case, rows 9-12. Go to the Data tab -> Group -> Group. A box will now appear to the left of your column.

group3

If you click on the box with the line through it, it will collapse (hide) your columns. Now repeat this process for rows 14-17. Look how clean that looks! Best of all, now you can easily hide/unhide the rows/data!

group4

Also, note that you can group columns as well. And, if you want to go really crazy, you can nest groups. For instance, you can group columns 9-18 then group columns 9-12 and 14-17 like we did above. To ungroup, simply highlight the rows and go to the Data tab -> Ungroup -> Ungroup. Or to eliminate all of your groups (atomic bomb), click Clear Outline.

group5

group6

There you have it! You have just become a more sophisticated Excel user. And, you now know how to better organize your spreadsheets. Good luck, my fellow Excel nerds!