Author: Debra Dalgleish

You know how to sort an Excel list alphabetically, and with Excel 2007 you can even sort an Excel list by colour.

Did you know that you can also create a custom list in Excel and use that to sort your data, instead of sorting in alphabetical or numerical order?

See how, in the written steps and video below

Sort by Custom List

Instead of sorting the products in this table alphabetically, we’ll create a custom list of products, and use it when sorting the list.

Create a Custom List in Excel

You can create a custom list in Excel by importing a list from a worksheet, or by typing a new list. In this example, there is a worksheet named Lists, and it contains a product list.

We’ll import that list, to create the custom list.

To open the custom list window:

• Select the cells that contain the list items
• On the Ribbon, click the File Tab (or the Office Button in Excel 2007)
• Then click Options.
• Excel 2010 and later: Click the Advanced category, then scroll down to the General section
• Excel 2007: Click the Popular category, then look in the Top Options section
• Click Edit Custom Lists

To add a custom list:

• In the Custom Lists dialog box, the list address — $A$2:$A$5 — should appear in the Import range box. If not, you can click in the Import range box, and type a range, or select a range on the worksheet.

• To add the selected range as a custom list, click the Import button.
• The list items will appear in the List entries section of the Custom List dialog box, and at the end of the list of existing Custom Lists.

• Click OK to close the Custom Lists dialog box, and click OK to close the Excel Options window.

Use the Custom List

You can use the custom lists when sorting, and you can also use them with the AutoFill feature. Type any item from a custom list in a cell, then use the Fill handle to complete the list.

Sort Excel List in Custom Order

To sort your list based on your custom list, follow these steps:

• Select a cell in the table that you want to sort.
• On the Ribbon’s Data tab, click Sort
• In the Sort dialog box, select a Column from the first drop down, and select Values from the Sort On drop down.
• In the Order drop down, click Custom List
• In the Custom List dialog box, select your custom list, and click OK

• Click OK to close the Sort dialog box

The list is sorted in the order of the items in your custom list.

Watch the Excel Sort Video

To see the steps for adding an Excel Custom List, then sorting by that Custom List, watch this short Excel video tutorial.

____________

A very nice email, from someone who visited the Contextures website, made me think about how long I’ve been using Excel.

My guess was that I’d started around 1987, so I fired up the old Mac laptop, and dug some old floppies out of the storage cupboard.

Excel 3.0 on Mac PowerBook

In the photo below, you can see my Mac PowerBook 170 laptop, from late 1991. It’s running the Mac version of Excel 3.0.

There’s a Mac 128K model in the back room too, but I didn’t have the strength to dig that out. (I hope the producers of the tv series, The Hoarders, don’t call me now.)

Excel Files

As you can see, the application files were much smaller in Excel 3.0. Of course, that still took a good chunk out of my 40MB hard drive.

Do you remember the days when a Microsoft Office application was just over a megabyte in size?

Flashy Excel Charts

Even though the machine didn’t have colour, I was still able to make some pretty flashy 3-D charts in the old days.

I have no idea what this was supposed to show, but maybe it was the results of an Olympic ski jumping event.

Black and White Tetris

I’m sure that I never wasted any time playing TETRIS, when I was supposed to be working on Excel files.

And it must have been harder to play back then, when all the Tetris shapes were the same shade of grey!

Sorting a List

Way back then (and until Excel 2007), we were only able to sort by 3 levels, and we couldn’t sort by colour.

That didn’t matter much to me, since I didn’t have colour!

My Oldest Excel File

There may be something older on a floppy disk at the back of the storage cupboard, but I finally found an Excel file that I’d worked on in April 1987. I was creating some Excel training files, to be used by Apple vendors.

The Excel 3.0 File and Excel Toolbar

Here’s what the file looked like, and it’s interesting to see the minimalist toolbar too. Do you remember what all those icons were for?

How Long Have You Used Excel?

There’s an Excel poll on my Debra D blog, so if you have a minute, please go and answer the question – How Long Have You Been Using Excel?

[update: the poll is closed now]

Spreadsheet Demo From May 1987

In an episode of Computer Chronicles from May 1987, the guests talked about spreadsheets.

This video shows an Excel demo, by Mike Slade from Microsoft, using a Macintosh. He shows multiple worksheets open at the same time, links the sheets, creates a chart, and runs a macro.

• Note: This link will take you to a longer version of the video, which also includes a Trapeze spreadsheet demo.

____________

In the old days, the Sort dialog box in Excel only had 3 levels.

