That's where multiple, or nested, IF functions come in handy. However, sometimes you need to work with situations where there are more than two possible outcomes. sales are greater than $1000) and calculate a different value for each outcome. Go to the Formulas tab, and then click Define Name.The IF() function in Excel allows you to evaluate a situation which has two possible outcomes (e.g.In this example, we'll select the cell range A2:A10 on the Shipping worksheet (we're including empty rows just in case more shipping options are added later on). Select the cell range you wish to name.Naming cell ranges is one way to keep track of important cell ranges in your spreadsheet. But before we do this, we'll actually need to name the cell range first. Since we already have a list of shipping options in the Shipping worksheet, we're going to tell Excel to use the data in that list to control which values a user can select. We'll show you how to add data validation in Microsoft Excel and Google Sheets. Depending on what spreadsheet program you're using, the process of adding data validation will vary. Since users won't have to type in data manually, the spreadsheet will be faster to use, and there's a much lower chance that someone can introduce an error. This kind of data validation allows you to build a powerful, fool-proof spreadsheet.

To make things even easier, we can insert a drop-down list of the possible options.

In our example, we can use data validation to ensure that the user chooses one of the three possible shipping options. Data validationĭata validation allows you to control exactly what a user can enter into a cell.

To make sure this doesn't happen again, we can add data validation to our spreadsheet. While we could simply type in the correct spelling, that wouldn't prevent someone from making the same mistake in the future. The Shipping worksheet doesn't contain the word ' Standrd', so our VLOOKUP function doesn't return any results. If we look at cell E6, we can see that the word Standard is misspelled.

Vlookup with drop down mac excel 2013