Beyond Excel's (BXL's) Add-in:

Dynamic Array Naming +

What is the point of names anyway?

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!!!

How does BXL DAN help?

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.

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. In the first two pictures we see a group of rows selected under Expenses. 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, Expenses.DepreciationAndAmortization, Expenses.Interest. While this creates longer names, they are easy to understand and use.

2. In the third example, we have a collection of dynamic arrays. If we provide a block name, like Process, and select Headers and All, BXL DAN creates two group names: Process.Headers, containing only the labels, and Process.All containing headers and data. Process.All can be used by PowerQuery and by PivotTables.

But wait! There's 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.

Format Extension

When tables expand, formulas and formats follow. When dynamic arrays expand, formats are left behind. This is also true for BXL DAN's banded rows. So when named ranges grow beyond their original size, 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 is as close as we can get without VBA.