How to master using different Excel techniques together

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.

Neale Blackwood CPA:
Welcome to Excel Tips. My name is Neale Blackwood. And in this episode, we're going to look at how we can use a couple of different techniques in Excel together. The first technique is custom lists.

Custom lists explained:
Now you probably already use custom lists because that's where Excel has the functionality that you can type in January in a cell and drag the cell across or down and you'll get the other months of the year. It also works for the days of the week. Now they are both custom lists, but what Excel allows you to do is to create your own custom lists. Now that's really useful for things like department names or divisions or branches. And you can even use it for people's names. So if you're creating a roster for example, you might want a list of all of the people for the roster. And so you can create a custom list for that particular list. And basically what you do is you enter the first entry on the list into a cell, and then you use the fill handle. That's that little plus sign on the bottom right-hand corner of the cell to drag down or across to populate the remaining entries on the list.

Creating custom lists:
To create a custom list, select the list. Now if you haven't already got it, then just type it out and then select the list, click on the file tab, then click on options, which is about 2/3 of the way down on the left-hand side. Then click Advanced, which is on the left-hand side of the dialogue. Now it's almost as if they're trying to hide this feature because what you then have to do is scroll all the way down to the bottom and there will be an Edit custom lists button. So, click on that and there's an ‘Import’ button.

So click on the Import button, and that will update the list. So now the list has been created. And basically you can just click Okay, and Okay again, and that will create the list. Now that Edit custom list button is also where you go if you want to edit the list. So for example, if you are using it for staff names, then to handle people joining and leaving, then you would need to edit that list. So you can go into the Edit custom list and change the list. There's two lists. There's a list on the left-hand side, which is the existing lists, and then on the right-hand side is where you can edit that list.

So basically go into the dialogue, edit the list, click Okay, and then that will update the lists. Once you've created your list, all you need to do then is to enter the first entry into a cell and then you can just drag it around using that fill handle. The downside of custom lists is that list only exists on that machine. So if that was your desktop computer, then if you wanted to use that custom list on your laptop, you would have to then do exactly the same process on your laptop. Once you've done it, that's it. You don't need to do it again. So it's a one-off type thing.

The only thing that can reset it is if you reinstall Windows or reinstall Excel or something like that. But once it's done, it's there for good. And again, if you send a file that has one of your custom lists in it to someone else, that won't create the list on their computer. They would have to go through those steps to create the list if they want to use it. The other downside of custom lists is that it is a manual process. You've got to enter the first entry and then drag it manually, and you've got to drag it as far as the list is. So depending on what you're using it for, you might have to drag it for a long way.

Automating lists with range names:
What I wanted to share with you was a way you can automate the list, and that's with using a range name. Now this works really, really well in the latest versions of Excel where we have dynamic arrays because the list will automatically spill to populate all of the entries. All you need to do is enter the range name in a cell and then all of the other entries in that list will populate automatically. We're going to look at three different ways to create the range names. And each of the range names has advantages and disadvantages. The last one is probably the best in terms of it has the least disadvantages.

Creating range names (method 1):
So to start off with, I'll explain how you can create a normal range name. So again, if you've got that list somewhere, just select the list, click in what's called the name box. So that's a box that's above the letter A. If you are at the top left of your spreadsheet, it's on the left-hand side of the formula bar. So it usually displays the cell reference, but you select the list that you want, you click inside that name box and you start typing.

Now, the examples I've used in the article and also the companion video, which you can see INTHEBLACK website, I have a prefix of RNG, and then I've used, as an example, the weekdays. So it's ‘RNG’, capital ‘Week’, capital ‘Days’. So I capitalised the first letter of each word that I use. The reason I do that is that when you use a range name, if Excel recognises it, it will also capitalise that name in the formula. So it saves me having to figure out if I've made a typo when I've entered it. You type in that name and then you must press Enter. And when you press Enter, that name should remain in the name box. A couple of things. You can't use spaces in range names and you can't start with a number. But you can have a number in it, but it just can't start with a number.

Also, the name can't be what could be a cell reference. So something like CAT1, for example, is a cell reference because there is a column of CAT, so you can't use any reference that also is a cell reference. Having created the name, you can then go into any cell in the file and start typing the name. In our example, my name started with RNG, short for range. So if I started typing =RNG, that name pops up on the IntelliSense, which is just below the formula bar. So because it's highlighted, you can just hit the tab key and that will enter the range name for you. And then when you hit Enter, that will populate the listing of, in this case, all of the weekdays. So it's going to spill. So that means it's going to go down the column. There'll be a little blue outline around the range because it is a spill range, which is part of the dynamic arrays, which I have discussed in previous podcasts.

Transpose function:
If you want a listing that goes across the page rather than down the page, you can use the transpose function. So basically =transpose(), and then just type in the range name and hit Enter. And that will display the range name entries all across columns rather than down a single column. So that's the transpose function.

The downside of using range names (method 1):
The downside with this range name technique is that it is linked to a range in this file. So if you copied the sheet to another file, you would end up with links going back to the original file. So that's a little bit of a downside. Another downside with this technique is that it doesn't automatically extend. So if I added some extra entries to the bottom of the list, it's not going to pick that up because it's got a fixed range that it's referring to.

