Questions
About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars
Message Board
MrExcel Store
Podcast
Search
Media
Contact
Home
Having an Excel Emergency?


Excel Gurus Gone Wild

Do the IMPOSSIBLE with Microsoft Excel




 

  Do not try any tricks in this book
without first consulting a physician...

Most tricks will make your head spin!
Excel Gurus Gone Wild
Do the IMPOSSIBLE with Microsoft Excel by Bill Jelen


Without apologies, this book is filled with the weird and the arcane from the world of Excel. Most tricks will only be of interest to the hard-core Exceller !

Like all good things, this book was conceived in a British pub. I was in England in November 2007 and meeting up with Russ Cockings, Bryony Stewart-Seume, Richard Schollar, Jon Von Der Hayden and Mel Smith. They were talking about some amazing tricks that they’ve seen at the board when someone, probably either Jon or Richard comments that their MrExcel favorites list was a veritable reference guide to Excel and VBA. These are very smart people who know a whole lot about Excel. You have to wonder what types of things would impress this group enough to cause them to add it to their favorites list. This book is a compendium of the strange, bizarre, and sometimes useful things you can do with Excel.

To say that this book is a niche book is an understatement.

I am not out to reach the masses with this book. Topics in this book are arcane. A person who uses Excel for 2000 hours per year might need to use any given topic once every 20,000 years. It is probably 1 tenth of one percent of the people using Excel will find any of this stuff fascinating. If you always wondered why people put minus minus in their SUMPRODUCT formulas, then this book might be for you. If that last sentence makes no sense, then I’ve written plenty of other books which would appeal to you. If you are the power Excel person in your office, using Excel 20-40 hours a week, check out Learn Excel 97-2007 from MrExcel.
 


Tips for Solving 60 Incredibly Difficult Problems
You will find amazing ideas as you browse the topics. Yes, some are niche topics and many are arcane. However, if you use Excel all day, it is pretty wild to find someone who was able to coax the impossible out of a favorite spreadsheet.

For the first two sections of this book, my general requirement for inclusion was that the topic had to be amazing to either me or favorited by a number of MrExcel MVP’s. Some things came from the MrExcel Message Board, others came up during my Excel seminars.
In the third section, I go through some basics to get you comfortable with Excel VBA and then launch into some amazing VBA utility macros.
The appendix is an Excel function reference, with suggested uses for 120 of the 362 functions.

The tips for solving 100 incredibly difficult problems are covered in depth and include extracting the first letter of each word in a paragraph, validating URL's, generating random numbers without repeating, and hiding rows if cells are empty. The answers to these and other questions have produced results that have even surprised the Excel development team.

About the Author: Bill Jelen is the host of MrExcel.com. You will see him on the road with his Power Excel seminar or on iTunes with nearly 1000 podcast episodes. He is the author of 24 books, including Learn Excel 97-2007 from MrExcel, Guerilla Data Analysis Using Microsoft Excel, Pivot Table Data Crunching, and VBA & Macros for Excel. He lives in Akron, Ohio.


Table of Contents:

SECTION 1 - FORMULAS

    1. FIND THE FIRST NON-BLANK VALUE IN A ROW
    2. CALCULATE WORKDAYS FOR 5, 6, 7 DAY WORKWEEKS
    3. STORE HOLIDAYS IN A NAMED RANGE
    4. SUM EVERY OTHER ROW OR EVERY THIRD ROW
    5. WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/ FALSE
    6. INTRODUCING THE BORING USE OF SUMPRODUCT
    7. UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND IS *,OR IS + AND EVERYTHING ELSE IS TRUE
    8. USE GET.CELL TO HIGHLIGHT NON-FORMULA CELLS
    9. REFER TO A CELL WHOSE ADDRESS VARIES, BASED ON A CALCULATION
    10. POINT TO ANOTHER WORKSHEET WITH INDIRECT
    11. GET DATA FROM ANOTHER WORKSHEET BY USING INDIRECT
    12. USE INDIRECT TO GET A DATA FROM A MULTI-CELL RANGE
    13. ALWAYS POINT TO CELL B10
    14. USE NATURAL LANGUAGE FORMULAS WITHOUT USING NATURAL LANGUAGE FORMULAS
    15. SUM A CELL THROUGH SEVERAL WORKSHEETS
    16. SUM VISIBLE ROWS
    17. LEARN R1C1 REFERENCES
    18. RANDOM NUMBERS WITHOUT DUPLICATES
    19. SORT WITH A FORMULA
    20. DEAL WITH DATES BEFORE 1900
    21. USE VLOOKUP TO GET THE NTH MATCH
    22. USE A SELF-REFERENCING FORMULA
    23. USE TWO-WAY INTERPOLATION WITH A SINGLE FORMULA
    24. FIND THE SUM OF ALL DIGITS OCCURING IN A STRING
    25. GET AN ARRAY OF UNIQUE VALUES FROM A LIST
    26. AUTO-NUMBER RECORDS AND COLUMNS IN AN EXCEL DATABASE
