Time Saving Advanced Excel Tips To Make You Pro

Number 1 - Goal Seek. Goal Seek is an advanced function in Excel and is part of the What-if Analysis tools. In this example say we have a number of items that we sell. We know how many the quantity that we've sold of the first two items and the price each and what the total dollar amount is but for the third item we want to know how many of these we need to sell in order to get the overall total to $6,000. Well, you can punch in numbers right here randomly to try to figure that out and then keep working it until you get to a number that matches the 6000 that you're targeting but you can also use the What-if Analysis to figure it out for you. To use Goal Seek you want to start by clicking on the target cell, go to Data, What-if Analysis, and choose Goal Seek. It fills in the target cell as your Set Cell. The value we're trying to reach is 6000 and the cell we're going to change is the quantity for Item3. When you hit OK it goes through and calculates 197 as the magic number to reach that goal of 6000 total. Just be aware that you can use your imagination to come up with all kinds of scenarios for the What-if Analysis using Goal Seek. It handles very complex solutions.

Number 2 - VLOOKUP. VLOOKUP is a very commonly used tool to find data in a list. In this example I have a list of names with an associated ID. Over here I'm creating a new table and I want to reference the names in this table to look up the ID from this list and fill them in. To do that I want to use the VLOOKUP function. Click on the insert function button and you want to find VLOOKUP in the list. You can type it in, do a search against all and once it's there select it and it brings up the function arguments. To remind you of what you fill in, for each one of these there's a description down here. The lookup value is what value do I want to look up in the list, and that is the name Nancy. The list that I'm going to choose from is the entire list and the Column Index Number is the column that I want to return the value from. In this case  his is column 1 and this is column 2 so I'm going to choose column 2. And finally, the range lookup is either True or False. If you use True it looks  for the closest match. False is an exact match and in almost every situation you want to use False. Hit OK and you can see that it looked up Nancy in the table and found the ID and returned it over here. All we have to do then, let's copy this data down. Now you'll notice right here Carol isn't in the list so it returns an N/A. Now also note that there is an HLOOKUP function which is horizontal instead of vertical so if you're looking up something in a different format you may need to use the HLOOKUP but it essentially has the same parameters. The beauty of the VLOOKUP function is if you change a value it will update it automatically in the table.

So many students had completed our various Software Training Courses and most of them are placed in leading Software/Telecom Companies

Sponsor Ads

• Ad Space Available - Rent it Now!

Report this Page

Created on Sep 5th 2018 18:06. Viewed 350 times.

Share on APSense

Comments