Every now and then I have to reverse the order of a long list in Excel that is not in alphabetical or numerical order—a tedious job. Do you know of a shortcut?

Yes, you can adapt Custom Lists, a tool you should become familiar with because it is versatile and very handy. Once I show you how to adapt it to reverse a list, you’ll quickly see how you can use it for other functions as well.

First, copy (Ctrl+C) the whole list (One, Two, Three…Six) and then paste it in another location (any empty space in your worksheet or to a new worksheet) by right-clicking and selecting Paste Special, Values, OK (see screenshot at the left).

Then select the whole list and go to Tools, Options, Custom Lists. Notice that the copied list address appears at the bottom of the screen (see screenshot below).

Click on Import and the list itself will appear (see screenshot above).

Now comes the neat part. Go back to an empty column next to the original list and enter the last item in your original list (Six) at the top of the column. Then enter the second from last item (Five) under that; that gives Excel the critical clue to the sequence you wish—reversing the list. Finally, select both cells and double-click on the Fill Handle (small black square) and the whole list, in reverse, will fill the column.

If you return to Custom Lists (Tools, Options), you’ll see your list under Custom Lists.

When you’re finished with the custom list, you can erase it, if you wish, by highlighting it and clicking on Delete.

As you can see, it’s a handy little tool when dealing with custom lists.

I use a spreadsheet for my work that’s formatted in a very complicated way. So every time I open a new worksheet, I have to go through all the preliminary format setup steps to prepare it for my special requirements—such as columns with varying widths and even different colors for numbers in certain rows. Is there some way to adjust Excel to do that time-consuming setup for me?

You can create a macro to do that by clicking on Tools, Macro, Record New Macro, and then, after you type in where you want the macro stored and add a Shortcut key and a Description, click on OK and…

…then go through all the formatting steps. When you’re finished, click on the Stop Recording button (see screenshot at left).

Another way—and one that I personally prefer because it’s so simple and avoids some of the security drawbacks inherent with macros—is to create a template of your ideal worksheet. Then, when you need your special worksheet, just one click brings it up. You can evoke it as many times as you wish. In fact, if you like the template idea, you may even want to set up a group of templates—one for each unique setup (whether in Excel or Word, for that matter) and then create a folder to store them and a shortcut for the collection on your desktop for convenient access.

By the way, a template stores not just formatting (borders, shading, etc.), it also stores column and row headings, and even charts and formulas and toolbars for customized features. So you see, it can be quite powerful.

To create a template, open a new Excel file and set up your model worksheet. Then save it (Ctrl+S) to a convenient folder using the Template designation (see screenshot below). If you’re starting from an existing file, you must use the Save As command.

Unlike ordinary shortcuts, templates have a welldeserved golden crown (see screenshot at right).

I’ve been told that any time I make a major change in my computer—such as installing new software or new hardware— I should create a new System Restore point. But that function is buried under many layers, and so, when I’m busy, more often than not I fail to follow my own wise advice. Is there some easy way to launch it so I’d be more inclined to do it?

You were given good advice. It’s not uncommon for a change in your computer setup to trigger problems. The System Restore function is designed to save your computer’s settings to an earlier time when it was operating correctly, and then, when evoked, returns your computer’s settings to that earlier, healthy state. Although the restoration will revert your settings, turning the clock back will not cause you to lose any data—files or e-mail. Only the settings will revert.

Although a major systems change in your computer will automatically trigger the creation of a new restore point, called a system checkpoint, you also can initiate the action yourself, and, as you say, if you have the triggering mechanism handy, it’s more likely you’ll take advantage of it.

To create a shortcut, click on Start, All Programs, Accessories, System Tools, and right-click on System Restore. Then select Create shortcut and drag the shortcut onto your desktop. If you don’t want the shortcut on your desktop, you can put it in your Start menu instead (that’s where system icons appear stacked in a column when you click on your Start button). To put it there, instead of clicking on Create a shortcut, click on Pin to Start menu.

While you’re there, customize its settings so it meets your particular needs. When you first open the System Restore screen, you have the option to adjust its settings by clicking on System Restore Settings.

You also have the option of turning it off (not recommended) and selecting which drives you want monitored. Since I keep all my system files on the C: drive, using the G-Iomega drive for backup only, I only activate C:.

Click on the Settings button to bring up the Disk space usage screen, where you can adjust how much of the C: drive to reserve for restore point storage. I would suggest setting it to Max. Then click on OK.

Now create a system checkpoint. Return to the opening screen and click on Create a restore point.

Add a description for your checkpoint and click on Create at the bottom of the screen.

After a few seconds, when this screen appears (see screenshot below), the checkpoint has been recorded.

Each year I take some of my prior-year Excel workbooks and delete all the old data except for the formulas—a tedious process. Is there a better way to do this?

Excel has a tool for that. Highlight the area of the worksheet you want cleared. In this case, since the data is scattered throughout the entire worksheet, press Ctrl+A. However, if you want to save your labels, just highlight the range you want cleared. Don’t worry, this won’t erase your formulas. Now press F5, which brings up the Go To menu, and then click on Special, evoking the Go To Special screen (see screenshot on right). Since you only want the numbers erased—the constants—click on the Constants radio button. Click OK. If you want comments removed also, return to the Go To menu and click on that button.

Now press the Del key, and the worksheet is immediately ready for next year—with all the constants removed but all the formatting and formulas in their proper place.

I usually set up my annual budget in one Excel workbook, and then I add 12 more sheets—one for each month of the year—so I can easily track the monthly data. Each sheet contains the same rows, columns and formulas. Only the headings on each worksheet are different, along with the raw data for each month, of course. I use this setup because I was told it would make it easy for me to keep all the worksheets in sync—that is, if I make changes in the annual budget sheet, say, that change will be duplicated in the corresponding cells in all the monthly sheets—all in one swoop. Sounds great, except I can’t figure out how to do it. Can you help?

It’s a very simple process. In this example, let’s say you want to change the 2009 Budget for Plant C (see screenshot below) from $44,000,000 to $56,700,000, and you want that change to be reflected in all 12 monthly sheets. Select the tab for the first worksheet in the series; in this case it’s Annual (see screenshot below). Hold down the Shift key as you click on the tab of the last sheet in the series in which you want the change reflected (that would be May). Notice how all the tabs change color—indicating that you’ve “linked” them.

Notice, too, that Excel also added the word [Group] to the title bar to alert you that you have a group of worksheets selected; as you’ll see, that alert is important.

Now make your changes to the worksheet that appears on the screen; they will now be simultaneously copied to the corresponding cells in every other linked sheet as well.

Important: When done, be sure to click on a tab of any worksheet other than the first in the range. That deselects the link with all the sheets. If you fail to do that, any subsequent changes you make will continue to be copied in all the other sheets, and that could lead to quite a mess.

• Ctrl+1: Opens the Format Cells menu
  
  
• Ctrl+Shift+&: Places a border around a cell or group of highlighted cells
  
  
• Ctrl+Shift+-: Removes the border
  
  
• Ctrl+Shift+$: Turns a cell or highlighted row or column into a dollars-and-cents format
  
  
• Windows logo key+E: Opens Windows Explorer