Using formatted tables for range names (method 2):
That's where we can use a formatted table. So the second example, what I do is create a formatted table. So there's a format as table button on the home ribbon, and that creates a formatted table. And if you refer to a formatted table as your source for your range name, that does expand. So in the example on the companion video, I used an example of states. So in cell A1, I had the word states.

And then I listed four states underneath that. Ctrl+T is the shortcut to create a formatted table. So I did that and just accepted the entry there. Then I selected A2 down to A5, which was the range of the entry. So I didn't include the heading. And then I went back into the name box. So then I created a range name RNG states and I pressed Enter. You must always remember to press Enter. Once that's ready to go, I can use that in exactly the same way as the other example I used. But this one, if I add an extra state, for example, to the bottom of the formatted table, it will automatically appear in the range name. And this also works with the transpose option as well. That's the advantage with this one. The downside is still there is a link to a range in this file. So if I try and use that range name outside of this file, then I will get linking issues.

Array-based range names:
So that gets us down to the last type of range name, and that is the array-based range name. Arrays are pretty much just like a range, but when you want to create a standalone range name, you have to use what's called array syntax. Now this is a combination of the curly braces type brackets, double quotation marks, commas, and it's a long, long text string. So in the companion video, I created a text string of all of the days of the week.

There's a fairly complex formula that creates that text string for me. I do not like typing the array syntax. I'm always going to make an error. So basically, I've created a formula that's in the companion file. So on the INTHEBLACK website, you can download the companion file there and it's got the example of that formula. And that will work with any list and it will create the array syntax you need to build your range name. So if you've got a very long list, then this might not work for it. It works for shorter lists, creating this array syntax. The formula creates the syntax, but I need it to be able to copy it. So what you do is you copy the result and then use paste special values.

Special values keyboard shortcut:
Now there's a new keyboard shortcut for paste special values, it's Ctrl+Shift and the letter V. So hold Ctrl and Shift down and type V and that paste values. I then copy that, which is just the array syntax. So I went to define name in the formulas tab, created the name. I used the prefix ARR, and this time I used ARRweekdays. And then in the bottom section of the dialogue that opens, you delete everything except the equal sign and then paste in that array syntax and then click Okay. That creates a standalone range name that in this case returns all of the days of the week. And that is very, very powerful because you can copy that to any other file and it doesn't have any links back to it. If you needed to edit it, you would need to edit that array syntax to delete an entry or to add an entry.

Examples using the range names:
So what I'd like to do is just share some ideas on how you can use those names in practice. The first example was the abbreviated version of the weekdays. So if you wanted to use MON instead of the whole Monday, then what you can do is use the left function. Basically, it's =left(ARRweekdays,3). And what that will do is that will return a list of all of the weekdays, but it'll only be the first three letters because the left function extracts the characters from the left of the text. And so because you put comma 3 there, it extracts the first three characters. If you left out the comma 3, it will actually extract out the first character, which also might be useful if space is at a premium and you just want to list the first letter of every word in the day of the week.

Using helper cells and the index function:
The examples I've used in the companion video all start on a Monday. So Monday is the first day of the week. But you might want to start your week on a Sunday. So what you can do is you can use helper cells. So I had the number 7 followed by then the numbers 1 to 6, and then I used the index function. So the index function allows you to extract from normally a range. But because we've got a range name, all you need to do is go =index, open bracket, And then ARRweekdays, which was our range name, comma, and then I referred to the cell reference that had the 7 in it, which was A1, and closed the bracket. And so that returns the 7th entry in the range name, which in this case is Sunday. And then I copied it across, which then extracted the 1, 2, 3, all the way to 6, which gave you the rest of the week. So you can use the helper cells to sort of rearrange the week if you need it.

Repeating the range name sequence:
And the last thing I shared is the ability to repeat the sequence. When you use the range name, it just gives you one list of all of the entries, but you might want to repeat them. Just like when you drag January across and it keeps repeating January through December, you might want to repeat that for your range name. To do that, I combine the mod function, M-O-D, with the sequence function, which I was able to create a repeating list of the numbers 1 to 7. So you can check out the article or the video for the formula that does that. But once you've got those repeating numbers from 1 to 7, you just need to use that index function again, and you can then extract out all of the days using the 1 to 7s to get from Monday through to Sunday.

Recap and benefits of automated custom lists:
So custom lists are really, really useful. They can save you a lot of typing. So you can just type the first entry of the list and drag it. But if you wanted to automate things, that's where the range names come in. So I sort of handled the range names in the easiest through to the more complex. If you are only going to use the name in the file, first two techniques are the way to go. I would recommend using the formatted table version because that automatically updates. It's a lot easier to work with. But if you did want to use the range names in other files, then using that array syntax is the way to go. Two separate techniques that when you sort of combine them, make things even better. Hope you found that useful, and thanks for listening.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. And we hope you can join us next time for another episode of Excel Tips.