Using Drop Down Menus in Excel Formulas

Trouble viewing the screencast at work? Try signing up for new issues on our home page with your personal email address and watch the screencast from home.

How to Use Drop Down Menus in Excel for Formulas 

Excel has incredible tools for sorting and harnessing data. In this article we will explore drop down menus and embedded =IF formulas to create a quote for a custom product. Follow these steps:

1. We start by filling out an Excel spreadsheet with our company, client, and quote information. We need to decide what elements of the product are customizable; fill out heading cells for these elements as well. 



2. Next, create the drop down menus. Select a cell for which you want to have multiple values available. Go to the Data tab > Choose Data Validation > Data Validation. 



3. In the Data Validation box that opens, choose Allow: List. In the Source field, enter the available options separated by commas. Click OK. 



Now a drop down menu is in place in the cell to the right of the Base: heading cell.



4. Repeat steps 2 and 3 for the rest of the customization options. 

5. In the price cells, we will create =IF formulas that calculate the price of each element based on the chosen menu option. Basically, we want Excel to read the selection in each menu cell and display a correlated number in the price cell based on what it finds. 

=IF formulas work in the following way: IF a certain logical fact is true, the cell will display a certain value. If that logical fact is false, the cell will display a different value. A simple =IF formula might look like this: =IF(A1>0, "Good","Bad"). In other words, if cell A1 has a value greater than 0, the cell containing the formula will display the word Good. If the value is not greater than 0, the cell will display the word Bad. The logical test, value if true, and value if false are all separated by commas and enclosed in parentheses. 

In the case of the custom umbrella, our formula will tell Excel, "IF the menu cell says 'Solid Plain,' display 10. If not, the cell should see IF the menu cell says 'Solid Floral.' If it does, display 13. If it doesn't, see IF it says 'Open Lattice...'" This "chain" of IF formulas continues, looking for each of the options in the drop down menu.

Formula in cell C12:
=IF(B12="Solid Plain",10,IF(B12="Solid Floral",13,IF(B12="Open Lattice",15,IF(B12="Square",13,0))))

While this may appear confusing, just remember that the value if false will simply lead into the next IF condition. The last IF in the chain should have a value if false of 0. (When Excel does not find any of the menu selections it was looking for, we want the numerical value displayed to be $0.) 

The other formulas used here for the Stand/Base Finish and Fabric are as follows:

Formula in cell C14:
=IF(B14="Light Wood",20,IF(B14="Dark Wood",23,IF(B14="White Metal",15,IF(B14="Black Metal",15,IF(B14="Bronze Metal",18)))))

Formula in cell C16:
=IF(B16="White & Red Striped",22,IF(B16="Yellow & Brown Striped",22,IF(B16="Blue Solid",20,0)))

Keep in mind that when an Excel formula is looking for or displaying words instead of numbers, you need to enclose those specific words in quotes within the formula.

Once these formulas are in place, the cells that show the prices for each element of the patio umbrella will be automatically filled out. 



You can format the price cells to Currency to insert dollar signs before the numbers; you can also use AutoSum to calculate the total price of the umbrella. 


 

If you have an unanswered technical question that you would like to see addressed in an upcoming issue, feel free to email it to social@navitend.com.