Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets

Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets

Language: English

Pages: 412

ISBN: 0596528345

Format: PDF / Kindle (mobi) / ePub


Millions of users create and share Excel spreadsheets every day, but few go deeply enough to learn the techniques that will make their work much easier. There are many ways to take advantage of Excel's advanced capabilities without spending hours on advanced study. Excel Hacks provides more than 130 hacks -- clever tools, tips and techniques -- that will leapfrog your work beyond the ordinary.

Now expanded to include Excel 2007, this resourceful, roll-up-your-sleeves guide gives you little known "backdoor" tricks for several Excel versions using different platforms and external applications. Think of this book as a toolbox. When a need arises or a problem occurs, you can simply use the right tool for the job. Hacks are grouped into chapters so you can find what you need quickly, including ways to:

  • Reduce workbook and worksheet frustration -- manage how users interact with worksheets, find and highlight information, and deal with debris and corruption.
  • Analyze and manage data -- extend and automate these features, moving beyond the limited tasks they were designed to perform.
  • Hack names -- learn not only how to name cells and ranges, but also how to create names that adapt to the data in your spreadsheet.
  • Get the most out of PivotTables -- avoid the problems that make them frustrating and learn how to extend them.
  • Create customized charts -- tweak and combine Excel's built-in charting capabilities.
  • Hack formulas and functions -- subjects range from moving formulas around to dealing with datatype issues to improving recalculation time.
  • Make the most of macros -- including ways to manage them and use them to extend other features.
  • Use the enhanced capabilities of Microsoft Office 2007 to combine Excel with Word, Access, and Outlook.

You can either browse through the book or read it from cover to cover, studying the procedures and scripts to learn more about Excel

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Remove Phantom Workbook Links H A C K #14 Remove Phantom Workbook Links Hack #14 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

File ➝ Properties… ➝ General). Clean Up Your Macros If you have macros, now you need to address the modules that the macro code resides in. This is a fairly quick, painless, and straightforward process that entails exporting all modules (this functionality is not available on Mac OS X) and UserForms to your hard drive and then deleting the existing modules and UserForms, pressing Save, and importing the modules you exported. To do this, go into the Visual Basic Editor and, from within the

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

formatting, you can do things most people would think is possible only through the use of VBA code. H A C K #19 Identify Formulas with Conditional Formatting Hack #19 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

the Refers To: box: Expand down as many rows as there are numeric entries In the Refers To: box, type the following: =OFFSET($A$1,0,0,COUNT($A:$A),1) Expand down as many rows as there are numeric and text entries In the Refers To: box, type the following: =OFFSET($A$1,0,0,COUNTA($A:$A),1) Expand down to the last numeric entry In the Refers To: box, type the following: =OFFSET($A$1,0,0,MATCH(1E+306,$A:$A)) If you expect a number larger than 1E+306 (a 1 with 306 zeros), change this to a larger

Download sample

Download