User question: How to enter the same value on multiple sheets (or in multiple cells at once)

Press control+enter to enter the same value in all selected cells

Today we received the following email: Let me start by saying how wonderful I think your program is. It saves me a lot of time. I just created 100 sheets and named them from a list using ASAP -great! I then inserted the name of each sheet into a cell within the sheet using ASAP […]

How to: Change the shortcuts used by ASAP Utilities

Every now and then people ask us if it is possible to change the shortcuts that are used by ASAP Utilities. For example: "Hello, is it possible to change the Ctrl + Alt + J shortcut to something else?" Often because people already use the same shortcut for their own macro. Well, you can already […]

How to: Select a number of worksheets and export each as a single workbook

Save worksheets as separate workbooks

I just received the following question from Azad from Australia: Hi, Is it possible to select a number of worksheets and export as a single workbook? As you probably already know, Excel itself doesn't have a option for this. It can be a time consuming job to save each worksheet as a separate workbook. Fortunately […]

How to use the extra functions you get with ASAP Utilities

We have created a example workbook that shows how to get things done - that are normally not possible at all or difficult in Excel - by using the functions that ASAP Utilities adds. Especially the function to calculate the SUM of cells with a certain color is a popular request and still can't be […]

Video: An easy way to split each group into a separate worksheet tab with ASAP Utilities

In addition to my previous post, a short video that shows a demonstration of the new "Split the selected range into multiple worksheets..." utility. The video is in HD quality and for best quality I recommend to watch it on full screen: The above video is without sound. For me this is also a test […]

An easy way to split sheets

Split your selected range into multiple sheets

In the new version of ASAP Utilities (4.6.4), which is available as a pre-release, we have included a tool to easily splits your data into separate sheets. Sheets » Split the selected range into multiple worksheets... With this tool you can quickly split your data into multiple sheets. You can do this either by value, […]

How to: Set column width and row height in centimeters or inches?

Set the rowheight and columnwidht in centimers or inches

In standard Excel you can't set the height or with of cells in centimeters or inches. Creating square cells is also difficult because Excel uses different units for the width and height of cells. Setting the width and height in centimeters or inches can be helpful if you use the Excel for drawing purposes, such […]

How to: Quickly remove duplicated records

Remove duplicated records

At this moment ASAP Utilities already contains several tools to manage duplicates. However a special tool for duplicate records was still missing... until now! You could already identify or remove duplicated records with a workaround by merging the cell contents into a temporary added column and then use ASAP Utilities' "Conditional row and column select, […]

How to: Remove or replace line breaks and carrage returns in cells

There are few quick methods to remove the line breaks and carriage returns in cells. By default you might not know an easy and quick way to do this but it is fairly simple, either with just Excel, or with the help of ASAP Utilities. Let's say we have the following example. In this case […]

How to: Create a pipe delimited file

Create a pipe-delimited text file

Last month I received the following question: I need to know how to convert an excel file into a pipe delimited file using the ASAP software. You can use the following tool to do this: ASAP Utilities » Export » Export selection or active sheet as new file... Then choose "CSV (comma separated value) or […]

How to: Insert a blank row after every 3rd row

initial-dataset

I just received the following question which might be interesting the share with you. ASAP Utilities contains many tools and one of the strengths is that you can often use a combination of the tools to accomplish what you need. The question I received this week: I want to insert a blank row after every […]

Remove all empty rows – within your selection

remove all empty rows

Last week I received the following question: Bastien, First, thank you so much for the best Excel add-on ever! Is there a way to remove empty rows in a only a selected section of the worksheet, not the whole worksheet? Or have I missed this option that is already included? Surely you can do this! […]

Macro: Keep only ASCII characters 32-127

Last week I received the following question: I get sent Excel files from various vendors. I have to convert these to SAS datasets that get moved to a UNIX system. The SAS datasets can only contain ASCII 32-127. So I need to remove these from the Excel file. You can use the "Text » Advanced […]

How to: Remove all negative numbers

Select the negative numbers

Last week I received the following question: Is there an ASAP Utilities function that can remove all negative numbers from a spreadsheet? I haven't found it yet. Yes there is. You can use ASAP Utilities to help you to quickly delete negative numbers. You can use the following tool to select all cells with negative […]

Identify duplicates

I just received the following question: I will like to know how I can see the duplicates in a selection data? I'm aware I can delete duplicates, however I will like to know how to identify them first before deleted. Here's how to do this: You can use the following tool to select the cells […]

Video: How to move or resize the selected range in Excel with ASAP Utilities

0003-1-move-or-resize-selected-range

The following video will show you how to how to move or resize the selected range in Excel by using ASAP Utilities. With this utility you can easily move or resize your selected range of cells. This utility will only change you selected range of cells only, not the data underneath it. By default you […]

How to: Quickly find the utility you need

Run and find a utility

With the new version 4.3 of ASAP Utilities it will be easier to find the utility you need with the tool "Find and run a utility...". We have improved this already existing utility. The results are presented better and you can now immediately start/run the utility from the search results. If you click on the […]

How to: Remove all spaces, dashes and other garbage from numbers

I got a question from Ryan: I have a bunch of excel cells and I need them all to have no spaces and no dashes so the first one would be: 185444001 and so on. How can I use ASAP Utilities to remove all spaces and dashes? You can do this very quick with the […]

How to: Delete blank lines in a cell with multiple lines of text?

Example of cells with linebreaks

I'm sure you know you can enter a new line/line break by pressing Alt+Enter. But working with these newline characters can sometimes be a bit difficult, for example if you want to remove or replace them. If you have cells with multiple empty lines it can be difficult to quickly replace them all. Method 1: […]

How to: Remove all blank rows at once

If you have a lot of data it can be difficult to identify and remove the empty rows. There is no way in standard Excel to quickly remove all empty rows* so it can be a time-consuming job. You need to use a macro for that. Empty rows in your table can cause problems with […]

When Excel doesn't recognize empty cells correctly

Enno asked me the following: "... When I value-copy ranges where the If-function was used with a return value of "" (empty string), the cells are not blank, hence ISBLANK() = FALSE. Could you add a feature to remove empty strings? ..." Well, you can do this already with ASAP Utilities, although you cannot find […]

Unhide all columns and rows

A short macro that will unhide all columns and rows in order to make all data visible again. Might be useful in some cases. Sub UnhideAllColumnsRows() ' unhide all columns and rows on the current worksheet Cells.EntireColumn.Hidden = False Cells.EntireRow.Hidden = False End Sub If you want more control on which rows and/or columns to […]

Count the occurrences of a character in a cell

count-character-in-string.gif

Just got a question that I found interesting to mention here: I am looking for following feature in ASAP Utilities: Count number of user specified characters in a cell. e.g. I have a cell that has values "a,b,c,d,e". I would like to have a utility that tells me either a. there are 4 "," characters […]

Video: How to fix trailing minus signs in Excel with ASAP Utilities

Numbers with a trailing minus sign

The following video will show you how to how to fix trailing minus signs in Excel by using ASAP Utilities. This can be useful if you have imported data from a system that reports negative numbers with a trailing minus. If you've ever worked with imported numbers you might have faced the problem that negative numbers […]

Video: How to delete all empty rows in Excel with ASAP Utilities

Example file with empty rows that need to be removed

The following video will show you how to remove all empty rows in Excel by using ASAP Utilities. This can be useful if you have a large amount of data imported or copied together that you now want to clean up. An approach to remove all empty rows can be to sort the worksheet. However if […]

Page 3 of 41234