2. Naming Arrays

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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

Summary

  1. Naming Arrays of Cells (00:04)

    When performing lookups on large datasets, it’s useful to be able to name an array of cells. To do this, select the array, then open the Name Manager by pressing Ctrl + F3. From here, you can create a new name for the selected array. By convention, names should be in all capital letters with words separated by an underscore.

    Once we’ve named an array, we can use the name instead of the cell references in formulas, such as a VLOOKUP. This makes your formulas much more readable, and saves you time creating formulas in the future. A single cell can be part of multiple named arrays, so you can create a variety of named arrays. These will make your formulas easy for you to write and easy for other people to read.

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.

Excel Excel for Business Analytics Learning Plan
Lookups and Database Functions

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial