Custom List Trick

Create looping sequential codes

It is common to use Q1 for quarter one. Excel will even cycle through Q1,Q2,Q3 and Q4 when you drag a cell contain Q1. What if you want to use the sequence M1 to M12 for months?  Custom Lists to the rescue!

Type M1 in a cell and drag it until you get to M12. If you want to use this code for sorting see Sort Warning and suggestion at the bottom of the post.

Select the range and press, in sequence not held down, Alt t o a. This opens the Advanced section of Excel Options.

M0

Scroll to the bottom of the window on the right.

M1

Click the Edit Custom Lists button.

M2

Click the Import button and then click OK twice.

Now when you drag a cell with M1, Excel will cycle through M1 to M12

As you can see in the above dialog, you can create Custom Lists for any list of items you use frequently eg departments, branches or staff.

WARNING: Custom Lists exist on your PC or laptop. You need to create the lists on each computer you use.

SORT WARNING: If you use the month code for sorting, it would be better to use a zero in front of the single digits – see below.

M3

 

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *