O'Reilly logo

Excel Hacks, 2nd Edition by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 1. Reducing Workbook and Worksheet Frustration

Hacks 1–16

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 percent of your time planning your spreadsheet and about 20 percent 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 that the planning gets easier after you’ve done it for a while. Remember that spread-sheets 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 1,048,576 rows (65,536 pre-2007) but only 16,384 columns (256 pre-2007). 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 use 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 spread-sheets. 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 under Home → Alignment Group. The arrow in the bottom right will display the Format dialog with the Alignment tab active. Use the Horizontal drop-down to select Center Across Selection or right-click and choose Format Cells from the shortcut menu (pre-2007, Format → Cells).

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.

Another common problem with poorly designed spreadsheets is painfully slow recalculation. Many people suggest that shifting calculation mode into Manual via the Office button → Excel → Formulas (pre-2007, Tools → Options → Calculations; Mac OS X, Excel → Preferences → Calculation) 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 ( ⌘-= on Mac OS X). 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 use 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 "Sum or Count Cells While Avoiding Error Values” [Sum or Count Cells While Avoiding Error Values]. Also, the Excel Help offers some 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

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 to make 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.

First, open all the workbooks you will need.

Tip

To open more than one workbook at a time, select the Office button → Open…, press the Ctrl key (⌘ key on the Mac) while selecting the workbooks you want to open, and then click Open (pre-2007, select File → Open).

From any of the workbooks (it doesn’t matter which one), select Windows → View → Arrange All (pre-2007, 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.

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

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

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

Once your workbooks are displayed in your preferred view, you can easily move data between them (e.g., copy, paste, drag and drop).

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 Windows → Save Workspace (pre-2007, 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 .xlsx of Excel 2007. To restore your Excel workspace to one full window of a particular workbook, just double-click the title bar (on the Mac, click the green Zoom button in the upper left of the window) 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.

Four workbooks in a tiled view
Figure 1-1. Four workbooks in a tiled view
Four workbooks in a horizontal view
Figure 1-2. Four workbooks in a horizontal view
Four workbooks in a vertical view
Figure 1-3. Four workbooks in a vertical view
Four workbooks in a cascade view
Figure 1-4. Four workbooks in a cascade view

Zoom button in the upper left of the window) 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 those 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

Enter Data into Multiple Worksheets Simultaneously

It’s fairly ordinary to need some data to be duplicated in multiple worksheets. You can use Excel’s tool for grouping so that data entered in one workbook can be entered into multiple worksheets simultaneously. But there is also 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 ⌘ 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, right-click any Name tab and select Ungroup Sheets, or Ctrl/⌘-click the sheets you added to the group.

Warning

When your worksheets are grouped together, you can look up to the title bar and 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 that 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. For example, you might want the simultaneous entries to occur only when you are in a particular range of cells. However, simultaneous data entries will occur regardless of the cell you are in at the time.

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.

Tip

In most cases, you will be taken directly to the private module when you right-click on a workbook or worksheet and select View Code. You can confirm that you’re in the private module by looking at the state of the drop-down menu in the upper left of the code window—this window is usually labeled something like “Workbook - sheetname(Code)”. If the drop-down menu says “Workbook” or “Worksheet,” then you in the private module. If it says “(General),” change it before typing in the code.

To enable grouping, first 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 “Address Data by Name” [Address Data by Name].) 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 click the View Microsoft Excel tool, close the module window, or press Alt/⌘-Q to get back to Excel, then save your workbook.

Code for automatically grouping worksheets
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, use 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

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. 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. Click the Office button and choose Excel Options → Trust Center → Trust Center Settings and press the Macro and press the Macro Settings Button (pre-2007, Tools → Macro → Security). If their security is set to "Disable all macros with notification” (Medium in pre-2007 versions), 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 "Disable all macros without notification” (High in older versions) 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.

On Mac OS X, you cannot control macro protection at this level of detail. Instead, you can select Excel → Preferences → Security and toggle the setting “Warn before opening a file that contains macros.”

