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)
|
