In this Excel formulas invoice interactive, use data validation in Excel to help solve a real-world shipping problem.
If we don't type the exact name of the shipping option, the VLOOKUP function stops working. Think you can fix this? This is the last thing I'll ask for, I swear!"
This lesson is part 5 of 5 in a series. You can go to Invoice, Part 1: Free Shipping if you'd like to start from the beginning.
Once you've downloaded our spreadsheet, open the file in Excel or another spreadsheet application. Cell E7 is using the same VLOOKUP formula we added in Invoice, Part 4: More Shipping Options, but it's no longer pulling in the Shipping Cost correctly. And since the formula in cell E9 depends on that value, it's not able to calculate correctly either.
If we look at cell E6, we can see that the word Standard is misspelled. The Shipping worksheet doesn't contain the word "Standrd", so our VLOOKUP function doesn't return any results. While we could simply type in the correct spelling, that wouldn't prevent someone from making the same mistake in the future. To make sure this doesn't happen again, we can add data validation to our spreadsheet.
Data validation allows you to control exactly what a user can enter into a cell. In our example, we can use data validation to ensure that the user chooses one of the three possible shipping options. To make things even easier, we can insert a drop-down list of the possible options.
This kind of data validation allows you to build a powerful, fool-proof spreadsheet. 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. Depending on what spreadsheet program you're using, the process of adding data validation will vary. We'll show you how to add data validation in Microsoft Excel and Google Sheets.
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. But before we do this, we'll actually need to name the cell range first. Naming cell ranges is one way to keep track of important cell ranges in your spreadsheet.
If you ever add more data to your spreadsheet, you can easily check all of your named ranges with the Name Manager to make sure they are including all of the data.
Creating a drop-down list for Google Sheets is actually a little simpler than Excel, because you don't need to name a cell range beforehand.
There we go—with our new data validation drop-down list, it'll be a lot harder for someone to break our function in the future!
You know, it's amazing to think about how much this spreadsheet does now! Some of our competitors pay for expensive invoicing software, but you've created something really powerful with just a few well-placed spreadsheet formulas."