ASAP Utilities user guide easy to use tools that save time and speed up your work in Excel ASAP Utilities user guide easy to use tools that save time and speed up your work in Excel author Bastien Mensink pages 375 latest revision December 12, 2018 ASAP Utilities version 7.5.3 © 1999-2018 A Must in Every Office BV Emmawijk 1 8011 CM Zwolle The Netherlands www.asap-utilities.com Information in this document is subject to change without notice. Except as permitted by such license, no part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, recording, or otherwise, without the prior written permission of the author. While every precaution has been taken in the preparation of this user guide, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained in herein. Microsoft, Microsoft Office, and Excel are registered trademarks of Microsoft Corporation. 1 Contents 1 CONTENTS ____________________________________________________________3 2 INTRODUCTION ________________________________________________________8 2.1 Save paper .................................................................................................................... 8 2.2 Intended audience ........................................................................................................ 8 2.3 The Excel screen in English ........................................................................................... 9 2.3.1 Excel 2019, 2016, 2013, 2010 and 2007 and newer ......................................... 9 2.3.2 Excel 2003 and earlier .................................................................................... 10 3 GETTING STARTED ____________________________________________________11 3.1 How to get a copy of ASAP Utilities ............................................................................ 11 3.1.1 Download from our website ........................................................................... 11 3.1.2 Receive it by email .......................................................................................... 11 3.2 System requirements.................................................................................................. 12 3.2.1 Excel versions supported ................................................................................ 12 3.2.2 Windows versions supported ......................................................................... 12 3.2.3 Other requirements ........................................................................................ 12 3.2.4 International versions of Excel, all supported ................................................ 12 3.3 Install and uninstall ..................................................................................................... 12 3.4 Change the language of ASAP Utilities ....................................................................... 13 3.4.1 ASAP Utilities in English .................................................................................. 14 3.4.2 ASAP Utilities in Dutch (Nederlands) .............................................................. 15 3.4.3 ASAP Utilities in German (Deutsch) ................................................................ 16 3.4.4 ASAP Utilities in Spanish (Español) ................................................................. 17 3.4.5 ASAP Utilities in French (Français) .................................................................. 18 3.4.6 ASAP Utilities in Portuguese (Brazil) (Português do Brasil) ............................ 19 3.4.7 ASAP Utilities in Italian (Italiano) .................................................................... 20 3.4.8 ASAP Utilities in Russian (Русский) ................................................................ 21 3.4.9 ASAP Utilities in Chinese (Simplified) (中文(简体)) ........................................ 22 3.4.10 ASAP Utilities in Japanese (日本語) ............................................................. 23 3.4.11 Office Update................................................................................................ 24 ASAP Utilities User Guide FREQUENTLY UPDATED 3.4.12 Windows update ........................................................................................... 24 3.5 How to operate ASAP Utilities .................................................................................... 24 3.5.1 ASAP Utilities in Excel 2019, 2016, 2013, 2010 or 2007 ................................. 24 3.5.2 ASAP Utilities in Excel 2003, 2002/XP or 2000 ................................................ 25 3.6 Undo ............................................................................................................................ 25 3.6.1 Most (but not all) tools in ASAP Utilities have an undo .................................. 25 3.6.2 No undo in macros are by design of Excel ...................................................... 25 4 PURCHASE ASAP UTILITIES ______________________________________________ 27 4.1 Time saved, “return on investment” .......................................................................... 27 4.2 Editions ........................................................................................................................ 27 4.3 When to buy a license ................................................................................................. 28 4.3.1 Business Edition .............................................................................................. 28 4.3.2 Home & Student edition, Charitable Organizations ....................................... 28 4.4 Key benefits of a license.............................................................................................. 28 4.5 Who uses ASAP Utilities? ............................................................................................ 28 4.6 How to order ............................................................................................................... 28 4.6.1 Instant delivery ............................................................................................... 29 4.6.2 Secure ordering, online and offline payment methods .................................. 29 4.7 How to enter your license details ............................................................................... 30 5 AVAILABLE TOOLS _____________________________________________________ 32 5.1 English, German, Spanish, French, Portuguese (Brazil), Italian, Russian, Chinese (Simplified), Japanese and Dutch descriptions ........................................................... 32 5.2 ASAP Utilities in the Excel menu ................................................................................. 34 5.2.1 In Excel 2019, 2016, 2013, 2010 and 2007 ..................................................... 34 5.2.2 In Excel 2003, 2002/XP and 2000:................................................................... 34 5.2.3 Right-click/context menu ................................................................................ 35 5.3 Save time with keyboard shortcuts ............................................................................ 37 5.3.1 In Excel 2019, 2016, 2013, 2010 and 2007 ..................................................... 38 5.3.2 In Excel 2003, 2002/XP and 2000:................................................................... 39 5.4 Description of the tools in English .............................................................................. 40 5.4.1 Vision Control... ............................................................................................... 40 5.4.2 Favorites & Shortcut keys ............................................................................... 42 5.4.3 Select ............................................................................................................... 45 4 Chapter 1 FREQUENTLY UPDATED ASAP Utilities User Guide 5.4.4 Sheets ............................................................................................................. 64 5.4.5 Range .............................................................................................................. 84 5.4.6 Columns & Rows ........................................................................................... 101 5.4.7 Numbers & Dates ......................................................................................... 119 5.4.8 Text ............................................................................................................... 139 5.4.9 Formulas ....................................................................................................... 158 5.4.10 Fill ............................................................................................................... 177 5.4.11 Format ........................................................................................................ 190 5.4.12 Objects & Comments .................................................................................. 204 5.4.13 Web ............................................................................................................ 210 5.4.14 File & System .............................................................................................. 216 5.4.15 Information ................................................................................................. 230 5.4.16 Import ......................................................................................................... 244 5.4.17 Export ......................................................................................................... 249 5.4.18 Start ............................................................................................................ 258 5.4.19 ASAP Utilities Options ................................................................................. 261 5.4.20 Find and run a utility... ................................................................................ 281 5.4.21 Info... ........................................................................................................... 282 6 PRACTICAL TRICKS TO SAVE HOURS IN EXCEL WITH ASAP UTILITIES ___________ 283 6.1 Latest tips on our website ........................................................................................ 283 6.2 How to... examples ................................................................................................... 283 7 TROUBLESHOOTING FAQ _____________________________________________ 318 7.1 Priority support for customers ................................................................................. 318 7.2 Bug report guidelines ............................................................................................... 318 7.2.1 The basics: what you did, what you wanted, and what happened. ............. 318 7.2.2 Excel and windows version ........................................................................... 319 7.2.3 Always search our website and documentation first ................................... 319 7.2.4 Be brief, but don't leave any important details out ..................................... 319 7.2.5 Use meaningful, specific subject headers .................................................... 320 7.2.6 Please use English or Dutch (if possible) ...................................................... 320 7.2.7 Don't report bugs about old versions. .......................................................... 320 Contents 5 ASAP Utilities User Guide FREQUENTLY UPDATED 8 ABOUT ASAP UTILITIES ________________________________________________ 322 8.1 History of ASAP Utilities ............................................................................................ 322 8.2 The primary goals of ASAP Utilities ........................................................................... 323 8.3 Testimonials .............................................................................................................. 324 8.3.1 Here's what our users say ............................................................................. 324 8.3.2 Enthusiasm and 5 star rating ........................................................................ 324 8 .3 .3 A small selection of the feedback we received via email: .......................... 326 8.4 Our clients, who uses ASAP Utilities? ....................................................................... 328 9 CONTACT & SUPPORT ________________________________________________ 333 9.1 Get the latest information on ASAP Utilities ............................................................ 333 9.2 Contact us ................................................................................................................. 333 9.2.1 Priority support for customers ...................................................................... 333 9.3 Social media .............................................................................................................. 333 10 INDEX _____________________________________________________________ 334 11 APPENDIX 1: SCREENSHOTS ____________________________________________ 336 11.1 ASAP Utilities in Excel 2019, 2016, 2013, 2010 or 2007 ........................................... 336 11.1.1 English/international................................................................................... 336 11.1.2 Dutch (Nederlands) ..................................................................................... 336 11.1.3 German (Deutsch) ....................................................................................... 337 11.1.4 Spanish (Español) ........................................................................................ 337 11.1.5 French (Français) ......................................................................................... 337 11.2 ASAP Utilities in Excel 2000, 2002/XP or 2003 .......................................................... 338 11.2.1 English ......................................................................................................... 338 11.2.2 Dutch (Nederlands) ..................................................................................... 338 11.2.3 German (Deutsch) ....................................................................................... 339 11.2.4 Spanish (Español) ........................................................................................ 339 11.2.5 French (Français) ......................................................................................... 340 12 APPENDIX 2: TECHNICAL INFORMATION ABOUT THE SETUP __________________ 341 12.1 Installation ................................................................................................................ 341 12.1.1 Standard setup ............................................................................................ 341 12.1.2 Non-admin setup ........................................................................................ 341 6 Chapter 1 FREQUENTLY UPDATED ASAP Utilities User Guide 12.1.3 Automatic installation ................................................................................ 341 12.1.4 Manual installation Excel 2000, 2002/XP, 2003 ......................................... 350 12.1.5 Manual installation Excel 2019, 2016, 2013, 2010, 2007 ........................... 352 12.1.6 Upgrade to a new version .......................................................................... 356 12.1.7 Network installation ................................................................................... 358 12.2 Uninstall .................................................................................................................... 362 12.2.1 Automatic ................................................................................................... 362 12.2.2 Automatic uninstall #2................................................................................ 364 12.2.3 Manual ........................................................................................................ 364 12.2.4 Temporarily deactivating ASAP Utilities from startup ............................... 366 12.3 User data and registry settings ................................................................................. 369 12.3.1 Registry ....................................................................................................... 369 12.3.2 Favorites menu ........................................................................................... 369 12.3.3 Add ASAP Utilities to the Excel menu for all users ..................................... 370 12.3.4 Registering the DLL/COM add-in part ........................................................ 370 12.3.5 Registration/license settings ...................................................................... 371 12.4 Macro security .......................................................................................................... 371 12.4.1 Trusted sources and security settings to “very high” ................................. 372 12.5 How to use ASAP Utilities in your own macros ........................................................ 375 Contents 7 ASAP Utilities User Guide FREQUENTLY UPDATED 2 Introduction This User Guide will show you what you can do with ASAP Utilities. It will tell you how to install this Excel add-in and it gives a description of all the utilities available. ASAP Utilities makes YOU rock in Excel! ASAP Utilities contains easy to use tools that save time and speed up your work in Excel. Since 1999 it has grown to become probably one of the world’s most popular add-ins for MS Excel. In our opinion a good program should be easy to use without the need of any documentation. However, during the last years, the need for documentation has increased. ASAP Utilities has more tools than ever, and this user guide will help you to get the best out of it. Besides documenting the utilities in ASAP Utilities, we have also included a “How to” chapter. In that chapter (chapter 6, “Practical Tricks to Save Hours in Excel with ASAP Utilities” on page 283) we have listed common Excel questions and how to use ASAP Utilities to answer them. The most recent version of this User Guide is available on our website: https://www.asap-utilities.com This document currently covers everything available in version 7.5 which is, when this edition of the User Guide is released, the latest version. 2.1 Save paper To save paper, this document is optimized for duplex printing. To save even more paper, you can read this User Guide in PDF format on your computer, tablet or e-reader. 2.2 Intended audience The principal audience for this user guide is people that use Excel on a regular basis. The focus is on introducing you to the tools available in ASAP Utilities . We assume that you are already familiar with Excel and its structure (workbooks, worksheets, ranges etc.). If you do not use the international/English version of Excel then we recommend you to read paragraph 2.3, “The Excel screen in English” on page 9. 8 Chapter 2 FREQUENTLY UPDATED ASAP Utilities User Guide 2.3 The Excel screen in English This documentation is in English since most people that use Excel speak this language. Even though most people speak English, translating certain things in Excel might become difficult or look odd. That is why we show you the layout of Excel and how certain things are called in English. 2.3.1 Excel 2019, 2016, 2013, 2010 and 2007 and newer title bar ribbon (menu) formula bar column headers grid lines active cell (B2) selection or range of cells vertical scrollbar page break horizontal scrollbar name box (work)sheet tabs status bar row headers Introduction 9 ASAP Utilities User Guide FREQUENTLY UPDATED 2.3.2 Excel 2003 and earlier title bar menu bar command bars or toolbars formula bar column headings grid lines selection or range of cells page break vertical scrollbar active cell (B2) horizontal scrollbar (work)sheet tabs name box row headings status bar The different sheet types in English: 10 Chapter 2