O'Reilly logo

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

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

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

Start Free Trial

No credit card required

Preface

Millions of Microsoft Excel users are busy creating and sharing spreadsheets every day. Indeed, the spreadsheet has grown from a powerful convenience to a transformative foundation for many businesses, driving decision-making around the planet.

Although Excel is a critical tool, many Excel users know only about a subset of its functionality. They utilize the pieces they need, often reusing more complex pieces from existing templates, and don’t dive too deeply into everything Excel has to offer. Odds are good that no single user actually needs every feature in Excel, so this approach is pretty reasonable. At the same time, though, it means a lot of people never get far enough along the learning curve to see the techniques they can use to make their work much easier.

With the release of MS Office 2007 comes a new version of Excel. There are many changes with Excel 2007, the most obvious being the new user interface. The introduction of the ribbon provides a results-oriented interface that presents tools when you need them, in a clear and organized fashion. The size of a spreadsheet has also been greatly increased, with the number of columns now well over 16,000 and the number of rows over 1,000,000. The total amount of memory that Excel can use has also been increased and is limited only by the maximum available memory Windows will allow on your PC.

Other improvements include easier use of PivotTables, conditional formatting and named ranges, live visual previews, predefined style galleries, table formats, and SmartArt graphics allowing you to use more complicated graphics in your spreadsheets. As most of us are usually required to work across a range of applications, share workbooks, and connect with the Web, Excel 2007 makes this much more user-friendly and easy to manage.

In this book, we have again used real-life situations for the content. Excel 2007 has been used as a base for almost all of the hacks, although most of the hacks can be used in previous versions as well and a few are specific to earlier versions. Differences are highlighted in the text and most of the old menu items can still be found, just in a different place and possibly named slightly differently.

Why Excel Hacks?

Although it’s possible to accomplish an enormous amount of work using a relatively simple subset of Excel’s capabilities, the software offers a lot of powerful techniques that can leapfrog your work beyond the ordinary without requiring that you spend years using and studying Excel. However, most people focus on the content they create—data and formulas, with the occasional chart—so moving to more advanced levels of Excel usage seems difficult.

There are lots of ways to take advantage of Excel’s capabilities to greatly extend your ability to create great spreadsheets, but that don’t require years of study. These tools, or hacks—quick and dirty solutions to problems, or clever ways of doing things—were created by Excel users looking for simple solutions to complex issues. The hacks in this book are designed to show you what’s possible and how to make them work immediately.

You can benefit from these hacks in two important ways. First, you can use the hacks directly as you build and improve your spreadsheets. Second, by studying the hacks and possibly learning a little Visual Basic for Applications (VBA) code, you can customize the hacks to meet your needs precisely.

Getting and Using the Hacks

To save you the time and effort of typing scripts and spreadsheets by hand, all the hacks (except those that are only a few lines long or use only the GUI) are available for download from the authors’ web site at http://www.ozgrid.com/BookExamples/excel-hack2-examples.htm.

You’ll undoubtedly want to cut and paste from the examples and modify their contents to make them fit your spreadsheets more precisely. Excel spreadsheets are tremendously diverse, and you’ll want to change things to make them fit your work.

How to Use This Book

Although this book is divided into chapters, as described in the following section, you can use it in a variety of different ways. One approach is to think of the book as a toolbox and start by becoming familiar with the tools in each chapter. Then, when a need arises or a problem occurs, you can simply use the right tool for the job. Or, you might decide to browse through the book or read it from cover to cover, studying the procedures and scripts to learn more about Excel. Some of the hacks are helpful in this area because they contain tutorials about complex subjects or well-documented scripts. You also might pick one chapter and see what you find useful to your current situation or what you might find helpful in the future.

How This Book Is Organized

Whichever way you choose to use this book, you will probably want to familiarize yourself with the contents first, so here’s a brief synopsis of each chapter and what you’ll find:

Chapter 1, Reducing Workbook and Worksheet Frustration

Workbooks and worksheets are the primary interface to data in Excel, but sometimes this set of giant open grids doesn’t do precisely what you want. These hacks enable you to manage how users interact with work-sheets, help you find and highlight information, and teach you how to deal with debris and corruption.

Chapter 2, Hacking Excel’s Built-in Features

Excel includes many built-in features for analyzing and managing data. However, these features often have limitations. The hacks in this chapter enable you to extend and automate these features, moving beyond the limited tasks they were designed to perform originally.

Chapter 3, Naming Hacks

Although cell references such as A2 and IV284:IN1237 are certainly useful, as spreadsheets become larger, it’s often easier to reference information by name. These hacks show you not only how to name cells and ranges, but also how to create names that adapt to the data in your spreadsheet.

Chapter 4, Hacking PivotTables

For many Excel users, PivotTables already seem like a complicated but magical hack. The hacks in this chapter teach you how to get the most out of PivotTables by showing you how to extend them and avoid the problems that make them frustrating.

