The creation of this Excel Addin started in mid-2015 and the trigger was the need to streamline and automate general recurring and/or time consuming “manual” work in Excel. This is a project at which I work constantly by creating new features or improving the existing ones.
In this post you can find examples of how to use each feature of the tool. If you have ideas of more general tools please write me an e-mail. If you want a trial copy of the Excel Addin please contact me.
The standard version of the tool can be customized and developed according to the needs of each client.
Click on the below buttons to Expand or Collapse the different sections of this presentation !
• Right click the received Archive and extract the containing folder anywhere on your PC:
• Open the INSTALL.xlsm file from the folder (very important: for the installation to succeed, both files, Add-in file and Installation file must be in the same folder !):
• Press the “Enable content” and then the “Install Addin” button:
• If you have an old version of the Add-in already installed, the application will ask you if you want to replace that one with the new one:
If you press “YES”, the new Add-in will be copied automatically in the Add-ins Folder and will be installed to your Excel. You will also receive a confirmation message that the installation was successful. If you press “No”, nothing will be changed.
• If you install the Add-in for the first time, it will be copied automatically in the Add-ins Folder and installed to your Excel without other confirmation step. You will also receive a confirmation message that the installation was successful.
• After the installation, you should have in Excel a new Tab named “GENERAL.TOOLS”:
• For example, select a cell with a US GAAP GL Account and press the GL Account button. This will return information about the selected GL Account and it’s parent group:
In this section can be added more features on request.
• If you selected a Range of cells on the Active sheet and clicked the Trim button, the method will be applied only to that range:
• If you selected only one cell, the method will be applied to all the cells from Active sheet:
• If you selected anything else than a range (ex: a chart) and clicked the Trim button, you will receive an information message and nothing will happen:
• It’s used when you need to delete all rows from the Active Worksheet that contain in the column of the current selection the same string or number as in the current selection.
• One example of situation when you should use it is when you have a worksheet from a System (ex: SAP) exported with the header of each page in your work file:
• To delete the “extra-headers”, select a cell in the first row from where you want to start the deletion and press the button from the Tab General.Tools
• The macro will start running and you can see the process into Excel’s Status Bar:
• After it completes deleting all the rows you receive confirmation message with the number of deleted rows and your file has no repeating headers in it’s rows:
• It works the same as the previos tool (Delete Rows as Selection): you can see the progress in the status bar and when it finishes you receive a confirmation message. In our example, the result would be the following:
1. All in one column:
• This macro takes all the values from the selected range (can be one column or more columns !) and outputs a list only with the unique values in one column. The user selects the first cell of the output range with the help of an Input Box:
• If we select data from more columns, this feature will not consider the dependence between the column and will only output in the selected column the unique values from selection:
2. More Columns:
• This feature works similar to Excel’s Remove Duplicates feature: you can select more columns and output the unique values considering the values from all selected columns. It essentially checks if the combination of values from the selected columns is unique for each row in the selection range ! For example if we select the same range as in our last example (B2:C37) we will obtain a different result that with the “All in one column” feature:
* The advantage of using this feature instead of Excel’s Remove Duplicates default function is the speed and the fact that with General Tools’s feature you are not affecting the original data. With the Excel’s function you have more steps to follow if you want to accomplish this: copy the original values, paste them in other location, confirm:
3. Sumif 1 Criteria:
• This feature allow you to perform a very quick sumif with one criteria. Consider the following example:
• Consider the following example which was imported to Excel from a SAP image. If you try to calculate using these amounts you are not able, since the values are strings which only look like amounts. The comma is actually in the string and should be deleted, the minus sign must be removed from the end of the string and the resulted string multiplied with -1 in case of negative values 😉
Don’t worry, you don’t have to do this !
Just, select the Range(C2:C27) and press “Numeric Selection” if you want to ask General Tools Add-in to convert very quick those strings to numeric values for you!
• Many times (usually when you import data from an information systems), you have in Excel the column with the date in a format which is not recognized by Excel as a Date. Because of this, you cannot use the dates in formulas.
• In columns C, D, E you have formulas for getting the Year, Month and Day of each Date, but you get an error because the strings from column B are not actually Dates for your Excel. They just look like dates !
• To solve this issue, you can use text formulas to take the Year: right(B2,4), Day: Left(B2,2) and Month: Mid(B2,4,2). You put those values in 3 separated columns and in a 4th column you use the Excel function Date(year,month, day):
• Don’t worry, you don’t have to know to do this or spend time with it !
• Just, select any cell from the column with the dates and press “Date Column” if you want to ask General Tools Add-in to convert very quick those strings to dates for you!
• As you can see in the print screen from the next page, the values in column B, are now recognized as Dates by Excel:
This feature can handle different text formats that look like date, but not all of them. On request, it can be improved to transform to dates more formats.
1. Proper Case:
• Converts the text within selected Range to Proper Case (the first letters of each word in Upper Case):
2. Upper Case:
• Converts the text within selected Range to Upper Case:
3. Lower Case:
• Converts the text within selected Range to Lower Case
4. First Upper Case:
• Converts only the first letter of the string to Upper Case and all other letters to Lower Case
• Let’s see how this feature works by testing it on the amounts which we used before also for the “Numeric Selection” and “Sumif 1 Criteria” features.
• Suppose that in F2 and F3 we have the amounts without the VAT Rate and we need the amounts including VAT (20%). We can select as “Applicable Range” the Range(“F2:F3”) and write the following formula:
• As you can see in the above image, the range now contains the formula. If we want to have only the values we can choose the option 2: “As Values” :
• If our data is filtered, we can choose the option “Apply to visible cells only” and the macro will apply the formula only to the visible cells:
• Only amounts from year 2014 (as filtered) where modified by the inserted formula:
• If we insert a bad formula, the code will not work:
• If at “Applicable Range” we input something else than a Range address, the box will get red and we receive a message about the invalid Range input:
• If we apply a valid formula to a valid range which contains something else than numeric values we have 2 options:
o Mark those cells with color red (default option):
o Don’t highlight errors (will do nothing to the cells with no numeric values, but will apply the inserted formula to the numeric cells within the selected range):
_initial_Testing General Tools
_final_Testing General Tools
At the moment you cannot download this Excel Addin for free, but if you want to request a trial sample, any other details or contribute with suggestions/ideas to improve the tool, please contact me.