Blog


← Back to BLOG

06
May
2015
Using Drop Down Menus in Excel Formulas

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

Testimonials

  • "Thanks so much again for taking care of everything in such an expedient manner. It's a pleasure to work with navitend and its staff as always!"

    Lawrence Wolfin / Textol Systems, Inc.
  • "Thanks so much!  You are a class act!  
    You and your team have really done an excellent job on this!"

    Steve Van Ooteghem, The C12 Group in Houston, Texas
  • "We've dedicated our lives to growing our retail and ecommerce business and it's a relief to have found a company like navitend who treats our business likes it's their own. navitend's personal approach to project management and problem solving are top-notch."

    Stamatis, Co-owner Twisted Lily, Fragrance Boutique and Apothecary
  • "I appreciate that they didn’t just build the application. They made it better by bringing ideas to the table that not only made for a better user experience, but also kept the development costs down."

    Andy Lynch / North Star Marketing
  • "navitend has been a great IT partner for our company.  Their helpdesk response time is the best I have experienced in my 30 year career.  navitend has helped me to have great IT services without the need to have a full time, in house, technician at significant savings to our company."

    Bob Bradley, President, Bradley Graphics
  • “Navitend’s expertise helped our firm over the past year to effectively elevate our I.T. game, powering our website into a highly interactive tool. Well done to Frank and his team!”  

    Chuck Steege, CFP®, CEP, President, SFG Wealth Planning Services, Inc.
  • "Our company is more efficient and has grown as a result of navitend’s work. navitend helped us get to the next level."

    Greg Niccolai / Madison Insurance
  • "I look forward to working with you again in the future. Once again, thanks to your organization for your prompt response."

    Luke Wolters / Luke Wolters Tax Consultants