Subscribe: asap utilities / excel blog
http://www.asap-utilities.com/blog/index.php/feed/atom/
Added By: Feedage Forager Feedage Grade B rated
Language: English
Tags:
asap utilities  asap  cells  data  easily  excel  minutes day  numbers  quickly  save minutes  save  time  tool  utilities 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: asap utilities / excel blog

ASAP Utilities for Excel – Blog



“Working in Excel will be fun again with all these extra supersmart tools”



Updated: 2016-05-21T07:38:11Z

 



Tip: An easier way to fix the numbers that Excel doesn't recognize

2015-08-26T12:06:53Z

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 […]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 created in another program, such as an accounting package, downloaded from a mainframe or copied from a website, then Excel may fail to recognize the numbers. Some Excel values may then look like numbers, but Excel thinks they are text. These numbers are then usually left-aligned (by default, text is left-aligned and numbers are right-aligned in Excel). You sometimes may experience that: A formula isn't calculating your numbers properly, such as the SUM of cells, because the 'text'-number are ignored. Excel does not allow you to change the number format of selected cells. The sort order is confusing and incorrect. You have fractions in your cells that Excel doesn't recognize and can't calculate, such as 1/4. Excel's built in error checking option and correction isn't available or does not fix the numbers. But this is easy to fix. Just select the cells and use: ASAP Utilities » Numbers & Dates » Convert unrecognized numbers (text?) to numbers This "Convert unrecognized numbers (text?) to numbers" tool quickly fixes the numbers in your selection. It will: Remove leading and trailing spaces around cells with numbers. Remove apostrophes in front of numbers. If the number format in the cells with numbers is "Text" then it will be changed to "General" in these cells. Handle non breaking spaces (this so called character, char 160, is commonly used in web pages as the HTML entity,  ). Remove leading zeros. Recognize and fix text-numbers that are displayed as fractions (such as 1/4, 1/2, etc.) Preserve long numbers such as credit cards. Unlike Excel, it will not change the last digit to a zero such as from credit card numbers, which is what Excel's built-in "Convert Text to Number" does. Empty cells that only contain one or more spaces. Turn all empty cells into truly empty/blank cells that Excel recognizes as being empty. Skip cells with formulas or text. Tip: If your formulas don't give the correct result or don't calculate, then in addition to fixing the numbers, you may also want to verify that the calculation of your workbook is set to Automatic (or press F9 to calculate when calculation is set to Manual): ASAP Utilities compared to Excel's built in "Convert to number" Excel (2002+) has built in "Error Checking Options", which can help to detect numbers stored as text and help sometimes to fix them. Numbers that are formatted as text are left-aligned instead of right-aligned in the cell, and are often marked with an error indicator. If the cells in which numbers are displayed as text contain an error indicator in the upper-left corner (a small green triangle), you can select that cell and then click the error button next to the cell. Then you can choose "Convert to Number" on the pop-up menu. While this is a handy tool, it doesn't always detect all numbers that are stored as text properly and you cannot choose your own range to run Microsoft Excel's built-in "Convert to Number" tool on. When compared to Excel's built-in solution, these are the benefits of ASAP Utilities: You decide which cells to convert and when. It even works in multiple non-adjacent selected ranges. You can assign your own shortcut to this tool for quick use. It fixes numbers that Excel can't. Do you recognize any of these situations? I copied some numbers on a web page and pasted them onto an excel spreadsheet. When I use the sum formula, or average formula, excel is n[...]



Tip: Quickly remove spaces from the beginning and end of your data

2014-10-08T13:59:30Z

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 […]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 trailing spaces can cause unexpected results when you sort, filter, or use formulas. But it is easy to fix, just select the cells and use: ASAP Utilities » Text » Delete leading and trailing spaces This will: Remove leading and trailing spaces. Also handle non breaking spaces (this so called character, char 160, is commonly used in Web pages as the HTML entity,  ). Empty cells that only contain one or more spaces. Turn all empty cells into truly empty cells that Excel recognizes. Do you recognize any of these situations? I need to delete a leading space from multiple cells, 800 or more. The leading space is in front of text that is often more than one word so I can't just do a find and replace on the spaces. How can I remove spaces to the left and right of a text string only, and preserve the spaces inside the text string? Excel's =TRIM() function reduces the spaces inside the text to single spaces. I have some cells containing imported text with a lot of leading spaces. I try to get rid of them by using the =TRIM() function on those cells, but it doesn't work. My workbook contains data imported from another application and unfortunately many of these cells have padded leading blanks added. I need to remove these, but 4000+ cells individually will take hours. I have difficulty sorting my data due to leading spaces. Manually removing these spaces takes hours. Is there some magic trick for this? Your =VLOOKUP() formulas can't find any matching values because of unwanted spaces. You were sent a list with addresses for a mailing but it looks bad with all extra spaces. Cells that appear empty aren't because they contain a space. Excel doesn't recognize some cells as being empty. That happens often with imported data. You try to remove the spaces by using Excel's =TRIM() function but that doesn't remove the spaces. That can happen with so called "non-breaking" spaces. See also this article from Microsoft that describes how to remove these unwanted spaces. Duplicate values aren't removed because of leading and/or trailing spaces in some values. Just select the cells and then choose the following tool in the Excel menu: ASAP Utilities » Text » Delete leading and trailing spaces This is often easier and faster than Microsoft's advised method and using the =TRIM() function. Bonus tips, also interesting ASAP Utilities » Format » Clean data and formatting... ASAP Utilities Tip: Easily remove leading characters ASAP Utilities Tip: Quickly remove the leading zeros from numbers ASAP Utilities Tip: Easily remove certain or several characters at once ASAP Utilities Tip: Select cells automatically based on their contents, formatting, etc. ASAP Utilities Tip: Easily count and/or color duplicate values ASAP Utilities Tip: Get rid of unwanted spaces in your data ASAP Utilities Tip: Quickly (un)protect all sheets at once How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by c[...]



Tip: Easily COUNT or SUM cells based on their Color

2014-10-08T19:41:29Z

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 […]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 your Excel workbook easier to understand. Many people also use it to mark cells. Unfortunately Excel has no built in functions to use cell colors as conditions in formulas. Constructing formulas that only count or sum cells with certain colors can become really cumbersome. It often results in complex formulas that are prone to errors when changes are made. Fortunately with ASAP Utilities it is easy to do. Please see below example: You can COUNT or SUM the cells with a certain color with the following functions: =ASAPCOUNTBYCELLCOLOR(SearchRange, Color) Counts the number of cells in the given range that have a certain fill color. =ASAPSUMBYCELLCOLOR(SearchRange, Color) Sum the values in the cells that have a certain fill color. The following page shows a list of all the functions that are available in ASAP Utilities: ASAP Utilities » Formulas » Insert function from the ASAP Utilities library... Remarks on calculation Since Excel only recalculates a formula if the value in a (referenced) cell changes and not when the colors are changed, some of the ASAP Utilities worksheet formulas don't always get triggered to update. In that case you can use Excel's shortcuts F9 or Control+Alt+F9 to have the ASAP Utilities formula(s) updated. Remarks on distributing If you use these worksheet functions in your worksheet there a few considerations to make if other people are going to work on the same workbook: Everybody that works with the file using the ASAP Utilities worksheet functions should have ASAP Utilities installed. If somebody is going to work with your file but he/she doesn't have ASAP Utilities, then you should convert the formula-results to their values (Copy » Paste Special » Values) or in ASAP Utilities: ASAP Utilities » Formulas » Change formulas to their calculated values If you see the result of a function displayed as "#NAME?" this means that ASAP Utilities is not available or installed in another folder. (In case you do have ASAP Utilities installed it can also mean that there is a typing-error in the formula name). Do you recognize any of these situations? Not sure if ASAP has this feature, but it would be useful to be able to count colored cells in a range. Say I have 17 pink colored cells in range B1:B500. I would like formula that would count these 5 pink cells, even if they have no text or data in them. Can a color be used as a condition of SUMIF? I have a column that needs summed, but only the cells that are yellow. I noticed that in recent Excel versions it is possible to sort by cell and font colours. What about Excel function? Can the formulas work on cell and font colours too? How can I count or sum cells by their fill or background color? There seems to be no formula in Excel that allows to count or sum a range of cells based on a specified background/shading color. Several of my colleagues have been tracking their work by manually coloring the cells based on who did the particular task (ex. John colors the cells he's worked on blue, Suzan colors hers red, Daniel colors his yellow). I need to get a total of each color. I'm looking for something automated so we don't have to sort by color every time to get a total. The issue seems to be that they're manually coloring the cells, so there's no criteria to base a sum off of, other than the colors. Just use one of the following functions: =ASAPCOUNTBYCELLCOLOR(SearchRange, Color) Counts the number of cells in the given range that h[...]



Tip: Quickly round the numbers in selected cells

2014-10-08T19:39:40Z

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 […]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 don't appear to add up correctly. For example, in the below screenshot it shows 2+2+2=7. That is because the underlying numbers are actually 2.44+2.46+1.42 The solution here is to use Excel's rounding functions. A common approach is to use an extra 'helper' column to show the rounded results by using the =ROUND() function. However, with ASAP Utilities it is also easy to round the number 'in place', in the cells themselves. You can use one of the following tools to quickly round the numbers: ASAP Utilities » Text » Insert before and/or after each cell in your selection... This utility adds a value in front of and/or at the end of the content of each cell in your selection. You can use this to wrap a formula around the contents of the cells. The benefit of this is that you can still see the actual numbers. ASAP Utilities » Numbers & Dates » Round numbers (not formatting, but changing)... Instead of rounding the number by using cell formatting this tool will actually replace the value in the cells with the rounded value.Round the selected numbers to 2 decimals: Round the selected numbers to-the-nearest integer (zero decimal places): Round the selected numbers to the nearest multiple of 100: Do you recognize any of these situations? How do I apply a rounding formula to cells with different formulas easily? I have a large spreadsheet that has different formulas in various cells. I have figured out how to round to two decimals as I wanted, but I need to apply that formula to all the other cells that have various formulas, without manually doing it for each cell. I have the amounts in column A. I know how to use the ROUND() function in B to get rounded result. However, is there a way to effectively format A1 to round the result of the formula it contains so I'm making use of one cell instead of two? I can't just use the cell-formatting because formatting to two decimals just rounds the number for viewing; underlying number remains the same. The reason I need this is because I have a column of numbers which, when summed, have a result which appears to be different (usually just a cent) from the sum of the numbers as they are displayed. Want to get rid of this 'rounding error' by actually rounding the numbers before summing them and if possible without an extra 'helper' column. I need some help with a spreadsheet I created to show me the net cost and vat of supplies. The trouble is that totals are out by .01 dollar. After a lot of searching I found that by setting 'Set Precision as displayed' the totals were correct but I received a warning that data will permanently lose accuracy. So I'm not sure when should I be using this option and is this the correct approach to solve this rounding issue? Just select the cells and use the following tool: ASAP Utilities » Text » Insert before and/or after each cell in your selection... and wrap around the needed function. or use ASAP Utilities » Numbers & Dates » Round numbers (not formatting, but changing)... This is often easier than manually editing the formulas and it reduces the need for helper columns. If you want to use a macro we've shared to do this. Bonus tips, also interesting ASAP Utilities Tip: Quickly add a prefix or suffix to your data ASAP Utilities Tip: Quickly perform a calculation on all selected cells at once ASAP Utilities » Numbers & Dates » Convert u[...]



Tip: Quickly reset the last cell and minimize the used range

2016-05-21T07:38:11Z

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 […]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 last cell of an Excel sheet may be far beyond the range of your actual used data. This can cause you to be unable to easily locate the end or your data-set, have a larger file size than necessary, printing can result in extra (empty) pages, or you may even receive "Out of Memory" error messages. Resetting the last cell can help to resolve these issues. You can test if this is true by pressing Control+End and Excel takes you to a blank cell below or to the right of the actual data. In such a case, you can use one of the following tools to fix it: ASAP Utilities » Sheets » Reset Excel's last cell (Control+Alt+R) or the following tool which is more thorough and works on all selected sheets, but can be a bit slower. ASAP Utilities » Sheets » Delete unused empty ending rows/columns (on selected sheets) I'm a huge fan of keyboard shortcuts, and whenever Control+End doesn't take me to the actual last cell on the sheet I'm working on, I just press Control+Alt+R to reset the used range with this tool. And then Control+End works as expected again. Do you recognize any of these situations? I like to use the shortcuts Control+Home and Control+End to quickly move to the top or end of my worksheets. In one sheet, the end point is for reasons unknown several thousand rows below the actual last row. How can I change the end point so that the Control+End command will take me to where I want to go? My job involves adding data to the end of a very large Excel worksheet. To get to the last cell to begin data entry, I use the shortcut Control+End. However, Excel takes me to a blank cell way below and right of the actual data. And then I have to scroll back to get to the last cell containing data, which is something I tried to avoid by using the shortcut keys. How can I fix that? My Excel file size is too big. I noticed something odd. When I hit control-end, it takes me to the bottom of the sheet (row 65536), instead of row 457 which is the actual end of my data. Why is that? Just use the following tool: ASAP Utilities » Sheets » Reset Excel's last cell (Control+Alt+R) or if the "Reset Excel's last cell" tool can't reset the last cell, then you can use the following tool which is more thorough and works on all selected sheets at once, but it can be a bit slower. ASAP Utilities » Sheets » Delete unused empty ending rows/columns (on selected sheets) This is often easier than Microsoft's advised method. The Contextures website demonstrates how you can do it manually or with a macro. Bonus tips, also interesting ASAP Utilities Tip: Quickly extend your selection down to the end without scrolling ASAP Utilities » Format » Clean data and formatting... ASAP Utilities » Sheets » Remove all page breaks on the selected worksheets ASAP Utilities » Sheets » Remove all unused styles on every sheet ASAP Utilities Tip: Remove all hidden rows and/or columns ASAP Utilities Tip: Quickly (un)protect all sheets at once How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&S[...]



New ASAP Utilities 5.1 just released!

2014-01-29T14:41:41Z

(image) The new and improved version of ASAP Utilities has just been released! Based on the feedback we received, we've added many improvements to make your work in Excel easier, faster and more fun. Newsletter, see what's new http://www.asap-utilities.com/newsletter-2014-01.php Download the new version 5.1 You can download this new version from: http://www.asap-utilities.com/download.php If you have a […]

The new and improved version of ASAP Utilities has just been released!
Based on the feedback we received, we've added many improvements to make your work in Excel easier, faster and more fun.

(image)

Newsletter, see what's new

http://www.asap-utilities.com/newsletter-2014-01.php

Download the new version 5.1

You can download this new version from:
http://www.asap-utilities.com/download.php

If you have a license then you can download this new version of ASAP Utilities from:
http://www.asap-utilities.com/download-latest-licensed.php

Upgrade

To upgrade, just install the new version 'on top' of the current version on your computer.

Weekly tip, discover new tools

Almost every week, we write a new tip on our website to show you how to benefit from the tools in ASAP Utilities.
You can find the latest and previous tips at:
http://www.asap-utilities.com/blog/index.php/category/how-to/
http://www.asap-utilities.com/asap-excel-tutorial-how-to.php

My thanks go out to each and every one of you for your feedback the past 15(!) years.
ASAP Utilities continues to grow and improve thanks to your support, suggestions and encouragement.

Thank you for using ASAP Utilities.

Kind regards,
Bastien Mensink




Latest pre-release new version 5.1 now available

2013-12-17T14:35:29Z

(image) We're happy to announce that the latest pre-release of the new version 5.1 of ASAP Utilities is now available. Many improvements have been made. If you use Excel 2013 or 2010 you will also see speed improvements when editing data formatted as Tables. We tested this pre-release thoroughly and have now made it available to you […]

We're happy to announce that the latest pre-release of the new version 5.1 of ASAP Utilities is now available.
Many improvements have been made. If you use Excel 2013 or 2010 you will also see speed improvements when editing data formatted as Tables.
We tested this pre-release thoroughly and have now made it available to you to help you finish your deadlines in time.
More information and download.

(image)




Tip: Auto numbering on steroids

2014-10-08T19:35:59Z

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 […]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 or suffix, auto number in a filtered list, filling merged cells too, etc. In such a case, just select the cells and use: ASAP Utilities » Fill » Quick numbering of selected cells... (Control+Alt+Q) Please have look at below examples to see the various possibilities: Example 1 Start with number 1 Add 1 every time Restart counting after number 5 Example 2 The most basic scenario, start with number 1 and add 1 for every cell: Example 3 Start with 10 Add 10 Restart counting after number 50 Fill up with leading zeros to a total of 3 digits: Example 4 Start with 1 Add 1 Restart counting if the value in column [C] changes Example 5 Start with 1 Add 1 Restart counting if the value in column [D] changes Add the value for column [D] in front of the number Example 6 Use the letters A-Z to number the cells Add the value "comp [" in front Add the value "]" at the end Example 7 Start with 1 Add 1 Restart counting after number 4 Add the value "Field_" in front Example 8, works on Merged Cells too Merged cells are not a problem with our numbering tool (Excel's (auto) fill tool can't be used when your selection contains merged cells, "all the merged cells need to be the same size") Start with 1 Add 1 Example 9, Auto numbering data in a filtered list too Quick numbering also works on the filtered data. Only the visible cells are numbered, the hidden rows are skipped. Start with 1 Add 1 Fill up with leading zeros to 2 digits Add the value "ES_" at the front Do you recognize any of these situations? Please help me how to fill series with only visible rows after auto filtered applied. How do I fill an excel column with consecutive numbers without typing each one separately? Many times I need to create excel sheets that have a unique ID (typically sequential). I think that a feature that allows the user to define the columns and format of the unique ID (Numeric starting 'X", alphanumeric with fix string (AAxxxx), etc.), would be a great improvement. I'm trying to get Excel to count to a defined end number, then restart at 1. How can this be done? Help, I'm trying to do an auto fill and a part of the range for the auto fill has some merged cells. I keep getting the error: "This operation requires the merged cells to be identically sized." Just select the cells and use the following tool: ASAP Utilities » Fill » Quick numbering of selected cells... This tool can help you in addition to Excel's built-in methods to auto fill cells. Bonus tips, also interesting ASAP Utilities Tip: Quickly extend your selection down to the end without scrolling ASAP Utilities » Fill » Random numbering of cells (without duplicates) ASAP Utilities » Fill » Copy values to empty cells below filled cells in selection / Tip: Quickly fill blank cells with value above in Excel ASAP Utilities » Fill » Insert before and/or after each cell in your selection... To quickly create many worksheets, you can use the quick numbering tool in combination with: ASAP Utilities » Sheets » Insert multiple sheets (uses the cell values as sheet names)... How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working[...]



Tip: Easy ways to combine the contents of multiple cells

2014-10-08T19:32:27Z

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 […]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 or Excel's =concatenate() formula can become cumbersome when it involves a lot of cells." Yes we have a few tools available to make it easier to merge the contents of many cells at once. For example, you can use the following tool to merged the contents of the cells side by side: ASAP Utilities » Columns & Rows » Merge column data (join cells)... To merge the data from cells from top to bottom, you can use: ASAP Utilities » Columns & Rows » Merge row data (selected cells)... Or ASAP Utilities » Columns & Rows » Merge row data, separated cells by a line break And, for maximum flexibility in the specified range, you can use the function =ASAPMERGECELLS() from the ASAP Utilities functions library. For example =ASAPMERGECELLS(C3:AD3;"-"), merges the values of every cell in range C3:AD3 and separates the values with a - character. Compared to Excel's =CONCATENATE() function this new function has the benefit that you can specify an entire range, and not just the values of the cells are used, but the values as you see them (cell's number formatting). Do you recognize any of these situations? I was working today on an excel file and came across a requirement which is not (I think) in ASAP utilities arsenal: a command which can connect text strings from different cells. I do a lot of data manipulation which involves "stacking" columns of data sometimes. It would be very handy if you could include a command that can perform such manipulation in your next edition of ASAP. Thanks Is there an existing ASAP function that will take data from multiple columns and combine it all into a single column? The only alternative I know of is to sort each row to the top and then manually combine, which is very time-consuming. I have an Excel worksheet which contains numbers in Cell A1 to X1. I need to merge all the numbers in those cells into one single cell but each number need to be separated by the "|" sign. I know the Concatenate formula (=concatenate(A1,"|",B1,"|",C1.....) can do this but it will be tedious to write, given I am merging a large number of cells. Is there a variation to this formula that can perform this task? Or maybe a different formula? I have a file in Excel where the zip code is broken into 2 cells. The 5 digit part of the zip code is in column D, the 4 digit part of the zip code is in column E. I need to join them into a single cell. I have a spreadsheet that has text in multiple cells across a row. I need to have a space between each character in the row. I thought I could merge the cells in each row into one cell, but Excel only wants to keep the contents of the first cell if I click on the 'merge and center' button. I know Excel its Data > Text to Columns, but I need to do the reverse, merge data from the columns back into a single column. Does that exist? Such tasks can be easily accomplished with one the following tools: ASAP Utilities » Columns & Rows » Merge column data (join cells)... ASAP Utilities » Columns & Rows » Merge row data (selected cells)... ASAP Utilities » Columns & Rows » Merge row data, separated cells by a line break or the function =ASAPMERGECELLS() from the ASAP Utilities functions library. This often a quicker alter[...]



Tip: Quickly fix the trailing minus sign for negative numbers

2014-10-08T19:30:30Z

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 […]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 appear on the left?" Just select the cells and then use: ASAP Utilities » Numbers & Dates » Move minus sign from back to front (e.g. 65- to -65) This will automatically change all number with a trailing minus sign to negative numbers so that you can use it again in your calculations. Do you recognize any of these situations? When using copy/paste from AS400 to Excel, the negative numbers are not recognized as negative numbers because the negative sign remains on the right side of the number. Is there an easy way to flip the negative sign from the right side of the number to the left? Our manufacturing system extracts negative numbers in a strange way (at least for me). For instance is lists -134 as 0000134- (for positive, it just list 134). What formula can I write to convert it to -134 and remove the leading zeros and move the - sign to the front? I'm importing a COBOL report into Excel and this report, negative numbers have the negative sign in a trailing position. To use cells which contain negative numbers in an Excel formula, the negative sign needs to appear in front of the number. To be able to write formulas using these cells, I have to change the negative number cells to put the negative sign in a leading position. Other than change each cell manually (which is what I've been doing), is there some way to accomplish this re-format using a macro or something else? We received an Excel file that has negative numbers with a trailing (instead of leading) negative sign. These are cash transactions from a cash register. Is there a way to reformat only those numbers (there are tens of thousands of transactions) that have a trailing negative sign to make them a negative number? Just select the cells and use the following tool: ASAP Utilities » Numbers & Dates » Move minus sign from back to front (e.g. 65- to -65) If you are importing a text file that has the trailing negative numbers, then (starting from Excel 2002), you can let Excel fix the trailing negative numbers during the import. This article from Microsoft shows how to do that. Debra Dalgleish even explains nicely how you can use this same built-in feature from Excel to fix already imported numbers with negative signs at the end. Bonus tips, also interesting ASAP Utilities Blog: Tip: Quickly make numbers positive or negative ASAP Utilities » Numbers & Dates » ASAP Utilities Change not recognized numbers (text?) to numbers ASAP Utilities » Numbers & Dates » Apply formula/calculation to selected cells... How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Easily keep only the filtered results

2014-10-08T19:28:17Z

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 […]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 manually by copying the cells to a new sheet, but wondered if ASAP had something built in to accomplish the same thing." Yes we have. You can use the following tool for this: ASAP Utilities » Columns & Rows » Delete all hidden rows and/or columns... This will remove all hidden rows and/or columns on your worksheet (depending on the option that you choose). Do you recognize any of these situations? Once I do simple auto filtering in a file, I want to have the ability to save only the filtered results. I have a excel spread sheet containing approx. 65000 zip codes. When I apply a filter to one of the columns (to remove the data I don't need) I'm left with 34000 records. Now how can I save a file which contains only the 34000 rows/records I need? I have a very large table in Excel (thousands of rows) and I filter it to only show 10 rows. Now I wonder if there is a way to delete the rows not shown (i.e. don't meet the filter conditions)? This would enable me to reduce the file size before I send it. Just use the following tool: ASAP Utilities » Columns & Rows » Delete all hidden rows and/or columns... This a quicker alternative than using Excel's method to copy only the visible results to a new worksheet. Or, if you have Excel 2007 or newer you can use its "Inspect document" feature to remove hidden rows, columns, and worksheets. Bonus tips, also interesting ASAP Utilities » Export » Export worksheets as separate files... (allows you to save a copy without the hidden rows and columns) ASAP Utilities » Export » Export selection or active sheet as new file... (allows you to skip the hidden rows and columns) ASAP Utilities » Columns & Rows » View and manage hidden rows and columns... Allows you to easily unhide certain rows or columns ASAP Utilities Blog: Problem with copying filtered data in Excel 2007 + workaround Microsoft: Copy visible cells only How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Quickly select or filter by strikethrough formatted text

2014-10-08T19:27:13Z

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 […]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 this type of formatting is via the Excel shortcut Control+5. Although visually appealing, working with such cells can become cumbersome. Excel itself doesn't allow you to easily filter, sort or select cells based on the strikethrough formatting. Fortunately we've got a few tools to help you with this and speed things up. Just start the following tool and choose "Strikethrough" in the "Based on formatting and more" tab: ASAP Utilities » Select » Select cells based on content, formatting and more... When the cells are selected, you can choose to whatever you want to be done with the selected cells, rows or columns, such as hiding them, deleting them or copying their data to another worksheet. To sort your data based on the strikethrough formatting you can use: ASAP Utilities » Range » Advanced sorting... Do you recognize any of these situations? I have a spreadsheet with a few thousand rows of data that I need to filter by a column "Part Number". A few hundred of the Equipment Numbers have a Strikethrough to indicate that they are no longer required. I would like to remove these from my spreadsheet. Can I filter for items that have the Font Effect "Strikethrough"? If so, How? My division tracks certain data on an excel spreadsheet. Old data is shrunk to a smaller font and strike-through. Searching for this data is a pain and it would be nicer if I could create a filter to only show rows that are not strike-through, can Excel do that? I've got a huge database in Excel, and many of the records have had text formatted with a strike-through. Initially, this was so I could track what had been 'deleted', but now I want to remove those records. Rather than go through them individually, is there a quicker way? Just start the following tool and choose "Strikethrough" in the "Based on formatting and more" tab: ASAP Utilities » Select » Select cells based on content, formatting and more... This a quicker alternative than using Excel's method that is 'hidden' in it's Find and Replace dialog. Bonus tips, also interesting ASAP Utilities Tip:  Sort your data on anything that you can think of ASAP Utilities Tip: Select cells automatically based on their contents, formatting, etc. ASAP Utilities » Select » Inverse selection... Microsoft: Format text as strikethrough How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Easily transpose 1 column into multiple rows, creating a table

2014-10-08T19:20:13Z

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 […]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 into a table? No more! We've got this question asked many times from our users. They received for example an address-list that had all the information below each other, in one single column and needed to change that into a table-format. Just select the cells in the column and then start: ASAP Utilities » Columns & Rows » Transpose column in multiple steps... Do you recognize any of these situations? I have a list of 7000 numbers in a column and I would like this data transposed in groups of 7, to create 7 columns and 1000 rows.  Thanks for any suggestions. I am trying to converts a large list of client details from a vertical to a horizontal format. Do you have a tool for that? Just select the cells in the column and then start: ASAP Utilities » Columns & Rows » Transpose column in multiple steps... This a quicker alternative than using Excel's Copy > Transpose over and over again to switch (transpose) columns and rows. How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Easily insert an English formula in your non-English Excel version

2014-10-08T19:21:30Z

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 […]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 names, a comma as list separator and a point as decimal separator. If you use an non-English Excel version or other settings then translating and inserting such example-formulas can be a difficult or tedious task. Fortunately, with this tool from ASAP Utilities you can easily use such international formulas. Independent from your Excel version or country settings. ASAP Utilities converts them automatically to your local settings so they can be used in your Excel version, such as for example Spanish, French, German, Dutch, etc. etc. Just select the cell(s) where the formula should be inserted and then start: ASAP Utilities » Formulas » Insert "international" formula... You can use the shortcut Control+V to paste the formula in the form: Easily paste English formulas into your local Excel version, such as for example a Dutch Excel version Do you recognize any of these situations? When finding excel examples on the internet they are often written in English, but my office installation takes all worksheet function names in my local language. Can the function naming language be switched between local language and English, or is there a translation table somewhere? Is it possible on a French (or whatever you want) Excel to use the English names for functions? It's sometimes a nightmare to copy a formula from a web site finding the name in French, replacing , by ;. Just select the cell(s) where the formula should be inserted and then start: ASAP Utilities » Formulas » Insert "international" formula... Bonus tips, also interesting Dolf Trieschnigg: Microsoft Excel 2010 function translations Microsoft: Buy, install, configure, or uninstall an Office language pack How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Insert random dates and times in Excel

2013-06-28T13:33:38Z

We just received the following question: "Would it be possible to add random date and times?" Yes, this can (already) be done with just Excel. We might add a tool for it in ASAP Utilities that will ask you for the input in the future, but it isn't hard to do yourself with the combination […]We just received the following question: "Would it be possible to add random date and times?" Yes, this can (already) be done with just Excel. We might add a tool for it in ASAP Utilities that will ask you for the input in the future, but it isn't hard to do yourself with the combination of a few Excel formulas. These are a few examples of how you can generate random dates and times in Excel within a specified period. (Please note: After you insert the formula you need to set the cell's number-formatting to date/time, otherwise you'll just see numbers.) Insert a random date between Jan 1, 2000 and June 28, 2013: =RANDBETWEEN(DATE(2000,1,1),DATE(2013,6,28)) Insert a random time between 8 AM and 5 PM: =RANDBETWEEN(TIME(8,0,0)*10000,TIME(17,0,0)*10000)/10000 Insert a random time between 08:00 and 17:00: =TIME(8;0;0)+RAND()*(TIME(17;0;0)-TIME(8;0;0)) Insert a random date+time between Jan 1, 2000 and June 28, 2013 with a time between 8:00 and 17:00: =RANDBETWEEN(DATE(2000,1,1),DATE(2013,6,28))+RANDBETWEEN(TIME(8,0,0)*10000,TIME(17,0,0)*10000)/10000 Insert a random date+time between Jan 1, 2000 and June 28, 2013: =DATE(2000,1,1)+(RAND()*DATE(2013,6,28)-(DATE(2000,1,1))) (Some example uses the =RANDBETWEEN() Excel function. If you have Excel 2003 or earlier then you have to load/activate the Analysis ToolPak add-in which comes with Excel and is from Microsoft). If you have a non-English version of Excel or use a different separator than the comma, then you can use the following tool to easily insert these example formulas in your Excel workbook. The formula will then automatically be converted to your local Excel language and settings: ASAP Utilities » Formulas » Insert "international" formula... Download example workbook You can download an example workbook that demonstrates how to insert random dates and times: Example - Random dates and times.xls (40kb) Resources The following two articles explain how you can insert random dates: Dummy Data – How to use the Random Functions http://chandoo.org/wp/2011/05/04/dummy-data-random-functions/ Generate random dates within a specific date range http://www.techrepublic.com/blog/msoffice/generate-random-dates-within-a-specific-date-range/8153   [...]



Tip: Easily change the text in selected cells to UPPERCASE

2014-10-08T19:22:37Z

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 […]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 and then start: ASAP Utilities » Text » Change to UPPERcase Do you recognize any of these situations? How can I select all cells or group of cells and make all of the text capital letters? How do I capitalize letters in excel? I've got columns of names and addresses - some are in lower and some in upper form - how do I change from lower to upper -meaning how do i capitalize these letters? Thanks I have copy and pasted text from different documents to create one. Some are upper case and some are not. I would like to capitalize all of it. How can I format the cells to capitalize ALL letters in a word? I generated a large spreadsheet and now for legal reasons all the data has to be in Upper case. Is there a quick way to convert all the text? Just select the cells and then use the following tool: ASAP Utilities » Text » Change to UPPERcase This a quicker alternative than using a combination of temporary columns, Excel's =UPPER() function and Copy > Paste Special > Values . Bonus tips, also interesting ASAP Utilities Tip:  Quickly change the text-case in all selected cells at once How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of data you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Quickly find the tool you need

2014-10-08T19:23:53Z

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 […]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 search-tool will help you. With this utility you can search in the names, descriptions and keywords of all available tools in ASAP Utilities. You can also immediately run the utility from the results box by clicking the "Run"-button, click the ?-button to view the details and a screenshot (if available) on our website or click on the +-button to have the tool immediately added to your "Favorites & Shortcut keys" menu. Just use the following tool: ASAP Utilities » Find and run a utility... Bonus tips, also interesting ASAP Utilities » Favorites & Shortcut keys » Edit your favorite tools and shortcut keys... How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel and ASAP Utilities. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Test your model/template in different screen resolutions

2014-10-08T19:25:08Z

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. […]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. Some information might not fit in their Excel window and users then need to scroll to see it. Often the people that develop templates have large high-resolution monitors with display resolutions such as 1920x1200 or higher. If a user use a smaller resolution, for example 1366x768 pixels, then information might be displayed outside the visible area of the screen. To quickly test how your model or template will look on computers with different screen resolutions, you can use: ASAP Utilities » File & System » Resize the Excel window to standard screen resolutions... The following table shows the top 10 screen resolutions of the visitors of our ASAP Utilities website the past 6 months: Do you recognize any of these situations? I have created a workbook/worksheets using a monitor with 1920 x 1080 resolution. My worksheets were developed to perfectly fit the screen. But when someone else uses it they may not have this resolution but perhaps 1024 x 768 and then a part of the worksheet is outside of the screen. How can I easily test this? I have many different users for one of my workbooks. Some forms are too large for the users screen. Is there a way I can quickly test how it looks/fits on other screens? Just use the following tool to help you with this: ASAP Utilities » File & System » Resize the Excel window to standard screen resolutions... Bonus tips, also interesting ASAP Utilities » Information » Display your screen resolution ASAP Utilities Tip: Quickly toggle Full Screen with the F11 key How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of workbooks you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Quickly re-start the last used tool

2013-05-17T20:22:20Z

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 […]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 shortcut for you. To quickly re-start the last used tool, you can simply press the button "start last tool again" or use the shortcut Control+Alt+J. ASAP Utilities » Start last used tool again... In Excel 2003, 2002/XP and Excel 2000 the shortcut is the same, but you can find the button in the menu in: ASAP Utilities » ASAP Utilities Options » Start the last used utility again... Bonus tips, also interesting ASAP Utilities Tip: Save time with keyboard shortcuts Manage the shortcuts and add other tools you frequently use via: ASAP Utilities » Favorites & Shortcut keys » Edit your favorite tools and shortcut keys... Wondering which tools you use the most? ASAP Utilities » ASAP Utilities Options » Show me the time that I saved by using ASAP Utilities How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel and ASAP Utilities. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]



Tip: Quickly copy page layout settings from one sheet to other sheets

2013-05-10T09:26:23Z

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 […]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 quickly copy some or all of the page setup settings to the new sheet(s). Therefore we've created a tool for this (about 10 years ago) that will help you to do this in a few seconds. You can easily copy the page and print settings from one worksheet to other worksheets with: ASAP Utilities » Sheets » Copy a sheet's page and print settings... Do you recognize any of these situations? For proper printing I need to copy the page layout from one tab into all of the other tabs in the workbook. I have 40 tabs in the file. Is there a way to copy the format at once? I selected all sheets to specify the page layout, but some settings are disabled in Excel and can only be set one sheet at a time. Is there an easier way than doing this one sheet at a time? Just use the following tool: ASAP Utilities » Sheets » Copy a sheet's page and print settings... Bonus tips, also interesting ASAP Utilities » Sheets » Insert multiple sheets (uses the cell values as sheet names)... ASAP Utilities » Sheets » Print multiple sheets at once... How much time will it save? It's guaranteed that you'll save yourself time and effort by using this tool. However, the actual time saved depends on how much you use Excel, the amount of worksheets you are working with and how often you use this particular tool. You can easily see how much time ASAP Utilities has saved you so far. Download In case you don't have ASAP Utilities yet, you can download the free Home&Student edition (for home projects, schoolwork and use by charitable organizations) or the fully functional 90-day Business trial. Download page [...]