Preventing Save As… in a Workbook

You can specify that any workbook be saved as read-only by choosing Office button → Save → Tools Button → General Options and enabling the "Read-only recommended” checkbox (pre-2007, File → Save As → Tools [Options on the Mac] → General options in the Save options dialog). 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 and choose Developer → Visual Basic, then select View → Code, and double-click on ThisWorkbook in the Project Explorer (pre-2007, right-click the Excel icon immediately to the left of the File menu item on the worksheet menu bar, and select View Code, as shown in Figure 1-6).

Tip

You might have to enable the Developer tab (not standard in Excel 2007) by selecting Office button → Excel Options → Popular, checking the option "Show Developer tab in the Ribbon” and clicking OK.

Quick access menu (in Excel 2003) to the private module for the workbook object
Figure 1-6. Quick access menu (in Excel 2003) 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 press Alt/⌘-Q to get back to Excel proper, then save your workbook:

	Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
	 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
Code once it’s entered into the private module (ThisWorkbook)
Figure 1-7. Code once it’s entered into the private module (ThisWorkbook)

Give it a whirl. Select Office button → Save (pre-2007, File → Save) and your workbook will save as expected. However, select Office button → Save As (pre-2007, File → Save As…) and you’ll be informed that you’re not allowed to save this workbook under any other filename, unless you’ve disabled macros.

Tip

