Excel Hacks | O'Reilly Media

Content preview·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% of your time planning your spreadsheet and about 20% implementing it. Although this can seem extremely inefficient in the short run, we can assure you that the long-term gain will far outweigh the short-term pain, and the planning gets easier after you've done it for a while. Remember that spreadsheets are about making it easy for users to get correct information, not just about presenting information that looks good only once.

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

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

Content preview·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% of your time planning your spreadsheet and about 20% implementing it. Although this can seem extremely inefficient in the short run, we can assure you that the long-term gain will far outweigh the short-term pain, and the planning gets easier after you've done it for a while. Remember that spreadsheets are about making it easy for users to get correct information, not just about presenting information that looks good only once.

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!

Content preview·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. This makes it easier to use or view data from multiple workbooks. The next few paragraphs describe how to do this in a neat and organized way.

Open all the workbooks you will need.

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

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

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

Figure 1-1: Four workbooks in a tiled view

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

Figure 1-2: Four workbooks in a horizontal view

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

Figure 1-3: Four workbooks in a vertical view

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

Figure 1-4: Four workbooks in a cascade view

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

If you think you might want to return to a view you created, you can save this preferred view as a workspace. To save a workspace, simply select File → Save Workspace, enter the workspace's filename in the File Name box, and click OK. When saving your workspace, the file extension will be

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

Content preview·Buy reprint rights for this chapter

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

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

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

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

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

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

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

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

Content preview·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 when you're through. 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. If their security is set to Medium, they'll be notified of macros in the workbook upon opening it and will be offered the opportunity to turn them off. A security setting of High will simply turn them off automatically. On the other hand, if using the spreadsheet requires the use of macros, users might be more likely to have macros turned on. These hacks are a convenience and do not provide heavy-duty data security.

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

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

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

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

Content preview·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 Tools → Macro → Macros (Alt/Option-F8). Reopen the workbook and you'll still be prompted to enable macros, even though there are none to enable.

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

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

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

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

Content preview·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 Format → Sheet → Hide..., it's a good idea to ensure that users can't unhide it by selecting Format → Sheet → Unhide....

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

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

Select the sheet you want to hide from the Project Explorer and reveal its properties by selecting View → Properties Window (or by pressing F4). Make sure the Alphabetic tab is selected, and look for the

Visible
property

at the very bottom. Click the value box on the right associated with the Visible property and select the last option, 2 - xlSheetVeryHidden, as shown in Figure 1-9. Press Alt/

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

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

Content preview·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 File → Save As... and choose Template from the dialog's Save As Type drop-down list. If your template is to be a workbook template—i.e., it will contain more than one worksheet—again add a new workbook, make all the necessary changes, select File → Save As..., and save as a template.

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

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 right there on the Insert dialog.

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

Content preview·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 couple of ways.

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

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

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

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

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

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

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

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

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

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

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

Content preview·Buy reprint rights for this chapter

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

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

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

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

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

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

Figure 1-13: The Go To Special dialog

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

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

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

Content preview·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.

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

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

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

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

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

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

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

while in cell A2, it will read:

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

in cell A3, it will read:

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

and so forth.

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

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

Content preview·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.

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

Content preview·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 already set up a lot of formulas that contain not absolute references, but relative references. You would usually do this so that when you copy the original cell formula down or across, the row and column references change accordingly.

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

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

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

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

Content preview·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 workbook. Knowing why they're there doesn't always help you find them, though. Here are a few ways to deal with the spooky phantom link problem.

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

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

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

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

Once you're confident there are no formula links, you need to ensure that you don't have any nonphantom links lurking somewhere else. To do this, we like to start from within the Excel workbook containing the phantom links. Select Insert

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

Content preview·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.

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

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

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

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—the bottom righthand corner of your data, if you will. Don't rely on Edit → Go To... → Special → Last Cell, as this might take you to the last cell containing formatting, not actual data. Having manually located the cell you know to be your last cell containing legitimate data, highlight the row immediately following it. While pressing the Ctrl and Shift keys, press the down arrow on your keyboard to highlight all rows beneath that row and select Edit → Clear → All to clear them.

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

Content preview·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. Obviously this is not possible if you already are using the latest version of Excel.

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

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

