Here in this post we will see custom excel function that are useful to the finance professionals. Let’s start:
Install the Utility File
Step 1: Download the Excel Addin File from this link. 
Step 2: Install the Addin File
(i) Open your “Microsoft Excel”
(ii) Go to “Files:
(ii) Go to “Options”
(iv) Go to “Add-ins” tab in the new window
(v) At the bottom at “Manage” Section choose “Excel Add-ins” and click “Go”
(vi) Click “Browse” and browse and select the file downloaded from Step 1
(vii) Click “OK” and return to Excel Spreadsheet
Functions
REVERSE Function
This is really useless function. It just reverses the content of your cell from right to left. I don’t know if this is ever useful.
=REVERSE(“nepal”) = lapen
SHEETNAME Function
This custom function returns the name of your worksheet within the workbook.
=SHEETNAME() returns the name of your active worksheet where you are working.
TALLYVALUE Function
TALLYVALUE function is very useful function for tally users. This functions converts the Dr. and Cr. text formatting in the trial balance imported from the Tally Accounting Software.
Procedures
Procedures are not excel functions. They cannot be called from the spreadsheet itself. You will need to assign a shortcut key or a icon shortcut to the procedure. It will then be executed.
Autofit Merged Cells
Some excel users are very much into using spreadsheet even for word processing works. But the trouble one always comes with that approach is that the “merge” tool in excel was developed by devil and once the merge tool is used autofit doesn’t work in that case. This procedure solves that problem. You will just need to go to the particular cell where you want to use this procedure and execute.
Before using the autofit tool
After using the autofit tool
Highlight Precedents
Auditors who review excel worksheet want to sometimes overdose on drugs and die. Its not an easy task reviewing the excel formula with all those dependents, precedents, references and cross-workbook and worksheets links. Its not an easy thing to track. But if you had a excel tool that could tell you about all the precedents of the formula in a excel cell and highlight all the dependents and show them all in a manageable watch-window, wouldn’t that be just awesome? Just install the above addin file and assign a preferred shortcut / icon link to the “highlight precedents” macro and you are ready to use the tool.
Step 1: Just locate your formula cell
Step 2: Execute the code, select your color and see the results in the watch window
Refine Trial Balance
Just like TALLYVALUE formula above, the macro called “refine trial balance” from the addin file above, works similarly. If you have imported a tally trial balance in a detailed format with opening and debt/credit transactions enabled for the period, the macro runs through each row and converts your tally data into a manageable trial balance in the form of +ve and -ve numbers for debit and credit values.
Step 1: Get your trial balance ready in excel formant. Don’t change any of the default formatting.
Step 2: Activate the trial balance worksheet. Run the macro and you will see the result of the operation as follows.
