Accountants' Toolbox

“Accountants' Toolbox” Add-In

This Microsoft Excel Add-In contains some useful tools for the busy accountant. It was developed primarily as a way to simply my daily tasks when dealing with complex and large accounting spreadsheets. Functionality has been refined over the years to only include the basic tools that I cannot work without.

The Add-In is accessible via its own ribbon tab as shown below:

The following functions are available:

1. Format the current selection i.e. selected cells

   1. As accountancy e.g. 1,000,123.12;(1,000,123.12);0.00

   2. As accountancy but without zero e.g. 1,000,123.12;(1,000,123.12);

   3. As accountancy but replacing zero with a dash e.g. 1,000,123.12;(1,000,123.12);-

   4. As rounded to integer i.e. Pound (but without the "£" sign) e.g. 1,000,123;(1,000,123);0

   5. As rounded to integer i.e. Pound (but without the "£" sign) but without zero e.g. 1,000,123;(1,000,123);

   6. As rounded to integer i.e. Pound (but without the "£" sign) but replacing zero with a dash e.g. 1,000,123;(1,000,123);-

   7. As rounded to thousand i.e. £k (but without the "£" sign) e.g. 1,000;(1,000);0

   8. As rounded to thousand i.e. £k (but without the "£" sign) but without zero e.g. 1,000;(1,000);

   9. As rounded to thousand i.e. £k (but without the "£" sign) but replacing zero with a dash e.g. 1,000;(1,000);-

   10. As rounded to million i.e. £m (but without the "£" sign) e.g. 1;(1);0

   11. As rounded to million i.e. £m (but without the "£" sign) but without zero e.g. 1;(1);

   12. As rounded to million i.e. £m (but without the "£" sign) but replacing zero with a dash e.g. 1;(1);-

   13. As percent with one zero precision e.g. 5.2%;(5.2%);0.0%

   14. As percent with two zeros precision e.g. 5.23%;(5.23%);0.0%

   15. As date e.g. 02/01/2019

   16. As date in short text format e.g. 2 Jan 2019

   17. As date in long text format e.g. 2 January 2019

2. Clipboard

   1. Paste values only.

   2. Paste formulas only.

   3. Paste as link.

   4. Paste all but formulas. This is particularly useful if you don't want to have links created if you were to paste all.

   5. Paste transpose values only

   6. Copy sum to clipboard. The sum of the selected cells is copied to the clipboard ready to be pasted (values only) somewhere else.

   7. Copy visible cells. To avoid pasting some hidden cells that you don't want.

3. Transform selection i.e. selected cells

   1. Neg e.g. from 50 to -50. The function is clever enough with formulas. For instance =50+60 will become =-(50+60).

   2. Trim removes leading and trailing spaces from text values. The function is clever enough with formulas. For instance =" this is a text" will become =TRIM(" this is a text").

   3. Change case. Each of the next functions is clever enough with formulas by appending or removing the associated Excel macro (UPPER, LOWER or PROPER)

      1. Uppercase

      2. Lowercase

      3. Propercase

4. Miscellaneous

   1. Update all pivot tables found in the active workbook.

   2. Add SUBTOTAL. I prefer to use SUBTOTAL than SUM as it is easier to add or remove sub-totals without having to change the grand total formula.

   3. Set default Header and Footer.

5. Workbooks

   1. Clean up active workbook by deleting empty rows and columns. This can dramatically reduce your workbook file size and also helps with the scrollbars size. If you want to reduce your file size even further you should try to save your workbook as ".xlsb" file extension.

   2. Clean up active worksheet by deleting empty rows and columns.

   3. Save all opened workbooks.

   4. Save and close all opened workbooks.

   5. Close all opened workbooks without saving.

   6. Close active workbook without saving.

Download

“Accountants' Toolbox” is available free of charge.

Click here to download the latest version of Accountants' Toolbox.

Requirement: a working internet connection, Windows 7 or above, Excel 2013 or later and Macros must be enabled.

Installation

1. Download “Accountants' Toolbox” Add-In.

2. Go to Excel Add-ins manager located in Excel Options.

3. Browse and select “Accountants' Toolbox” Add-In.