Custom views
Unused number formats
Unused styles
Data consolidation settings
Scenarios
Natural language formulas (they are converted to standard range references)
Custom function categories
Strikethrough, subscript, and superscript elements
Change History
Customized page setup settings for charts that are embedded on a worksheet
List settings for ListBoxes and ComboBoxes from the Forms toolbar
Conditional formatting that is stored on an XLM macro sheet

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

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

Content preview·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.

In this hack, we provide 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 Chapter 3), 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 select Data → Validation. Select List from the Allow: field, and in the Source: box enter =MyRange. Click OK.

Because you used the named range, your list (even though it resides on another worksheet) now can 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 select 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!

Content preview·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!

Content preview·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.

In this hack, we provide 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 Chapter 3), 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 select Data → Validation. Select List from the Allow: field, and in the Source: box enter =MyRange. Click OK.

Because you used the named range, your list (even though it resides on another worksheet) now can 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 select Data → Validation and 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 box is checked 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

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

Content preview·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 menus and dialog boxes of the GUI. 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 Forms toolbar return either a TRUE or FALSE value (checked/not checked) to their linked cell. By combining a checkbox from the Forms toolbar with conditional formatting using the Formula Is option (shown in Figure 2-1), you can turn conditional formatting on and off via a checkbox.

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

When used in conjunction with a formula (such as the Formula Is 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 Forms toolbar, select View → Toolbars → Forms and click the checkbox, then click near cell C1 on your sheet to position the checkbox. Right-click the checkbox and select Format → Control → Control. Type C1 in the Cell Link box, as shown in Figure 2-2, and click OK.

Figure 2-2: The Format Control dialog

When you select the checkbox floating over cell C1, it will return

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

Content preview·Buy reprint rights for this chapter

Excel does not have a built-in function that identifies formulas. 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. 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.

To become a clever formula hunter, start by selecting Tools → Macro → Visual Basic Editor (Alt/Option-F11) and then select Insert → Module. Enter the following function:

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 titlebar). 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 selecting Insert → Function, then selecting UserDefined 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.

One of the best things about using this method is that your spreadsheet's formula identification capabilities will be dynamic. This means that if you add or remove a formula, your formatting will change accordingly. Here we explain how to do this.

Select a range of cells on your spreadsheet—say, A1:J500—and incorporate some extra cells in case more formulas are added at a later stage.

Avoid the temptation of selecting an entire worksheet, as this can add unnecessary overhead to your spreadsheet.

With these cells selected, and with A1 the active cell of the selection, select Format → Conditional Formatting.... Under Cell Value Is, select Formula Is and enter

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

