Using Custom Number Formats

Category: General | [Item URL]

A companion file is available: Click here to download

One of the most useful (and underutilized) features in Excel is the ability to create custom number formats. Although Excel offers a wide variety of standard number formats, it's often advantageous to develop custom formats. For example, if you use large values, you can scale the display of those values so they appear "in thousands." You can make 123,456,789 appear as 123,457 by applying this format:

  #,##0,

A number format consists of three parts: code for negative, code for zero, and code for positive values. The code for each part is separated with a semicolon. If you would like a cell to appear empty, use this format (which is three semicolons and nothing else):

;;;

To apply a custom number format:

1. Select the cell or range that you want to format
2. Choose the Format Cells command (or press Ctrl+1)
3. Click the Number tab on the Format Cells dialog box
4. Click the Custom category
5. Enter the number format code into the edit box labeled Type.

Rather than provide a complete tutorial on custom number formats, I refer you to Excel's help. You'll find a comprehensive reference for the formatting codes.

NOTE: It's important to understand that a number format affects only the way in which the number appears. A number format does not change the underlying value in the cell.

Navigating Excel’s Sheets

Category: General | [Item URL]

Every Excel user knows that you can activate a different sheet in a workbook by clicking its sheet tab. Most users also know that you can press Ctrl-Page Up to activate the previous sheet, and Ctrl-Page Down to activate the next sheet.

But if your workbook contains many sheets, and not all of the sheets' tabs fit on the screen, you may find it tedious to scroll through the tabs or use the keyboard to activate a distant sheet.

Here's a little-known trick that will allow you to bypass repeated scrolling or typing: Display a pop-up list of sheet names by right-clicking one of the arrows to the left of the sheet tabs that are located at the bottom of the worksheet window (see the figure below). Select a sheet from the list, and you're there in a flash.

Override Excel’s Text Import Wizard

Category: General | [Item URL]

If you import text files into Excel, you've undoubtedly encountered the Text Import Wizard, which guides you through parsing the text.

If the files you import are always parsed correctly, you may prefer to bypass this wizard and accept the defaults. To do so:

1. Select File, Open
2. Locate the file to be imported
3. Hold down Shift while you click Open

Note: When you import a file with a .csv extension, the Text Import Wizard won't kick in. To override this default, you need to change the file's extension from .csv to .txt.

Sharing Autocorrect Shortcuts

Category: General / General VBA | [Item URL]

Q. I've set up approximately 200 Excel AutoCorrect shortcuts that represent various products and services offered by my company. What's the best way to transfer these shortcuts to other systems so that my coworkers can use them?

AutoCorrect, which debuted in Excel 95, can correct common spelling errors on the fly. As many users of Excel have discovered, you can also assign "shorthand shortcuts" that expand to a longer sequence of characters. For example, you may define "awc" as an AutoCorrect shortcut for "Amalgamated Widget Corporation of America." When you type awc into a cell, Excel will then replace it with the associated text string. You define and manage your shortcuts by using the Tools, AutoCorrect command.

Excel and all other Microsoft Office applications store AutoCorrect shortcuts in a single *.acl file in your Windows folder (the exact file name will vary). So changes you make from Excel, say, will be available in Word. However, there's no tool for manually editing the binary *.acl file and moving such a file from one system to another will invalidate your existing entries. The solution is to use a VBA macro to create the shortcuts.

Start Excel with a new workbook with one sheet, and then enter your shortcuts and their associated text into columns A and B, respectively (as in the figure below). Enter as many as you like, beginning in row 1, and don't include any blank rows between the entries. Save and name this worksheet.

Select Tools, Macro, Macros to display the Macros dialog box. Type CreateShortcuts in the Macro Name field and click Create. Then enter the following macro into the VBA module, and press Alt-F11 to return to Excel.

Sub CreateShortcuts()
  ItemCount = Application.CountA(Range("Sheet1!A:A"))
  For Row = 1 To ItemCount
    ShortText = Cells(Row, 1)
    LongText = Cells(Row, 2)
    Application.AutoCorrect.AddReplacement ShortText, LongText
  Next Row
End Sub

Save the workbook and distribute it to your coworkers. To add the AutoCorrect shortcuts, open the workbook, select Tool, Macro, Macros, and then execute the CreateShortcuts macro. Be aware that existing shortcuts with the same name will be overwritten without warning.

Making A Worksheet Very Hidden

Category: General | [Item URL]

You can use Excel's Format, Sheet, Hide to hide an entire worksheet. But unless you password-protect the workbook structure, anyone can select Format, Sheet, Unhide to see the hidden sheet.

If you use Excel 97 or later, here's another option:

1. Press Alt-F11 to display the Visual Basic Editor
2. in the Project window, double-click Microsoft Excel Objects and select the sheet to hide.
3. Press F4 to display the Property box
4. Set the sheet's Visible property to xlSheetVeryHidden.

To unhide the sheet, repeat these step, but set the Visible property to xlSheetVisible.

