Excel Gurus Gone Wild
April 2009
Do not try any tricks in this book without first consulting a physician... Most tricks will make your head spin!
Add to Cart: $9.99 »- 238 Pages
- Publisher: Holy Macro! Books
- ISBN: 978-1932802-40-5
- PDF ISBN: 978-1932802-50-4
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
- FIND THE FIRST NON-BLANK VALUE IN A ROW
- CALCULATE WORKDAYS FOR 5, 6, 7 DAY WORKWEEKS
- STORE HOLIDAYS IN A NAMED RANGE
- SUM EVERY OTHER ROW OR EVERY THIRD ROW
- WHY THE MINUS MINUS? COERCE NUMBERS FROM TRUE/ FALSE
- INTRODUCING THE BORING USE OF SUMPRODUCT
- UNDERSTAND BOOLEAN LOGIC: FALSE IS ZERO; AND IS *,OR IS + AND EVERYTHING ELSE IS TRUE
- USE GET.CELL TO HIGHLIGHT NON-FORMULA CELLS
- REFER TO A CELL WHOSE ADDRESS VARIES, BASED ON A CALCULATION
- POINT TO ANOTHER WORKSHEET WITH INDIRECT
- GET DATA FROM ANOTHER WORKSHEET BY USING INDIRECT
- USE INDIRECT TO GET A DATA FROM A MULTI-CELL RANGE
- ALWAYS POINT TO CELL B10
- USE NATURAL LANGUAGE FORMULAS WITHOUT USING NATURAL LANGUAGE FORMULAS
- SUM A CELL THROUGH SEVERAL WORKSHEETS
- SUM VISIBLE ROWS
- LEARN R1C1 REFERENCES
- RANDOM NUMBERS WITHOUT DUPLICATES
- SORT WITH A FORMULA
- DEAL WITH DATES BEFORE 1900
- USE VLOOKUP TO GET THE NTH MATCH
- USE A SELF-REFERENCING FORMULA
- USE TWO-WAY INTERPOLATION WITH A SINGLE FORMULA
- FIND THE SUM OF ALL DIGITS OCCURING IN A STRING
- GET AN ARRAY OF UNIQUE VALUES FROM A LIST
- AUTO-NUMBER RECORDS AND COLUMNS IN AN EXCEL DATABASE
-
SECTION 2 - TECHNIQUES
- USE AUTOFILTER WITH A PIVOT TABLE
- SORT SUBTOTALS
- COPY AN EXACT FORMULA BY USING DITTO MARKS
- RIGHT-DRAG BORDER TO ACCESS MORE COPYING OPTIONS
- QUICKLY CREATE A HYPERLINK MENU
- QUICKLY CREATE MANY RANGE NAMES
- ADD FORMULAS TO SMARTART
- CREATE A PIVOT TABLE FROM DATA IN MULTIPLE WORKSHEETS
- DETERMINE THE HEIGHT AND WIDTH OF THE DATALABEL OBJECT
- ADJUST XY CHART SCALING FOR CORRECT ASPECT RATIO
-
SECTION 3 - MACROS
- MAKE A PERSONAL MACRO WORKBOOK
- RUN A MACRO FROM A SHORTCUT KEY
- RUN A MACRO FROM A BUTTON
- RUN A MACRO FROM AN ICON
- CREATE A REGULAR MACRO
- CREATE AN EVENT HANDLER MACRO
- EXTRACT AN E-MAIL ADDRESS FROM A CELL CONTAINING OTHER TEXT
- FIND THE CLOSEST MATCH
- USE TIMER TO MICRO-TIME EVENTS
- DISCOVER THE TEMP FOLDER PATH
- USE EVALUATE IN VBA INSTEAD OF LOOPING THROUGH CELLS
- RENAME EACH WORKSHEET BASED ON ITS A1 VALUE
- USE A CUSTOM PULL FUNCTION INSTEAD OF INDIRECT WITH A CLOSED WORKBOOK
- IN VBA, DETERMINE THE NUMBER OF THE ACTIVE WORKSHEET
- CREATE WORKSHEET NAMES BY USING THE FILL HANDLE
- COPY THE PERSONAL MACRO WORKBOOK TO ANOTHER COMPUTER
- ADD FILTER TO SELECTION FUNCTIONALITY
- USE A MACRO TO HIGHLIGHT THE ACTIVE CELL
- REMOVE THE CAPTION BAR FROM A USER FORM
- KEEP A BUTTON IN VIEW
- ADD A RIGHT-CLICK MENU TO A USER FORM
- FORMAT A USER FORM TEXT BOX AS CURRENCY OR A PERCENTAGE
- DELETE RECORDS IN VBA
- SELF-SIGN YOUR MACROS FOR CO-WORKERS
- MAGNIFY A SECTION OF YOUR SCREEN
- LIST COMBINATIONS OF N ITEMS TAKEN M AT A TIME
- APPENDIX 1 - ALPHABETICAL FUNCTION REFERENCE
