Skip to content

Excel Hacks Tips and Tools for Streamlining Your Spreadsheets

ISBN-10: 0596528345

ISBN-13: 9780596528348

Edition: 2nd 2007 (Revised)

Authors: David Hawley, Raina Hawley

List price: $29.99
Blue ribbon 30 day, 100% satisfaction guarantee!
Rent eBooks
Buy eBooks
what's this?
Rush Rewards U
Members Receive:
Carrot Coin icon
XP icon
You have reached 400 XP and carrot coins. That is the daily max!

Description:

The tips and tools in 'Excel Hacks' include little-known 'backdoor' adjustments for everything from reducing workbook and worksheet frustration to hacking built-in features such as pivot tables, charts, formulas and functions, and even the macro language.
Customers also bought

Book details

List price: $29.99
Edition: 2nd
Copyright year: 2007
Publisher: O'Reilly Media, Incorporated
Publication date: 6/30/2007
Binding: Paperback
Pages: 412
Size: 6.25" wide x 9.00" long x 1.00" tall
Weight: 1.188
Language: English

Asbj�rn Wahl is director of the Campaign for the Welfare State. He is also an adviser at the Norwegian Union of Municipal and General Employees and holds an elected position at the International Transport Workersrsquo; Federation. He is an active member of the Labour and Globalisation network.

Raina Hawley of Perth, Australia is a professional Microsoft Excel consultant whose company, OzGrid Business Applications, offers services in all aspects of Excel, and VBA for Excel. The company's web site at www.ozgrid.com provides online and email Excel training, a directory of useful Excel add-ins, a user forum, and lots of tips and tricks. Raina is also a lecturing team member at West Australian Institute of Management (WAIM) with specific expertise in Word, Excel, Powerpoint, Internet and email.

