By David Hawley, Raina Hawley
Book Price: $29.99 USD
£20.99 GBP
PDF Price: $23.99

Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Reducing Workbook and Worksheet Frustration

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Hacks 1–16

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Create a Personal View of Your Workbooks

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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 .

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

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

Finally, as shown in , 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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Enter Data into Multiple Worksheets Simultaneously

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Prevent Users from Performing Certain Actions

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Prevent Seemingly Unnecessary Prompts

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

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

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

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Hide Worksheets So That They Cannot Be Unhidden

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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 . 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…).

Once you have selected 2 -xlSheetVeryHidden

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Customize the Templates Dialog and Default Workbook

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Create an Index of Sheets in Your Workbook

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Limit the Scrolling Range of Your Worksheet

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Lock and Protect Cells Containing Formulas

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Find Duplicate Data Using Conditional Formatting

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Find Data That Appears Two or More Times Using Conditional Formatting

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

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

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Tie Custom Toolbars to a Particular Workbook

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Outsmart Excel's Relative Reference Handler

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Remove Phantom Workbook Links

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Reduce Workbook Bloat

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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.

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

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Extract Data from a Corrupt Workbook

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

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

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

• New Excel 2007 features
• PivotTables and charts (they can be saved, but are lost when the file is opened in this format again in Excel)
• VBA Projects

If you are using a pre-2007 version, the following features will be lost:

• 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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Hacking Excel's Built-in Features

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although Excel comes with a wide variety of standard features for managing and analyzing data, the boundaries of these features are often frustrating. The hacks in this chapter provide numerous ways in which you can escape these boundaries and make Excel a much more powerful tool.

Data validation makes it easy to specify rules your data must follow. Unfortunately, Excel insists that lists used in data validation must appear on the same worksheet as the data being validated. Fortunately, there are ways to evade this requirement.

This hack provides two methods you can use to validate data based on a list on another worksheet. The first method takes advantage of Excel's named ranges (which are covered in more detail in ), and the second uses a function call.

Perhaps the easiest and quickest way to overcome Excel's data-validation barrier is by naming the range where the list resides. To create a named range, select the cells containing the list and enter a name in the Name box that appears at the left end of the Formula bar. For the purposes of this example, we will assume your range is called MyRange.

Select the cell in which you want the drop-down list to appear and then, under the Data tab select Data Tools → Data → Validation (pre-2007, Data → Validation). Select List from the Allow: field, and in the Source: box enter =MyRange. Click OK.

Because you used a named range, your list (even though it resides on another worksheet) can now be used for the validation list.

The INDIRECT function enables you to reference a cell containing text that represents a cell address. You then can use that cell as a local cell reference, even though it gets its data from another worksheet. You can use this feature to reference the worksheet where your list resides.

Assume your list resides on Sheet1 in the range $A$1:$A$8. Click any cell on a different worksheet where you want to have this validation list (pick list) appear. Then, under the Data tab, select Data → Data Validation (pre-2007, Data → Validation). Choose List from the Allow: field. In the Source: box, enter the following code:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Hacks 17–43

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although Excel comes with a wide variety of standard features for managing and analyzing data, the boundaries of these features are often frustrating. The hacks in this chapter provide numerous ways in which you can escape these boundaries and make Excel a much more powerful tool.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Validate Data Based on a List on Another Worksheet

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Data validation makes it easy to specify rules your data must follow. Unfortunately, Excel insists that lists used in data validation must appear on the same worksheet as the data being validated. Fortunately, there are ways to evade this requirement.

This hack provides two methods you can use to validate data based on a list on another worksheet. The first method takes advantage of Excel's named ranges (which are covered in more detail in ), and the second uses a function call.

Perhaps the easiest and quickest way to overcome Excel's data-validation barrier is by naming the range where the list resides. To create a named range, select the cells containing the list and enter a name in the Name box that appears at the left end of the Formula bar. For the purposes of this example, we will assume your range is called MyRange.

Select the cell in which you want the drop-down list to appear and then, under the Data tab select Data Tools → Data → Validation (pre-2007, Data → Validation). Select List from the Allow: field, and in the Source: box enter =MyRange. Click OK.

Because you used a named range, your list (even though it resides on another worksheet) can now be used for the validation list.

The INDIRECT function enables you to reference a cell containing text that represents a cell address. You then can use that cell as a local cell reference, even though it gets its data from another worksheet. You can use this feature to reference the worksheet where your list resides.

Assume your list resides on Sheet1 in the range $A$1:$A$8. Click any cell on a different worksheet where you want to have this validation list (pick list) appear. Then, under the Data tab, select Data → Data Validation (pre-2007, Data → Validation). Choose List from the Allow: field. In the Source: box, enter the following code:

	=INDIRECT("Sheet1!$A$1:$A$8")

