Creating an Interactive Pricing Application using Excel’s New Dynamic Array Functions (2022)

Dropdown style inputs are very useful in the sense that their use can expedite the data entry process in most applications. Imagine you want to use this feature to create sequential questions that affect each other, like disabling some options based on the previous selection. A pricing application where you start with higher-level product details, then move onto the specific item is the perfect example for this. One caveat is that Excel lists are typically static and inputs that pull options from a list can’t function dynamically to narrow down its results. Ultimately, using dropdown inputs in Excel like filters won’t work without creating helper columns and reorganizing the lists. You can download our sample workbook here.

You can use the INDIRECT function to update the option list of a dropdown with another list which has a static list if items to create a dependent dropdown. Although this will work in most cases, dealing with lists that contain hundreds of items can prove challenging. The INDIRECT approach is also not viable if the entire data is stored in a single place, such as a database table. Needless to say, if you are working with external sources, database sources will be necessary.

(Video) How to Create an Excel Interactive Chart with Dynamic Arrays

We’ve covered a good example for a database table and multiple dependent dropdowns in Transforming a Complex Pricing List into a Quoting Tool in Excel – Part 1. The pricing application has a table for drink prices that are listed based on type, brand, and size. The columns, except for Description, are tied to the previous columns and selecting an option for each is needed to find the price.

Traditionally, you would need 12 helper columns to manually create unique item lists with the Remove Duplicate feature, possibly across multiple sheets to keep layout clean and tidy. Finally, you would need additional formula work to make the list size dynamic by excluding the empty cells from list range.

Thanks to the new dynamic array features and formulas, you can now do this using 4 formulas. At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it’s ready, the feature is planned for release for Office 365 users.

Excel Table

Begin by converting the data range into an Excel Table. This is not required, but Excel Tables are useful for managing and analyzing data in tabular format. An Excel Table provides the data in a special structure, which includes filtering, formatting, resizing, and auto populating formulas dynamically. Once a range becomes a table, you can use structured references (e.g. Table1[Description]) instead of regular A2 reference. The structured references and auto-sizing ability of tables remove the need to update the formulas when you add or remove data.

To create an Excel Table, select a cell in the table and follow either one of the following paths:

  • Home > Format as Table > Select a style
  • Insert > Table

Confirm the range and header availability in the Create Table dialog and click OK.

From now on, we can use structured reference Table1[Description] instead of manually selecting a range in column A.

Naming Dropdown Cells

This is an optional step. Naming cells or ranges make it easier to write and read formulas, as you won’t have to memorize cell references. We named the cells we used as in the dropdown cells in the sample file. Below is the list of these fields:

(Video) Excel Dynamic Arrays (How they will change EVERYTHING!)

  • Description
  • Brand
  • Size

These names refer to the value selected in the corresponding dropdown. For example, Description named range is equal to "TENNESSEE WHISKEY" in the following screenshot.

Description is the first column in the table. This means it does not have any dependencies. For this type data, all we need is a list of unique items for a dropdown. To take it one step further, you can also sort the values. We’re mainly going to be using the UNIQUE and SORT functions. These functions return an array of sorted unique values from a specified range.

=SORT(UNIQUE(Table1[Description]))

We entered the formula into cell F2. Pressing Enter will populate all values through the column F. This is behavior of dynamic arrays is called spilling.

Now, we need to assign the list to as a dropdown input for the cell Description. This means following the same steps as you would when adding Data Validation list to cell, until the part where you need to enter a reference for the Source field:

  1. Select the cell Description
  2. Follow Data > Data Validation
  3. Select List in Allow field
  4. Enter =List!F2# into Source field

F2# here refers to a spilled range starting from the cell F2. You can refer a spilled range by placing the spilled range operator (#) after the reference of the cell where the formula ends. These types of references work like a structured reference of an Excel Table, and the size is unimportant. If there the spilled range changes, formulas will continue to execute with the correct reference.

Next step is to generate a dynamic list for the Brand dropdown. Now, we’re going to utilize the FILTER formula. The FILTER function returns an array of values after a filtering process. We need the FILTER function to return only the Brands that are listed under the selected Description. For example, if the Description is "GRAIN ALCOHOL", the Brand list should include only "Everclear Alcohol 151".

