BXL DAN - FREE: Create dynamic array names and more

Designed to make dynamic array collections compatible with PivotTables and Power Query, BXL DAN also adds easy dynamic named ranges, block naming & formatting.

by Craig Hatmaker
IT Executive and author of Ribbon Recipes: Beginner's Guide to Creating Excel Menus available on AmazonFollow 27

4 reviews238 views|Start the discussion!|Bookmark download for free

Description
NOTE! PDF download only - Link to the Add-in is in the PDF on Page 3 under "Installing".


The Naming Advantage
I love tables and structured references because structured references are automatic, maintenance free, dynamic named ranges and names make formulas self-documenting. Self-documenting formulas are easier to understand. Prove it to yourself. Below are two formulas from the same cell that do exactly the same thing. Which do you understand best?


=E62+E63-E64
Or
=NetEarnings + Plus·DepreciationAndAmortization - Less·ChangesInWorkingCapital


But when we do not use tables, we do not have structured references; thus, to use names we must suffer the burden of name creation and maintenance. That was not a problem for me until dynamic arrays arrived.


I love dynamic arrays because they automatically extend, and a single cell can calculate an entire row or column. This speeds development and reduces risk because, unlike cells in every column, dynamic arrays cannot be inconsistent. It is just not possible. But dynamic arrays do not come with names. Ugh! And if we use Excel’s Create from Selection to name them, the names are not dynamic. UGH!! And if we try to use them in Power Query, we run into more problems UGH!!!


BXL DAN Can
Excel provides a function that easily creates names from selected cells. It is found on Excel's Formulas tab > Defined Names group > Create from Selection icon. The keyboard shortcut is SHIFT-CTRL-F3. Unfortunately, it does not work for some very common situations. 


Non-Adjacent Lables
Most financial standards have things in between a row's label and its values. Create from Selection cannot work with that. BXL DAN can. So naming in financial modeling just got a lot simpler.


Dynamic Named Ranges
Create from Selection cannot create dynamic named ranges. So for those who want names to automatically expand to accommodate as many values as there are in a row (or column), BXL DAN can create dynamic named ranges for your. Just select the first value or values and let BXL DAN do the rest.


Dynamic Arrays
If we use Create from Selection to create a name over a dynamic array, the name is not dynamic. Once again, BXL DAN can create individual dynamic array names that are fully dynamic, as well as block dynamic array names.



Block Names
In each of the pictures we showed selecting groups of rows or columns. When this is the case, BXL DAN will offer to create block names that mimic table's #Headers, and #ALL names. This provide two important features.


1) When creating names from a group of selected cells, we can add a block name making it easier to determine what a name is, and where it came from. If we provide a group name like Expenses, BXL DAN names each row like so: Expenses.SalesAndBenefits, Expenses.RentAndOverhead, etc. While this creates longer names, they are easy to understand and use.


2) If we provide a block name, like Expenses, and select Headers and All, BXL DAN creates three group names: Expenses containing data, Expenses.Headers, containing only the labels, and Expenses.All containing headers and data. Expenses.All can be used by PowerQuery and by PivotTables. 


And more!
There are two more features that I really like about tables that are missing from all other ranges: banded rows, and automatic format extension.


Banded Rows
Another feature from tables I really like is lightly banded rows. BXL DAN makes adding that to a named range, or any selected range, easy. 


Format Extension
When tables expand, formulas and formats follow. When dynamic arrays expand, formats are left behind. BXL DAN has an option to extend the formats from the first row (or column), to all other rows (or columns) in the named range. It isn't automatic, but it helps.

This Best Practice includes
1 User Guide with link to Add-in (Windows Office 365 Only).