However, with a bit of planning, you could sort Excel data by 4 columns or more, and once you learned that trick, life was good. Or at least it was sort of good. 😉

Sorting in Excel 2007

In Excel 2007, the Sort dialog box is much fancier, and you can include up to 64 sorting levels.

I’ve never needed anywhere near that many – 5 or 6 fields is plenty for most tables that I’ve had to sort.

Sort By Colour

Another new feature in Excel 2007 is the ability to sort by cell or font colour, or by cell icon.

If you have different colours in a column, you can choose one to show up at the top or bottom of a sorted list.

If you used conditional formatting to add cell icons, such as traffic lights, you can sort by those icons.

To put the colours or cell icons in a specific order, you can add the same field multiple times in the Sort dialog box, and choose a different colour or cell icon for each sorting level.

This won’t be too difficult if you have only a few colours in the list, but will be more challenging if you have lots of colours.

Worksheet List Sorted by Colour

The list on my worksheet, that was previously sorted by date, is now sorted by the colours, in the order that I selected above.

Yellow isn’t in the Sort level specifications, so it appears at the bottom of the list.

Sort By Colour At Your Own Risk

Even though you can sort by colour now, I wouldn’t recommend it. I’ve seen too many rainbow coloured Excel worksheets, where nobody can remember what the colours mean.

Is yellow good? Is blue bad? Is there a colour code somewhere?

Sort By Text or Number

I’d rather add another column in the worksheet, and put a number code or text comment there. S

o, instead of marking the overdue accounts with a red fill colour, type “Overdue” in another column, or use a formula to calculate which accounts are overdue.

Or, instead of highlighting the customer names that you want to send an email to, type an X in an Email column.

Then, you can sort or filter the Overdue accounts or the Email column, to focus on the rows of interest.

Do You Sort By Colour?

Maybe I’m missing something, and the feature is working well for you. Some people must have asked for the feature, since they added it to Excel 2007.

In your Excel worksheets, do you ever sort by colour? In what kind of lists or situations is it most helpful for you?
_______________

You might not love the Ribbon in Excel 2007, but the user interface does have new features that are an improvement over Excel 2003.

The new features are useful when you’re working with large formulas or long names. There are written steps and a video below, that show those features

Change the Formula Bar Height

In Excel 2003, if you click on a cell that contains a long formula, it can spill onto the worksheet, hiding the column headings.

It’s nice to see the formula, if you want to edit it, but annoying if you’re trying to do something else.

In Excel 2007, you can adjust the height of the formula bar, and the long formulas don’t cover the worksheet. To adjust the height:

1. Point to the bar at the bottom of the formula bar.
2. When the pointer changes to a two headed arrow, drag up or down, to change the formula bar height

Restore Formula Bar Height

After you’ve adjusted the height, you can quickly restore it to its previous height, by clicking the Collapse Formula Bar button.

When the formula is collapsed, the button changes to Expand Formula Bar. Click that to return to the previous height setting.

Also, if the formula is too long to show in the formula bar at its current height, scroll buttons appear, as you can see in the screen shot above.

You can click the scroll buttons to view the formula, if you don’t want to change the formula bar height.

Widen the Name Box

Another new feature that I find really helpful is the ability to widen the Name Box. Instead of just seeing the start of a long name, and three dots, you can widen the Name Box to see the entire name.

To adjust the Name Box width, point to the dividing line at the right edge of the Name Box, and drag to the right.

Restore Name Box Width

To return to the default Name Box width, double-click the dividing line. (At least I think it’s the default width – I’ve changed my settings too many times to remember exactly.)

Excel’s Help says, “The maximum width of the name box is half of the width of the worksheet.” However, I’m able to widen the Name Box to the full width of the worksheet, and narrow it to nothing.

Maybe that restriction applied in the Beta version, and the Help wasn’t changed.

Do You Use These Features?

I’ve used Excel for so many years without these features, that I often forget they’re available. Long formulas can be hidden in the formula bar when condensed, because they don’t spill onto the worksheet, the way they used to.

If a formula is cut off in a logical place, you might not even realize that part of it is hidden. Those scroll buttons are very subtle, and are the only clue that a formula doesn’t fit.

Do you remember to use the new sizing features? Do you prefer them to the Excel static settings?

Watch the Video

Here’s a short video that shows the new features for adjusting the formula bar height and the Name Box width.

It also shows how to temporarily collapse the Ribbon, so only the tabs are visible. That gives you an extra inch of space, if you need it when working on a large worksheet.

_______________