Misc Tools

Add Random Numbers: Adds random numbers between 0 and 100 to the selected range

Break Links: Converts all formulas linked to other Microsoft Excel sources or OLE sources to values. Example: Workbook Test1.xlsx has a formula linked to Workbook Test2.xlsx "=[Test2.xlsx]Sheet1!$A$1" whose value is 250. This function will convert the formula in Test1.xlsx to the value 250. This also works with charts. This is useful when the sources are no longer available.

Create Compressed Copy: Creates a compressed copy of the active workbook in C:\Temp. XLSX and XLSM files are "zipped" XML files. This process unzips and then re-zips the Excel file to gain some additional compression. This is can reduce large data files but does not have a significant impact to files containing images. "Compressed xx-xx-xx" is appended to the compressed copy file name. Below demonstrates the amount of compression gained as compared to zipping in Windows Explorer.

Delete Names: Delete all named ranges in the workbook (except hidden names). This is useful when the named ranges are causing issues with copying worksheets within the workbook i.e. warning dialogs are displayed. Note: Deleting Named Ranges with Japanese Names from the Name Manager on English machines may not actually delete the Name from the workbook only from the Name Manager. This function will delete the Japanese Name as well.

Formula to Value: Converts formulas to values in the current selection.

Paste To Filtered Range: Copies the currently select range within a single column and pastes it to a user selected single column range while skipping any hidden rows. Typically used on filtered ranges. The source range can be a filtered list as well.

Personal Tab Creator: Open a workbook which includes a Personal Tab on the Ribbon that can be used to run up to 10 user created macros.

Basic Steps

Display the Visual Basic Editor (VBE)
– key Alt + F11.
Add your own code to the starter macros provided in the Custom_Macro_Module . DO NOT change the names of the macros!
Optional but recommended - delete this sheet
Save the presentation (you can change the Name but make sure you save it as a Macro-Enable file (*.xlsm or *.xltm).

Optional: You can change:

The name of the Tab (from Personal Tab)
The name of the Group (from Personal Macros)
The name of the Button (from Macro 1, Macro 2, etc…)
The additional info for the macros in the “supertip”

If you do not need all 10 Buttons, you can hide them by setting the Visible Constant to False.

To create an Excel Add-In:

After completing the steps above, use the SAVE AS option to save the presentation as a Excel Add-in (*.xlam). Save it to the default folder provided.

To Install the Add-in (.xlam) File

Go To File, then Options
Select Add-Ins
Select Excel Add-Ins from the “Manage” drop-down and click Go
Check the box beside the newly created Add-in and Click OK. Your Personal Tab should now be available each time you open Excel. Note: if you didn't see your file name, select Browse, go to where you saved the file, double click it and Click OK.