Excel Power Moves E-Book

MrExcel's Holy Macro! Books

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 48

Veröffentlichungsjahr: 2024

Beschreibung

This book dives deep into the world of advanced Excel techniques, providing creative solutions to everyday challenges. From powerful formulas like SUMIF to dynamic arrays, it equips readers with tools to streamline data management and boost productivity. Learn to create hyperlinked table of contents, automate tasks, and uncover hidden features that transform your workflow.
The guide also explores formatting tricks and VBA shortcuts for building efficient processes. Discover the magic of the Camera Tool, advanced filtering techniques, and tips for securing workbooks. With clear instructions, you'll unlock Excel’s full potential while simplifying complex tasks.
Ideal for all users, this book covers everything from advanced formulas to innovative use of Excel’s lesser-known features. Whether you're automating tasks, applying advanced filters, or mastering macros, this guide provides the insights and strategies needed to take your Excel skills to the next level.

Leseprobe

Excel Power Moves

© 2021 by Bob Umlas and Holy Macro! Books

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book to be trademarked have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books.

Every effort has been made to make this book as complete and accurate as possible, but not warranty or fitness is implied. The information is provide on an “as is” basis. The author and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Printed in the USA by Hess Print Solutions

First Printing: April 2022 (e-book April 2021)

Author: Bob Umlas

Cover Design: Shannon Travise

Publisher: Bill Jelen

Index: Nellie Jay

Tech Editor: Bill Jelen

Published by Holy Macro! Books, PO Box 541731, Merritt Island FL 32954

Distributed by Independent Publishers Group, Chicago IL

ISBN 978-1-615470-073-0 (print) 978-1-615470-159-1 (digital)

Library of Congress Control Number: 2021TBD

Table of Contents

Foreword

Acknowledgments

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!

Index

Other books by Bob Umlas:

This isn’t Excel, it’s Magic (2005)

This isn’t Excel, it’s Magic 2nd Edition (2007)

Excel Outside the Box (2012)

More Excel Outside the Box (2015)

Excel Preschool (2019)

Foreword

I actually learned Microsoft Excel on the Macintosh in 1986 or so, using version 0.99! When the real version (1.00?) shipped, I read the manual (yes, Excel came with manuals then) from cover to cover. Six times. Especially in order to learn Data Tables. I just didn’t get it. (Okay, so I’m a geek. Actually, I once heard that you’re a geek if you double-click the TV remote)!

Around 1993 I received the nickname “Excel Trickster” from one Will Tompkins, a real Excel Guru. I got that because during a seminar he was hosting in Washington, I believe, for what he called the Excel SWAT team, he was showing his then famous Tompkins methodology – a macro structure using Excel 4 style macros (before VBA existed in Excel) which was quite sophisticated. He showed a line of code which used a range name, a label, and he wanted to show us the code at that label. So, he used F5(Goto), and we saw literally many hundreds of defined names which he had to laboriously scroll through to get to the one he was looking for. After he had done this about 4-5 times, I raised my hand and said, “You know, you can get to that label directly. Just press Ctrl/[.” He tried it and was flabbergasted! So, he tried it again. Then he remarked that I just saved him about 2 hours every day scrolling through his defined names!

When I also told him you could return to the location you were just at by using Ctrl/], he called me the Excel Trickster, and that nickname has stuck with me ever since. So, thank you, Will.

I’ve been an Excel MVP for 25 years – 1993-2018, and I currently lead an online Master Class in Excel which is 12 3-hour sessions and a VBA class which is 2 3-hour sessions. I have presented at various global events (EIEFreshTalk and GlobalExcelSummit) with 1750 and 9400+ participants, respectively!

Acknowledgments

I’d like to thank my wife, Judy, for her continued support of my pursuing my Excel “studies”, former and current Excel MVPs for their continued eye-opening ideas upon which I have built some of these ideas.

Several ideas in this book were first discussed in the 2007 book, “This Isn’t Excel, It’s Magic”, published by IIL Publishing in New York. That book features 111 Excel tips and is still available from the publisher.