ASAP Utilities for Excel

​By Kristin McKinley, Director of Research Administration, Lawrence University

ASAP Utilities is an add-in for Excel that offers over 300 time-saving features. The following tips are a few I use regularly in my work, but I would encourage you to explore the ASAP Utilities website and determine which features would be most useful in your work. It is possible that execution time may vary. Depending on your needs, the ribbon can be customized and shortcuts can be assigned to further enhance your experience. The tools are organized into categories, as shown below in the ribbon.  

TTNov117.PNG

Numbers & Dates Tip: A quick and easy way to number selected cells

I know this can be done in Excel, but I find it more efficient to accomplish using this feature. I use this when preparing panels for Qualtrics, as it is a quick and easy way to assign a number to each panel member.

  1. Select the range of cells you wish to quickly number.

  2. Select Number & Dates in the ribbon.

  3. Select Quick numbering of selected cells (#13). You will then have the ability to select the number you want to start with, the number you want to increment with, and if you want to restart counting after a particular number. Select OK.  The range of cells selected in step 1 will be numbered as specified in series settings.

    TTnov317b.PNG

Format Tip: A speedy way to count duplicates and have each appear in their own color 

Excel can remove duplicates, but it simply finds and deletes duplicates after the first, which is not always ideal depending on the task at hand. While duplicates can be identified in Excel using conditional formatting, they are formatted in the same color which is not always helpful. ASAP Utilities formats each identifier in a different color making it easier to find the duplicates. Note, for this example I did not use student identifiers for obvious reasons.

  1. Highlight the desired range of cells (A1-D10 in the example below).

    TTNov317.PNG
  2. Select Format in the ribbon.

  3. Select Count duplicates and give each set of duplicates its own color (#22) and select OK. Note, each duplicate is assigned its own color.

    TTNov817.PNG

Another Format Tip: A fast way to copy a worksheet’s page and print settings

This comes in handy when an Excel file contains multiple worksheets. For example, I often place survey results in Excel and depending on the length of the survey or analysis being conducted the file may contain multiple worksheets. Rather than formatting each worksheet, I can do it all at once. 

  1. Select Format in the ribbon. 

  2. Select Copy a sheet’s page and print settings (#1). Specify the source worksheet to read from and which worksheets you want to copy to. Specify the settings you want to copy. Select OK.

    TTnov517.PNG

I recognize the remaining tips may not have equal value to the ones above, but I still find them useful.

Columns & Rows Tip: A fast way to insert multiple empty rows at once

I discovered this is very useful for adding in rows after the fact or when I desire equal spacing between data and/or graphs in a given worksheet.

  1. Select the row where you would like to insert multiple empty rows (A4 in the example below).

    TTNov617.PNG
     
  2. Select Columns & Rows in the ribbon.

  3. Select Insert multiple empty rows at once (#9). Determine how many rows you wish to insert (in the example below I desired five).

    TTNov717.PNG
  4. Select OK and the empty rows will be inserted automatically (see A4-A8 below). 
    TTNov817b.PNG

Another Column & Rows Tip: An efficient way to delete all empty rows and/or columns at once

This is an easy way to quickly remove all unnecessary rows and/or columns in a worksheet, especially if it is not as “clean” as you would like.  

  1.  Begin by highlighting the entire worksheet.

    TTNov917.PNG
  2. Select Columns & Rows in the ribbon.

  3. Select Delete all empty rows (#12) or Delete all empty columns (#13). You can only perform one command at a time. A message will appear asking if you want to remove all completely empty rows or columns on your worksheet to confirm your decision. Select OK and the following appears (depending on which you deleted).

a. Deleting empty rows:

TTNov1017.PNG
 

b. Deleting empty columns:

TTNov1117.PNG

Text Tip: An easy way to change text from one format to another

I find this feature useful when I want to change text from its current format to another, especially when working with student or course names.

  1. Highlight range of cells (A1-16 in the example below).   

    TTNov1217.PNG

  2. Select Text in the ribbon.

  3. Select Change to lowercase (#3). 

    TTNov1317.PNG

  4. Select Change to UPPERcase (#2) to revert back.

  5. You can also select Make the first character UPPERcase, the rest lowercase (#5) or Start each word with UPPERcase (#6). See examples that follow for end product.

TTNov1417.PNG

Interested in trying or buying ASAP Utilities?

ASAP Utilities has a free version, trial version, and license for purchase. The “Home and Student” edition is free for home projects and schoolwork. This edition is also free to non-profit organizations. There is a free 90-day trial version that can be downloaded. A “Business” edition license can be purchased by companies for $49, which includes priority support, and supports the following Excel versions:

Excel 2016 (32-bit and 64-bit edition), Excel 2013 (32-bit and 64-bit edition), Excel 2010 (32-bit and 64-bit edition), Excel 365 (32-bit and 64-bit edition), Excel 2007, Excel 2003, Excel 2002/XP, Excel 2000

I purchased the business license and find it well worth the cost due to the time saving features. ASAP Utilities will generate a report showing the tools used, how often you used them, and how much time and money you saved. In the Excel menu select ASAP Utilities, ASAP Utilities Options, and Show Me the Time I Saved by Using ASAP Utilities).

 

 

 Comments

 
To add a comment, Sign In
There are no comments.