(Video) Learn about the *NEW* Dynamic Array Functions in Excel (Filter, Sort, Unique etc.)

The FILTER function has two required arguments:

  • array which is the data we want to filter.
  • include which is an array of Boolean values to determine which array values are to be returned. TRUE means the values will be returned, FALSE

Here, we want to list the Brand values, so we need the items in the column Table1[Brand] for the array.

For the include argument, we need to create a conditional test that queries whether the value of the dropdown is in the data or not. For example, Table1[Description]=Description.

The SORT & UNIQUE will help like in the previous example:

=SORT(UNIQUE(FILTER(Table1[Brand],Table1[Description]=Description)))

We entered the formula into cell H2.

The Brand list is now ready, and can be added as a dropdown list by entering =List!H2# for the reference.

The same steps should be followed for the Size list with an addition to the FILTER function's include argument. The difference is, while the Brand list depends only 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:

(Video) Dynamic arrays in Excel | Understanding the biggest change to Excel.... ever | Excel Off The Grid

  • Table1[Description]=Description
  • Table1[Brand]=Brand

Logical values can be combined in two ways: AND and OR. In Excel, these operations can be done using product (*) and plus (+) operators - use the product operator (*) for AND, and the plus operator (+) for OR. For our example, combining will be an AND operation. Thus, our formula is:

=SORT(UNIQUE(FILTER(Table1[Brand],(Table1[Description]=Description)*(Table1[Brand]=Brand))))

This is the screenshot of the formula which is placed in the cell J2.

The last dropdown list is now ready. The Data Validation reference will be =List!J2#.

It is time to get the Price value based on selections for the dropdowns. To lookup the price values based on multiple columns traditionally required using helper columns or an array formula that made things more challenging.

To do this, we’re again going to use the FILTER formula. Once more, we need to extend the include argument content to include the Size selection.

  • Table1[Size]=Size

At this step, we do not need to the SORT and the UNIQUE functions, since we know that the result will be a single value. Here is what our formula will be after including the Size:

=FILTER(Table1[Brand],(Table1[Description]=Description)*(Table1[Brand]=Brand) *(Table1[Size]=Size))

(Video) Build Interactive Dashboards with Dynamic Arrays

Note that we placed this in the same sheet for demonstration purposes, and you might want to move it to a separate area.

FAQs

How do you create a price matrix in Excel? ›

How to create a matrix in Excel
  1. Display the "SmartArt" window. To display the "SmartArt" window, click on the "Insert" tab on the command ribbon. ...
  2. Choose your matrix. ...
  3. Enter your data into the matrix. ...
  4. Design your matrix. ...
  5. Include additional details. ...
  6. Save your worksheet.
29 Sept 2021

How do I create a dynamic function in Excel? ›

Dynamic Formulas begin with &== and are followed by an Excel formula. Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula. You may use most of Excel's functions in a Dynamic Formula.

Does Excel 2016 support dynamic arrays? ›

Backward compatibility: Dynamic Arrays are only available on Microsoft 365 versions of Excel (Current Channel) and are not available on Excel 2019/2016 (or earlier versions).

How do you use an array function in Excel? ›

Enter an array formula

Select the cells where you want to see your results. Enter your formula. Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.

How is a dynamic array defined? ›

In computer science, a dynamic array, growable array, resizable array, dynamic table, mutable array, or array list is a random access, variable-size list data structure that allows elements to be added or removed. It is supplied with standard libraries in many modern mainstream programming languages.

How do you create a price spreadsheet? ›

How to create a pricing sheet
  1. Perform market research. As I mentioned right off the bat, you need to first develop a pricing strategy. ...
  2. Calculate profit margins. ...
  3. Open your spreadsheet document. ...
  4. Create a column for products and services. ...
  5. Create a column for prices. ...
  6. Enter business contact information.

What does dynamic mean in Excel? ›

Dynamic Arrays allow you to work with multiple values at the same time in a formula. This feature enables Excel formulas to return numerous results based on a single formula entered in one cell. The results are also dynamic, meaning that if the source data changes, the results will dynamically update.

