{"id":1705,"date":"2022-01-04T22:12:54","date_gmt":"2022-01-04T22:12:54","guid":{"rendered":"https:\/\/www.bjorn-meijer.nl\/?p=1705"},"modified":"2022-10-13T18:28:05","modified_gmt":"2022-10-13T18:28:05","slug":"dynamisch-bereik-in-excel-maken","status":"publish","type":"post","link":"https:\/\/www.bjorn-meijer.nl\/en\/2022\/01\/04\/create-dynamic-range-in-excel\/","title":{"rendered":"Create Dynamic Range in Excel"},"content":{"rendered":"

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<\/a> possible, but is a little more complicated.<\/p>\n\n\n\n

Dynamic range via a table<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

\"customer
Customer base for dynamic range<\/figcaption><\/figure>\n\n\n\n

You can select the relevant debtor via a drop-down list, after which all other details are entered.<\/p>\n\n\n\n

\"Select
Select the customer name from the drop-down list<\/figcaption><\/figure>\n\n\n\n

Convert customer file to table<\/h2>\n\n\n\n

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<\/strong> <\/i> the group Tables<\/strong> and click Table<\/strong>. Or use the key combination Ctrl+L<\/strong>.<\/p>\n\n\n\n

\"Convert
Convert range to table<\/figcaption><\/figure>\n\n\n\n

Create Named Range<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

In the ribbon, go to the Formulas tab <\/i> the group Defined Names<\/strong> and click Define name<\/strong>.<\/p>\n\n\n\n

A new window will open called New name<\/strong>.<\/p>\n\n\n\n

\"\"
Window New name<\/figcaption><\/figure>\n\n\n\n

Fill in the input field Name:<\/strong> an easy-to-remember name for your scope (e.g. selClientname). <\/p>\n\n\n\n

Then click on OK<\/strong> and the defined name is saved. <\/p>\n\n\n\n

Make preset list dynamic<\/h2>\n\n\n\n

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<\/strong> <\/i> the group Data Tools<\/strong> and click Data Validation<\/strong>. A new window will open.
under the tab Settings<\/strong> choose you at To allow:<\/strong> for \u201cList\u201d and at Source:<\/strong> enter the range (see image below). <\/p>\n\n\n\n

\"Drop-down
Drop-down list with defined table<\/figcaption><\/figure>\n\n\n\n

<\/i> Forgot the name of the named range? Select the input field Source:<\/strong> and press the F3 function key. A new window will open with all named ranges. This also prevents the chance of typos.<\/p>\n\n\n\n

If you now add a new customer to the customer database, it will automatically be added to the drop-down list. <\/p>\n\n\n\n

\n