Author: Debra Dalgleish

Without family, where would Excel blog ideas come from?

My daughter called today, and asked how to sort more than 3 columns in Excel. She uses Excel 2003, and the Sort dialog box only allows three column choices.

There’s no box to choose the fourth column – Name.

Sort By Multiple Columns

Obviously, she didn’t check my website, where she would have found the instructions to sort by four columns in Excel.

You can sort in groups of 3, working up from the least important to the most important.

So, I told her to sort the fourth column (Name), then the first three (Service, City, Rating).

Sorting in Excel 2007

If you like to sort lots of columns, you’ll be happier in Excel 2007. There, you can sort up to 64 columns at one time.

If my daughter had Excel 2007, she could choose all four columns, and sort them in one step.

In the Sort dialog box, click the Add Level button, to include another column in the sort specifications.

Automating the Sort

If you turn on the macro recorder while sorting in Excel 2007, you can create a macro to sort the table.

Then, run that macro later, when you need to sort the table again, using the same settings.

Macro Error in Excel 2003

However, if you’re sending the workbook to someone who uses Excel 2003, they’ll get an error message if they try to run your sort code.

Just like families, sometimes the different generations don’t communicate too well. 😉 Because the sort feature changed so much in Excel 2007, the older versions don’t recognize some of the new properties.

Sort Macro for All Excel Versions

If you plan to use the workbook in both versions of Excel, record the sort macro in Excel 2003. This will also run without problems in Excel 2007.

Here’s the code from this example, with the Name in column D being sorted first.

_______________

You open lots of Excel files every day, and sometimes open the same file several times.

In Excel 2007, if you click the Office Button, you can see a list of documents that you’ve opened recently. Click on file name, to open that file again.

Change the Number of Documents

My Recent Documents List was showing 17 files, and I guess that’s the default number, since I don’t remember changing it.

Instead of leaving the default setting, you can show more or fewer files.

To change the setting:

1. Click the Office Button, then click the Excel Options button
2. Click the Advanced category
3. In the Display section, change the number for Show this number of Recent Documents.
4. Click OK, to close the Excel Options window.

The maximum number of files is 50, but that many might not show unless you have a really tall monitor.

Change the Setting in Excel 2003

In Excel 2003, the maximum number of files you can show in the list is 9. To change the setting:

1. On the Tools menu, click Options.
2. On the General tab, change the number for the Recently Used File List.
3. Click OK, to close the Option dialog box.

Clear the Recent Documents List

In either version of Excel, you can clear the list, by changing the number to zero.

In Excel 2003, you can also remove the check mark from the Recently Used File List setting.

After the list is cleared, you can change the setting to a higher number, to start building the list again.

Pin Items to the List

Normally, the older items in the list drop off the bottom, as new files are opened. In Excel 2007, there’s a push pin icon at the right of each file name. To keep a file on the list, click that push pin, to activate it.

This is another one of the Excel 2007 features that I didn’t notice until recently. (Maybe that’s why it’s call the Recent Documents list!) Now I use it quite often, to “stick” files that I’m working with for a few days.

In the screen shot below, the ProjectWorkCurrent.xlsm file is pinned to the list. Instead of the flat grey push pin, there’s a vertical green push pin.

That file will work it’s way down the list, if it’s not opened for a while, but it will stick to the list, and won’t drop off.

_____________

Almost a year ago, we talked about creating a table of contents sheet in Excel. In that post, one of the suggestions was to type a list of sheets on a worksheet, then change each sheet name into a hyperlink.

Today, you’ll see a quicker way to create hyperlinks — by dragging and dropping. There are written steps and a video below.

Continue reading “Creating Excel Hyperlinks Is a Drag”