Create drop down price list in Excel

Dropdown-style user interface controls are fairly common in online forms. This type of controls help with data organization and help users enter data faster by limiting the users to a set of predetermined options. Dynamic dropdowns take the user experience one step further and filter the list of options based on the user selection in another control. In this guide, were going to show you how to create a pricing application with multiple products in Excel.

Download Workbook

Data of a pricing application

In this example, we are going to be using a pricing list for a liquor store. We have a long list of items that are each of a different size, brand, type, and cost. Press the button above to download the spreadsheet template.

Although the first two steps are optional, we recommend following them regardless to reduce your work for later.

Preparation

Start by giving names to the cells containing your product dropdowns. You can easily assign a name to a cell by typing the name into the reference box when the cell is selected. In the following image, you can see the names we have already assigned.

Moving forward, you don't need to use the cell references like C3 or D5. Instead, you can use the cell names like Description1 and Brand1 to specify cells for the description of the first product and the brand of the third.

Next, convert the data table including all tiers of a product into a table. An Excel table comes with many advantages like easier formula creation, as well as an automatically resizing table range.

You can create a table by pressing Ctrl + T when a cell in the data range is selected. For further information, you can visit: Tips for Excel Tables.

Formulas for pricing application with multiple products

Top category [Description]

Since the highest category is the Description in our table, we need to create that control first for our pricing application. The top category doesnt need any filtering. However, there are the multiple items in the lower tiers [under Brand and Size]. Since this list contains duplicate names, it will look much better when converted into a list of unique items.

If you are a Excel 365 user, this is fairly easy to do using UNIQUE function. The UNIQUE function can populate a list of unique items from a given list.

We also recommend using the SORT function to create a sorted list of unique items.

Dynamic array functions, like UNIQUE and SORT, can populate [spill] the following cells automatically. Thus, a single formula is enough to generate all values of unique and sorted list.

Once the list is ready, we can convert it into dropdown items.

1. Select the cells you want to convert into a dropdown.
2. Follow Data > Data Validation in the Ribbon.
3. Set Allow to List.
4. Select the first cell of the list which is automatically spilled and enter a dash character [#].
5. Click OK to create the control.
   

The dash character [#], as known as spill operator in Excel, determines the spill range, starting with the cell containing the formula. Thanks to the spill mechanic, we do not need to use complex formulas when creating dynamic ranges.

Lower tiers [Brand, Size]

The lower tier categories are dependent on those in the higher-level. Each category should be filtered based on the selections made before. This type of filtering can be done using the FILTER formula by providing the range we want to filter and criteria.

Second Tier [Brand]

For example, Brand column should be filtered based on Description selection in the user interface sheet.

To generate a list of Brands based on the first products description cell, the following function can be used:

Note that instead of a cell reference, the formula contains the Description1 named range. When the name range is combined with the table references, the formula becomes easier to read. The formula filters Brand column in Table1 based on Description1 values in the Description column in Table1. The empty string [] in the latest argument determines what will be display if the filtering doesnt return anything. You can enter a more informative message here if you'd like.

The FILTER function spills the return values automatically. Combining the FILTER function with the SORT and UNIQUE is our next step, because the Brand column contains multiple occurrences for the different size options of the same brand.

Once the list is ready, we can use it as the dropdown options. However, this list belongs to the first product. Thus, this list should be bound to the Brand1 cell only. This is the step where pricing for multiple products can be incorporated.

To populate the brand­ dropdown list for other products, the formula should be copied and modified to check the description of the corresponding product. Lets see what we did for the second product.

1. Our first step is to put a placeholder header for the third product [and any other lower tiers].
2. Next, we copy the headers, and the formula for the higher tier to adjacent columns.
3. Our final and the most important step is to modify the formula which refers to the description of the second product , Description2.
4. Repeat steps 1-3 for each product on the list.
   

Third and lower tiers [Size]

The challenge in creating the lower tiers is adding multiple levels into the FILTER function. While the Brand list only depend on the selected Description, the Size list is dependent on both the selected Description and Brand values. As a result, both selections should take place in the FILTER function. This field needs to point to two condition arrays:

• Table1[Description]=Description1
• Table1[Brand]=Brand1

Logical values can be combined using the AND and OR functions. In Excel, these operations can also be done using the product [*] and plus [+] operators use the product operator [*] for AND, and the plus operator [+] for OR. For our example, combining is done using an AND operation. Thus, our formula is:

We avoided using the SORT function here since this is a Size list. Feel free to include it if your data set is suitable.

You can modify this formula by adding conditional arrays for more tiers. Such as,

Copy the lower tier formulas for other products by updating the product references.

Remember to populate the dropdowns for lower tiers.

Pricing application with multiple products on a web application

With the dynamic dropdown controls in your form, the next step would be converting it into a web application and share it online with your users without actually sending them spreadsheets.

Embedded below is the product selection tool from our example. Feel free to play around with it and see the pricing totals update as you change your selections.