Excel Hacks

Chapter 1. Reducing Workbook and Worksheet Frustration

Hacks #1-15

Excel users know that workbooks are a powerful metaphor. But many users are equally aware that dealing with workbooks can cause a huge number of snags. The hacks in this chapter will help you avoid some of these snags while taking advantage of some of the more effective but often overlooked ways in which you can control your workbooks.

Before we leap into the hacks, though, it’s worth taking a quick look at some basics that will make it much easier to create effective hacks. Excel is a very powerful spreadsheet application, and you can do incredible things with it. Unfortunately, many people design their Excel spreadsheets with little foresight, making it difficult for them to reuse or update the spreadsheets they’ve so carefully built. In this section, we provide several tips you can follow to ensure that you’re creating spreadsheets that are as efficient as possible.

The 80/20 Rule

Perhaps the most important rule to follow when designing a spreadsheet is to take a long-term view and never assume you will not need to add more data or formulas to your spreadsheet because chances are good that you will. With that in mind, you should spend about 80% of your time planning your spreadsheet and about 20% implementing it. Although this can seem extremely inefficient in the short run, we can assure you that the long-term gain will far outweigh the short-term pain, and the planning gets easier after you’ve done it for a while. Remember that spreadsheets are about making it easy for users to get correct information, not just about presenting information that looks good only once.

Structural Tips

Without a doubt, the number one mistake most Excel users make when creating their spreadsheets is that they do not set up and lay out the data in the manner in which Excel and its features expect. Here are, in no particular order, some of the most common mistakes users make when setting up a spreadsheet:

• Unnecessarily spreading data over many different workbooks

• Unnecessarily spreading data over numerous worksheets

• Unnecessarily spreading data over different tables

• Having blank columns and rows in tables of data

• Leaving blank cells for repeated data

The first three items on the preceding list add up to one thing: you should always try to keep related data in one continuous table. Time and time again we see spreadsheets that do not follow this simple rule and thus are limited in their ability to take full advantage of some of Excel’s most powerful features, including PivotTables, subtotals, and worksheet formulas. In such scenarios, you can use these features to their full potential only when you’ve laid out your data in a very basic table.

It is no coincidence that Excel spreadsheets can comprise 65,536 rows but only 256 columns. With this in mind, you should set up tables with column headings going across the first row of your table, and related data laid out in a continuous manner directly underneath their appropriate headings. If you find you are repeating the same data over and over for two or more rows in one of these columns, resist the temptation to omit the repeated data by using blank cells to indicate repetition.

Make sure your data is sorted whenever possible. Excel has a rich set of lookup and reference formulas, some of which require that your data be sorted in a logical order. Sorting also will speed the calculation process of many functions significantly.

Formatting Tips

Moving beyond structure, formatting also can cause problems. Although a spreadsheet should be easy to read and follow, this should rarely be at the expense of efficiency. We are big believers in “keeping it simple.” Far too many people spend tremendous amounts of time formatting their spreadsheets. Although they don’t necessarily realize it, this time frequently comes at the expense of efficiency. Often the overuse of formatting adds size to your workbook, and although your workbook might look like a work of art to you, it might look terrible to someone else. Some very good universal colors to consider using in your spreadsheets are black, white, and gray.

It is always a good idea to leave at least three blank rows above your table (at least three, preferably more). These can then be used for criteria for features such as Advanced Filter and Database functions.

People also tinker with the alignment of cell data. By default, numbers in Excel are right-aligned and text is left-aligned, and there are good reasons to leave it this way. If you start changing this formatting, you will not be able to tell at a glance if the contents of a cell are text or numeric. It is very common for people to reference cells, which look like numbers but in reality are text. If you have altered the default alignment, you will be left scratching your head. Perhaps headings are an exception to this rule.

Format cells as text only when completely necessary. All data entered into cells formatted as text become text, even if you meant for them to be numbers or dates. Worse still, any cell housing a formula that references a text-formatted cell also will be formatted as text. Generally, you do not want formula cells to be formatted as text!

Merged cells can also cause problems. The Microsoft knowledge base is full of frequently encountered problems with merged cells. As a good alternative, use “Center across selection,” found on the Alignment tab of the Format Cells dialog under the Horizontal tab.

Formula Tips

Another enormous mistake users often make in Excel formulas is referencing entire columns. This forces Excel to examine potentially thousands, if not millions, of cells it otherwise could have ignored.

Assume, for example, that you have a table of data ranging from cell A1 to cell H1000. You might decide you want to use one or more of Excel’s lookup formulas to extract the required information. Because your table might continue to grow (as you add new data), it is common to reference the entire table, incorporating all rows. In other words, your reference might look something like A:H, or possibly A1:H65536. You would use this reference so that when new data is added to the table, it will be referenced in the formulas automatically.