Note that when you save a workbook in Excel 2007 and it contains either macros or code, you will be prompted to save your workbook as an Excel macro-enabled workbook (*.xlsm) and will be unable to save in the standard Excel file format (*.xlsx).

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 get back to Excel, then save your workbook. 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 hassle 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 Review → Changes → Protect Workbook, and then press the Protect Structure and Windows button (pre-2007, 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 also prevent many other Excel features from working.

Prevent Seemingly Unnecessary Prompts

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 Developer → Macros or Alt/Option-F8 (pre-2007, Tools → Macro → Macros). 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 “Disable all macros with notification” (Medium in pre-2007 versions). If it’s set to "Enable all macros” (Low in pre-2007 versions), macros are enabled without a peep; if it’s set to “Disable all macros without notification” (High in pre-2007 versions), 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 Developer → Visual Basic under Code options or by pressing Alt/Option-F11 (pre-2007, Tools → Macro → Visual Basic Editor and select View → Project Explorer).

Tip

On the Macintosh and in Excel 2007, the Projects window is always open, so you don’t need to open the Project Explorer.

You’ll see a window like the one shown in Figure 1-8.

Project Explorer modules
Figure 1-8. Project Explorer modules

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 if they are not already showing. 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 (Tools → Macro → Record New Macro) and specify "Personal Macro Workbook” in the “Store Macro in” drop-down menu. It is 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_BeforeClose(Cancel As Boolean)
	    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 View → Unhide under Window options (pre-2007, 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 that you want to recalculate and you want to save the changes, you need to explicitly tell Excel to do so:

	Private Sub Workbook_BeforeClose(Cancel As Boolean)
	    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 Developer → Macros under Code options or Alt/Option-F8 (pre-2007, Tools → Macro → Macros) 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—i.e., the first line without an apostrophe in front of it—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 re-enable 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

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 View → Hide under Window options (pre-2007, Format → Sheet → Hide), it’s a good idea to ensure that users can’t unhide it by selecting View → Unhide under the Window options (pre-2007, 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. Go to Developer → Visual Basic or Alt/Option-F11 to get to the VBE (pre-2007, go to Tools → Macro → Visual Basic Editor and 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 return to Excel, then save your changes. The sheet will no longer be visible via the Excel interface and won’t appear as a choice under View → Unhide under Window options (pre-2007, 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 when you select View → Unhide under Window options (pre-2007, Format → Sheet → Unhide…) you know it had the desired effect.

Properties window of a worksheet having its visible property set to 2 - xlSheetVeryHidden
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

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 Office button → Save As… (pre-2007, File → Save As…) and choose Excel Template (Template on Mac OS X) from the dialog’s Save As Type (Format on Mac OS X) 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 Office button → Save As…, and save as an Excel template.

Template in hand, you can create a clone at any time by either selecting the Office button → New (pre-2007, File → New…; File → Project Gallery on the Mac) 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 to make it easier for you to manage them.

From within any workbook, choose the Office button → Save As… (pre-2007, File → Save As…). Then, from the Files of Type pop-up menu, select Excel Template (.xltx); for older versions, select Template (.xlt). By default Excel will select the standard Templates folder in which all your home-grown templates are kept.

If you want to create tabs in which to store your templates, create a subfolder by using the New Folder button.

Tip

On Mac OS X, Excel 2004 defaults to your My Templates directory for saving new templates, so this step is not needed.

Now, go to the Office button, select New, and click the “My templates” option button (for Excel 2000 and above, choose General Templates from the New Workbook dialog that will appear; for older versions, select File → New… on the worksheet menu bar). You should now see the tab you created (named Ozgrid 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.

Tip

In Excel 2007, as a default if you do not create any tabs, your templates are stored under “My templates” in the My Templates tab.

The Templates dialog
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 in Windows, and in Applications/Microsoft Office 2004/Office/Startup/Excel under Mac OS X). Once you have done this, Excel will automatically use whichever workbook(s) you have in there 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 View → Hide under Window options (pre-2007, 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.

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 XLSTART folder.

Create an Index of Sheets in Your Workbook

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 few ways: by hand, auto-generated by code, or as a context-sensitive menu option.

Creating an Index Sheet by Hand

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 worksheets’ names, and hyperlink each to the appropriate sheet by selecting Insert → Hyperlink under Link options (pre-2007, 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.

Auto-Generate an Index Using VBA

An alternative is to use VBA to auto-generate the index. 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 or select Alt/Option-F11.

Enter the following Visual Basic code:

	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. To make the code run, you will first need to deactivate your worksheet (select another sheet) and select the index sheet.

Notice that in the same way you name a range of cells, the code names 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

Be aware that if you select the Office button → Prepare → Properties → Document Properties → Advanced Properties ([pre-2007, File → Properties → Summary) and enter a URL as a hyperlink base, the index created from the preceding code might 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.

The third 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.

Tabs command bar displayed by right-clicking the sheet scroll tabs
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 private module of ThisWorkbook:

	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, then save your workbook.

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

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 2007 have a column limit of 16,384 (256, A to IV, in previous versions) and a row limit of 1,048,576 (65,536 pre-2007). 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. You can do this by hiding rows and columns, by specifying a valid range, or by activating only the used range.

Hiding Rows and Columns

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 Home → Format → Hide & Unhide → Hide Rows or right click and select Hide (pre-2007, 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 then again on the Home tab, select Format → Hide & Unhide → Hide Columns (pre-2007, Format → Column → Hide). If all went according to plan, your useful cells should be surrounded by a moat past which you cannot scroll.

Specifying a Valid Range

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. If you are using a version of Excel before 2007, you may need to select View → Project Explorer (Ctrl-R) on Windows to visit the Project Explorer (it is always visible on the Mac). 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.

You will be unable to scroll outside the area you have specified. Unfortunately, Excel will not save this setting after you close the window. 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, select View Code, and 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.

Project Explorer Properties window
Figure 1-12. Project Explorer Properties window

Although you will not see a visible clue, such as the 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 View → Macros under Macro options, or Developer → Macros under Code options, or Alt/Option-F8 (pre-2007, Tools → Macro → Macros…), then 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/2007 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 un-bolds 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 (DailyBudget),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.

Activating Only the Used 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 Developer → Visual Basic under Code options (pre-2007, 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 the view tab, then Macros, or press Alt/Option-F8 (pre-2007, Tools → Macro → Macros…). 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

Lock and Protect Cells Containing Formulas

You may want to let users change cells that contain 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. Three solutions are: locking the formula cells, using data-validation on the formula cells, and auto-toggling worksheet protection, although none of these solutions is bulletproof.

Locking Formula Cells

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 square at the intersecting point of column A and row 1. Then select Home → Format → Lock Cell (under Cells options, toggles to Lock/unlock cells),or if you prefer you can right-click and select Format Cells and on the Protection tab, uncheck the Locked checkbox to remove the tick (pre-2007, select Format → → Cells → Protection and uncheck the Locked checkbox to remove the tick). Click OK.

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

The Go To Special dialog
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, under Cells options choose Format → Lock Cell (pre-2007, select Format → Cells → Protection, check the Locked checkbox to insert a tick), and click OK.

Now you need to protect your sheet. Select Format → Protect sheet under the Cells options (pre-2007, Tools → Protection → Protect Worksheet). Apply a password if required and click OK.

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.

Data Validation

Using data validation doesn’t rely on worksheet protection at all, and instead simply prevents accidental overtyping of formula cells.

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, press Ctrl-G or select Home → Find & Select → Go to Special, or press F5 and click Special (pre-2007, Edit → Go To… → 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 Data → Data Validation, under Data Tools options (pre-2007, Data → Validation). Then, select the Settings page tab, choose Custom from the Allow: box, and in the Formula box, enter ="”, as shown in Figure 1-14. Click OK.

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.

Validation formulas
Figure 1-14. Validation formulas

Auto-Toggle Worksheet Protection

This method dynamically turns worksheet protection on and off, but will also enable you to use all of Excel’s features 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.

If you’re worried about your users getting into the macro and disabling it, you can password-protect your macro by selecting Tools → VBAProject Properties, going to the Protection tab, selecting “Lock Project for Viewing”,and entering a password.

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 will automatically unprotect itself.

Find Duplicate Data Using Conditional Formatting

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. The improved functionality in Conditional Formatting in Excel 2007 makes this hack suitable for prior versions only.

People frequently have to identify duplicated data within a list or table, and 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 don’t drag from H100 to A1. 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 (unlike 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.

Find Data That Appears Two or More Times Using Conditional Formatting

Find Data That Appears Two or More Times Using Conditional Formatting

While Excel’s Conditional Formatting is vastly improved in Excel 2007 and can easily take care of duplicated data, it still does not offer the facility to identify data that appears two or more times.

If you need to identify data that appears two or more times, you can use Conditional Formatting with as many conditions/rules as your system memory will allow (note that you can use up to three different conditions only in versions pre-2007) and color-code each condition for visual identification. To do this, select cell A1 (the cell in the top-left corner) and drag it down to H100. Again, it is important that A1 is the active cell in your selection.

Now, select Home → Conditional Formatting → New Rule under Styles options. For Select Rule Type, choose “Use a Formula to determine which cells to format” and then click in the white strip under “Format values where this formula is true.”

Tip

Pre-2007, select Format → Conditional Formatting… and, from the box containing the text Cell Value Is, select Formula Is. Click in the white box to the right of Formula Is.

Enter the following formula:

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

Click the Format button, select a color you want to apply to identify data that appears more than three times, and click OK; or for pre-2007 versions, go to the Patterns page tab, select a color, and click OK.

Click New Rule (on the Mac, click Add>>) and repeat the previous steps, entering the following formula (pre-2007, use the Condition 2 box and select Formula Is) and selecting a different color this time:

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

Tip

Instead of retyping the formula, highlight it after you have entered it, then press Ctrl/⌙-C to copy, then paste where necessary by pressing Ctrl/⌙-V to paste, and then change >3 to =3.

Click New Rule, repeat the steps a third time (pre-2007, from the Condition 3 box, select Formula Is), and add the following formula:

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

Again, select a different color from those previously chosen. 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 (unlike 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.

Tip

In Excel 2007, your Conditions are limited only by your system memory, whereas in pre-Excel 2007 versions, the limit is 3 Conditions.

Tie Custom Toolbars to a Particular Workbook

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. The Quick Access Toolbar options in Excel 2007 make this hack suitable for prior versions only.

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, then save your workbook. 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 is gone; switch back and it reappears like magic.

Outsmart Excel’s Relative Reference Handler

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 may have 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 another range on the same worksheet, another sheet in the same workbook, or perhaps even another sheet in a different workbook.

To do this without changing any range references inside the formulas, select the range of cells you want to copy and then select the Home tab → Find & Select → Replace (pre-2007, Edit → Replace…). In the Find What: box, type an equals sign (=) and in the Replace With: box, type an ampersand (&). (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 ampersand sign.

You can now simply copy this range, paste it to its desired destination, select the range you just pasted, and select the Home tab → Find & Select → Replace (pre-2007, Edit → Replace…). This time replace the ampersand sign with an equals sign (don’t forget to do this with the source range you just copied). 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 work-book. 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://www.microsoft.com/downloads/Search.aspx?displaylang=en) and downloading 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 Formulas → Name Manager under Defined Names options and check in the Refers To: column to make sure none of the names are referencing a different workbook (pre-2007, select Insert → Name → Define and scroll through the list of names, clicking to highlight each one and looking in the Refers To: box at the bottom).

Tip

Pre-2007 users, instead of clicking each name in the Define Name dialog, 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.

Excel 2007 users can see all the names in the Define Name dialog easily, but if you wish to paste to a workbook, select Formulas → Use in Formula → Paste Names, then select the Paste List button from the Paste List dialog.

F3 will also bring up the Paste Name dialog and works in all versions.

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 Home → Find & Select → Go to Special or click F5 and check the Objects option, then click OK (pre-2007, Edit → Go To… → Special, 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 View → Unhide under Window options (pre-2007, Format → Sheet → Unhide). If the Unhide option on the right-click 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 “Hide Worksheets So That They Cannot Be Unhidden” [Hide Worksheets So That They Cannot Be Unhidden] 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 Data → Edit Links under Connection options (pre-2007, 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 problem workbook, select Data → Edit Links (pre-2007, Edit → Links…) and use the Change Source button to refer all links to the well-behaved 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 of your workbook 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.

If this technique worked, 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 Home → Clear → Clear All under Editing options (pre-2007, Edit → Clear → All). Are you absolutely sure you saved a copy? Save, close, and reopen the problem 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 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

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. The introduction of workbook size being limited only by the amount of memory your system in Excel 2007 will allow should eliminate workbook bloat; however, you may find some of the following tips handy if you have a particularly large workbook.

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 that it no longer functions 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—e.g., the bottom righthand corner of your data, if you will. Don’t rely on Find & Select → Go To Special → Last cell,(pre-2007, 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 Home → Clear → Clear All to clear them (pre-2007, Edit → Clear → All).

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 Home → Clear → Clear All under Edit options to clear them (pre-2007, 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 the Office button → Prepare → Properties → Document Properties → Advanced Properties (pre-2007, File → Properties… → General).

Clean Up Your Macros

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 (this functionality is not available on Mac OS X) 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 them, always save a backup copy of your file 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 rows in order to avoid manually updating ranges as new data is added. If this is your modus operandi, use dynamic named ranges [Create Ranges That Expand and Contract] 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 View → Unhide under Window options or right click and select Unhide (pre-2007, 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 the Office button → Prepare → Properties → Document Properties dropdown → Advanced Properties (or File → Properties → General in pre-2007 versions). 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

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 it again. 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 (see the “What You Lose in HTML or HTM” sidebar for a warning about these formats), then close the file and reopen it, this time saving again in the format you require—e.g., .xlsx.

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 .xlsx.

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 (note that much of your formatting, formulas, etc, will 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 percent 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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required