Ensure that the In-Cell drop-down checkbox is selected and click OK. The list that resides on Sheet1 should appear in your drop-down validation list.

If the name of the worksheet on which your list resides contains spaces, you need to use the INDIRECT

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Control Conditional Formatting with Checkboxes

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Although conditional formatting is one of Excel's most powerful features, it's a nuisance to turn it on and off through the ribbon. Adding checkboxes to your worksheet that turn formatting on and off makes it much easier to read data in any way you want, whenever you want.

Conditional formatting, a feature available since Excel 97, applies formats to selected cells that meet criteria based on values or formulas you specify. Although conditional formatting is usually applied based on cell values, applying it based on formulas provides the flexibility to extend the conditional formatting interface all the way to the spreadsheet grid.

The checkboxes from the Form Controls—found under the Developer tab by selecting Controls → Insert (Forms toolbar for pre-2007 versions)—return either a TRUE or FALSE value (checked/not checked) to their linked cell. By combining a checkbox from the Form Controls with conditional formatting using the "Use a formula to determine which cells to format" option (Formula Is in pre-2007 versions), as shown in , you can turn conditional formatting on and off via a checkbox.

When used in conjunction with a formula (such as the "Use a formula to determine which cells to format" option), conditional formatting automatically formats a cell whenever the formula result returns TRUE. For this reason, any formula you use in this hack must return either TRUE or FALSE.

To see what we mean, try this simple example, which hides data via the use of conditional formatting and a checkbox. For this example, we will use the range $A$1:$A$10, filled consecutively with the numbers 1-10. To obtain a checkbox from the Form Controls, go to the Developer Tab Controls options and select Insert (pre-2007, go to the Forms toolbar by selecting View → Toolbars → Forms) and click the checkbox, then click near cell C1 on your sheet to position the check. Right-click the checkbox and select Format Control → Control. Type C1 in the Cell Link box, as shown in , and click OK.

Figure 2-1: The Conditional Formatting dialog with the Formula option

Figure 2-2:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Identify Formulas with Conditional Formatting

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once a formula is entered into a cell, you can tell whether the cell is a static value or a value derived from a formula only by clicking in each cell and looking in the Formula bar, or by pressing Ctrl-~ (tilde). This hack fills that gap with a custom function.

The VBA code in this custom function (also called a user-defined function) enables you to identify cells that contain formulas without having to click through 10,000 cells and examine each one.

You could select Conditional Formatting → New Rule (pre-2007, Format → Conditional Formatting → Formula Is) and use =CELL("Type",A1) in the "Use a formula to determine which cells to format" section, but you must be aware that this is a volatile function. This means that every time you make any changes at all in the workbook, or another workbook while the workbook containing the conditional formatting is still open, it will force all the cells using the CELL function to recalculate. These global recalculations can add considerably to your overhead in a large spreadsheet. This hack presents a better way.

To become a clever formula hunter, start by going to the Developer tab and selecting Code → Visual Basic (pre-2007, go to Tools → Macro → Visual Basic Editor) or Alt/Option-F11 and then select Insert → Module. Enter the following function into the window that appears:

	Function IsFormula(Check_Cell As Range)
	 IsFormula = Check_Cell.HasFormula
	End Function

Close the window (press Alt/⌘-Q, or use the Close button in the window's title bar). Now this function is available in any cell on any worksheet in this workbook when you enter the formula =IsFormula($A$1). You also can access the function by going to the Formulas tab, selecting Function Library → Insert Function (pre-2007, Insert → Function), selecting User Defined from the Category option, and choosing IsFormula from the functions displayed.

The formula returns TRUE if the reference cell houses a formula and FALSE if it does not. You can use this Boolean result in conjunction with conditional formatting so that all formulas are highlighted automatically in a format of your choice.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Count or Sum Cells That Meet Conditional Formatting Criteria

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Once you can see the results of conditional formatting, you might want to create formulas that reference only the data that was conditionally formatted. Excel doesn't quite understand this in its calculations, but it can learn.

Excel users regularly ask, "How can I do calculations on only the cells that have a specific background color?" This question arises so often because Excel has no standard function for accomplishing this task; however, it can be accomplished with a custom function [].

The only trouble with using a custom function is that it does not pick up any formatting that is applied using conditional formatting. With a bit of lateral thinking, however, you can achieve the same result without bothering with a custom function.

Say you have a long list of numbers in the range $A$2:$A$100. You applied conditional formatting to these cells so that any numbers that fall between the range 10 and 20 are flagged. Now you have to add the value of the cells that meet the criterion you just set and then specify the sum of the values using conditional formatting. You don't need to worry about what conditional formatting you applied to these cells, but you do need to know the criteria that were used to flag the cells (in this case, cells with values between 10 and 20).

You can use the SUMIF function to add a range of cells that meet a certain criterion—but only one criterion. If you need to deal with more than one factor, you can use an ar