Mar 31, 2015

Did you know that Microsoft Excel is very smart?

If you type the name of the month (e.g. January), it knows automatically what comes next in the series so that if you use the copy tool known as the Fill Handle (that little black cross found only on the bottom right corner of any active cell), it will create a list of all the months that follow January. You can fill the series rather than just it copying the same thing to the cells you drag through. This is because this list is programmed into Excel. The same goes for the name of the days of the week and their shortened versions too, such as “JAN” or “Mon.”

In addition, you can create custom lists to use for whatever you want, such as sorting. Perhaps you have a list of store locations that you use all the time and you want to be able to type the first one and then have the rest of them generated automatically without having to type them in each time. You could copy and paste but that is not going to allow you to sort by that order later so it is not the best solution. Let’s try an example.

So let's say your list is made up of all the shopping centres where you have your stores in and you usually use them in order of store floor space, not alphabetical order. In Excel, you would type this list the first time in a column then highlight or select it.

Next, open up your options and go to the “Advanced Tab” and look under the “General Section” for the “Edit Custom Lists” button.

Click on it. Notice the other lists mentioned above with regard to the months and days of the week already are there? Also, notice that in the lower right corner there is an import button? The cells you have highlighted in your list should read this:

Next hit “Import” and you should now see the list of the shopping centres in the same order you typed them up in. Next time, all you need to do is type the first entry of the list (in this case, Bondi Junction) and then use the fill handle to drag down. You should see the rest of the 13 locations that follow be filled in automatically for you. If you then decide to sort them in alphabetical order or by another column, for example, and then you want them to go back to the original order then you can open the sort dialogue box, choose “Custom Sort” and choose the list that you want to sort by. Voila! Now you can type in any one of the suburbs and use the fill handle to complete the list or if you have the list in another order, you can sort it back to the original order by choosing custom list from the bottom of the sort dialogue box.