MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Gurus Gone Wild


April 2009

Excel Gurus Gone Wild

Do not try any tricks in this book without first consulting a physician... Most tricks will make your head spin!

Bill Jelen, MrExcel

category: Spreadsheets
covers: Excel

Product Details
  • Paperback: 238 Pages
  • Publisher: Holy Macro! Books
  • Print ISBN: 978-1932802-40-5
  • PDF ISBN: 978-1932802-50-4
  • Mobi (Kindle) ISBN: 978-1932802-49-8

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 Power Excel With MrExcel - 2017 Edition.

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.

  • 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

    1. USE AUTOFILTER WITH A PIVOT TABLE
    2. SORT SUBTOTALS
    3. COPY AN EXACT FORMULA BY USING DITTO MARKS
    4. RIGHT-DRAG BORDER TO ACCESS MORE COPYING OPTIONS
    5. QUICKLY CREATE A HYPERLINK MENU
    6. QUICKLY CREATE MANY RANGE NAMES
    7. ADD FORMULAS TO SMARTART
    8. CREATE A PIVOT TABLE FROM DATA IN MULTIPLE WORKSHEETS
    9. DETERMINE THE HEIGHT AND WIDTH OF THE DATALABEL OBJECT
    10. ADJUST XY CHART SCALING FOR CORRECT ASPECT RATIO
  • SECTION 3 - MACROS

    1. MAKE A PERSONAL MACRO WORKBOOK
    2. RUN A MACRO FROM A SHORTCUT KEY
    3. RUN A MACRO FROM A BUTTON
    4. RUN A MACRO FROM AN ICON
    5. CREATE A REGULAR MACRO
    6. CREATE AN EVENT HANDLER MACRO
    7. EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING OTHER TEXT
    8. FIND THE CLOSEST MATCH
    9. USE TIMER TO MICRO-TIME EVENTS
    10. DISCOVER THE TEMP FOLDER PATH
    11. USE EVALUATE IN VBA INSTEAD OF LOOPING THROUGH CELLS
    12. RENAME EACH WORKSHEET BASED ON ITS A1 VALUE
    13. USE A CUSTOM PULL FUNCTION INSTEAD OF INDIRECT WITH A CLOSED WORKBOOK
    14. IN VBA, DETERMINE THE NUMBER OF THE ACTIVE WORKSHEET
    15. CREATE WORKSHEET NAMES BY USING THE FILL HANDLE
    16. COPY THE PERSONAL MACRO WORKBOOK TO ANOTHER COMPUTER
    17. ADD FILTER TO SELECTION FUNCTIONALITY
    18. USE A MACRO TO HIGHLIGHT THE ACTIVE CELL
    19. REMOVE THE CAPTION BAR FROM A USER FORM
    20. KEEP A BUTTON IN VIEW
    21. ADD A RIGHT-CLICK MENU TO A USER FORM
    22. FORMAT A USER FORM TEXT BOX AS CURRENCY OR A PERCENTAGE
    23. DELETE RECORDS IN VBA
    24. SELF-SIGN YOUR MACROS FOR CO-WORKERS
    25. MAGNIFY A SECTION OF YOUR SCREEN
    26. LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME
  • APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE

Where to Buy