How do you add a dynamic value in Excel? ›

  1. Select the text box.
  2. Go to the formulas tab.
  3. Write “=” and the cell address to which you want to link the cell, then press Enter.
  4. Now when the value in the cell changes the text box will dynamically change.
16 Aug 2016

How do I create a dynamic array in VBA? ›

Create a Dynamic Array in VBA

First, declare an array with its name. After that, the elements count left the parentheses empty. Now, use the ReDim statement. In the end, specify the count of elements you want to add to the array.

What's the primary difference between Excel's sort and Sortby functions? ›

The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results. The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array.

What is a dynamic array in C? ›

Dynamic arrays are resizable and provide random access for their elements. They can be initialized with variable size, and their size can be modified later in the program. Dynamic arrays are allocated on the heap, whereas VLAs are allocated on the stack.

How do I find an array formula in Excel? ›

When you select such a cell(s), you can see the braces in the formula bar, which gives you a clue that an array formula is in there. Manually typing the braces around a formula won't work. You must press the Ctrl+Shift+Enter shortcut to complete an array formula.

What is an advantage of using an array to match a value in a range of values? ›

One of the major advantages of an array is that they can be declared once and reused multiple times. It represents multiple values by making use of a single variable. This helps in the improvement of the reusability of code and also improves the readability of the code.

What is dynamic array with example? ›

Dynamic arrays are those arrays which are allocated memory at the run time with the help of heap. Thus Dynamic array can change its size during run time. Example- int*temp=new int[100]; thumb_up |

How do you create a dynamic array? ›

A simple dynamic array can be constructed by allocating an array of fixed-size, typically larger than the number of elements immediately required.

What is an array explain dynamic array with suitable example? ›

A dynamic array is an array with a big improvement: automatic resizing. One limitation of arrays is that they're fixed size, meaning you need to specify the number of elements your array will hold ahead of time. A dynamic array expands as you add more elements. So you don't need to determine the size ahead of time.

What is dynamic pricing example? ›

Dynamic pricing is widespread with ride-sharing services such as Uber and Lyft. In this industry, snowy, rainy, or stormy days and the rush hour dictate the prices (surge pricing) to get extra benefits from these environmental or time-based conditions. The food delivery industry uses follows similar practices.

What is a pricing matrix? ›

Matrix pricing is an estimation technique used to estimate the market price of securities that are not actively traded. Matrix pricing is primarily used in fixed income, to estimate the price of bonds that do not have an active market.

What are the 4 basic pricing strategies? ›

What are the 4 major pricing strategies? Value-based, competition-based, cost-plus, and dynamic pricing are all models that are used frequently, depending on the industry and business model in question.

How do you create a price list for a product? ›

How to make a price list online
  1. Choose the size of your price list. First of all, create an account on Flipsnack. ...
  2. Choose the price list template that suits you most. ...
  3. Customize and personalize your price list. ...
  4. Download, print your price list or share it with your customers online. ...
  5. Publish your price list online.

How do you calculate cost per unit in Excel? ›

For the first item listed below (pencils), this could be done by making the value of the total price (cell D2), the value of the unit price (held in cell C2) multiplied by the number of items ordered (held in D2). This formula would be written "=B2*C2".

How do I create a price list in Photoshop? ›

Click on the image to see it in full scale.
  1. Step 1: Create a New Photoshop Document. Go to File > New, set your options as shown below and hit OK. ...
  2. Step 2: Styling the Background. ...
  3. Step 3: Building the Middle Price Column. ...
  4. Step 3: Create the Other Price Columns. ...
  5. Step 4: Enlarge the Middle Price Column. ...
  6. Step 5: Final Touches.

What are examples of pricing models? ›

To help you with your own price decisions, here are seven common types of pricing models:
  • Cost-plus pricing model. ...
  • Value-based pricing model. ...
  • Hourly pricing model. ...
  • Fixed pricing model. ...
  • Equity pricing model. ...
  • Performance-based pricing model.

What is dynamic pricing strategy? ›

