Cool Excel Sh*t
March 2021
50 More Awesome Excel Techniques from Bob Umlas!
Bob Umlas is the Excel MVP who knows everything about Excel. I think that every time Bob discovers a new technique that is surprising even to him, it shows up in this book!
Add to Cart: $11.99 »Product Details
- 95 Pages
- Publisher: Holy Macro! Books
- PDF ISBN: 978-1-615470-153-1
Cool Excel Sh*t is designed with the Excel guru in mind, introducing advanced, creative solutions and hacks for the software's most challenging problems. Through a series of more than 50 techniques, formulas, dynamic arrays, and VBA macros, this guide details processes that may be used in any application and across all disciplines.
-
FORMULAS
- An amazing formula to SUMIF the visible rows
- Putting in a Grand Total of data already subtotaled
- Using notes inside formulas via the N-function
- Fun with relatively defined names
- Bulk formula change
- Changing cell reference
- Numbering entries tip
-
FORMATTING
- Removing the leading apostrophe from many cells at once.
- Window/Arrange Tiled (except this one!)
-
MISCELLANEOUS
- Returning from clicking on a hyperlink
- You can type R1C1 notation into the Name box:
- Play a trick on your coworkers by hiding even row numbers
- Print Area manipulation
- Change Rows to Repeat at Top On All Worksheets
- A few miscellaneous tips & tricks
- Fill Handle technique to skip percentage columns
- Distributing
- 4 Ways to remove everything before the colon “puzzle”
- What’s so special about viewing your spreadsheet at 39% or less?
- Finding after the Find dialog is closed
- Understanding the order of View/Arrange All
- Using the buried feature: Home/Editing/Fill/Justify
- Shift/Scrolling
- Drag/Scrolling
- Mailing Label tricks - 1
- Mailing Label tricks - 2
- Mailing Label tricks - 3
- Ensuring users don’t open your workbook with the shift key down to prevent your macros from kicking in!
- Oddities with the Camera Tool
- Getting at the formatting of a cell to determine the sign
- A piece of silliness:
-
DYNAMIC ARRAYS
- Amazing Dynamic Array to list all files in a folder
- Getting an NxN grid of non-repeating integers
- Reversing a string
- Filter Magic
- More Filter Magic - Adding a Total Row
- Filtering as you type
- Create a hyperlinked, sorted Table of Contents of all sheets
-
VBA
- You can easily get to your VBA routine from the Excel sheet by using F5 (Go To) and typing the routine name:
- Using R1C1 to copy an involved formula to use in your VBA routine
- A VBA Shortcuts to select from a list
- More VBA Keyboard Shortcuts
- VBA Shortcut to fill ranges
- VBA Boolean test
- Multi-select offset
- VBA Array
- Split Bars in VBA
- In VBA, did you know the Locals window is read/write?
- Putting images in a Userform
- Make your own tools in the toolbox!
