2. Naming Arrays

Subtitles Enabled

Next lesson: Better Lookups with INDEX/MATCH

Overview

Naming arrays saves you buckets of time when writing formulas and has the happy knack of making them much more readable.

Lesson Notes

Naming arrays

- Save you large amounts of time
- Reduce the probability of formula errors

Keyboard shortcuts

CTRL + F3: Open the Name Manager dialog box
ALT + N: Create a new Name
ALT + E , S , F: Paste formulas
CTRL + SHIFT + : Select all cells within data region
SHIFT + : Select an additional cell
F2: Jump inside formula

Transcript

You might remember from our course on formulas that we learned how to name individual cells, such as interest rate, tax rate and other constants.

We can also name arrays of cells.

And this becomes very useful when performing lookups on large data sets.

For our lookup formula in the previous lesson, you can see that we anchored the array, A1 to H150, repeatedly in our formulas.

To make our formulas more readable and to save some time, we should name this array and then use it in our formula.

First, let's select the array with Ctrl + Shift + Left Arrow and Ctrl + Shift + Down Arrow.

I'll then press Ctrl + F3 to bring up the Name Manager.

We'll then create a new name, and I'll call this our SALES_TRANSACTIONS Press OK and exit to name the array.

And we can now see in the top left hand corner that this name is in place.

Now let's use this name in our lookup formulas.

So I'll re-enter the formula by pressing F2, I'll delete the existing array, and then I'll type sales and I press Tab to finish the name.

I'll press Enter and I can see that the value hasn't changed.

We can then copy with Ctrl + C, and paste into the remaining cells.

And as you can see, this is a much more readable VLOOKUP now that we have the named array in place.

Cells can actually be part of many different named arrays.

For example, we could create another named array, which is the first column, and call it Company Name.

So I'll press Alt + N, and then COMPANY NAME in caps, and Esc to create the new array.

I can also create another array across the top called Sales Headers.

Both of these named arrays will come in use for our next lesson when we perform lookups with the INDEX and MATCH functions.

4 mins

2 mins

5 mins

5 mins

3 mins

3 mins

4 mins

4 mins

3 mins

4 mins

7 mins