Dynamic pricing — also known as surge pricing, demand pricing, or time-based pricing — is a strategy where businesses adjust the prices of their offerings to account for changing demand. For instance, an airline will shift seat prices based on seat type, number of remaining seats, and time until the flight.

What are the 4 main factors that influence a business pricing strategy? ›

Five factors to consider when pricing products or services
  • Costs. First and foremost you need to be financially informed. ...
  • Customers. Know what your customers want from your products and services. ...
  • Positioning. Once you understand your customer, you need to look at your positioning. ...
  • Competitors. ...
  • Profit.
22 Nov 2018

How do you create a named range in Excel dynamically? ›

The easiest way to create a dynamic named range is to start by creating a named Excel table. Then, define a range based on one or more columns in that table. In this example there is a list of parts on the worksheet, and a named table, and dynamic named ranges will be created.

How do you create an array? ›

Arrays are used to store multiple values in a single variable, instead of declaring separate variables for each value. To create an array, define the data type (like int ) and specify the name of the array followed by square brackets [].

How do you build an array? ›

An array is a sequence of values; the values in the array are called elements. You can make an array of int s, double s, or any other type, but all the values in an array must have the same type. To create an array, you have to declare a variable with an array type and then create the array itself.

How do I convert text to array in Excel? ›

To convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate text at the character level.

How do you create a dynamic range in a table? ›

The solution is to create a dynamic named range that expands and contracts as data is added or removed. The easiest way to create a named range in Excel is to use the Table feature. Just put the cursor anywhere in the data and use the keyboard shortcut Control + T.

How do I create an array in VBA? ›

The first way to create an array is the simplest, we just use the Array() function in VBA:
  1. Sub CreateArrayTest() ...
  2. MsgBox myarray(0) & " " & mysecondarray(2) ...
  3. Sub CreateArrayTest2() ...
  4. Dim myarray(2) As Variant. ...
  5. myarray(0) = "red" ...
  6. myarray(1) = "green" ...
  7. Sub CreateArrayTest3() ...
  8. Dim myarray(1 To 3) As Variant.

How do I create a dynamic range in VBA? ›

Dynamic Range
  1. First, we declare one variable and two Range objects. ...
  2. We add the line which changes the background color of all cells to 'No Fill'. ...
  3. We initialize rng with the numbers. ...
  4. We initialize maximum with the maximum value of the numbers. ...
  5. Finally, we color the maximum value. ...
  6. Add a number.

How do I use an array in Excel VBA? ›

Use the Option Base statement at the top of a module to change the default index of the first element from 0 to 1. In the following example, the Option Base statement changes the index for the first element, and the Dim statement declares the array variable with 365 elements.

Why would you use SORTBY instead of sort? ›

It sorts an array of values. However, when using the sort function the sort column must be contained within the sort array. What if you wanted to sort an array based on a column that is not included? In this case you would use SORTBY.

How does SORTBY work in Excel? ›

The SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array. Note: This function is currently available to Microsoft 365 subscribers in Current Channel. It will be available to Microsoft 365 subscribers in Semi-Annual Enterprise Channel starting in July 2020.

How does v look up work? ›

In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

Which keyword is used in dynamic array? ›

Dynamic arrays in C++ are declared using the new keyword. We use square brackets to specify the number of items to be stored in the dynamic array. Once done with the array, we can free up the memory using the delete operator.

Can I create dynamic array in C? ›

We can create an array of pointers also dynamically using a double pointer. Once we have an array pointers allocated dynamically, we can dynamically allocate memory and for every row like method 2.

Which of these data type is dynamic array? ›

Explanation: Dynamic arrays are used in the implementation of list data type in python.

What is a dynamic table in Excel? ›

Dynamic tables in Excel are the tables where when a new value is inserted into it. As a result, the table adjusts its size by itself. To create a dynamic table in Excel, we have two different methods: making a table of the data from the table section while another using the offset function.

What is Xlookup vs VLOOKUP? ›

VLOOKUP defaults to the closest match whereas XLOOKUP defaults to an exact match. To correct that in VLOOKUP, you have to type FALSE as your fourth argument.