Content preview·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, as shown in [Hack #88].

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

You use an array formula like this:

=SUM(IF($A$2:$A$100>10,IF($A$2:$A$100<20,$A$2:$A$100)))

When entering array formulas, don't press Enter. Press Ctrl-Shift-Enter. This way, Excel will place curly brackets around the outside of the formula so that it looks like this:

{=SUM(IF($A$2:$A$100>10,IF($A$2:$A$100<20,$A$2:$A$100)))}

If you enter these brackets yourself, it won't work. You must allow Excel to do it for you.

Also, note that using an array formula can slow down Excel's recalculations if there are too many references to large ranges.

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

Content preview·Buy reprint rights for this chapter

You've surely seen Excel spreadsheets that have alternating row colors. For instance, odd-numbered rows might be white, while even-numbered rows might be gray. Conditional formatting makes this easy.

Alternating colors or shading looks professional and can make data easier to read. You can apply this formatting manually, but as you can imagine, or might have experienced, it's a rather time-consuming task that requires constant updating as you add and remove data from the table. It also requires infinite patience. Fortunately, conditional formatting can reduce the amount of patience required and enhance your professional image.

We'll assume your data occupies the range A1:H100. Select this range of cells, starting with A1, thus ensuring that A1 is the active cell in the selection. Now, select Format → Conditional Formatting.... From the drop-down that currently says Cell Value Is, select Formula Is. In the Formula box, type the following formula, as shown in Figure 2-5:

=MOD(ROW( ),2)

Figure 2-5: Conditional Formatting dialog containing the MOD formula to specify a format to every second row in a range

Click the Format button and choose the format you want to apply to every second row. Click OK, and then click OK again. The format you specified should be applied to every second row in the range A1:H100. You also should have some patience left for the rest of the day.

If you need to apply this to columns rather than rows, use this formula instead:

 =MOD(COLUMN( ),2)

Although this method applies the formatting specified to every second row or column quickly and easily, it is not dynamic. Rows containing no data will still have the formatting applied. This looks slightly untidy and makes reading the spreadsheet a bit more difficult. Making the highlighting of every second row or column dynamic takes a little more formula tweaking.

Again, select the range A1:H100, ensuring that A1 is the active cell. Select Format → Conditional Formatting..., and from the Cell Value Is pull-down menu, select Formula Is. In the Formula box, enter the following formula:

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

Content preview·Buy reprint rights for this chapter

Whenever you see a nifty 3D effect in a program or application such as Excel, you are actually seeing an illusion created by specific formatting. It is easy to create this illusion yourself by applying formatting to a cell or range of cells.

To start off with a simple example, we'll give a cell a 3D effect so that it appears raised, like a button. On a clean worksheet, select cell D5. (You're selecting D5 because it's not on an edge.) Select Format → Cells → Border. From the Line box, choose the second thickest line style. Ensure that the color selected is Black (or Automatic, if you haven't changed the default for this option). Now click the righthand border and then click the bottom border. Return to the color option and select White. The second thickest border still should be selected, so this time click the two remaining borders of the cell, the top border and the left border. Click the Patterns tab on the Format Cells dialog and make the cell shading Gray. Click OK and deselect cell D5. Cell D5 will have a raised effect that gives the appearance of a button. You did it all with borders and shading.

If, for fun or diversity, you want to make a cell look indented or pushed in, select cell E5 (because it's next to D5 and it makes the next exercise work). Select Format → Cells → Border, select the second thickest border from the line styles, and ensure that the color is black.

Apply the formatting to the top and left border of the cell. Select White for the color option and apply a white line to the right and bottom borders. Click the Patterns tab and change the cell's format to Gray. Click OK. Cell E5 should appear indented. This works even better in contrast with cell D5, which has the raised effect.

Next, we'll experiment with this tool to see the sorts of effects you can apply to your tables or spreadsheets to give them some 3D excitement.

Select cells D5 and E5, and click the Format Painter tool (the paintbrush icon) on the standard toolbar. While holding down the left mouse button, click in cell F5, and drag across to cell J5 and release.

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

Content preview·Buy reprint rights for this chapter

Data validation can make it far less likely that a user accidentally will enter incorrect data. Sometimes, however, you might need to make it easier to enter data that otherwise would be flagged as incorrect by conditional formatting or blocked completely by the validator.

Usually, you would enable users to enter data that otherwise would be flagged as incorrect by removing conditional formatting and/or data validation from the cells. There is an easier way, however: you can combine a simple checkbox from the Forms toolbar with data validation.

For this example, you'll apply conditional formatting to a range of cells so that any data appearing more than once is highlighted for easy identification. We'll assume your table of data extends from cell $A$1:$H$100. To conditionally format this range of data so that you can identify duplicates requires a few steps.

Select cell K1 and name this cell CheckBoxLink by typing the name into the Name box to the left of the Formula bar and pressing Enter. If the Forms toolbar is not already showing, right-click any toolbar and select Forms, then click the Checkbox icon. Now click your worksheet somewhere outside the range A1:H100 to add the checkbox to the worksheet.

Right-click the checkbox and select Format Control → Control. In the Cell Link box, type the name CheckBoxLink and click OK. Select cell A1, then drag and select a range down to cell H100. It is important that cell A1 is the active cell in your selection. Select Format → Conditional Formatting..., and from the box with the Value Is cell, select Formula Is. In the box to the right of Formula Is, enter this formula (as shown in Figure 2-7):

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

Click the Format tab and then the Patterns page tab and select a color you want to be applied to duplicated data. Click OK, then OK again.

Figure 2-7: Conditional Formatting dialog showing formula to conditionally format a range to highlight duplicates

Although the checkbox you added to the worksheet is checked, the cell link in K1 (CheckBoxLink) will read

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

Content preview·Buy rep

Table of Contents