Create Dynamic Range in Excel

Create Dynamic Range in Excel

January 4, 2022 0 Door Bjorn Meijer

For example, in Excel you can easily fill a drop-down list with data using a range. If data is added over time, the range must be adjusted manually. If there are not often changes, you can do this manually. If the range changes regularly, it is more convenient to use a dynamic range. The length and/or width of the range will automatically adjust as you add values. Such a dynamic range can be performed in two ways. If you work with a somewhat newer version of Excel, you can use tables. This works easier. If you work with an older version of Excel, then a dynamic range with formulas possible, but is a little more complicated.

Table of contents

Dynamic range via a table

For the sake of convenience, in this example, we will assume a customer base. We can assume that there are regular changes here. If things go well, new customers will be added regularly.

customer base for dynamic reach
Customer base for dynamic range

You can select the relevant debtor via a drop-down list, after which all other details are entered.

Select customer name from drop-down list
Select the customer name from the drop-down list

Convert customer file to table

We are going to convert the customer base from a range to a table. We do this by selecting the data including headers (range A3:G5). Go to the tab Insert the group Tables and click Table. Or use the key combination Ctrl+L.

Convert range to table
Convert range to table

Create Named Range

The next step is to put the customer names in a named range. We do this by selecting all the data in the first column of the table. To do this, click at the top of the header cell. The cursor changes to an arrow pointing downwards. If you click, all items in the relevant column are selected.

In the ribbon, go to the Formulas tab the group Defined Names and click Define name.

A new window will open called New name.

Window New name

Fill in the input field Name: an easy-to-remember name for your scope (e.g. selClientname).

Then click on OK and the defined name is saved.

Make preset list dynamic

To create a drop-down list, select the cell in which you want the list to appear (for example, B17). In the ribbon, go to the tab Facts the group Data Tools and click Data Validation. A new window will open.
under the tab Settings choose you at To allow: for “List” and at Source: enter the range (see image below).

Drop-down list with defined table
Drop-down list with defined table

Forgot the name of the named range? Select the input field Source: and press the F3 function key. A new window will open with all named ranges. This also prevents the chance of typos.

If you now add a new customer to the customer database, it will automatically be added to the drop-down list.