Credits
Preface
Reducing Workbook and Worksheet Frustration
Create a Personal View of Your Workbooks
Enter Data into Multiple Worksheets Simultaneously
Prevent Users from Performing Certain Actions
Prevent Seemingly Unnecessary Prompts
Hide Worksheets So That They Cannot Be Unhidden
Customize the Templates Dialog and Default Workbook
Create an Index of Sheets in Your Workbook
Limit the Scrolling Range of Your Worksheet
Lock and Protect Cells Containing Formulas
Find Duplicate Data Using Conditional Formatting
Find Data That Appears Two or More Times Using Conditional Formatting
Tie Custom Toolbars to a Particular Workbook
Outsmart Excel's Relative Reference Handler
Remove Phantom Workbook Links
Reduce Workbook Bloat
Extract Data from a Corrupt Workbook
Hacking Excel's Built-in Features
Validate Data Based on a List on Another Worksheet
Control Conditional Formatting with Checkboxes
Identify Formulas with Conditional Formatting
Count or Sum Cells That Meet Conditional Formatting Criteria
Highlight Every Other Row or Column
Create 3-D Effects in Tables or Cells
Turn Conditional Formatting and Data Validation On and Off with a Checkbox
Support Multiple Lists in a ComboBox
Create Validation Lists That Change Based on a Selection from Another List
Use Replace...to Remove Unwanted Characters
Convert Text Numbers to Real Numbers
Extract the Numeric Portion of a Cell Entry
Customize Cell Comments
Sort by More Than Three Columns
Random Sorting
Manipulate Data with the Advanced Filter
Create Custom Number Formats
Add More Levels of Undo to Excel for Windows
Create Custom Lists
Boldface Excel Subtotals
Convert Excel Formulas and Functions to Values
Automatically Add Data to a Validation List
Hack Excel's Date and Time Features
Enable Grouping and Outlining on a Protected Worksheet
Prevent Blanks/Missing Fields in a Table
Provide Decreasing Data Validation Lists
Add a Custom List to the Fill Handle
Naming Hacks
Address Data by Name
Use the Same Name for Ranges on Different Worksheets
Create Custom Functions Using Names
Create Ranges That Expand and Contract
Nest Dynamic Ranges for Maximum Flexibility
Identify Named Ranges on a Worksheet
Hacking PivotTables
PivotTables: A Hack in Themselves
Share PivotTables but Not Their Data
Automate PivotTable Creation
Move PivotTable Grand Totals
Efficiently Pivot Another Workbook's Data
Charting Hacks
Explode a Single Slice from a Pie Chart
Create Two Sets of Slices in One Pie Chart
Create Charts That Adjust to Data
Interact with Your Charts Using Custom Controls
Four Quick Ways to Update Your Charts
Hack Together a Simple Thermometer Chart
Create a Column Chart with Variable Widths and Heights
Create a Speedometer Chart
Link Chart Text Elements to a Cell
Hack Chart Data So That Empty or False Formula Cells Are Not Plotted
Add a Directional Arrow to the End of a Line Series
Place an Arrow on the End of a Horizontal (X) Axis
Correct Narrow Columns When Using Dates
Position Axis Labels
Tornado Chart
Gauge Chart
Conditional Highlighting Axis Labels
Create Totals on a Stacked Column Chart
Hacking Formulas and Functions
Add Descriptive Text to Your Formulas
Move Relative Formulas Without Changing References
Compare Two Excel Ranges
Fill All Blank Cells in a List
Make Your Formulas Increment by Rows When You Copy Across Columns
Convert Dates to Excel Formatted Dates
Sum or Count Cells While Avoiding Error Values
Reduce the Impact of Volatile Functions on Recalculation
Count Only One Instance of Each Entry in a List
Sum Every Second, Third, or Nth Row or Cell
Find the Nth Occurrence of a Value
Make the Excel Subtotal Function Dynamic
Add Date Extensions
Convert Numbers with the Negative Sign on the Right to Excel Numbers
Display Negative Time Values
Use the VLOOKUP Function Across MultipIe Tables
Show Total Time As Days, Hours, and Minutes
Determine the Number of Specified Days in Any Month
Construct Mega-Formulas
Hack Mega-Formulas that Reference Other Workbooks
Hack One of Excel's Database Functions to Take the Place of Many Functions
Extract Specified Words from a Text String
Count Words in a Cell or Range of Cells
Return a Worksheet Name to a Cell
Sum Cells with Multiple Criteria
Count Cells with Multiple Criteria
Calculate a Sliding Tax Scale
Add/Subtract Months from a Date
Find the Last Day of Any Given Month
Calculate a Person's Age
Return the Weekday of a Date
Evaluate a Text Equation
Lookup from Within a Cell
Macro Hacks
Speed Up Code While Halting Screen Flicker
Run a Macro at a Set Time
Use CodeNames to Reference Sheets in Excel Workbooks
Connect Buttons to Macros Easily
Create a Workbook Splash Screen
Display a "Please Wait" Message
Have a Cell Ticked or Unticked upon Selection
Count or Sum Cells That Have a Specified Fill Color
Add the Microsoft Excel Calendar Control to Any Excel Workbook
Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
Retrieve a Workbook's Name and Path
Get Around Excel's Three-Criteria Limit for Conditional Formatting
Run Procedures on Protected Worksheets
Distribute Macros
Delete Rows Based on a Condition
Track and Report Changes in Excel
Automatically Add Date/Time to a Cell upon Entry
Create a List of Workbook Hyperlinks
Advanced Find
Find a Number Between Two Numbers
Convert Formula References from Relative to Absolute
Name a Workbook with the Text in a Cell
Hide and Restore Toolbars in Excel
Sort Worksheets
Password-Protect a Worksheet from Viewing
Change Text to Upper- or Proper Case
Force Text to Upper- or Proper Case
Prevent Case Sensitivity in VBA Code
Display AutoFilter Criteria
Cross-Application Hacks
Import Data from Access 2007 into Excel 2007
Retrieve Data from Closed Workbooks
Automate Word from Excel
Automate Outlook from Excel
Index