SECTION 2 - TECHNIQUES
    27. USE AUTOFILTER WITH A PIVOT TABLE
    28. SORT SUBTOTALS
    29. COPY AN EXACT FORMULA BY USING DITTO MARKS
    30. RIGHT-DRAG BORDER TO ACCESS MORE COPYING OPTIONS
    31. QUICKLY CREATE A HYPERLINK MENU
    32. QUICKLY CREATE MANY RANGE NAMES
    34. ADD FORMULAS TO SMARTART
    35. CREATE A PIVOT TABLE FROM DATA IN MULTIPLE WORKSHEETS
    36. DETERMINE THE HEIGHT AND WIDTH OF THE DATALABEL OBJECT
    37. ADJUST XY CHART SCALING FOR CORRECT ASPECT RATIO
SECTION 3 - MACROS
    38. MAKE A PERSONAL MACRO WORKBOOK
    39. RUN A MACRO FROM A SHORTCUT KEY
    40. RUN A MACRO FROM A BUTTON
    41. RUN A MACRO FROM AN ICON
    42. CREATE A REGULAR MACRO
    43. CREATE AN EVENT HANDLER MACRO
    44. EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING OTHER TEXT
    45. FIND THE CLOSEST MATCH
    46. USE TIMER TO MICRO-TIME EVENTS
    47. DISCOVER THE TEMP FOLDER PATH
    48. USE EVALUATE IN VBA INSTEAD OF LOOPING THROUGH CELLS
    49. RENAME EACH WORKSHEET BASED ON ITS A1 VALUE
    50. USE A CUSTOM PULL FUNCTION INSTEAD OF INDIRECT WITH A CLOSED WORKBOOK
    51. IN VBA, DETERMINE THE NUMBER OF THE ACTIVE WORKSHEET
    52. CREATE WORKSHEET NAMES BY USING THE FILL HANDLE
    53. COPY THE PERSONAL MACRO WORKBOOK TO ANOTHER COMPUTER
    54. ADD FILTER TO SELECTION FUNCTIONALITY
    55. USE A MACRO TO HIGHLIGHT THE ACTIVE CELL
    56. REMOVE THE CAPTION BAR FROM A USER FORM
    57. KEEP A BUTTON IN VIEW
    58. ADD A RIGHT-CLICK MENU TO A USER FORM
    59. FORMAT A USER FORM TEXT BOX AS CURRENCY OR A PERCENTAGE
    61. DELETE RECORDS IN VBA
    62. SELF-SIGN YOUR MACROS FOR CO-WORKERS
    63. MAGNIFY A SECTION OF YOUR SCREEN
    64. LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME
APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE

Product Details
Paperback: 238 Pages; Dimensions (in inches): 7 x 9 x 0.75
ISBN: 978-1932802-40-5 (Print); 978-1932802-50-4 (e-Book); 978-1932802-49-8 (Kindle)
Publisher: Holy Macro! Books
Distributed by: Independent Publishers Group
Price: $24.95 (print), $19.95 (e-book)


There are over 150,000 pages at MrExcel.com. Use Google to search our site:




MrExcel Consulting can be hired to implement this concept, or many other cool applications, with your data.



Privacy Policy

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2009 by MrExcel Consulting.