BXL DAN

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

Dynamic Array Naming +

Overview

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.

Non-Adjacent Labels

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

Banded Rows

At right is we can see adding banded rows to a named range: Expenses.

We select the name, then the Accent color, and then we adjust the lightness using the tint slider. The result is similar to table banded rows which helps guide the eye across a long series of numbers. Keeping the bands subtle prevents them from detracting from the information.

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.