Chapter 5, Charting Hacks

Excel’s built-in charting capabilities are very useful, but they don’t always provide the best method for viewing spreadsheet data. These hacks teach you how to tweak and combine Excel’s built-in charting capabilities so that you can create customized charts.

Chapter 6, Hacking Formulas and Functions

Formulas and functions are at the heart of most spreadsheets, but sometimes the way Excel handles them just isn’t quite what you want. These hacks cover subjects ranging from moving formulas around to dealing with datatype issues to improving recalculation time.

Chapter 7, Macro Hacks

Macros (and VBA) are Excel’s escape hatch, enabling you to build spreadsheets that go well beyond Excel’s own capabilities or develop spreadsheets that look more like programs. These hacks help you make the most of macros, from managing them to using them to extend other features.

Chapter 8, Cross-Application Hacks

Although most spreadsheets are self-contained, this chapter shows you how you can work with other Microsoft Office applications to get information into and out of your spreadsheets and into and out of other programs.

Windows, Macintosh, and Earlier Excel Versions

The hacks in this book were written for Excel 2007 and were tested on previous versions of Excel for Windows and on a Macintosh using Excel 2004. Where steps or menu options differ, the main text shows how to accomplish the task in Excel 2007, with instructions for “pre-2007” called out in notes or parentheses.

Most of the differences between the Windows and Mac platform versions are cosmetic, and most involve changes to key combinations and the occasional menu. Where the key combinations differ, they are written with the Windows modifier first, as in Alt/Command(⌘)-Q, which means Alt-Q for Windows and ⌘-Q on the Macintosh. There are a few cases, especially in the Visual Basic Editor (VBE), where the interfaces look different and have different menu choices, and these are explained on first encounter. There are also a few Windows-only hacks, using the Windows registry and other features that are supported only on Windows versions of Excel. These are noted in the text.

Macintosh users with one-button mice should also note that holding down the Control key while clicking is the equivalent of right-clicking. (Macintosh users with two or more buttons can just right-click.) Recent models of Apple MacBook and MacBook Pros allow you to specify a right-click by holding two fingers on the trackpad and clicking. You must enable this in System Preferences.

Most of the hacks should work with any version of Excel from Excel 97 onward; the text will indicate when this isn’t the case. Whenever possible, screenshots were taken using Excel 2007, but the figures are not an indicator of which hacks work with which versions.

Conventions Used in This Book

The following typographical conventions are used in this book:

Plain text

Indicates cell identifiers, named ranges, menu titles, menu options, menu buttons, and keyboard accelerators (such as Alt and Ctrl).

Italic

Indicates new terms, URLs, email addresses, filenames, file extensions, pathnames, directories, and variables in text.

Constant width

Used for commands, options, switches, variables, attributes, keys, functions, types, classes, namespaces, methods, modules, properties, parameters, values, objects, events, event handlers, XML tags, HTML tags, macros, the contents of files, and the output from commands.

Constant width bold

Used to show commands or other text that should be typed literally by the user, as well as to emphasize important lines of code.

Constant width italic

Used in examples, tables, and commands to show text that should be replaced with user-supplied values.

A carriage return (↵) at the end of a line of code is used to denote an unnatural line break—that is, you should not enter these as two lines of code, but as one continuous line. Multiple lines are used in these cases due to page width constraints.

You should pay special attention to notes set apart from the text with the following icons:

Tip

This icon signifies a tip, suggestion, or general note.

Warning

This icon indicates a warning or caution.

The thermometer icons, found next to each hack, indicate the relative complexity of the hack:

Conventions Used in This Book

beginner

Conventions Used in This Book

moderate

Conventions Used in This Book

expert

The following icons, found below each hack, indicate which versions of Excel are compatible with the hack:

Conventions Used in This Book

Works with all versions of Excel

Conventions Used in This Book

Works with Excel 2007

Conventions Used in This Book

Works with versions of Excel prior to 2007

Conventions Used in This Book

Works with Excel 2003

Conventions Used in This Book

Works with Excel 2000

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Excel Hacks, Second Edition, by David and Raina Hawley. Copyright 2007 O’Reilly Media, Inc., 978-0-596-52834-8.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at

Safari® Enabled

When you see a Safari® Enabled icon on the cover of your favorite technology book, that means the book is available online through the O’Reilly Network Safari Bookshelf.

Safari offers a solution that’s better than e-books. It’s a virtual library that lets you easily search thousands of top tech books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

800-998-9938 (in the United States or Canada)

707-829-0515 (international or local)

707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at:

A collection of spreadsheet files for each individual hack is available at:

Visit the official web site of the Hacks series of books:

To comment or ask technical questions about this book, send email to:

For more information about our books, conferences, Resource Centers, and the O’Reilly Network, see our web site at:

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

Start Free Trial

No credit card required