Contextures Blog

Yes, if your last name is Dalgleish, some people think it sounds like “Dog Leash”, and hilarity ensues. And no, I never get tired of that joke, thanks for asking. 😉

Anyway, unlike the proverbial old dog, those of us who have been using Excel for a long can CAN learn new tricks. Keep reading to discover which new Excel feature I recently discovered.

Long Time Excel Users

Maybe you’re a long time Excel user too. Last month I polled readers on my Debra D’s Blog, and asked How Long Have You Been Using Excel?

Almost half of the 80 respondents have used Excel for 16 years or more, and they shared some interesting stories in the comments.

Old Excel Habits

You learn a lot about Excel over the years, and appreciate both its strengths and limitations. You find workarounds for some of the features that don’t work the way you’d like, and accept that some things can’t be done in Excel.

Maybe you’ve even learned to love (tolerate?) the Excel Ribbon, despite all the years that you spent learning where the Menu commands were.

But occasionally, new features are added, without much hoopla, and you don’t even notice them. At least, that’s what happened to me!

Old Excel Header Tricks

Excel has customizable headers and footers, where you can place items like the date, or file name, in one of three sections — left, centre or right.

On the Excel Ribbon, click the Insert tab, and click the Header & Footer command, to change to Page Layout view, with the Header activated.

See Header and Footer Tools

While the Header or Footer are activated, there’s a Design tab on the Ribbon, with Header and Footer tools.

You can click on an Element, like Page Number, to add it to a section in the Header or footer.

Or, select one of the default options from the Header or Footer drop down lists.

The Page Layout wasn’t available in previous versions of Excel, but the Header and Footer Elements are pretty much the same as they’ve always been.

New Excel Header Tricks

After working with Excel 2007 for almost three years, I finally noticed that the Excel Header and Footer have some fancy new Options. (How embarrassing!)

Now you can have a different header/footer on the first printed page, and different header/footer on the odd and even printed pages.

So, you could show a title on the first page only, then have page numbers at the left on even pages, and on the right on odd pages.

Microsoft Word has always had these options, but Excel didn’t. We just accepted that “Excel isn’t a word processing program” and did without the header options.

Align Header and Footer

You can also align the Excel Header and Footer with the page margins, which is another nice feature. In the old days, the Header and Footer margins couldn’t be changed, so they sometimes looked a bit out of line with the rest of the printed page.

Here’s the Page Layout view, with the Align With Page Margins feature turned on.

I hope you found these new Excel Header and Footer options long before I did, and remember to use them in your printed worksheets.
__________

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.

____________