Nice Excel feature: Customs List
Here’s a new one that I learned – or I kind of knew but never really implemented it.
It’s called Customs List.
I took an advanced Excel class from this guy who knows of some interesting features in software products. I think he might have been an engineer at one point, but he teaches how to use Microsoft products in a very easy manner. Nothing too complicated.
Again, I have a document that goes through in a very easy fashion on how to set up this feature. It can come in handy, and I already have an idea that can be applied in finance/ FP&A.
You know when you have days of the week such as Sunday through Saturday or you have months such as January through December and you want them to automatically fill? Sometimes when you place your cursor just so to get a little plus sign and you move your mouse, days of the week or the months automatically fill? Am I making sense here?
Example: I want to fill in the days of the week, starting with Sunday.
So, I place my cursor over Sunday until I get the + symbol and then move my mouse down as if I’m copying Sunday down but I’m really creating the days of the week.
So, I place my cursor over Sunday until I get the + symbol and then move my mouse down as if I’m copying Sunday down but I’m really creating the days of the week. I end up with an image like that on the left.
The days of the week Sunday through Sunday or the months January through December are already set up in Excel as custom lists. The custom lists allow you to automatically fill the rest of the days or months when you start with just Sunday or January.
You can create your own autofill for any list such as a list of products or a list of names or division units. These custom lists become really useful in pivot tables because once you have the custom list, pivot tables will automatically sort them in the order you want.
Custom lists help you to sort automatically or autofill.
The Presentation
So, let’s first start off with the document that shows how to set up the customs list.
Loading…
Reload document 
Open in new tab Since the image can be kind of small, I’m going to pull out a couple of really important ones to help you get started.
The Example
Here’s an example file where we will want to organize the cardinal directions east, north, south and west in a certain order. Pivot table will automatically sort in alphabetical order.
Let’s say we want the order to be north, south, east, west.
The Steps for Custom Lists
The first thing you want to do is go to File and then on the left-hand side, look down at the bottom for “Options”. In the image on the left, I have a red arrow pointing to “Options”.
Next, find either General or Advanced. I think most of the time, the one you want will be Advanced. Scroll down to the bottom and find “Edit Custom List”, as shown in the next enlarged image. It is circled in red.
Now, type in your list in the “List entries” box and click on the “Add” button.
The End Result
Now, you will get a pivot table that will automatically generate east, north, south, west as north, south, east and west.
Today, I just came up with a new idea of a cool way to use a custom list but that will be a topic for another post.
You must be logged in to post a comment.