Excel Hacks Ebook [Electronic resources] نسخه متنی

Sitemap

Excel Hacks

Table of Contents

Copyright

Dedication

Credits

About the Authors

Contributors

Acknowledgments

Preface

Why Excel Hacks?

Getting and Using the Hacks

How to Use This Book

How This Book Is Organized

Windows and Macintosh Users

Conventions Used in This Book

Using Code Examples

How to Contact Us

Chapter 1. Reducing Workbook and Worksheet Frustration

Hacks #1-15

Hack 1 Create a Personal View of Your Workbooks

Hack 2 Enter Data into Multiple Worksheets Simultaneously

Hack 3 Prevent Users from Performing Certain Actions

Hack 4 Prevent Seemingly Unnecessary Prompts

Hack 5 Hide Worksheets So That They Cannot Be Unhidden

Hack 6 Customize the Templates Dialog and Default Workbook

Hack 7 Create an Index of Sheets in Your Workbook

Hack 8 Limit the Scrolling Range of Your Worksheet

Hack 9 Lock and Protect Cells Containing Formulas

Hack 10 Find Duplicate Data using Conditional Formatting

Hack 11 Tie Custom Toolbars to a Particular Workbook

Hack 12 Outsmart Excel's Relative Reference Handler

Hack 13 Remove Phantom Workbook Links

Hack 14 Reduce Workbook Bloat

Hack 15 Extract Data from a Corrupt Workbook

Chapter 2. Hacking Excel's Built-in Features

Hacks #16-38

Hack 16 Validate Data Based on a List on Another Worksheet

Hack 17 Control Conditional Formatting with Checkboxes

Hack 18 Identify Formulas with Conditional Formatting

Hack 19 Count or Sum Cells That Meet Conditional Formatting Criteria

Hack 20 Highlight Every Other Row or Column

Hack 21 Create 3D Effects in Tables or Cells

Hack 22 Turn Conditional Formatting and Data Validation On and Off with a Checkbox

Hack 23 Support Multiple Lists in a ComboBox

Hack 24 Create Validation Lists That Change Based on a Selection from Another List

Hack 25 Force Data Validation to Reference a List on Another Worksheet

Hack 26 Use Replace... to Remove Unwanted Characters

Hack 27 Convert Text Numbers to Real Numbers

Hack 28 Customize Cell Comments

Hack 29 Sort by More Than Three Columns

Hack 30 Random Sorting

Hack 31 Manipulate Data with the Advanced Filter

Hack 32 Create Custom Number Formats

Hack 33 Add More Levels of Undo to Excel for Windows

Hack 34 Create Custom Lists

Hack 35 Boldface Excel Subtotals

Hack 36 Convert Excel Formulas and Functions to Values

Hack 37 Automatically Add Data to a Validation List

Hack 38 Hack Excel's Date and Time Features

Chapter 3. Naming Hacks

Hacks #39-44

Hack 39 Address Data by Name

Hack 40 Use the Same Name for Ranges on Different Worksheets

Hack 41 Create Custom Functions Using Names

Hack 42 Create Ranges That Expand and Contract

Hack 43 Nest Dynamic Ranges for Maximum Flexibility

Hack 44 Identify Named Ranges on a Worksheet

Chapter 4. Hacking PivotTables

Hacks #46-49

Hack 45 PivotTables: A Hack in Themselves

Hack 46 Share PivotTables but Not Their Data

Hack 47 Automate PivotTable Creation

Hack 48 Move PivotTable Grand Totals

Hack 49 Efficiently Pivot Another Workbook's Data

Chapter 5. Charting Hacks

Hacks #50-59

Hack 50 Explode a Single Slice from a Pie Chart

Hack 51 Create Two Sets of Slices in One Pie Chart

Hack 52 Create Charts That Adjust to Data

Hack 53 Interact with Your Charts Using Custom Controls

Hack 54 Three Quick Ways to Update Your Charts

Hack 55 Hack Together a Simple Thermometer Chart

Hack 56 Create a Column Chart with Variable Widths and Heights

Hack 57 Create a Speedometer Chart

Hack 58 Link Chart Text Elements to a Cell

Hack 59 Hack Chart Data So That Blank Cells Are Not Plotted

Chapter 6. Hacking Formulas and Functions

Hacks #60-80

Hack 60 Add Descriptive Text to Your Formulas

Hack 61 Move Relative Formulas Without Changing References

Hack 62 Compare Two Excel Ranges

Hack 63 Fill All Blank Cells in a List

Hack 64 Make Your Formulas Increment by Rows When You Copy Across Columns

Hack 65 Convert Dates to Excel Formatted Dates

Hack 66 Sum or Counting Cells While Avoiding Error Values

Hack 67 Reduce the Impact of Volatile Functions on Recalculation

Hack 68 Count Only One Instance of Each Entry in a List

Hack 69 Sum Every Second, Third, or nth Row or Cell

Hack 70 Find the nth Occurrence of a Value

Hack 71 Make the Excel Subtotal Function Dynamic

Hack 72 Add Date Extensions

Hack 73 Convert Numbers with the Negative Sign on the Right to Excel Numbers

Hack 74 Display Negative Time Values

Hack 75 Use the VLOOKUP Function Across Multiple Tables

Hack 76 Show Total Time as Days, Hours, and Minutes

Hack 77 Determine the Number of Specified Days in Any Month

Hack 78 Construct Mega-Formulas

Hack 79 Hack Mega-Formulas that Reference Other Workbooks

Hack 80 Hack One of Excel's Database Functions to Take the Place of Many Functions

Chapter 7. Macro Hacks

Hacks #81-94

Hack 81 Speed Up Code While Halting Screen Flicker

Hack 82 Run a Macro at a Set Time

Hack 83 Use CodeName to Reference Sheets in Excel Workbooks

Hack 84 Connect Buttons to Macros Easily

Hack 85 Create a Workbook Splash Screen

Hack 86 Display a

Hack 87 Have a Cell Ticked or Unticked upon Selection

Hack 88 Count or Sum Cells That Have a Specified Fill Color

Hack 89 Add the Microsoft Excel Calendar Control to Any Excel Workbook

Hack 90 Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop

Hack 91 Retrieve a Workbook's Name and Path

Hack 92 Get Around Excel's Three-Criteria Limit for Conditional Formatting

Hack 93 Run Procedures on Protected Worksheets

Hack 94 Distribute Macros

Chapter 8. Connecting Excel to the World

Hacks #95-100

Hack 95 Load an XML Document into Excel

Hack 96 Save to SpreadsheetML and Extracting Data

Hack 97 Create Spreadsheets using SpreadsheetML

Hack 98 Import Data Directly into Excel

Hack 99 Access SOAP Web Services from Excel

Hack 100 Create Excel Spreadsheets Using Other Environments

Glossary

Colophon