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!

Data Validation’s Most Powerful Tool: Drop Down List

Validation2-1Last week we talked about Data Validation and the fun and excitement one could have restricting cells with it. Today, we are going to talk about one specific tool in Data Validation, the List option.

This option is great because it limits what the user can input in a cell while giving them options from a predefined list you create. This can greatly reduce input error and can streamline data input. Here is how you do it!

To start, create a list of items in cells A1 through A5, as shown below.

validation2-2

Next, click on cell B1. Open up the Data Validation menu from the Data ribbon. Choose ‘List’ and under Source type: “=A1:A5”

Validation2-3

Now when you select cell B1, a drop down arrow will appear. When you click on it, you should be able to choose from the list you created in cells A1:A5. You can change this list at anytime and it will automatically update in B1.

validation2-4

And there you have it! Be the life of the party this weekend by showing your friends this cool trick. Stay tuned for our next Excel tip post!

Data Validation: Limiting Others’ Inputs w/o Limiting your Outputs

Validation1Are you ready to learn one of the slickest tricks in Excel? Then read on, my fellow Excelerites! Today we are going to learn about Data Validation.

The goal of Data Validation is to restrict a user to only inputting certain items into a cell. For instance, you can restrict the user to only put whole numbers into a cell.

To begin, select the area that you want to restrict. Next, go to the Data tab on your Ribbon and select Data Validation.

validation2

A form will appear. You will notice that it has 3 tabs on it. Under the Settings tab, select the dropdown arrow under Allow.

Validation3

The default is Any Value. In other words, the user can input any value into the cells. Be adventurous and try experimenting with some of the options. You can start by selecting Whole Numbers. To see if your restriction worked, try inputting a restricted value into your cell. For example, let’s say you restrict cell A1 to be only whole numbers between 1-10. If you try to put a 17 in that cell, an error message will appear.

Speaking of error messages, you can customize your error message.Under the Error Alert tab, you can change the error message Style, Title, and Message. Please note that the Style will determine what type of message and restriction is allowed. For instance, the Stop option will not allow any value outside of the restriction. On the other hand, the Information option will. Both, however, will produce a popup. I created the following error message to pop up if the user tries to put a 17 in cell A1. Because I selected the Information option, the user can still change the cell value to 17 but will get this warning before.

validation4

All of this is very easy to learn. Give it a whirl! Stay tuned for our next post where we delve into the most powerful part of Data Validation: the List!

Dissecting a String: =LEFT() & =RIGHT()

7378048_origExcel data dumps are very great at combining lots of individual pieces of good information into one string. Unfortunately, this makes it very frustrating to the end user who then needs to extract that data. What if we could use formulas to do that dirty work for us…

No doubt you’ve received a data dump that includes an employees ID number, last name, first name, date of birth and dog’s name all in one string that looks like this: 589493CoughranNate01011986Fluffy. Good information but useless in its current form. Let’s learn how to extract that data.

=LEFT()

=LEFT() allows you to extract a certain number of characters from a string, starting from the left-most character. This is useful when the number of characters will remain constant, like an employee number or zip code. The syntax for the formula is =LEFT(the cell you are extracting data from, number of characters you want to extract) as shown below:

left

=RIGHT()

=RIGHT() is the same exact concept except this will extract data starting from the right-most character. I will switch the Raw Data so the employee number is at the end:

right

One thing to watch out for! Since we are pulling a number from a text, the result will be text, and not a number. In other words, if I tried to sum the employee numbers, the result would be zero (0). If you require the extracted data to be a number, change your formula ever so slightly by multiplying it by 1 as shown below:

one_more

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!