Importing A Text File Into A Worksheet

Category: General | [Item URL]

Excel doesn't provide any obvious way to insert the contents of a text file into a worksheet. Until Excel 2000, all versions of Excel required you to open the text file separately, then copy and paste the data to your worksheet.

Excel 2000 (and later) makes the process much easier. You might expect to see this feature on the File menu, but instead you select Data, Get External Data, Import Text File. This command calls up a dialog box in which you select a text file, whereupon the Text Import Wizard lets you specify how Excel should import the data. Finally, in the Import Data dialog box, you indicate the desired worksheet destination.

Excel imports the text file as a database query, which you can update if the data in the text file changes (select Data, Refresh Data to do so). To import the file as static data, click Properties in the Import Data dialog, and remove the check mark from the "Save query definition" box.

Using A Workspace File

Category: General | [Item URL]

If you usually work with a number of workbooks simultaneously, you might like the idea of opening all of your workbooks exactly where you left off the last time you closed Excel.

If so, you need to create a workspace file. Before you quit for the day, select Files, Save Workspace and specify a file name. To pick up where you left off in the last session, reopen the workspace file: Use File, Open and choose the workspace file (it'll have an .xlw extension).

Note: A workspace file contains only configuration information, not the actual workbooks and worksheets. Therefore, you can't simply copy the workspace file -- you'll need the workbook files, too.

Protecting Cells, Sheets, Workbooks, And Files

Category: General | [Item URL]

Excel offers many ways to protect your data. But understanding the differences between the methods and the limitations of each of them can be daunting. Here's a quick review of four protection options in Excel 97 and later, plus explanations of how to use them.

Note: These protection features aren't foolproof. Password-cracking utilities exist, and anyone who wants to defeat your protection badly enough probably can.

Cell protection

Every cell has two key properties: locked and hidden. A locked cell can't be changed, and the contents of a hidden cell don't appear in the formula bar when the cell is selected. By default, every cell is locked and not hidden. Locked and hidden attributes have no effect unless the worksheet is protected.

To change these attributes, select the appropriate cell or a range and then choose Format, Cells. In the Format Cells dialog box, click the Protection tab and select Locked or Hidden. Unlock cells that accept user input, and lock formula cells and other cells that should stay unchanged (such as titles). To prevent others from seeing your formulas, lock and hide the formula cells--the results of the formulas will be visible, but the formulas will not.

To protect a sheet, choose Tools, Protection, Protect Sheet to bring up the Protect Sheet dialog, and make sure Contents is checked. You can enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited, and other worksheet changes are disabled. For example, no one can insert rows or columns, change column width, or create embedded charts.

Note: Excel 2002 offers additional options regarding what the user can and cannot do when a sheet is protected. For example, you can protect a sheet and still allow sorting and autofiltering.

Workbook window protection

The Tools, Protection, Protect Workbook command enables you to protect a workbook so no one can add or delete sheets or resize or move the workbook window.

Workbook file protection

Here's how to save a workbook in such a way that you must supply a password to open or modify it:

• In Excel 97, select File, Save As to display the Save As dialog box; then click Options in that box to call up the Save Options dialog.
• In Excel 2000 and later, select Tools, General Options when the Save As dialog box appears. If you enter a password in the "Password to open" field, the user must provide the correct password to open the file. If you enter a password in the "Password to modify" field, the user must give the right password to make changes to the file. Without the correct password, the file opens in read-only mode.

Note: Excel 2002 and later also lets you specify the type of encryption, if any, that will be used. Access this feature by clicking the Advanced button in the Save Options dialog box.

VBA project protection

If your workbook contains VBA macros, you can use these to prohibit others from viewing or modifying them. Press Alt-F11 to activate the VB Editor window, and select your project from the Project window. Choose Tools, VBA Project Properties. Click the Protection tab, place a check mark next to "Lock project for viewing," and enter the password twice. Click OK and save your workbook.

Resize Excel’s Sheet Tabs

Category: General | [Item URL]

If your eyesight isn't what it used to be, you may want to increase the size of the sheet tabs displayed in your Excel workbooks.

This parameter is determined by a Windows system-wide setting. To change it, select Start, Settings, Control Panel and double-click Display. In the Display Properties dialog box, click the Appearance tab, and choose Scrollbar from the Item list. Adjust the sheet tabs until they're the size you want. Be aware that this setting affects the scroll bars in all your applications.

Changing The Number Of Sheets In A New Workbook

Category: General | [Item URL]

By default, each new Excel workbook begins life with three worksheets. You can, of course, add more sheets to the workbook or delete sheets you don't need. The unused sheets don't occupy additional memory or increase file size, but I generally don't like them in my workbooks.

A better approach is to change the default. Select Tools, Options and click General in the Options dialog box. Then change the setting for "Sheets in new workbook". Now all new workbooks will have the number of sheets you specified.

I recommend that you change this setting to 1.

Page 2 of 3 pages
[Previous page]   [Next page]