Goal Seek

Goal seek is another function in excel very valuable for you. Use this link to download file or copy below data to your excel sheet. 

Brand	Item	List Price	Discount	Selling Price
Nike	Shoe	$111.99	10.71%	$100.00
Nike	Socks	$21.99		$0.00
Adidas	Shoe	$58.74		$0.00
Adidas	Socks	$16.99		$0.00
Reebok	Shoe	$26.59		$0.00

Here I listed details of some sports shoes and socks selling in a retail website like eBay.com (or Amazon.com). The owner of website would like to give discounts for above items for a limited time. He has an idea about selling price but not about the discount. He used goal seek function in excel which I describe below.

Select the selling price column of any item.

Go to Data tools group in data tab and select goal seek from what if analysis.

There are 3 parameters to set.

1. Select Cell - The cell you want to change. Here us wants to change the selling price cell and already it is selected.
2. To Value - Here we enter the value we want to reach the selected cell. This example site owner takes it as $100.00 for a Pair of Nike shoes.
3. By Changing Cell - Here you have to select the cell you want to change. It is corresponding discount cell for this example.

Click Ok. Excel automatically calculates the values for discount and give the solution.

Click Ok to keep the solution and Click Cancel to reset the excel sheet.

Please make sure there is a link (equation) between selected cell and Changing cell.

Excel Drop Down List to Avoid Repetitive Typing

Adding excel drop down list to your excel sheet is very useful if you are going to use repetitive words or phrases through the rows. Therefore enter below data to excel sheet or download the file from here to learn how we can use it in excel sheets. Assume this data represents the type of mobile phone using by the managers of ABC company.

Mobile Phone

Samsung

Sony

Sony Ericsson

Nokia

HTC

BlackBerry

There are few methods to add excel drop down list to a sheet.

1. Use direct reference to the Values.
2. Using named range as a reference
3. Use comma separated values

Use Direct Reference to add Excel Dropdown List

1. Select the cell you are going to have excel dropdown list.
   
   
   
2. Then Select data tab.
3. Then Click on data validation. Now you have pop up window to validate your data.
4. Then Select settings tab and 'list' from drop down list for allow parameter.
5. After that select the range where you have entered phone makers as source parameter.
6. Finally Click OK.

Use Named Range to add Excel Dropdown List

1. Select all phone models.
2. Then Rename that range as phone. (you can see position where you have to enter new name just above column A)
   
3. After that Select the cell you wants to add drop down list.
4. Then Select data tab.
5. Then Click on data validation. Now you have pop up window to validate your data.
6. Then Select settings tab and 'list' from drop down list for allow parameter.
7. After that Enter '=phone' in to source parameter.
8. Finally Click OK.

 

Use Comma Separated Values to add Excel Dropdown List

1. Select the cell you are going to have excel drop down list.
2. Then Select data tab.
3. Then Click on data validation. Now you have pop up window to validate your data.
4. Then Select settings tab and 'list' from drop down list for allow parameter.
5. After that Enter phone makes one by one separated by comma to the source parameter field.
6. Finally Click OK.

tab.

Then Click on data validation. Now you have pop up window to validate your data.

Then Select settings tab and 'list' from drop down list for allow parameter.

After that Enter phone makes one by one separated by comma to the source parameter field.

Finally Click OK.