How do I do a dynamic VLOOKUP? ›

Dynamic Vlookup Formula

Suppose columns are not ordered in the table the way you want values to be returned. Since the column index numbers are not in any proper order, we cannot use COLUMN function. Instead we can use MATCH function to return the column index number.

When using Microsoft Excel dynamic means? ›

Dynamic Arrays allow you to work with multiple values at the same time in a formula. This feature enables Excel formulas to return numerous results based on a single formula entered in one cell. The results are also dynamic, meaning that if the source data changes, the results will dynamically update.

Which function is used for creating a data table? ›

The correct answer is Table. 'Table' function is used to create a data table in MS Excel. The data tables allow the user to see the results of a variety of different inputs all at once.

What are all the functions in Excel? ›

Excel functions (alphabetical)
Function nameType and description
CODE functionText: Returns a numeric code for the first character in a text string
COLUMN functionLookup and reference: Returns the column number of a reference
COLUMNS functionLookup and reference: Returns the number of columns in a reference
177 more rows

What replaced VLOOKUP? ›

Microsoft recently announced the upcoming release of a new function called XLOOKUP. This function will be replacing the widely used VLOOKUP, HLOOKUP and INDEX/MATCH functions to run searches in a table of Excel data.

What function is better than VLOOKUP? ›

The superior alternative to VLOOKUP is INDEX MATCH. While VLOOKUP works fine in most cases, it tends to reveal flaws when you start using it in large and complex sheets. The INDEX MATCH formula is actually two different functions; INDEX and MATCH. array is range of cells or an array constant.

What is Xlookup used for? ›

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

Which function will help to make VLOOKUP dynamic? ›

Preface. Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number. However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns.

How do I automate a column number in VLOOKUP? ›

In order to make it automatic so that you can copy the formula across and have the numbers change you can look at creating a trigger row i.e. a row that contains the numbers of the columns you want to use. The VLOOKUP formula will then look at this row to determine which column to use.

How do you do a VLOOKUP with a macro? ›

In VBA code, one can use the VLOOKUP function:
  1. Application.WorksheetFunction.vlookup(lookup_value, table_array, col_index_num, range_lookup) ...
  2. student_id = 11004. ...
  3. Set myrange = Range(“B4:D8”) ...
  4. marks = Application.WorksheetFunction.VLookup(student_id, myrange, 3, False)

How do you do dynamic range in Excel? ›

Create a Dynamic Range in Excel... this is different! - YouTube

Which of the following functions helps create dynamic names within a model in Excel? ›

Using the INDEX Function in Dynamic Names

We can define the name MyData to return the data for the month specified in cell B2. In this example, I used the Create Name dialog to assign the name Month to the yellow cell. And I used the dialog to assign the Date and Data names to the columns shown beneath those labels.

Videos

1. Excel Weekend 7 - Practical applications using Dynamic Array Functions - Tony De Jonker, MVP
(Excel Turbo)
2. Microsoft Excel: Advanced spreadsheet modeling using Dynamic Arrays - BRK4001
(Microsoft Power BI)
3. 📊 How to Build Excel Interactive Dashboards
(Kevin Stratvert)
4. Dynamic Array Functions in Excel [Full Course]
(Mike Thomas)
5. Create an Interactive Top N Report in Excel (includes duplicate values) with Filter Function
(Leila Gharani)
6. Dynamic Array Formulas in Excel - 8 Things You Need to Know
(Computergaga)

Top Articles

Latest Posts

Article information

Author: Eusebia Nader

Last Updated: 12/31/2022

Views: 6237

Rating: 5 / 5 (80 voted)

Reviews: 87% of readers found this page helpful

Author information

Name: Eusebia Nader

Birthday: 1994-11-11

Address: Apt. 721 977 Ebert Meadows, Jereville, GA 73618-6603

Phone: +2316203969400

Job: International Farming Consultant

Hobby: Reading, Photography, Shooting, Singing, Magic, Kayaking, Mushroom hunting

Introduction: My name is Eusebia Nader, I am a encouraging, brainy, lively, nice, famous, healthy, clever person who loves writing and wants to share my knowledge and understanding with you.