This is a very bad habit to form and you should almost always avoid it. You still can eliminate the need to constantly update your formula references to incorporate new data as it is added to a table by using dynamic named ranges, discussed in [Hack #42]).

Another common problem with poorly designed spreadsheets is painfully slow recalculation. Many people suggest that shifting calculation mode into Manual via Tools → Options → Calculations will solve this problem.

However, this is generally very poor advice, fraught with potential disasters. A spreadsheet is all about formulas and calculations and the results they produce. If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information from your spreadsheet that will not have been updated. Your formulas might be reflecting old values and not the updated values because when you go into manual calculation mode, you must force Excel to recalculate by pressing the F9 key. However, it is very easy to forget to do this! Think of it this way. If your car brakes were rubbing and slowing down your car, would you disconnect the brake pedal and rely on the hand brake instead of fixing the problem? Most of us wouldn’t dream of doing this, but many people don’t hesitate to put their spreadsheets into manual calculation mode. If you need to run your spreadsheet in manual calculation mode, you have a design problem. Address it properly and do not apply a “Band-Aid” approach.

Array formulas are another common cause of trouble. They are best suited to referencing single cells. If you use them to reference large ranges, do so as infrequently as possible. When large numbers of arrays reference large ranges, your workbook’s performance will suffer, sometimes to the point where it becomes unusable and you are forced to run your spreadsheet in manual calculation mode.

Excel’s database functions provide many alternatives to array formulas, as discussed in [Hack #66]. Also, the Excel Help offers some very good examples on how you can use these formulas on large tables of data to return results based on multiple criteria. Another alternative that is often overlooked is the use of Excel’s PivotTable feature, discussed in Chapter 4. Although PivotTables might seem very daunting when first encountered, we highly recommend that you familiarize yourself with this powerful Excel feature because once you master PivotTables, you will wonder how you survived without them!

At the end of the day, if you remember nothing else about spreadsheet design, remember that Excel works best when all related data is laid out in one continuous table. That should make the rest of your hacking much easier.

Create a Personal View of Your Workbooks

Excel enables you to have multiple workbooks showing simultaneously, and to have a customized view of your workbooks arranged in different windows. Then you can save your view workspaces as .xlw files and use them when it suits you.

Sometimes when working in Excel, you might need to have more than one workbook open on your screen. This makes it easier to use or view data from multiple workbooks. The next few paragraphs describe how to do this in a neat and organized way.

Open all the workbooks you will need.

Tip

To open more than one workbook at a time, select File → Open..., press the Ctrl key while selecting the workbooks you want to open, and then click Open.

From any of the workbooks (it doesn’t matter which one), select Window → Arrange. If “Windows of active workbook” is checked, uncheck it, and then select the window arrangement you prefer and click OK.

If you select Tiled, you will be presented with your workbooks in a tiled fashion, as shown with blank workbooks in Figure 1-1.

Figure 1-1. Four workbooks in a tiled view

Selecting Horizontal gives you a view of your workbooks in a single stack, one on top of the other, as in Figure 1-2.

Figure 1-2. Four workbooks in a horizontal view

Checking the Vertical option will place all your open workbooks side by side, as shown in Figure 1-3.

Figure 1-3. Four workbooks in a vertical view

Finally, as shown in Figure 1-4, selecting the Cascade option will layer all your open workbooks one on top of the other.

Figure 1-4. Four workbooks in a cascade view

Once your workbooks are displayed in your preferred view, you can easily copy, paste, drag-and-drop, etc., between them.

If you think you might want to return to a view you created, you can save this preferred view as a workspace. To save a workspace, simply select File → Save Workspace, enter the workspace’s filename in the File Name box, and click OK. When saving your workspace, the file extension will be .xlw rather than the standard .xls. To restore your Excel workspace to one full window of a particular workbook, just double-click the blue titlebar appearing on any one of your workbooks. You can also click the Maximize button on any of the windows in your workspace. Close your workbooks as usual when you’re finished.

Whenever you need to open those same workbooks, simply open the *.xlw file, and the view you initially set up will be magically restored for all workbooks. If you need to open just one of these workbooks, open the file as usual. Any changes you make to the workbooks in the *.xlw file will be saved automatically as you close the workspace as a whole, or you can save workbooks individually.

If you spend a small amount of time setting up some custom views for repetitive tasks that require multiple open workbooks, you’ll find that these tasks become easier to manage. You might decide to use different views for different repetitive tasks, depending on what the task is or how you’re feeling that day.

Enter Data into Multiple Worksheets Simultaneously

It’s fairly ordinary to have the same data appear in multiple worksheets simultaneously. You can use Excel’s tool for grouping so that data in one workbook can be entered into multiple worksheets at the same time. We also have a quicker and more flexible approach that uses a couple of lines of Visual Basic for Applications (VBA) code.

Excel’s built-in mechanism for making data go to multiple places at once is a feature called Group. It works by grouping the worksheets together so that they’re all linked within the workbook.

Grouping Worksheets Manually

To use the Group feature manually, simply click the sheet into which you will be entering the data, and press the Ctrl key (the Shift key on the Macintosh) while clicking the Name tabs of the worksheets where you want the data to go. When you enter data into any cells on your worksheet, they will be entered automatically in the other grouped worksheets. Mission accomplished.

To ungroup your worksheets, either select one worksheet that is not part of the group or right-click any Name tab and select Ungroup Sheets.

Warning

When your worksheets are grouped together, you can look up to the titlebar and you will see the word Group in square brackets. This lets you know your worksheets are still grouped. Unless you have eagle eyes and a mind like a steel trap, however, it is highly likely that you won’t notice this or you’ll forget you have your worksheets grouped. For this reason, we gently suggest you ungroup your sheets as soon as you finish doing what you need to do.

Although this method is easy, it means you need to remember to group and ungroup your sheets as needed or else you will inadvertently overtype data from another worksheet. It also means simultaneous data entries will occur regardless of the cell you are in at the time. For example, you might want the simultaneous entries to occur only when you are in a particular range of cells.

Grouping Worksheets Automatically

You can overcome these shortcomings by using some very simple VBA code. For this code to work, it must reside within the private module for the Sheet object. To quickly go to the private module, right-click the Sheet Name tab and select View Code. You can then use one of Excel’s sheet events, which are events that take place within your worksheet, such as changing a cell, selecting a range, activating, deactivating, and so on, to move the code into the private module for the Sheet object.

The first thing to do to make grouping work is to name the range of cells you want to have grouped so that the data shows automatically on other worksheets.

Enter this code into the private module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("MyRange"), Target) Is Nothing Then
     'Sheet5 has purposely been placed first as this will
     'be the active sheet we will work from
        Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select
    Else
       Me.Select
    End If
End Sub

In this code, we used the named range MyRange. (If you aren’t familiar with named ranges, see [Hack #39]) Change MyRange to the range name you are using on your worksheet. Also change the three sheet names in the code, as shown in Figure 1-5, to the sheet names you want to be grouped. When you’re done, either close the module window or press Alt/

-Q to get back to Excel.

Figure 1-5. Code for automatically grouping worksheets

It is important to note that the first sheet name used in the array must be the sheet housing the code, and thus the worksheet on which you will enter the data.

Once the code is in place, each time you select any cell on the worksheet, the code checks to see whether the cell you selected (the target) is within the range named MyRange. If it is, the code will automatically group the worksheets you want grouped. If it isn’t, it will ungroup the sheets simply by activating the sheet you are already on. The beauty of this hack is that there is no need to manually group the sheets and therefore run the risk of forgetting to ungroup them. This approach can save lots of time and frustration.

If you want the same data to appear on other sheets but not in the same cell addresses, code like this:

Private Sub worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("MyRange"), Target) Is Nothing Then
       With Range("MyRange")
             .Copy Destination:=Sheets("Sheet3").Range("A1")
            .Copy Destination:=Sheets("Sheet1").Range("D10")
    End With
  End If
End Sub

This code also needs to live within the private module of the Sheet object. Follow the steps described earlier in this hack to get it there.

Prevent Users from Performing Certain Actions

Although Excel provides overall protection for workbooks and worksheets, this blunt instrument doesn’t provide limited privileges to users—unless you do some hacking.

You can manage user interactions with your spreadsheets by monitoring and responding to events. Events, as the term suggests, are actions that occur as you work with your workbooks and worksheets. Some of the more common events include opening a workbook, saving it, and closing it when you’re through. You can tell Excel to run some Visual Basic code automatically when any one of these events is triggered.

Warning

Users can bypass all these protections by disabling macros entirely. If their security is set to Medium, they’ll be notified of macros in the workbook upon opening it and will be offered the opportunity to turn them off. A security setting of High will simply turn them off automatically. On the other hand, if using the spreadsheet requires the use of macros, users might be more likely to have macros turned on. These hacks are a convenience and do not provide heavy-duty data security.

Preventing Save As... in a Workbook

You can specify that any workbook be saved as read-only by checking the “Read-only recommended” checkbox in the File → Save options. Doing so can prevent a user from saving any changes he might make to the file, unless he saves it with a different name and/or in a different location.

Sometimes, however, you might want to prevent users from being able to save a copy of your workbook to another directory or folder with or without a different name. In other words, you want users to be able to save on top of the existing file and not save another copy elsewhere. This is particularly handy when more than one person is saving changes to a workbook because you do not end up with a number of different copies of the same workbook, saved with the same name in different folders.

The Before Save event you’ll be using has existed since Excel 97. As its name suggests, this event occurs just before a workbook is saved, enabling you to catch the user before the fact, issue a warning, and stop Excel from saving.

Warning

Before trying this at home, be sure to save your workbook first. Putting this code into place without having saved will prevent your workbook from ever saving.

To insert the code, open your workbook, right-click the Excel icon immediately to the left of the File item on the worksheet menu bar, and select View Code, as shown in Figure 1-6.

Figure 1-6. Quick access menu to the private module for the workbook object

Tip

This shortcut isn’t available on the Mac. You’ll have to open the Visual Basic Editor (VBE) by pressing Option-F11, or by selecting Tools → Macro → Visual Basic Editor. Once you’re there, Ctrl-click or right-click This Workbook in the Projects window.

Type the following code into the VBE, as shown in Figure 1-7, and then press Alt/

-Q to get back to Excel proper.

Figure 1-7. Code once it’s entered into the private module (ThisWorkbook)

Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean,[RETURN] 
     Cancel As Boolean)
Dim lReply As Long
  If SaveAsUI = True Then
lReply = MsgBox("Sorry, you are not allowed to save this" & _
   "workbook as another name. Do you wish to save this " & _
   "workbook?", vbQuestion + vbOKCancel)
     Cancel = (lReply = vbCancel)
   If Cancel = False Then Me.Save
     Cancel = True
  End If
End Sub

Give it a whirl. Select File → Save and your workbook will save as expected. Select File → Save As..., however, and you’ll be informed that you’re not allowed to save this workbook under any other filename.

Preventing Users from Printing a Workbook

Perhaps you want to prevent users from printing your workbook—and probably having it end up in a recycling bin or left on a desk somewhere in plain sight. Using Excel’s Before Print event, you can stop them in their tracks. Enter the following code, as before, into the VBE:

Private Sub workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    MsgBox "Sorry, you cannot Print from this workbook", vbInformation
End Sub

Press Alt/

-Q when you’re done entering the code to save it and get back to Excel. Now each time users try to print from this workbook, nothing will happen. The MsgBox line of code is optional, but it’s always a good idea to include it to at least inform users so that they do not start hassling the IT department, saying there is a problem with their program!

If you want to prevent users from printing only particular sheets in your workbook, use this similar code instead:

Private Sub workbook_BeforePrint(Cancel As Boolean)
    Select Case ActiveSheet.Name
        Case "Sheet1", "Sheet2"
            Cancel = True
            MsgBox "Sorry, you cannot print this sheet from this workbook",_ 
vbInformation
    End Select
End Sub

Notice you’ve specified Sheet1 and Sheet2 as the only cases in which printing should be stopped. Of course, these can be the names of any sheets in your workbook; to add more sheets to the list, simply type a comma followed by the sheet name in quotation marks. If you need to prevent the printing of only one sheet, supply just that one name in quotes and drop the comma.

Preventing Users from Inserting More Worksheets

Excel lets you protect a workbook’s structure so that users cannot delete worksheets, rearrange the order in which they appear, rename them, and so forth. Sometimes, though, you want to prevent just the addition of more worksheets, while still allowing other structural alterations.

The following code will get the job done:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 Application.DisplayAlerts = False
    MsgBox "Sorry, you cannot add any more sheets to this workbook",_ vbInformation
    Sh.Delete
 Application.DisplayAlerts = True
End Sub

The code first displays the message box with the message and then immediately deletes the newly added sheet when the user clicks OK from the message box. The use of Application.DisplayAlerts = False stops the standard Excel warning that asks users if they really want to delete the sheet. With this in place, users will be unable to add more worksheets to the workbook.

Another way to prevent users from adding worksheets is to select Tools → Protection → Protect Workbook..., ensure that the Structure checkbox is checked, and click OK. However, as mentioned at the beginning of this hack, Excel’s worksheet protection is a rather blunt instrument and will prevent many other Excel features from working as well.

Prevent Seemingly Unnecessary Prompts

Excel’s chattiness can get a little old; always prompting you to confirm actions you just asked it to perform. Quit the conversation and let Excel get back to the action.

The types of prompts we are talking about are those that ask you whether you want to enable macros (when you do not have any), or whether you are sure you want to delete a worksheet. Here is how to get rid of the most common prompts once and for all.

Enabling Macros When You Don’t Have Any

Excel’s memory is like a steel trap when it comes to remembering that you recorded a macro in your workbook. Unfortunately, its memory of macros persists even though you might have since deleted one or more macros via Tools → Macro → Macros (Alt/Option-F8). Reopen the workbook and you’ll still be prompted to enable macros, even though there are none to enable.

Tip

You’ll be prompted to enable macros only if your security level is set to Medium. If it’s set to Low, macros are enabled without a peep; if it’s set to High, macros are disabled automatically for your protection.

When you record a macro, Excel inserts a Visual Basic module to hold your commands and home-brewed functions. Upon opening a workbook, Excel checks for the presence of modules, whether empty or macro-filled. Deleting a workbook’s macros deletes any code within the module, not the module itself—kind of like drinking the last of the milk, yet putting the empty carton back in the fridge. To avoid the unnecessary macro prompt, you need to remove the module. Here’s how to do that.

Open the VBE by selecting Tools → Macro → Visual Basic Editor (or by pressing Alt/Option-F11) and select View → Project Explorer. (On the Macintosh, the Projects window is always open, so you don’t need to open the Project Explorer.) You’ll see a window such as that shown in Figure 1-8.

Figure 1-8. Project Explorer modules with Modules folder expanded

Find your workbook in the Project Explorer and click the plus sign (+) to its left to expose the workbook’s component parts, particularly the modules. Click the plus sign to the left of the Modules folder to list any modules on board.

Right-click each module in turn and choose Remove Module from the context sensitive menu. Decline the offer to export the modules. Before blithely removing modules that might contain useful code, double-click each module in turn to make certain you don’t need them. Press Alt/

-Q as usual to get back to Excel’s spreadsheet view.

Prompting to Save Nonexistent Changes

You might have noticed that sometimes simply opening a workbook and taking a look around is enough to trigger Excel to prompt you to save changes to your personal macro workbook—despite the fact that you’ve made no changes whatsoever. Whether you know it or not, you most likely have a volatile function within your personal macro workbook.

A personal macro workbook is a hidden workbook created the first time you record a macro and opened each time you use Excel. A volatile function (or formula) is one that automatically recalculates each time you do almost anything in Excel, including opening and closing either the workbook or the entire application. Two of the most common volatile functions are the Today( ) and Now( ) functions.

So, although you might believe you’ve made no changes to the workbook at hand, those volatile functions running in the background might have. This counts as a change and triggers Excel’s prompt to save said invisible changes.

If you want Excel to stop prompting you to save changes you didn’t make, you have a couple of options open to you. The most obvious is not to store volatile functions within your personal macro workbook in the first place, and to delete any volatile functions that are already there. Or, if you need volatile functions, you can use this rather simple snippet of code to circumvent the check by tricking Excel into thinking your personal macro workbook has been saved the moment it opens:

Private Sub workbook_Open( )
    Me.Saved = True
End Sub

This code must live in the private workbook module of your personal macro workbook. To get there from any workbook, select Window → Unhide, select Personal.xls from Unhide Workbook, and click OK. Visit the VBE and enter the aforementioned code. Press Alt/

-Q to get back to Excel when you’re done.

Of course, if you have a volatile function you want to recalculate and you want to save the changes, you need to explicitly tell Excel to do so:

Private Sub workbook_Open( )
    Me.Save
End Sub

This macro will save your personal macro workbook automatically each time it is opened.

Stopping Excel’s Warning Prompts for Recorded Macros

One of the many drawbacks of recorded macros is that, although they’re pretty good at mimicking just about any command, they tend to forget your responses to prompts. Delete a worksheet and you’re prompted for confirmation; run a macro for the same and you’ll still be prompted. Let’s turn off those prompts.

Select Tools → Macro → Macros (Alt/Option-F8) to bring up a list of your macros. Make sure All Open Workbooks is selected in the Macros In: box’s pull-down menu. Select the macro you’re interested in and click the Edit button. Put the cursor before the very first line of code—the first line without an apostrophe in front of it, that is—and prepend the following:

Application.DisplayAlerts = False

At the very end of your code, append the following:

Application.DisplayAlerts = True

Your macro should look something like this:

Sub MyMacro( )
'
' MyMacro Macro
' Deletes the Active worksheet
'

'
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True
End Sub

Note that you’ve turned alerts back on at the end of your macro to reenable standard Excel prompts while working in Excel. Leave this out, and you’ll see no alerts at all, not even those that might have been good to include.

Warning

If your macro does not complete for any reason—a runtime error, for instance—Excel might never get to the line of code that turns alerts back on. If this happens, it’s probably wise to quit and restart Excel to set things back to the way they were.

Now you know how to use Excel without prompts. Be aware, though, that these prompts are there for a reason. Make sure you fully understand the purpose of a prompt before summarily turning it off.

Hide Worksheets So That They Cannot Be Unhidden

Sometimes you want a place for information that users can’t read or modify. Build a backstage into your workbook, a place to keep data, formulas, and other minutiae consumed by, but not seen in, your sheets.

A useful practice when setting up a new Excel workbook is to reserve one worksheet for storing information users do not need to see: formula calculations, data validation, lists, useful variables and special values, sensitive data, and so forth. Although you can hide the sheet by selecting Format → Sheet → Hide..., it’s a good idea to ensure that users can’t unhide it by selecting Format → Sheet → Unhide....

You can, of course, simply protect the worksheet. However, this still leaves it in full view—sensitive data, scary formulas, and all. Also, you can’t protect a cell linked into any of the controls available to you from the Forms toolbar.

Instead, we’ll fiddle with the worksheet’s Visible property, making it xlVeryHidden. From the VBE (Tools → Macro → Visual Basic Editor or Alt/Option-F11), make sure the Project Explorer window is visible by selecting View → Project Explorer. Find the name of your workbook within the Project Explorer and expand its hierarchy by clicking the + to the left of the workbook’s name. Expand the Microsoft Excel Objects folder within to reveal all your workbook’s worksheets.

Select the sheet you want to hide from the Project Explorer and reveal its properties by selecting View → Properties Window (or by pressing F4). Make sure the Alphabetic tab is selected, and look for the Visible property at the very bottom. Click the value box on the right associated with the Visible property and select the last option, 2 - xlSheetVeryHidden, as shown in Figure 1-9. Press Alt/

-Q to save your changes and return to Excel. The sheet will no longer be visible via the Excel interface and won’t appear as a choice under Format → Sheet → Unhide....

Warning

Once you have selected 2 - xlSheetVeryHidden from the Properties window, it might appear as though your selection had no effect. This visual bug sometimes occurs and shouldn’t concern you; if the sheet no longer appears in the Format → Sheet → Unhide... choices, you know it had the desired effect.

Figure 1-9. Properties window of a worksheet having its visible property set to 2 - xlSheetVeryHidden

To reverse the process, simply follow the preceding steps, this time selecting -1 - xlSheetVisible.

Customize the Templates Dialog and Default Workbook

If you tend to perform the same tasks or use the same spreadsheet layouts again and again, you can build your own Template tab into Excel’s standard Insert Template dialog to provide a quick starting point.

Imagine you have a spreadsheet containing days of the year and formulas summarizing various data for the days. You have formatted this spreadsheet beautifully with your company colors, logo, and required formulas, and you need to use it on a daily basis. Instead of reinventing the wheel (or copying and deleting what you don’t need) each day, you can save yourself a lot of time and trouble by creating a template.

Excel’s worksheet and workbook templates provide you with a running start on your next project, enabling you to skip the initial setup, formatting, formula-building, and so on. Saving a template worksheet simply means opening a new workbook, deleting all but one worksheet, and then creating the basic template you will be using. Once you’re finished, select File → Save As... and choose Template from the dialog’s Save As Type drop-down list. If your template is to be a workbook template—i.e., it will contain more than one worksheet—again add a new workbook, make all the necessary changes, select File → Save As..., and save as a template.

Template in hand, you can create a clone at any time by either selecting File → New... and selecting a workbook template, or by right-clicking the Worksheet tab and selecting Insert... from the context sensitive menu to insert a new worksheet from a template. Wouldn’t it be nice, though, to have those templates available to you right from Excel’s standard Insert Template dialog, or to set your preferred workbook as the default? You can, by creating your own Template tab.

Warning

This hack assumes you have a single installation of Excel running on your computer. If you have multiple copies or versions of Excel installed, this may not work.

Creating Your Own Template Tab

If you have a slew of templates—workbooks, worksheets, or both—that you use on a regular basis, you can group them together right there on the Insert dialog.

From within any workbook, select File → Save As... and, from the Files of Type pop-up menu, select Template (*.xlt). Excel will, by default, select the standard Templates or My Templates folder in which all your homegrown templates are kept. If you don’t already have a My Templates folder, create one as a subfolder of the Templates folder using the New Folder button.

Select File → New... on the worksheet menu bar (for Excel 2000 and above, choose General Templates from the New Workbook dialog that will appear). You should now see the tab you created (My Templates as depicted in the screen shot in Figure 1-10) on the dialog floating over your screen. You also should now see your Template workbooks and worksheets, as long as you saved them to this folder.

Figure 1-10. The Templates dialog

Using a Custom Default Workbook

Starting Excel opens a blank default workbook called Book1 containing three blank worksheets. This is fine and dandy if you want a clean slate each time you start Excel. If you’re like us, however, you tend to favor one workbook over the others. So, for us, opening Excel involves dismissing the default workbook and searching for our regular workbook. It sure would be handy to have that favored workbook open at the outset, ready for action.

To do so, save your default workbook (template) in the XLSTART folder (generally found in C:\Documents and Settings\Owner\Application Data\Microsoft\Excel\XLSTART on Windows, and in Applications/Microsoft Office X/Office/Startup/Excel under Mac OS X). Once you have done this, Excel will automatically use whichever workbook(s) you have in here as the default.

Tip

The XLSTART folder is where your personal macro workbook is created and saved automatically when you record a macro. The personal macro workbook is a hidden workbook. You also can have your own hidden workbooks open in the background if you want by opening the required workbook, selecting Window → Hide, closing Excel, and clicking Yes to save changes to the workbook you just hid. Now place this workbook in your XLSTART folder. All the workbooks you hide and place within the XLSTART folder will open as hidden workbooks each time you start Excel.

Note

Don’t be tempted to place too many workbooks into this folder, especially large ones, as all of them will open when you start Excel. Too many open workbooks can greatly slow down Excel’s performance.

Naturally, if you change your mind and decide to go back to a blank default workbook, simply remove the appropriate workbook or workbook template from the Startup folder.

Create an Index of Sheets in Your Workbook

If you’ve spent much time in a workbook with many worksheets, you know how painful it can be to find a particular worksheet. An index sheet available to every worksheet is a navigational must-have.

Using an index sheet will enable you to quickly and easily navigate throughout your workbook so that with one click of the mouse, you will be taken exactly where you want to go, without fuss. You can create an index in a couple of ways.

You might be tempted to simply create the index by hand. Create a new worksheet, call it Index or the like, enter a list of all your worksheet’s names, and hyperlink each to the appropriate sheet by selecting Insert → Hyperlink... or by pressing Ctrl/

-K. Although this method is probably sufficient for limited instances in which you don’t have too many sheets and they won’t change often, you’ll be stuck maintaining your index by hand.

The following code will automatically create a clickable, hyperlinked index of all the sheets you have in the workbook. The index is re-created each time the sheet that houses the code is activated.

This code should live in the private module for the Sheet object. Insert a new worksheet into your workbook and name it something appropriate—Index, for instance. Right-click the index sheet’s tab and select View Code from the context menu. Enter the following Visual Basic code (Tools → Macro → Visual Basic Editor or Alt/Option-F11):

Private Sub Worksheet_Activate( )
Dim wSheet As Worksheet
Dim l As Long
l = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    
    For Each wSheet In Worksheets
     If wSheet.Name <> Me.Name Then
      l = l + 1
      With wSheet
          .Range("A1").Name = "Start" & wSheet.Index
          .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
            "Index", TextToDisplay:="Back to Index"
      End With
         Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="",_ 
            SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
     End If
    Next wSheet
End Sub

Press Alt/

-Q to get back to your workbook and then save your changes. Notice that the code names (such as when you name a cell or range of cells in Excel) cell A1 on each sheet Start, plus a unique whole number representing the index number of the sheet . This ensures that A1 on each sheet has a different name. If A1 on your worksheet already has a name, you should consider changing any mention of A1 in the code to something more suitable—an unused cell anywhere on the sheet, for instance.

Warning

You should be aware that if you select File → Properties → Summary and enter a URL as a hyperlink base, the index created from the preceding code possibly will not work. A hyperlink base is a path or URL that you want to use for all hyperlinks with the same base address that are inserted in the current document.

Another, more user-friendly, way of constructing an index is to add a link to the list of sheets as a context-menu item, keeping it just a right-click away. We’ll have that link open the standard workbook tabs command bar. You generally get to this command bar by right-clicking any of the sheet tab scroll arrows on the bottom left of any worksheet, as shown in Figure 1-11.

Figure 1-11. Tabs command bar displayed by right-clicking the sheet scroll tabs

To link that tab’s command bar to a right-click in any cell, enter the following code in the VBE:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target 
As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
    On Error Resume Next
    Application.CommandBars("Cell").Controls("Sheet Index").Delete
    On Error GoTo 0
        Set cCont = Application.CommandBars("Cell").Controls.Add _
                        (Type:=msoControlButton, Temporary:=True)
        With cCont
                  .Caption = "Sheet Index"
             .OnAction = "IndexCode"
            End With
End Sub

Next, you’ll need to insert a standard module to house the IndexCode macro, called by the preceding code whenever the user right-clicks in a cell. It is vital that you use a standard module next, as placing the code in the same module as Workbook_SheetBeforeRightClick will mean Excel will not know where to find the macro called IndexCode.

Select Insert → Module and enter the following code:

Sub IndexCode( )
 Application.CommandBars("workbook Tabs").ShowPopup
End Sub

Press Alt/

-Q to get back to the Excel interface.

Now, right-click within any cell on any worksheet and you should see a new menu item called Sheet Index that will take you right to a list of sheets in the workbook.

Limit the Scrolling Range of Your Worksheet

If you move around your spreadsheet a lot, or if you have data you don’t want readers to explore, you might find it convenient to limit the visible area of your spreadsheet to only that which has actual data.

All Excel worksheets created in Excel 97 and above are 256 columns wide (A to IV) and 65,536 rows long. More often than not, your worksheet uses only a small percentage of the cells available to you. A nice bit of spring cleaning limits the worksheet’s scrollable area to just the part containing the data you want a user to see. You then can place data you do not want a user to see outside the scrollable area. Doing this also can make it less daunting to scroll around in a worksheet, as it is not uncommon for users to find themselves at row 50,000 and then start screaming that they are unable to find any data in a worksheet.

The easiest way to establish boundaries is simply to hide all the unused columns and rows. On your sheet, locate the last row containing data and select the entire row below it by clicking the row label. Press the Ctrl and Shift keys while pressing the down arrow to select all rows beneath. Select Format → Row → Hide to hide them all. Do the same thing for unused columns; find the last-used column, select the entire column to the right of it, press the Ctrl and Shift keys while pressing the right arrow, and select Format → Column → Hide. If all went according to plan, your useful cells should be surrounded by a gray moat past which you cannot scroll.

The second way to establish boundaries is to specify a valid range in the worksheet’s Properties window. Right-click the sheet’s tab at the bottom left of the window and select View Code from the context menu. Select View → Project Explorer (Ctrl-R) on Windows, or press

-R under Mac OS X to visit the Project Explorer. If the Properties window isn’t visible, press F4 to make it appear. Select the appropriate worksheet and visit the ScrollArea property in the Properties window.

Now, from within the Project Explorer, select the worksheet you want the scroll area limited to, and then, from the Properties window (shown in Figure 1-12), go down to the ScrollArea property. In the associated value field to the right, enter the preferred boundaries of your worksheet—$A$1:$G$50, for instance.

Figure 1-12. Project Explorer Properties window

You will be unable to scroll outside the area you have specified. Unfortunately, Excel will not save this setting after closing it. This means you need a very simple macro to automatically set the scroll area to the desired range by placing some code in the worksheet_Activate event.

Right-click the Sheet Name tab on which the scroll area should be limited and select View Code, then enter the following:

Private Sub Worksheet_Activate ( )
Me.ScrollArea = "A1:G50"
End Sub

As usual, press Alt/

-Q to return to Excel proper and save your workbook.

Although you will not see a visible clue, such as the gray moat of the first method, you won’t be able to scroll or select anything outside the specified area.

Warning

Any macro that tries to select a range outside this scroll area (including selections of entire rows and columns) will no longer be able to do so. This is true particularly for recorded macros, as they often use selections.

If your macros do select a range outside the scrollable area, you can easily modify any existing macros so that they are not limited to a specific scroll area while operating. Simply select Tools → Macro → Macros... (Alt-F8), locate your macro name, select it, and click Edit. Place the following line of code as the very first line of code:

ActiveSheet.ScrollArea = ""

As the very last line of code in your macro, place the following:

ActiveSheet.ScrollArea = "$A$1:$G$50"

So, your code should look something like this:

Sub MyMacro( )
'
' MyMacro Macro
' Macro recorded 19/9/2003 by OzGrid.com
'
 
'
ActiveSheet.ScrollArea = ""
    Range("Z100").Select
    Selection.Font.Bold = True
ActiveSheet.ScrollArea = "$A$1:$G$50"
Sheets("Daily Budget").Select
ActiveSheet.ScrollArea = ""
    Range ("T500").Select
    Selection.Font.Bold = False
ActiveSheet.ScrollArea = "$A$1:$H$25"
 
End Sub

Our recorded macro selects cell Z100 and formats it to boldface. It then selects the worksheet named Daily Budget, selects cell T500 on that sheet, and “unbolds” it. We added ActiveSheet.ScrollArea = "" so that any cell on the worksheet can be selected and then the scroll area can be set back to our desired range. When we select another worksheet (Daily Budget), we again allow the code to select any cell on this worksheet and set the scroll area for this worksheet back to the desired range.

A third method, the most flexible, automatically limits the scroll area to the used range on the worksheet within which you place the code. To use this method, right-click the Sheet Name tab on which you want the scroll area limited, select View Code, and enter the following code:

Private Sub Worksheet_Activate( )
    Me.ScrollArea = Range(Me.UsedRange, Me.UsedRange(2,2)).Address
End Sub

Now press Alt/

-Q or click the X in the top righthand corner to get back to Excel and save your workbook.

The preceding macro will run automatically each time you activate the worksheet in which you placed it. However, you might encounter a problem with this macro when you need to actually enter data outside the existing used range. To avoid this problem, simply use a standard macro that will reset your scroll area back to the full sheet. Select Tools → Macro → Visual Basic Editor, then select Insert → Module, and enter the following code:

Sub ResetScrollArea( )
    ActiveSheet.ScrollArea = ""
End Sub

Now press Alt/

-Q or click the X in the top righthand corner to get back to Excel and save your workbook.

If you want to, you can make your macro easier to run by assigning it to a shortcut key. Select Tools → Macro → Macros... (Alt/Option-F8). Select ResetScrollArea (the name of your macro), click Options, and assign a shortcut key.

Each time you need to add data outside the established bounds of your worksheet, run the ResetScrollArea macro to readjust the borders. After you run the macro, make any changes you were unable to make while the scroll area was limited. When you’re finished, activate any other worksheet and then activate the worksheet you just modified. Activation of the worksheet will cause the code to run and limit the scroll area to the desired range.

Lock and Protect Cells Containing Formulas

You may want to let users change cells containg data without providing them access to change formulas. You can keep cells containing formulas under lock and key without having to protect your entire sheet or workbook.

When we create a spreadsheet, most of us need to use formulas of some sort. Sometimes, however, you might not want other users to tamper/delete/overtype any formulas you included on your spreadsheet. The easiest and most common way of barring people from playing with your formulas is to protect your worksheet. However, protecting your worksheet doesn’t just prevent users from tampering with your formulas, it also stops users from entering anything at all. Sometimes you do not want to go this far.

By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.

Select all cells on your worksheet, either by pressing Ctrl/

-A or by clicking the gray square at the intersecting point of column A and row 1. Then select Format → Cells → Protection and uncheck the Locked checkbox to remove the tick. Click OK.

Now select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. You’ll see a dialog box such as that in Figure 1-13.

Figure 1-13. The Go To Special dialog

Select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK. With only the formula cells selected, select Format → Cells → Protection and check the Locked checkbox to insert a tick. Select OK. Now select Tools → Protection → Protect Worksheet to protect your worksheet and apply a password if required.

The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell. You can overcome this problem in two ways.

The first approach doesn’t use worksheet protection at all, and uses data validation instead.

Warning

Data validation is far from bulletproof when it comes to preventing users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation.

To see what we mean, select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. Now select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK.

With only the Formula cells selected, select the Data → Validation → Settings page tab, select Custom from the Allow: box, and in the Formula box, enter ="“, as shown in Figure 1-14. Click OK.

Figure 1-14. Validation formulas

This method will prevent a user from accidentally overtyping into any formula cells — although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc. However, the big advantage to using this method is that all of Excel’s features are still usable on the worksheet.

The last method also will enable you to use all of Excel’s features, but only when you are in a cell that is not locked. To start, ensure that only the cells you want protected are locked and that all other cells are unlocked. Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Locked = True Then
        Me.Protect Password:="Secret"
    Else
        Me.Unprotect Password:="Secret"
    End If
End Sub

If no password is used, omit Password:="Secret“. If a password is used, change the word Secret to your password. Press Alt/

-Q or click the X in the top righthand corner to get back to Excel and save your workbook. Now, each time you select a cell that is locked, your worksheet will automatically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself.

Warning

This hack doesn’t work perfectly, though it usually works well enough. The keyword used in the code, Target, will refer only to the cell that is active at the time of selection. For this reason, it is important to note that if a user selects a range of cells (with the active cell being an unlocked cell), it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet automatically will unprotect itself.

Find Duplicate Data using Conditional Formatting

Excel’s conditional formatting is generally used to identify values in particular ranges, but we can hack it to identify duplicated data within a list or table.

People frequently have to identify duplicated data within a list or table. Doing this manually can be very time-consuming and error-prone. To make this job much easier, you can hack one of Excel’s standard features, conditional formatting.

Take, for example, a table of data with a range of $A$1:$H$100. Select the top-left cell, A1, and drag it over and down to H100. It is important that A1 be the active cell in your selection, so dragging from H100 to A1 isn’t quite the same. Select Format → Conditional Formatting... and, in the Conditional Formatting dialog box, select Formula Is from the top-left pop-up menu. In the field to its right, enter the following code:

=COUNTIF($A$1:$H$100,A1)>1

Click the Format tab (that’s the Format button under Mac OS X), followed by the Patterns tab, and select a color you want applied to visually identify duplicate data. Click OK to return to the Conditional Formatting dialog box and click OK again to apply the formatting.

All those cells containing duplicate data should be lit up like a Christmas tree in the color you chose, making it much easier to eyeball duplicate data and delete, move, or alter it as appropriate.

It is vital to note that as A1 was the active cell in your selection, the cell address is a relative reference and is not absolute, as is your table of data, $A$1:$H$100. By using conditional formatting in this way, Excel automatically knows to use the correct cell as the COUNTIF criterion. By this we mean that the conditional formatting formula in cell A1 will read as follows:

=COUNTIF($A$1:$H$100,A1)>1

while in cell A2, it will read:

=COUNTIF($A$1:$H$100,A2)>1

in cell A3, it will read:

=COUNTIF($A$1:$H$100,A3)>1

and so forth.

If you need to identify data that appears two or more times, you can use conditional formatting with three different conditions and color-code each condition for visual identification. To do this, select cell A1 (the cell in the top lefthand corner of table) and drag it down to H100. Again, it is important that A1 is the active cell in your selection.

Now select Format → Conditional Formatting... and, from the box containing the text “Cell Value Is”, select Formula Is. In the box to the right of Formula Is, enter the following code:

=COUNTIF($A$1:$H$100,A1)>3

Click the Format tab and then the Patterns page tab, and select a color you want to apply to identify data that appears more than three times. Click OK, then click Add, and from the Condition 2 box, select Formula Is and enter the following formula:

 =COUNTIF($A$1:$H$100,A1)=3

Tip

Instead of retyping the formula, highlight it from the Condition 1 box, press Ctrl/

-C to copy, click the Formula box for Condition 2, press Ctrl/

-V to paste, and then change >3 to =3.

Click the Format tab and then the Patterns page tab and select a color you want to apply to identify data that appears three times. Click OK, and then click Add. From the Condition 3 box, select Formula Is and enter the following formula:

 =COUNTIF($A$1:$H$100,A1)=2

Finally, click the Format tab and then the Patterns page tab. Select a color you want to apply to data that appears twice. Click OK. You will have different cell colors depending on the number of times your data appears within your table of data.

Again, it is vital to note that as A1 was the active cell in your selection, the cell address is a relative reference and is not absolute, as is your table of data, $A$1:$H$100. By using conditional formatting in this way, Excel automatically knows to use the correct cell as the COUNTIF criterion.

Tie Custom Toolbars to a Particular Workbook

Although most toolbars you build apply to just about any work you do, sometimes the functionality of a custom toolbar applies to only one workbook in particular. With this hack, you can tie custom toolbars to their respective workbooks.

If you’ve ever created a custom toolbar, you have no doubt noticed that the toolbar is loaded and visible regardless of which workbook you have open. What if your custom toolbar contains recorded macros meant only for a specific workbook? It’s probably best to tie special-purpose custom toolbars to the appropriate workbooks to reduce both clutter and possible confusion. You can do this by inserting some very simple code into the private module of the workbook.

To get to this private module, right-click the Excel icon, which you’ll find at the top left of your screen, next to File, and select View Code.

Tip

This shortcut isn’t available on the Mac. You’ll have to open the Visual Basic Editor (VBE) by pressing Option-F11 or by selecting Tools → Macro → Visual Basic Editor. Once you’re there, Ctrl-click or right-click This Workbook in the Projects window.

Then, enter this code:

Private Sub Workbook_Activate( )
    On Error Resume Next
        With Application.CommandBars("MyCustomToolbar")
                   .Enabled = True
               .Visible = True
            End With
    On Error GoTo 0
End Sub
 
Private Sub Workbook_Deactivate( )
    On Error Resume Next
        Application.CommandBars("MyCustomToolbar").Enabled = False
    On Error GoTo 0
End Sub

Change the text "MyCustomToolbar" to the name of your own custom toolbar. To get back to the Excel interface, close the module window or press Alt/

-Q. Whenever you open or activate another workbook, your custom toolbar disappears and isn’t accessible. Reactivate the appropriate workbook, and poof! The toolbar’s back.

You even can take this down a level, making the custom toolbar available only to a specific worksheet within the workbook. Right-click the Sheet Name tab of the sheet on which you want the toolbar to be accessible and select View Code. Enter this code:

Private Sub Worksheet_Deactivate( )
    On Error Resume Next
        Application.CommandBars("MyCustomToolbar").Enabled = False
    On Error GoTo 0
End Sub

Private Sub Worksheet_Activate( )
    On Error Resume Next
        With Application.CommandBars("MyCustomToolbar")
                   .Enabled = True
               .Visible = True
            End With
    On Error GoTo 0
End Sub

Now press Alt/

-Q or close the window to get back to Excel.

The first procedure (Worksheet_Deactivate( )) will fire automatically each time you leave that particular worksheet to activate another one. The firing of the code changes the Enable property of your custom toolbar to False so that it cannot be seen or displayed. The second procedure is fired each time you activate the worksheet and sets the Enable property of your custom toolbar to True so that it can be made visible. The line of code that reads Application.CommandBars("MyCustomToolbar").Visible = True simply displays your custom toolbar again, so the user can see it. Switch worksheets and the toolbar’s gone; switch back and it reappears like magic.

Outsmart Excel’s Relative Reference Handler

In Excel, a formula reference can be either relative or absolute, but sometimes you want to move cells that use relative references without making the references absolute. Here’s how.

When a formula needs to be made absolute, you use the dollar sign ($) in front of the column letter and/or row number of the cell reference, as in $A$1. Once you do this, no matter where you copy your formula, the formula will reference the same cells. Sometimes, however, you already set up a lot of formulas that contain not absolute references, but relative references. You would usually do this so that when you copy the original cell formula down or across, the row and column references change accordingly.

If you already set up your formulas using only relative references, or perhaps a mix of relative and absolute references, you can reproduce the same formulas in either another range on the same worksheet, another sheet in the same workbook, or perhaps even another sheet in another workbook.

To do this without changing any range references inside the formulas, select the range of cells you want to copy and then select Edit → Replace.... In the Find What: box, type an equals sign (=) and in the Replace With: box, type an at sign (@ ). (Of course, these could be any symbols you are sure are not being used in any of the formulas.) Click Replace All. The equals sign in all the formulas on your worksheet will be replaced with the at sign.

You now can simply copy this range, paste it to its desired destination, select the range you just pasted, and select Edit → Replace.... This time replace the at sign with an equals sign. Your formulas now should be referencing the same cell references as your originals.

Remove Phantom Workbook Links

Ah, phantom links. You open your workbook and are prompted to “Update Links,” but there are no links! How can you update links when they don’t exist?

External links are links that reference another workbook. Unexpected external linking can occur for various reasons, many of them stemming from moving or copying charts, chart sheets, or worksheets into another workbook. Knowing why they’re there doesn’t always help you find them, though. Here are a few ways to deal with the spooky phantom link problem.

First, you need to see whether you have any real external links (nonphantom) that you forgot about. If you are not sure whether you have real external links, start looking in the most obvious place: your formulas. You can do this by ensuring no other workbooks are open and then searching for [*] within the formulas on each worksheet. Close all other workbooks to ensure that any formula links will include [*], where the asterisk represents a wildcard string.

Tip

Excel 97 doesn’t provide the option of searching the entire workbook, but you can search all worksheets in a workbook by grouping them. You do this by right-clicking any Sheet Name tab and choosing Select All Sheets. In later versions of Excel, Find... and Replace... provide the option of searching within the sheet or workbook.

Once you find the formula links, simply change the formula accordingly or delete it altogether. Whether you change the formula or delete it depends on the situation, and only you can decide which route to take.

You also might want to consider going to the Microsoft Office Download Center, located at http://office.microsoft.com/Downloads/default.aspx, and from the Add-Ins category selecting the Delete Links Wizard. The Delete Links Wizard is designed to find and delete links such as defined name links, hidden name links, chart links, Microsoft query links, and object links. However, in our experience, it does not find phantom links.

Once you’re confident there are no formula links, you need to ensure that you don’t have any nonphantom links lurking somewhere else. To do this, we like to start from within the Excel workbook containing the phantom links. Select Insert → Name → Define. Scroll through the list of names, clicking to highlight each one and looking in the Refers To: box at the bottom. Check to make sure none of these names is referencing a different workbook.

Tip

Instead of clicking each name in the Define Name dialog, you can insert a new worksheet and select Insert → Name → Paste. Then, from the Paste Name dialog, click Paste Link. This will create a list of all the names in your workbook, with their referenced ranges in the corresponding column.

If any of the names are pointing outside your workbook, you’ve found the source of at least one link that would prompt the updating question. Now it’s up to you to decide whether you want to change this range name to refer only to the workbook itself or leave it as it is.

Another potential source of links is in your charts. It’s possible that your charts have the same problem we just explained. You should check that the data ranges and the X-axis labels for the chart aren’t referencing an external workbook. Once again, you get to decide whether the link you’ve found is correct.

Links also can lurk in objects, such as text boxes, autoshapes, etc. Objects can try to reference an external workbook. The easiest way to locate objects is to select any single cell on each worksheet and then select Edit → Go To... (F5). From the Go To... dialog, click Special and then check the Objects option and click OK. This will select all objects on the worksheet. You should do this on a copy of your workbook. Then, with all objects selected, you can delete, save, close, and reopen your copy to see whether this has eliminated the problem.

Finally, the last not-so-obvious place to check for real links is in the hidden sheets that you might have cleverly created and forgotten about. Unhide these sheets by selecting Format → Sheet → Unhide. If the Unhide option on the Sheet submenu is grayed out, that means you have no hidden sheets. (If you think there are sheets that don’t turn up in the menu, see [Hack #5] for more information.)

Now that you have eliminated the possibility of real links, it’s time to eliminate the phantom links. Go to the haunted workbook with the phantom links and select Edit → Links.... Sometimes you can simply select the unwanted link, click Change Source, and then refer the link back to itself. Often, though, you will be told that one of your formulas contains an error, and you will not be able to do this.

If you can’t take the easy way out, note to which workbook Excel thinks it is linking (we’ll call it the well-behaved workbook). Create a real link between the two by opening both workbooks. Go to the problem workbook and, in any cell on any worksheet, type =. Now click a cell in the well-behaved workbook and press Enter so that you have a true external link to the other workbook.

Save both workbooks, but don’t close them yet. While in the phantom links workbook, select Edit → Links... and use the Change Source button to refer all links to the new workbook to which you just purposely created a link. Save your workbook again and delete the cell in which you created the true external link. Finally, save your file.

This often eliminates the offending phantom link, as Excel now realizes you have deleted the external link to the workbook. If this does not solve the problem, however, try these next steps, but make sure you save a copy first.

Warning

The following process involves deleting data permanently. Therefore, before you begin, create a backup copy of your workbook. Neglecting to do so could create new problems for you.

With the problem workbook open, delete one sheet, Save, and then close and re-open the workbook. If you are not prompted to update your missing links, the sheet you deleted contained the phantom link. This should solve the problem, but if it doesn’t, repeat the first step for each sheet in the workbook. You will need to add a new sheet before you delete the last sheet, as any workbook must have at least one sheet.

We’re going to assume this technique worked for you. So, here’s what you should do next. Open the copy of your workbook (the one that still has data in it) and make another copy. You’ve got to work with the problem worksheet (or worksheets) and use the process of elimination to discover where the problem is in the worksheet.

With the problem worksheet active, select a chunk of cells (about 10 x 10) and then select Edit → Clear → All. (Are you absolutely sure you saved a copy?) Save, close, and reopen the worksheet. If you are not prompted to update those links, you found the problem and your reward is to redo that block of cells. If you are prompted to update the links, continue deleting cells until you aren’t are no longer prompted. Then redo the badly behaved cells.

We hope these techniques will save you some of the frustration that arises when those dreaded phantom links appear in your workbooks. They’re not easy or fun to perform, but they can get you out of trouble.

Reduce Workbook Bloat

Ever notice that your workbook is increasing in size at an alarming rate for no apparent reason? There are several causes of workbook bloat, and some slimming solutions.

Have you ever eaten so much that you can’t function properly? Workbook bloat in Excel is much the same thing. Workbook bloat is a term for a workbook that has had so much done to it that it has swollen to such a size it can no longer function correctly.

We checked out the size of a typical workbook containing a fairly large amount of data. With data only, the workbook file size was 1.37 MB. Then we added a pivot table referencing four entire columns for its data source and noted that the file size increased dramatically to 2.4 MB. Add some formatting and your typical workbook size has blown out to almost double by performing a few actions.

One of the more common causes of file bloat, particularly in earlier versions of Excel, is the application of formats to entire columns or rows rather than to just the data range in use. Another mistake is referencing entire columns as the data source for charts and pivot tables rather than just the cells with actual data in them. To fix these problems, you will need to eliminate all the superfluous formatting and restrict your data source to only the useful range of cells.

Warning

Before doing such refactoring, always make a copy of your workbook for safekeeping.

Eliminating Superfluous Formatting

The first step in eliminating superfluous formatting is to figure out where your worksheet’s data ends—the bottom righthand corner of your data, if you will. Don’t rely on Edit → Go To... → Special → Last Cell, as this might take you to the last cell containing formatting, not actual data. Having manually located the cell you know to be your last cell containing legitimate data, highlight the row immediately following it. While pressing the Ctrl and Shift keys, press the down arrow on your keyboard to highlight all rows beneath that row and select Edit → Clear → All to clear them.

Now apply the same logic to unwanted formatting lurking in your columns. Locate the cell in the last column containing data and click the column header of the column immediately to the right. Press Ctrl-Shift and the right arrow on your keyboard to highlight all other columns to the right and then select Edit → Clear → All.

Warning

Don’t be tempted to actually delete these rows or columns rather than clearing them, as doing so often causes the dreaded #REF! error in any cells of any formulas that might reference them.

Save your workbook and take gleeful note of the change in its file size by selecting File → Properties... → General.

If you have macros, now you need to address the modules that the macro code resides in. This is a fairly quick, painless, and straightforward process that entails exporting all modules and UserForms to your hard drive and then deleting the existing modules and UserForms, pressing Save, and importing the modules you exported.

To do this, go into the Visual Basic Editor and, from within the Project Explorer, right-click each module and select Remove Module1 (or whatever the name of the module happens to be). When you are asked whether you want to export your module before removing it, say Yes, taking note of the path.

Do this for each module in turn, as well as for any UserForms you might have. Don’t forget the private modules of your workbook and worksheets if they house code as well. Once you have done all this, save the workbook. Then, select File → Import File and import each module and UserForm back into your workbook. Following this process will create a text file of each module and that, in turn, removes all extra baggage that the modules might be holding.

The Web contains some free utilities that will automate this task to some degree, but we have heard cases of these utilities making a mess of code or even increasing file sizes. If you do use one of these, always save a backup copy first, as the developers will take no responsibility for any loss of data.

Honing Data Sources

If, after performing the previous steps, you still believe your file size is unrealistically large, another possible suspect is referencing unused cells in PivotTables and PivotCharts. This is true particularly of PivotTables, as people frequently reference all 65,536 rows in order to avoid manually updating ranges as new data is added. If this is your modus operandi, use dynamic named ranges [Hack #42] for your data sources instead.

Cleaning Corrupted Workbooks

If you still believe your workbook is too large, it is possible that your workbook or component sheets are corrupt. Unfortunately, determining a point of corruption requires a manual process of elimination.

Warning

Again, we strongly advise you to save a copy of your workbook before proceeding.

To be sure you’re not missing anything, unhide any hidden sheets by selecting Format → Sheet → Unhide. If this menu option is grayed out, you have no hidden worksheets to worry about. With all your sheets visible, start from the sheet on the far left and move one-by-one to the right. For each in turn, delete it, save your workbook, and note its file size by selecting File → Properties → General. If the file size drops dramatically considering the amount of data on that sheet, you’ve probably found your corruption.

To replace a corrupt sheet in your workbook, create a new worksheet, manually select the data in the corrupt sheet, and cut (do not copy) and paste it into the new sheet. Delete the corrupt sheet from your workbook, save, and repeat.

Tip

By cutting rather than copying, Excel automatically will follow the data to the new sheet, keeping references intact.

Extract Data from a Corrupt Workbook

Workbook corruption can mean the loss of vital data, costing you more than just money. This hack explores some methods that might recover your data.

Workbooks sometimes become corrupt for no apparent reason. This can cause all sorts of problems, especially if the workbook is vital and for whatever reason you have no backup. Lesson 1: always back up your data somewhere. Realistically, though, this does not always happen, and corruption can, of course, occur right before your regularly scheduled backup.

To add to your frustration, even though you know your workbook is corrupt, you sometimes might still be able to open it and even perform certain actions in it.

If You Can Open Your Workbook

If you can open the offending workbook, before doing anything else, be sure to save a copy of it; otherwise, you might regret it. If you have a copy, you can always seek professional help!

Now, try opening the workbook in a later version of Excel and simply saving. Obviously this is not possible if you already are using the latest version of Excel.

If this doesn’t work, try opening your workbook and saving the file in HTML or HTM format, then close the file and reopen it, this time saving again in the format you require—e.g., .xls.

Tip

When saving in HTML or HTM format, the following features will be lost:

• Custom views

• Unused number formats

• Unused styles

• Data consolidation settings

• Scenarios

• Natural language formulas (they are converted to standard range references)

• Custom function categories

• Strikethrough, subscript, and superscript elements

• Change History

• Customized page setup settings for charts that are embedded on a worksheet

• List settings for ListBoxes and ComboBoxes from the Forms toolbar

• Conditional formatting that is stored on an XLM macro sheet

Also, shared workbooks will no longer be shared. The “Value (Y) axis crosses at category number” setting on the Scale tab of the Format Axis dialog box is not saved if the “Value (Y) axis crosses a maximum category” checkbox is checked. The “Vary colors by point” setting in the Format Data Series dialog box is not saved if the chart contains more than one data series.

Finally, try opening your file and saving it in SYLK (.slk, for symbolic link) format. Note that when you save a workbook in this format, only the active worksheet is saved. So, you will have to do the same for each worksheet. Reopen the file and save it in a desired format such as .xls.

If You Cannot Open Your File

If your workbook is corrupt to the point that you cannot even open it, open your spreadsheet in Microsoft Word or via the Spreadsheet viewer, which can be downloaded from the Microsoft web site, then copy your data from the open file. Much of your formatting, formulas, etc., will, however, be lost.

Next, open a new workbook and create an external link to the corrupt workbook—e.g., ='C:\Documents and Settings\Raina\My Documents\[ChookSheet.xls]Sheet1'!A1. Copy this link down as many rows and across as many columns as needed. Do the same for each worksheet in the workbook. If you cannot remember any of the names of the worksheets, create any old sheet name using the correct filename path, and Excel will display the sheet names for you when you press Enter.

One final thing you can do is visit the OpenOffice.org web site and download the free version of OpenOffice.org. Except for different names for different tools and commands, OpenOffice.org is very similar to Excel. OpenOffice.org is based on the same basic spreadsheet structure as Excel, making it simple for Excel users to use. In fact, about 96% of the formulas used in Excel can be created and applied by using the spreadsheet in OpenOffice.org.

To download the free version of OpenOffice.org, go to http://download.openoffice.org/index.html and download it from the FTP site of your choice. Then install the program. OpenOffice.org is also available for Macs.

In many cases, your Excel data can be recovered. However, no VBA code can be recovered due to incompatibility between OpenOffice.org and Excel.

Sadly, if none of these methods works, you probably will have to pay to try to have your workbook recovered with special software. One source where such reputable software (for Windows) can be purchased belongs to the authors of this book and is located at http://www.ozgrid.com/Services/corrupt-file-recovery-index.htm.

After purchase and installation, run the ExcelFix program. Click Select File, select a corrupt file, and then click Diagnose to recover the file. You should now see the recovered file in the workbook viewer. Click Save Workbook to save the workbook into a new readable file that you can open from Excel.

Also available is a demo version that does not enable you to save the file, but all versions of the program enable you to start again and recover as many files as you want.