Sit down and relax while ASAP Utilities does the rest!
Examples of how ASAP Utilities will save you time and make YOU rock in Excel
These examples give you an idea how to solve certain common issues with the use of ASAP Utilities.Here we show you some of the most asked questions we got either by email or found in the newsgroups or online forums that can be (or have been) answered by using ASAP Utilities.
Get the best out of ASAP Utilities with these examples
Frequently we write a new tip/tutorial on our blog and via our Facebook page to show you how to benefit from the tools in ASAP Utilities.Our last 20 tips/examples:
Tip: Quickly clean up your data in ExcelPosted on Thursday January 10, 2019 Save 5 minutes a day by using ASAP Utilities to quickly clean up your data in Excel. "Easily optimize and clean your dataset" One of the first things you often need to do, when you import a file or copy information, is clean it up before you can analyse the data. There are many things […] |
Tip: An easier way to fix the numbers that Excel doesn't recognizePosted on Tuesday August 25, 2015 Save 5 minutes a day by using ASAP Utilities to quickly fix the numbers that Excel may not recognize. "Easily make Excel recognize the numbers in your selection" Sometimes Excel fails to recognize numbers properly, which causes unexpected results when you sort, filter, or use formulas. For example, when you import a file that was […] |
Tip: Quickly remove spaces from the beginning and end of your dataPosted on Wednesday March 26, 2014 Save 5 minutes a day by using ASAP Utilities to quickly remove leading and trailing spaces from your data. "Easily trim all leading and trailing spaces" Sometimes your data contains leading or trailing spaces. Someone may have accidentally added extra space characters, or imported text data from external sources may contain extra spaces. Leading and/or […] |
Tip: Easily COUNT or SUM cells based on their ColorPosted on Friday March 21, 2014 Save 5 minutes a day by using ASAP Utilities to quickly count or sum cells based on their background color. "Did you ever have to SUM by Color or Count by cell Color?" I have a column that needs summed, but only the cells that are green... Working with cell colors can help to make […] |
Tip: Quickly round the numbers in selected cellsPosted on Friday February 21, 2014 Save 5 minutes a day by having ASAP Utilities quickly round the numbers for you. "Easily apply rounding to all numbers in your selection" It is a common practice in Excel to format decimal places to get the desired rounding of numbers. But the problem with this approach is that it can display totals that […] |
Tip: Quickly reset the last cell and minimize the used rangePosted on Thursday February 06, 2014 Save 5 minutes a day by using ASAP Utilities to quickly reset the used range on your worksheet. "Easily reset the last cell on your sheet" Have you ever been frustrated when Excel acts as if the active area of your worksheet is significantly larger than the actual area where you have data? Sometimes the […] |
Tip: Auto numbering on steroidsPosted on Monday November 18, 2013 Save 5 minutes a day by using ASAP Utilities to quickly insert a series of values into your cells. "Easily insert a series of values" Sometimes you need a bit more flexibility than Excel offers with its built-in Fill tool. For example to restart the count when a certain value is reached, add a pre-fix […] |
Tip: Easy ways to combine the contents of multiple cellsPosted on Thursday October 31, 2013 Save 5 minutes a day by using ASAP Utilities to quickly combine the contents of multiple cells into once cell. "Is there a quick way to merge the contents of multiple cells?" We were recently asked: "Does ASAP have a quick alternative for merging the contents of many cells into a single one? Using ampersands […] |
Tip: Quickly fix the trailing minus sign for negative numbersPosted on Tuesday October 15, 2013 Save 5 minutes a day by using ASAP Utilities to quickly fix numbers with a trailing minus sign. "How can I flip the minus sign and make it a real negative number?" We were recently asked: "I have downloaded a report that shows negative numbers as follows: 123.25- How can I make the negative symbol […] |
Tip: Easily keep only the filtered resultsPosted on Thursday September 12, 2013 Save 5 minutes a day by using ASAP Utilities to quickly remove all hidden rows and/or columns. "How can I quickly remove all hidden rows?" We were recently asked: "Does ASAP have a feature to convert filtered results 'in place' to convert a spreadsheet into just the filtered results? I know I can do it […] |
Tip: Quickly select or filter by strikethrough formatted textPosted on Monday August 12, 2013 Save 5 minutes a day by using ASAP Utilities to quickly select or filter by strikethrough formatted cells. "How can I filter for items that have a 'Strikethrough'?" Strikethrough formatting (my struck through text) is often used to mark tasks as being completed or items to be ready for removal. The quickest way to apply […] |
Tip: Easily transpose 1 column into multiple rows, creating a tablePosted on Monday August 05, 2013 Save 5 minutes a day by using ASAP Utilities to quickly transpose your single column into a table. "How to quickly change a long list to a table?" Have you ever had data in a single column that you manually needed to "Copy > Paste Special > Transpose" over and over again to get it […] |
Tip: Easily insert an English formula in your non-English Excel versionPosted on Friday June 28, 2013 Save 5 minutes a day by using ASAP Utilities to insert English formulas you found as examples. "How to use English formula examples in your local Excel version?" On the Internet you can find an enormous amount of useful formulas for Excel. Most of these examples use the "international" formula notation. They use English function […] |
Tip: Easily change the text in selected cells to UPPERCASEPosted on Monday June 17, 2013 Save 5 minutes a day by using ASAP Utilities to quickly change your text to uppercase. "How to quickly capitalize all text" Have you ever had data in a worksheet where all the text was all lowercase? Then you had to retype it all to get it into uppercase? No more! Just select the cells […] |
Tip: Quickly find the tool you needPosted on Friday June 07, 2013 Save 5 minutes a day by using the built in Search-tool to quickly find the tool from ASAP Utilities that helps you. "Easily find the tool that can help you" You can't find the tool that you used previously? Or you don't know if a specific tool exists in ASAP Utilities? Don't worry, our built-in […] |
Tip: Test your model/template in different screen resolutionsPosted on Friday May 31, 2013 Save 5 minutes a day by using ASAP Utilities to easily test how your workbook looks with other display settings. "Easily see how your template/model looks to other users" When you develop a template, you'll have to keep in mind that the information that you can see on screen, isn't always what other users see. […] |
Tip: Quickly re-start the last used toolPosted on Friday May 17, 2013 Save 5 minutes a day by quickly starting the last tool again that you used from ASAP Utilities. "Easily start again the tool you just used" Sometimes you need to use a tool several times in sequence. Instead of having to go through the (sub)menus to find and start the tool again, we've created a […] |
Tip: Quickly copy page layout settings from one sheet to other sheetsPosted on Friday May 10, 2013 Save 5 minutes a day by using ASAP Utilities to easily copy your worksheet's page setup settings to your other worksheets. "Easily copy a sheet's page settings to your other worksheets" When you add a new sheet to your workbook then Excel assigns the default page setup settings. In Excel there is no button to […] |
Tip: Quickly close multiple workbooks at oncePosted on Thursday May 02, 2013 Save 5 minutes a day by using ASAP Utilities to easily close multiple workbooks at once. "Close multiple or all files at once instead of just one at a time" I often have multiple files open in Excel. I hate having to close each file separately. Is there an easier way? Yes there is, just […] |
Tip: Sort your data on anything that you can think ofPosted on Tuesday April 23, 2013 Save 5 minutes a day by using ASAP Utilities to sort your cells on bold, italic, color, font, etc., etc.. "Sort by bold, italic, color, font, month, etc., etc." You can easily sort your data on all kinds of characteristics with: ASAP Utilities » Range » Advanced sorting... With this utility you can sort by: […] |
More detailed articles
More examples are available on our blogA few more simplified examples:
Most of the below questions/answers are also described in chapter 7 of the User Guide which can be downloaded in PDF format.Delete only the even rows in your spreadsheet
Is there a way I can delete only the even rows in my spreadsheet? I have over 6000 rows and I only need the odd ones. In the form choose "even rows" then "select". When selected right-click on a cell in your selection and choose "Delete..." and select "Entire row"Automatically alternate row colors
How can I set up alternating row colors in Excel? I don't want to have to change the row colors every time I insert, delete, or move a row.Create a hyperlink to each sheet
Copy a long list of file names
I have a long list of file names (240 from a search) that I need to put into Excel. Copy won't do it. How now?Extract number
How can I get the numeric value from the cell: "$4.57 dividend"? Then select all except the numbers and point.Removing spaces in cells with data in it
We have a personnel roster that is kept in a database. Occasionally, this roster is exported into Excel and sent out to members of the department. In one column are dates that each person was employed. When I try to sort the entire roster, it doesn't come up right because most of the cells that contain the date have spaces in front of it.Sometimes it's 1 or 2, sometime more, other times there are no spaces.
In order for me to sort the entire roster correctly, I'm having to manually remove the spaces in front of the date, then sort the roster according to date.
There are over 500 entries in this roster, is there a better way to remove the spaces other than manually?
Extracting Text only
I'm trying to extract text only from a column of cells with varying text and number strings. Sometimes the cell starts with numbers, other times text. There is no constant for the number at the beginning of the cells. or Then select all except the lettersStrip leading spaces from cell
I need a formula to remove/delete leading spaces from a cell. Column b contains data imported from another application. Unfortunately some of these cells have padded leading blanks added. I need to remove these, but 3000+ cells individually will take hours. ASAP Utilities " Text " Delete leading and trailing spaces. (note: this will also delete trailing spaces in each cell).How to make duplicate entries blank
I want to make a column that lists "city name" to only show each name once, and then leave each repeat of the name blank. Is there a formula for this?Printing a Column in Multiple Segments on One Page
I have a list of names in a spreadsheet column. The data takes up, say, ¼ of the page horizontally. Let's say I have 200 names, and want to print them in columns of 50. In other words, they should all be able to fit on one page - 1-50 in column 1, etc. I can cut and paste, but that's rather labor intensive. I can print 4-up, but that reduces the font size so that it's practically invisible.Is there anything else I can do?
Trailing negatives imported from ASCII file
How do I change a column of trailing negatives for example 123- to -123?Deleting empty rows
Is there an easy (quick) way of deleting empty rows in a large spreadsheet?Delete rows if Col C has a 0 value in it
What formula please will delete rows if Col C has a 0 value in the cell?Inserting rows
I have a spreadsheet that is approx. 200 rows long and I need to insert a new row after each existing row.White Space in a Cell
I'm collating a lot of spreadsheets that many other people have filled in onto one big spreadsheet template.My problem is that there is a free text cell and, because people don't know about the 'alt & enter' option, they've hit the space bar many many times make it look like a new point starts on a new line.
In Word, you have the option of showing all the keyboard strokes so you can tell what people have done - but I'm finding I have to go into each free text cell and hit delete, then the cell magically shifts up so that there is no white space.
Any ideas? My document is currently 200 pages long and I'm nowhere near done!
How do I conditionally delete rows based on cell contents?
Coloring every other row in a range
Removing Carriage Returns in Excel
To remove only trailing carriage returns: To remove all carriage returns, chr(10) and chr(13): Then select "line breaks".How to shade every third row?
Choose a color and set the steps to 3.How can I translate a comment to text in a cell?
You can use our User Defined Function (UDF) =ASAPGetComment() to get the comment out of a cell.How to convert Excel imported numbers from text to numbers?
We have a software package from which we can export data to MS Excel. However, when exported it arrives in Excel as text. Even though they are numbers, they act like text. The only way to convert them text to numbers is to edit the cell (press F2) and hit enter. I've tried every other method of copying and pasting (even pasting special), but nothing seems to work.Sorting Excel worksheets by worksheet title
Beginning zeros won't hold in field when saving as .CSV
When saving an Excel spreadsheet as a .CSV (comma delimited) file, beginning zeros disappear. The only way I know to keep the zeros is to format the field as 'text.' However, .CSV files wipe out all formatting. Is there a way to KEEP the zero in the number '08' instead of '8' when saving as .CSV? In the "How to export" select "As formatted", and in the "Format to export" select CSV.Print multiple sheets in one print job
I want to print several worksheets (e.g. sheet1, sheet 4, and sheet5) in one print job. This is a frequent task for users of one spreadsheet and would be much more convenient for them. I could probably write a macro to print each sheet individually, but it would be nice to print all at one time and have the printer staple them together.Reverse the sign in a range of cells?
I have a spreadsheet that is generated from an accounting program. It generates the debits as positive numbers and the credits as negative numbers. Is there a way to highlight a group of cells and reverse the sign on that group or range? There a few ways to do this. You can either multiply all values with -1: orEnding spaces need to be removed
I have exported an SQL database table to a text file and need to do some work on it in Excel. Each field is comma-delimited. However, the table must have used fixed field lengths because the data now has blank spaces at the end of every field. I want to remove the extra spaces. (note: this will also delete leading spaces each cell).Auto-naming of Sheets?
I have a list of numbers all 8 digits in length (roughly 200 numbers). I need a separate worksheet for each number. Is there a way of taking each number, inserting a worksheet and naming it according to the number? Select the cells with the numbers and then:Printing question on a long list
I have a fairly long excel sheet it's about 800-something rows but there aren't too many columns. How can I make it wrap up and print what would normally be 2 pages on to one page? The printer settings aren't working right and I'm getting it to be terribly small when I try and change those to fit. Do any of you know how i could do this? I'm pretty sure this is a really simple task and I just have never had to use excel for anything this long before or cared about manipulating it so that it could be done again.Mass Remove Hyperlinks
I want to remove 1200 Hyperlinks, is it possible to do it mass?Automate naming of tabs
In Excel 2002 XP I have a workbook with 13 spreadsheets. I need to automate the naming of the tabs on each spreadsheet so that each spreadsheet assumes the name of the contents of cell P1 for that respective spreadsheet. For instance, if the contents of cell P1 are 1999, the tab needs to be 1999. If the contents of cell P1 are changed to 2000, I need the tab to be 2000.Leading zero conversion problem
I have exported data from Outlook 2002 Contacts into an Excel 97 file. In the export process, all the leading zeros in the zip codes have become "'". For example, a Contacts zip code of 01075 has become '1075 in Excel. I tried using Excel "Find and Replace" to replace "'" with "0", but Excel can't find the "'" in the spreadsheet, although it shows in the cells.You can either use the number format "00000" to have the values displayed with leading zeros, or use ASAP Utilities to add the zeros:
Protecting multiple worksheets easily
Is there a way to protect worksheets without having to do each one individually?Sorting Worksheet Tabs
I really need to sort multiple worksheet tabs within a workbook. I would like to sort them numerically.Newspaper Columns
I have a long list of data consisting of three narrow columns. When I print the sheet, I'd like to repeat the set of three columns a few times per page (exactly like a telephone book) to prevent wasting paper.Changing range of cells to absolute references?
I am trying to figure out a way to change a table of formulas with multiple cell references to absolute cell references. Does anyone know how to do this without going into each cell and editing each one manually?Mainframe download to Excel has minus sign at the end of the number
The report has positive and negative numbers in columns. The positive amounts do not have a sign and are recognized in Excel as number format. The negative amounts have a "-" sign at the end of the number. Excel recognizes these as text format. Is there an easy way to reformat all the amounts with a negative sign to be recognized as number format in Excel?Unhide hidden Sheets
Is there a way to unhide multiple sheets at one time without code?Making Cells Absolute
I have a spreadsheet that has 75 columns with data that is from two other sheets. Stupid me when I did the formula I did not make it absolute, so now when I sort it the formulas change. Is there anyway I can make the formulas absolute without going into every cell and changing?Save selection as jpeg
I have just bought a mobile phone which allows me to view jpeg images. I would like to carry around some information that I have on Excel sheets. Can I convert a sheet or a selection on a sheet into a jpeg image? Usually when you don't have too many colors GIF gives a much better quality.Password protecting multiple sheets
Is there a way to set a password for multiple sheets without having to set each one separately? I have over 60 sheets in one of my workbooks. I don't want to protect the whole workbook with a password because the file is used by other users but rather lock cells in each of the sheets with a password thereby allowing users to only edit certain cells. Because I have many packages to do, I don't want to be setting these passwords for over 600 sheets.Reversing a cell
I need a function that will reverse the data in a cell. For example if the value in a cell A1 is "xyzzy", then REV(A1) would be yzzyx. If the value in cell Z19 is "32767" then REV(Z19) would be 76723.Edit Data - add *1000 to multiple cells, possible?
I have a set of data and I want to multiply each cell by 1000 so want to add *1000 in about 30-40 cells together. Is there any way to do it quickly rather than going into each cell manually and entering *1000? ASAP Utilities " Formula " Apply formula to selected cellsHow do you count the number of sheets in a workbook
The above examples are only a short list of the things that can be done with ASAP Utilities!
© 1999-2020 - Bastien Mensink - ASAP Utilities - A Must in Every Office BV - The Netherlands
Connect with us and receive our tips that show you how to benefit from the tools in ASAP Utilities at:
