Powerutils 1.9 is now available for download containing 99 utils.
Click Here to see what the users feel about this util.
Click here to find what is in store in the next version of Powerutils 1.8 which you can download in beta stage.
-
1.Text - Convert to Uppercase, ProperCase, LowerCase, Delete trailing / leading Space, delete specified Number of Characters etc.
-
2.Numbers - Convert Text Numbers to Real Numbers, convert numbers to text, Shift "-" from end to front, Find lowest / highest number in Range.
-
3.Header Footer - Put any combination of information including File Path, Name, Sheet Name etc in all sheets in file. Delete all Headers / Footers.
-
4. Sheets - Sort all Sheets in File, Create Index file (Clickable) of all sheets, Put file information in any cell. Delete all page breaks etc.
-
5. Range - Reverse data in range, replace all #N/A in sheet with anything, Show all cells with errors, Delete all blank rows / columns, delete / color all duplicates in selected Range, Apply formulas to selected range, apply rounding formula, join cells with anything in between etc.
-
6.Column & Range - Delete Blank Rows / columns, Insert specified number of rows / columns after every user defined number of rows / columns
-
7. Fill - Fill blank cells with anything, copy values down to blank cells, Quick numbering of Cells in rows / columns.
-
8.Add - Add text at begining / end of each cell in range. Add leading zeors to all numbers to make them same size.
-
9. Miscellaneous - Count number of characters in cell, Text whether cell content is number or Text, Color all cells containing Text / Numbers / Formulas.
The Full Details are given in the Table Below.
| What is Power Utilities. | Excel is a Powerfull
Spreadsheet and a reasonably good Database Management Software. The best
part about Excel is its Programming Language Called Visual Basic for
Application, it is a subset of the more Powerfull Visual Basic. With this
language you can even write games within Excel. Powerutility uses VBA to
give you these great Functions. The birth of this program was 2 years back when there were just 10 utilities and it was named "Backup". This software was well accepted and distributed in PC Quest and Chip (now Digit) magazine. Over the past year I have developed new Utilities which I felt was very much needed for Day to day working. I have renamed this Addin to PowerUtils to highlight that this has become a very powerful accessory for all Excel Users. There are currently 54 Uilities and my target is to make at least 100 utilities within the next 1 year and make it one of the most useful addin for Excel in the World. As an accountant, Programming is my hobby so finding enough time to write these Utilities was taxing and I had to stay awake late into the night many days. There may be few Programming errors which I hope you will bring to my notice. My email is prasantad@tatamotors.com and phone number is 0657-218-4051 (Office). My website which will be available soon, is http://www.powerutilsnet.com. You can get more information on this site by January'2004. |
| Text - Convert to Uppercase | Suppose you have typed a large amount of text on any sheet in Lower Case and later on you have to convert it to uppercase. In normal circumstances you can use Uppercase function for each cell but it will be very time consuming. Here, first select all the cells whose contents you want to covert to uppercase, then click and select the Uppercase function in powerutilities. All the text will be converted to uppercase. |
| Text - Convert to Lowercase | First select the range whose Text you want to convert to lower case, then apply the function. All the Text will be changed to Lower Case. |
| Text - Convert to ProperCase | In Proper case the first alphabet of each word will be capital and the balance lower case. First select the range whose Text you want to convert to Proper Case, then apply the function. All the Text will be changed to Lower Case. |
| Text - Convert to Sentence Case | Will be available in Next version. Contact the author. |
| Text - Delete N leading characters | Often we find data where the a few fixed characters are not required. With this function, first select the range of data for which you would like to remove a fixed number of characters from the beginning. Thereafter select this program. You will get a Edit Box asking for how many characters you want removed from the beginning. Fill any number and click okay. All the data will be there minus the number of characters you've' asked to be removed. |
| Text - Delete N trailing characters | With this function you can remove a fixed number of characters at the end of each cell from the selected range of data. |
| Text - Delete all Leading Blank Spaces | With this function you can remove any blank space appearing at the beginning of cells. First select the range for which you want to remove the leading blank space then execute this function. |
| Text - Delete all Trailing Blank Spaces | With this function you can remove any blank space appearing at the end of cells. First select the range for which you want to remove the trailing blank space then execute this function. |
| Text - Delete all Leading & Trailing Blank Spaces | With this function you can remove any blank space appearing at the beginning and end of cells. First select the range for which you want to remove the blank space then execute this function. |
| Number - Convert text number to numbers | Sometimes when you download data from another System like ERP package, the numbers which appear are really not numbers but are text. You can check this with another function under "Miscellaneous" Check for cell content is number. If you find that the data is number and you want it to be a number then just select the range and use this function to make the text numbers to real numbers. Also if a number is prefixed with a ' then also it is a text number. |
| Number - Convert Numbers to text numbers by adding '. | With this function you can convert any number to text by prefixing it with a '. This is specially required in queries and lookup formulas in Excel. First select the Range whose numbers you want to convert to text then use this function. |
| Number - Shift "-" from end to front | When you download Data from other Software like ERP packages or Financial packages, the negative numbers are formatted in such a way that when the data is opened in Excel, the negative numbers have the "-" (minus) sign at the end. This results in the number becoming a Text and cannot be further used in mathematical calculations unless you move the "-" from the end to the front. Manually doing it for a large database is mind boggling. However with this function just select the range within which such erroneous data is there and select this function. In a Jiffy the data will be corrected to show the negative figures. |
| Number - Select Cell with highest Value in selected Range | if you want to find the Cell with the largest value in a range just use this function. |
| Number - Select Cell with Lowest Value in selected range | if you want to find the Cell with the lowest value in a range just use this function. |
| Header Footer - Put Header & Footer | Putting the sheet name, date, time file name in a header / footer for a sheet is pretty easy. But suppose you had to also put the File Path (file is from a Network PC) and also put all these data formatted for 9 point font in 20 sheets in a file then what??? Use this function and put what you want in whatever font size in all the sheets of the file in seconds. |
| Header Footer - Delete all Headers and Footers in Current Sheet | Use this function to delete Headers and footers Current Sheet. |
| Header Footer - Delete all Headers and Footers in all Sheets. | Use this function to delete Headers and footers from Multiple Sheets. |
| Sheet - Sort all Sheets in a File | We are so used to sorting Excel Data in various ways. If only we could sort the sheet names so that they appear alphabetically so as to help us find any sheet very quickly. Well, Help is at hand, use this function to quickly sort the Sheets. |
| Sheet - Create clickable Index sheet of all sheets in File | Besides sorting, when we have files with multiple sheets say around 30-40, wouldn't it be great to have a Index sheet where all the sheet names would appear in Rows and also be clickable. Thereafter you could write comments about each sheet in the next column for easy understanding of what the sheet contains. |
| Sheet - Remove all empty Sheets | Remove all blank Sheets from the file. Specially useful when there are many sheets with default names like Sheet1, Sheet2 … and you do not know which are useless and which are empty and just creating confusion. Will be available in Next version, contact the author. |
| Sheet - Insert File Path, File Name & Sheet Name in Cell | Sometimes you would like to put the details of the File name, Path and sheet name in a particular cell within the worksheet. Just use this function to do the same. |
| Sheet - Insert File Name in Active Cell | Put the File Name in the Cell. |
| Sheet - Insert Sheet name in Active Cell | Put the Sheet Name in the Cell. |
| Sheet - Insert File Name & Sheet Name in Active Cell | Put the File Name and Sheet Name in the Cell. |
| Sheet - Delete all pagebreaks in selected sheet | Sometimes finding a page break can be tiring if you do not know where it is. Use this function to delete all page breaks in the current sheet. |
| Sheet - Delete all pagebreaks in all sheets | Sometimes finding a page break can be tiring if you do not know where it is. Use this function to delete all page breaks in the all the sheets of the file. |
| Range - Reverse data in selected Range | Similar to transpose but it reverses the data of the selected range. Please Save your file before trying this function, just in case it gives results which you had not in mind. |
| Range - Replace #NA with anything in Value Cells | After using certain excel functions like Vlookup, Hlookup you may get error #NA. Unless you remove them you cannot get sum totals. If you have converted these formulas to Values then use this function to quickly replace the #NA 's with anything you like. However if you have not converted to values the range then use the next function which will also work with formulas and values but will be a lot slower in replacement. |
| Range - Replace #NA with anything in Formula and Value Cells (Slow) | Similar to above Function but when the data range containing the #NA has not been converted to Values then this function can be used. However this function will take more time so please be patient. |
| Range - Replace #VALUE with anything | With this Function you can replace #VALUE error with any thing. Will be available in Next version. Contact the author. |
| Range - Show all cells with errors | With this function you can find Cells with errors so that you can take necessary action to correct them. |
| Range - Delete all Blank Rows in selection | When you have a large worksheet with intermittent data i.e.. There are blank rows in the middle and you want to remove only the blank cells at one go, then this function is for you. |
| Range - Delete all Duplicates in Selected column | This function will only work on one columns at a time. With this function you can eliminate all duplicates of data. The first instance of the data will be preserved and subsequent occurrence of the same data will be erased. Since this function involves a lot of searching and erasing it will take a lot of time for a large database. Please save your file before running this program. Please note that the data need not be sorted on the key column for this function to work. |
| Range - Colour all Duplicates in Selected column | This function will only work on one columns at a time. With this function you can colour all duplicates of data. Since this function involves a lot of searching and erasing it will take a lot of time for a large database. Please save your file before running this program. Please note that the data need not be sorted on the key column for this function to work. |
| Range - Count all Duplicates In Selected Columns | This function will only work on one column at a time. With this function you can count all duplicates of data. Since this function involves a lot of searching and erasing it will take a lot of time for a large database. Please save your file before running this program. Please note that the data need not be sorted on the key column for this function to work. Will be available in Next version. Contact the author. |
| Range - Count all Unique Items in Selected Columns | This will Count the Unique Number of items in the selected Column. This will be available in the Next Version/ Registered version. |
| Range - Apply Formula to all cells in selection. | I would rate this within top 5 utility. If you have a large spreadsheet with large data and you want to apply a formula to many cells at one go then this will take seconds with this program. Suppose you want to multiply each cell by 8 and divide by 128 then just select this program and in the edit box type *8/12 and click okay. in a jiffy all the cells selected will be multiplied by 8 and divided by 12. Similarly suppose you want to multiple the figures in a range by the contents of a cell say "c1" then in the edit box type *c1. You will appreciate this function when it result in saving a massive time for you. |
| Range - Apply Rounding to all cells in selection. | This too is a very useful program. Assuming you have made a lot of complicated calculations but you did not use the Rounding function which can result in dreadful errors, then use this program to round off all the cell formulas to any number of decimal points you desire. |
| Range - Delete print area in selected sheet | This will delete the print range set for the sheet. Will be available in Next version. Contact the author. |
| Range - Copy Formulas without change of reference | With this program you can copy a range of formulas to another place without changing the cell reference. Suppose you have put a formula in cell "A12 " which is "=sum(a1..a11)" then you can use this program to copy the formula to say cell a15 and it would still have the same formula. You can copy a range of formula but the destination select the upper left top corner. Will be available in Next version. Contact the author. |
| Range - Join Cells with | This again would be within the top 10 program. With this program you can join multiple column of data with a specified character in middle, all the joined character would now appear in the extreme left column. |
| Backup Open Files | With this utility you can Save files in its original place as well as a backup place. Not only that, you can save all the open files at one go in their own original place as well as in the backup path. |
| Column and Rows - Delete all Blank Rows in selection | In a large worksheet if you want to delete all the blank rows then use this function. You can also use this function for a range of data only. |
| Column and Rows - Delete all Blank Columns in selection | In a large worksheet if you want to delete all the blank columns then use this function. You can also use this function for a range of data only. |
| Column and Rows - Insert "N" rows after every Row | With this program you can insert as many rows as you desire in between each row of data for the selected range. For Example you have data from Row 3 to 1000 containing Part Number wise Rate and Issue value. If you want to insert 2 rows between each row then just select this function after having selected the range of data, you will given a edit box with 1 as default, just type 2 and click okay. You will find 2 rows inserted between each row. |
| Column and Rows - Insert "N" columns after every Column | It is same as inserting rows as above but in this case you can insert as many columns between the data range as you want after each column. |
| Fill - Fill empty cells with user selection | This will fill the selected range with whatever value you enter. For example you have a worksheet with the Salary of 1000 employees under various heads and few of the cells are blank . Suppose you want to replace those blank cells with 0 then this function will do it in seconds. |
| Fill - Copy Values to empty Cells below | This program is specially useful after using the Sub Total function in Excel. After you do a Sub total based on the Key column and the totals of specified columns are obtained, you will find some Text data not appearing in the sub total rows which might be critical for your working. Use this function to copy the data down to blank rows . |
| Fill - Quick numbering of rows | This may not be a great time saver but it can reduce an element of irritation from your head. Suppose you have to Number a column of data from say 1 to 300. Just select the starting cell and select this program. It will ask you for the starting serial number and ending serial number and it will put the serial numbers down the rows. |
| Add - Add Text at beginning of each cell | This program is again within the top 10. Suppose you have a large amount of data and you want to prefix this data with some other text, then use this function. For example you have employee Ticket Number list of 10,000. Suddenly you are asked to prefix all the ticket number with the Alphabet "A" for inputting into the mainframe. Just select the range of ticket number and select this function. It will ask you what to prefix and in a flash the alphabet "A" will be added in the beginning of each data. |
| Add - Add Text at end of each cell | This is same as the earlier Program but in this case the Text you decide will be added to the end of each data. |
| Add - Add leading Zeros to Numbers (will become Text) | There are times when the number of characters in a numeric column must be of same size. This is specially true for bank related data. E.g. 123.34 and 12355.56 must be of same size i.e.. 00123.34 and 12355.56. This program will help you do this, but mind you, the numbers will now become text. |
| Miscellaneous - Count Number of Characters in Cell | This will tell you the number of characters in the correct cell. |
| Miscellaneous - Check whether Cell Contents is Number | You may have two cells with 213.45 but one may be a number and the other may be test. This specially happens when you download data from other systems like ERP packages. When you are using Lookup Formulas you may get #N/A even though you see that the key column data is there in both the places (Master file & Transaction file) and also the formula put is correct. Then the problem lies with one of the key field being a number and the other text. Use this function in both the places to ensure that they are both either numbers or text. |
| Miscellaneous - Copy data from Excel to Word. | Will be available in Next version. Contact the author. |
| Miscellaneous - Send Customised mass email from Excel. | Will be available in Next version. Contact the author. |
| Miscellaneous - Insert Row on change of Key Field | Will be available in Next version. Contact the author. |