Range Related Utilities

Format in India Rs lakhs format

This program will be useful for Indian Accountants when they want to format their Financial Reports and want to forma the currency to truly reflect Indian Rupees with the commas at the correct places. The format put is dynamic and in case the amount is changed the format will correctly change.

After downloading data from ERP package like SAP, if a column is inserted and a formula is put, the formula keeps appearing as text.

Just select the first cell and use the program. Once the cell is correctly formatted copy it to the other cells.

Put Bullets in front of items

In word you have the option to put bullets in front of a list eg

Activity List

In Excel first type the list and use the program.  You will get various characters you can use before the text and the output would look like this.

It nearly resembles the Word version.

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.

               Similar to above Function but when the data range containing the #NA has not been converted to Values then use this function. However this function will take more time so please be patient.

Replace Errors with anything                With this Function you can replace all type of errors with any thing you want. You will get a Form where you can select the error type you want to replace and also with what value. Fill up the necessary data and run the program. You may also use this program to only find out which cells have errors. The various options available are:-

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 12 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 you use it as it results in massive time savings of yours.

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.

Copy Formulas without Change of Reference. Suppose you have typed a complicated formula in any cell and you want to copy the same formula to another cell without changing the cell reference then use this function.

Join Cells with anything you want. Suppose you have 2 or more columns of data which you want to join with some specific separator say "space" or "-". Just select the column Range and select this function. you will be asked the separator type either a blank, "-" or any other character and click okay. All the columns will be joined with the separator in between, the data will now sit in the extreme left column.

Delete all Comments in Cells in Selected Range. Cell Comments are very useful for remembering the calculations or the source of data for the cell. Over a period of time these become redundant and is a confusion creator. To quickly delete all the comments in the selected range use this util. There is no Undo Feature currently, so please save your file before using this util.

Delete all Range names in Current WorkBook

With this program you can delete all range names which are there in the current workbook.
 

Reverse data in selected Range