ASAP utilities is suite of over 300 excel addons that help you make the most out of Excel. First of all, this suite of addons is free only for non-commercial use, otherwise there's a price tag of $49. Beside the lift of the non-commercial use restriction, the paid version also lets you receive priority support and doesn't force you into updating it twice a year (not that it bothered me that much). Anyway, every now and then I have to do something in Excel that would either obligate me to create a macro, or to do a tedious work - and this is where ASAP Utilities helped me several times, enough to convince me to choose ten Excel tips to share with you. First of all, the suite of addons is available here and once installed it will show in Excel (2003 or 2007) as a new tab: Download ASAP Utilities (~10MB)
Excel tip #1 - Empty duplicate values in a selection
There's not much to explain here, as the title says exactly what this will do. Practically you have to:
- Select a range of cells you want to check for duplicates
- Click on the ASAP Utilities tab, go to the Range section and select Empty duplicates in selection
- You'll get a prompt if "you want to clear the contents of all duplicate values in the selected xx cells" which will process them when you press OK (the first value that is found is kept, other duplicate ones are deleted)
Excel tip #2 - Create a list of clickable links to all sheets in a workbook
It happened to me that I had a workbook with about 20 sheets in it, and I wanted to have a clickable list with all of them on one sheet. Then found this option, that gave me the option to have 20 links on one page that link to each sheet in the workbook. To create such an index in an Excel workbook with multiple sheets:
- Go to a new sheet where you want the index placed
- In the ASAP Utilities tab go to Sheets and click on Create an index page with all sheets (clickable)
- You can choose additional options, such as to order the links alphabetically ascending/descending, keep original order, include links to charts too
- Once you're ready, click on Create index sheet
Excel tip #3 - Remove all empty rows / Remove all empty columns
This is useful when you have a lot of rows/columns with data in them, but here and there you have an empty row or column. I know it sounds easy to select manually the empty rows/columns and delete them, but imagine if you have 10000 rows and you have to go through all of them to select the empty ones? Not that easy afterall my friend. This is how ASAP Utilities does it ... ASAP:
- Select the range of rows/columns you want to filter
- In ASAP Utilities go to Columns & Rows->Remove all empty rows (if it's rows you're removing) or Remove all empty columns
- You'll get a prompt Are you sure man? and you'll have to click on
Sure, there's no sugarOK
Excel tip #4 - Copy cells with formulas and paste in their values
This basically does the same thing you could achieve by selecting a cell containing a formula, copying it, going to Edit-Paste Special, selecting Values and hitting OK, just that it does all of this in one selection and click. To do it:
- Select the formulas you want to paste in as values
- In the ASAP Utilities tab go to Numbers and click on Convert formulas to their values
- Click OK on the prompt
Excel tip #5 - Convert text to uppercase or lowercase
In Microsoft Word there's an option that lets you select a bunch of text and hit Shift+F3 and have that text converted to lowercase or uppercase. In Excel there isn't such an option, but, ASAP Utilities lets you do it by:
- Selecting the cell that contains text to be upper/lower-cased
- Going in the ASAP Utilities tab and selecting Text and then the option Convert to UPPERcase or Convert to lowercase
- Voila
Excel tip #6 - Convert dates to other formats
In America the date has a specific format (month/day/year) in some countries in Europe has another format (day/month/year) so if you have a lot of dates in one of these formats and you want to convert them to the other format, you're in a pickle. Except that ASAP Utilities let you convert dates in a gazillion formats with the push of a click. To do so:
- Select the cells that have the dates you want converted
- In ASAP Utilities go to Text and select Convert Dates
- A new window will show up that lets you input the Current date-format, and lets you select what to Convert to, as well as showing an example of before and after on the right side. Not only that, they will let you input the character separator between the original/converted date. Smoking!
- Hit OK when you want the actual conversion to start
Excel tip #7 - Extract hyperlinks from hidden links & Activate hyperlinks
Figure this, you copy a table from a webpage where each text in each column actually is a hidden hyperlink to a webpage. Now, you want to extract those hyperlinks in Excel and have them show up so you'll be able to print them. You can do it manually by right clicking each hidden link in excel, and copying/pasting to a new location, but, ASAP Utilities lets you do this in a snap:
- Select the cells that contain hidden links
- In ASAP Utilities go to the Web section and select Extract hyperlinks
- A prompt will show up that lets you select where you want the links to be pasted (the cell itself or the one next to it) as well as if to do this to the selected cells or the entire sheet
- Click OK to do it
Another option in this same area is to convert a bunch of links (that are not clickable) to clickable ones. To do so just select them and go (in ASAP Utilities) to Web and select Activate hyperlinks.
Excel tip #8 - Merge several text files into a single one and import it in Excel
This is useful too. Think that you have 10 different text files with data in them that you want to Import in Excel. You can do it the old way, meaning you import each text file manually, or you can use an ASAP utilities feature that lets you merge them all in one document and import it in Excel. You can access this feature by:
- Going to ASAP Utilities and selecting Import and then Merge files together into a new one
- A new window will show up that lets you select what files to merge (Excel, dBase, Lotus 123, Quattro Pro, CSV, Text files)
- After you've chosen what file types you want merged and imported select where to Import them in (this or a new workbook) and click the Pick files and merge that will do exactly what it says
Excel tip #9 - Conditional select or delete columns or cells
Another excellent option is this one, as it lets you select rows, columns or cells based on matching options. For instance you can select all cells in a range that contain the word "text" or that contain values equal to one you're entering, as well as many other matching options:
- Select the cells/columns/rows that you want to apply this option to
- In ASAP Utilities go to Select and choose Conditional row and column select, hide or delete
- Choose the matching options you want and hit OK
Excel tip #10 - Transpose cells (can contain data or formulas) to another location
This is useful as it lets you transpose vertical cells to horizontal ones (among other possibilities). To do so:
- Select the cells you want to transpose
- In ASAP Utilities go to Sheets and select Range, Transpose data with formulas
- Select where you want to place the data you're transposing
Last but not least there's an utility that lets you display funny error messages. No productivity value, but good to prank a friend working on an important Excel file.
