Paste Tools: Several tools for pasting information from the Clipboard as pictures (video overview)
Paste to Size: Pastes the contents from the clipboard to fit the selected region. The dimensions are constrained by the ratios between the height and width of the region and the height and width of the clipboard content. The clipboard contents are pasted as an enhanced meta file.
Paste Replace: Pastes the contents from the clipboard to fit the selected shape’s height or width based on the ratio and deletes the original shape – effectively replacing the original shape. The dimensions are constrained by the ratios between the height and width of the original shape and the height and width of the clipboard content. The clipboard contents are pasted as an enhanced meta file.
Paste Offset: Pastes the contents from the clipboard to fit the selected shape’s height or width based on the ratio – with an offset. The dimensions are constrained by the ratios between the height and width of the original shape and the height and width of the clipboard content. The clipboard contents are pasted as an enhanced meta file.
Print Tools: Three simple printing tools.
Worksheet Tools: A series of tools for formatting and adding custom charts / forms. (video overview)
Navigator: The Navigator provides a number of functions to work with and move between open Workbooks and their associated Worksheets. Note: The Navigator does not function with Workbooks with multiple document windows in the same Workbook.
When opened, the Navigator displays all currently opened Workbooks in the Workbook Pane. The active Workbook is selected and the visible Worksheets are displayed in the Worksheet Pane with the active Worksheet selected. To display a different Workbook or Worksheet, select the appropriate item and then double click. The Navigator will display the selected Workbook or Worksheet.
❶ Sort Workbooks: Sorts the list of Workbooks in the Workbook Pane – Ascending or Descending.
❷ View 2 Workbooks: Displays the active Workbook and one other highlighted Workbook – Side by Side. The other workbooks are minimized.
❸ Stretch Across Monitors: Essentially maximizes the active Workbook across multiple monitors.
❹ New Workbook: Opens and New Workbook and activates it.
❺ Open Workbook: Opens a file dialog to choose another Workbook to open. It makes it the active Workbook.
❻ Close Workbook: Closes the highlighted Workbook. If the highlighted Workbook is the active workbook, no action is taken.
Close: Closes the Navigator form.
① Sort Worksheets: Sorts the list of Workbooks in the Workbook Pane – Ascending or Descending and the actual Worksheets in the Workbook.
② New Worksheet: Adds a New Worksheet after the currently selected Worksheet and activates it.
③ Hide Worksheet: Hides the currently highlighted Worksheet.
④ Unhide All Worksheets: Unhides all hidden Worksheets in the active Workbook.
⑤ Delete Worksheet: Deletes the highlighted Worksheet.Close: Closes the Navigator form.
Clear: Clears all formatting from the selected Range
Convert Text: Converts formatting to General e.g. a date entered as Text will be converted Date. Clicking again will convert it to the date serial.
Fill Same: Fills blank cells with the value above in the selected range. Merged cell are split up.
Transpose Records: Transposes columnar records based on user input to the left inserting the appropriate number of columns.
Convert Crosstab: Converts Crosstab Tables (2-Dimensional Tables) To Lists. The Crosstab can be a range or table; however, the first row must contain headers. The range must be separated from other data. The User is prompted to enter the number of columns on the left that will be used to create the list columns. The remaining rows in the Crosstab will be “unpivoted”. The resulting list is placed in a table on a new worksheet.
Normalize Superscripts: Converts Superscript characters to Normal in the Selection.
Remove Hyperlinks: Removes Hyperlinks in the Selection.
Convert Formulas: Converts formulas to values in the current selection.
Upper Case: Converts the text to upper case in the selected cells.
Lower Case: Converts the text to lower case in the selected cells.
Proper Case: Converts the text to proper case in the selected cells.
Convert HTML Entities: Convert HTML Entities such as: &lt;tag&gt; to <tag>
Blanks to Zeros: In the selected range, adds zeros to blank cells.
Normalize Subscripts: Converts Subscript characters to Normal in the Selection.
Dash to Minus: Converts Dashes and Hyphens to Minus signs.
U+2011 Non-Beaking Hyphen
U+2012 Figure Dash
U+2013 En Dash
U+2014 Em Dash
Non-visible Characters: Removes non-visible characters such as Tab and replaces nonbreaking space characters with space from the selection.
Extra Spaces: Removes extra spaces from the selection – basically Excel’s TRIM() function.
Double Quotes: Removes double quotes from the selection.
Single Quotes: Removes single quotes from the selection.
Superscript Chars: Removes Superscript Characters from the selection.
Before Nth Char: Removes the string before the nth character inputted into the textbox. e.g. 9 returns Test.xlsx from C:\Temp\Test.xlsx
After Nth Char: Removes the string after the nth character inputted into the textbox. e.g. 7 returns C:\Temp from C:\Temp\Test.xlsx
Specific Character: Removes the single character inputted into the textbox from the selected text.
Punctuation: Removes punctuation from the selection.
Numbers: Removes numbers from the selection.
Letters: Removes letters from the selection.
Subscript Chars: Removes Subscript Characters from the selection.
Shapes and Images: Removes Superscript Characters from the selection.
Before Character: Removes the string before the character inputted into the textbox
e.g. entering \ results in Temp\Test.xlsx from C:\Temp\Test.xlsx
After Character: Removes the string after the character inputted into the textbox as well as the character.
e.g. entering \ results in C:\ from C:\Temp\Test.xlsx.
Hidden Rows: Deletes all hidden rows on the sheet.
Empty Rows: Deletes all empty rows on the sheet.
Rows with only 1 Entry: Deletes all rows with only 1 entry. Can useful when importing text files with page header/footer rows.
Every Nth Row: Deletes every nth row specified by the User in the current selection.
Hidden Columns: Deletes all hidden columns on the sheet.
Empty Columns: Deletes all empty columns on the sheet.
Columns with only 1 Entry: Deletes all Columns with only 1 entry. Useful if column has a header but no data.
Every Nth Column: Deletes every nth column specified by the User in the current selection.
Duplicate Rows: Delete or Find Duplicate Rows based on Values from Column(s). Data to be checked must start in Cell A1.
1) Select the columns that determine duplicate rows.
2) Confirm if the data has a Header row – default is yes. If unchecked, a Header row is inserted with values Data1, Data2, etc…
3) Match Case if the data to be compared is case sensitive.
4) Confirm that duplicates are to be deleted – this keeps only the first row in multiple groups. The default is yes. If unchecked, duplicate rows are grouped together and highlighted.
5) A number of messages appear as the process is completed:
6) Results appear as such
Add Waterfall Chart: From the selection of a horizontal range (titles and values), adds a sheet with a Waterfall or Step Down Graph. Selected range must be 2 rows and at least 2 columns. The final column’s title cannot be a number. The final column value will be recalculated. Buttons on the sheet allow the user to
- Cycle Data Label Formats – 0 to 4 decimal places, $ and $0.00
- Set Labels Above – resets the labels if the values are manually changed in the sheets. It also re-sync’s the hidden secondary axis for the segment values.
- Reverse Colours – toggles colours between green and red e.g. if negative change is favourable.
Basic Chart Formatter: Based on the first selected chart, other charts’ settings can be changed. The Font settings for the following elements can be changed: Chart Title, X Axis, Y Axis. Font settings changed: Bold, Italic, Size and Name. Also, the Y Axis Scale: Min, Max, Crossover, Major and Minor Units can be copied and applied. The user can choose which elements to copy and choose between selected charts or all charts on the worksheet.
Create Basic Histogram: Based on the selected cell down, creates a basic Histogram. The chart’s intervals are fixed to 8 and the interval size is based on the data’s standard deviation. The accompanying Bell shaped curved is based on 2000 “random” numbers with the same standard deviation and mean value as the data from the histogram.
The following (linear) equations can be applied to any number x in the A-B range:
y = C + (x-A)*(D-C)/(B-A) or y =D-(C + (x-A)*(D-C)/(B-A) )
A = the upper range limit
B = the upper range limit
C = the lower scale limit
D = the upper scale limit
The “Flip Bit” is used to adjust the Actual Normalized Number when the smaller the Actual Number is, more favourable it is – as in the case of Safety Incidents. It adjusts the smallest number to be the number closet to 10 i.e. the most favourable. Use 1 as the Flip Bit value when required; otherwise, leave it blank.
The data must be arranged as below (5 columns and at least 3 rows including a title row). The titles can be changed to whatever. Mandatory fields – KPI Limits & Actual Numbers. These can be rates, dollar amounts, integers, etc…
A Sheet is added and a chart with the normalized results is created as shown below. Data can be modified on this sheet.
P Create Petal Chart: Based on user selected range, creates a modified radar chart to compare groups with specific categories. The Petal Chart can provide a clear visual comparison between different groups.
Create Plan vs Actual Chart: Based on user selected 3 row range, creates a simple Plan vs Actual Chart. The columns’ colours are set are automatically: Green for favourable to target and Red for unfavourable. Cell A1 is linked with the chart’s title.
Buttons on the sheet allow the user to
- Cycle Data Label Formats – 0 to 4 decimal places, $ and $0.00
- Reverse Colours – toggles colours between green and red e.g. if negative change is favourable.
Create Simple Chart: Select the chart to copy, size and “simplify”. A dialog box prompts the user to select a range. The chart is copied and sized to fit the range. Most Chart elements are removed to leave just the data series. The simplified chart remains linked to the data source.
Create T Chart: Based on user selected range, creates a modified stacked bar chart to compare positive and negative changes. Useful for creating comparisons between categories that improved or worsened. Colours can be switched based on user requirements. The third column is used to locate the x axis labels. Works best with integers.
Concatenate Range: Inserts the function – EnPConcat which concatenates a range of cells using an optional delimiter.
Delimiter (Optional): user defined separator between elements.
AsShown (Optional): False displays actual values (default) while True displays the text as formatted.
OmitBlanks (Optional: True omits blanks cells while False includes them (default).
Highlight Duplicates by Value: Highlights cells within a selected range based on a cell in the range. An Input Box is used to select the source cell for the comparison. If only 1 cell is found with the value, the cell is highlighted in Blue. Otherwise, found values are highlighted in Green.
Insert Footer: Allows the User to apply a Custom Center Section Footer on all worksheets. If it contains the words confidentiality, secret or classified, the text colour is set to Red.
Insert Table of Contents: Inserts a new sheet with hyperlinks to each sheet and an image of the 1st 5 rows and 11 columns of the sheets.
Split Text Right: For a cell containing carriage returns, Splits the text into cells in newly inserted columns to the right.
Join, Merge and Wrap: Based on the selected cells, merges the cells and text within the cells inserting a carriage return between cell entries.
Add Problem Tracking Sheet: Inserts a new Sheet formatted as a basic Problem Tracking sheet. There are associated PowerPoint functions to create a Problem Tracking chart. The column headings are set up as named ranges allowing the text to be modified and the columns rearranged while maintaining the PowerPoint output to remain the same.
Add SAP: Inserts a new Sheet with an editable SAP based on user input. The user selects the Start and End dates and can choose between Daily and Weekly SAPs. For Weekly SAPS, weeks start on Mondays and the first week contains the user selected Start date. The default Start Date is the current date. The default End Date is 31 days later.
Feature updated to allow Daily or Weekly SAPs to be collapsed to display by month.
Feature updated to include Start and End Dates that drive Taskbars.
Feature updated to allow users to change the Taskbar colours by changing the interior fill colour of 2 cells and clicking an update button.
Feature updated to allow users to include or exclude Sat Sun & Holidays in Days calculation.
The Add Shapes Section contains a number of utilities to add Clip Art and custom shapes. (video overview)
EnP Clips: Allows the user to insert an images and Clip Art found in specific folders located in user’s documentsEnPClips folder. The user can add their own “favourite” images to the exiting folder(s) or add their own folder(s) to the User DocumentsEnPClips folder. EnP Tools comes with a few examples to get you started.
The Custom Shapes menu has 3 specialized functions to add different shapes.
Greater is Favourable: From the selected range of Actuals, returns circular representations of task completion rates based on Plan vs Actual where Actual greater than Plan is favourable. The Progress Pies are pasted to the right or below the selected cells. Targets must be in the adjacent cells above or to the left of the Actuals – both to be entered as percentages.
Less is Favourable: From the selected range of Actuals, returns circular representations of task completion rates based on Plan vs Actual where Actual less than Plan is favourable. The Progress Pies are pasted to the right or below the selected cells. Targets must be in the adjacent cells above or to the left of the Actuals – both to be entered as percentages.
Progress Pie Black: From the selected range of Actuals, returns black circular representations of task completion rates. The Progress Pies are pasted to the right or below the selected cells. The Actuals are to be entered as percentages.
NOTE: Functions will work with horizontal or vertical ranges. Single cell selections will place the Progress Pie to the right.
Cross Over Connector – Vertical: Adds a set of connectors that can be used in flow charts for cross overs. It adds 3 joined MS Office shapes – a straight connector, an arc and a straight line arrow. The straight connector and arrow are meant to be connected to the flow chart symbols. The arc is best moved with the keyboard arrow keys – up and down, side to side. The same connectors are available in Excel.
FTA Symbols: Displays a form from which the User can add symbols to build an FTA chart. The Event/Gate will be pasted into the currently selected cell. If an Event/Gate is selected, the new element will be pasted and connected to the original element.
Add Linked Rectangles: Adds rectangles with text linked to the cell values of the selected range. If the range is horizontal, the rectangles will be placed beneath the selected range as below. If the range is vertical, the rectangles are placed to the right of the range. These rectangles can be used to start the Yamataka building process.
Cascade: Arranges the selected shapes (beginning with the first selected shape) into the basic cascade arrangement of a Yamataka or Waterfall chart. It takes into account whether or not the values in the rectangles are positive or negative and aligns the shapes appropriately. It also horizontally spaces the shapes based on the width of the first shape.
Add Summary Bar: Creates a rectangle with the sum of the values of the selected shapes as it’s text value, sets the height and width relative to the 1st selected shape then aligns it’s bottom to that shape.
Size and Arrange Shapes: A number of functions to size and arrange selected shapes. (video overview)
PowerPoint: Macros providing functionality between Excel and PowerPoint (video overview)
Problem Tracking Charts
The functions provided in this section provide 3 ways to create PowerPoint slides from a row in a specially formatted Excel sheet. Included are 2 mechanisms to allow users to format a sheet to work with the create functions.
The simplest way to use the first 2 functions is to add the specially formatted sheet via the Worksheet Tools – Forms function: Add Problem Tracking Sheet. This function provides a basic sheet that users can immediately start using without employing the “Prep” functions described further down. Each cell in the Title row is named (e.g. PT_01) in order to determine which content goes where in the PowerPoint slide. Note: moving the columns in the spreadsheet is possible; however, the PowerPoint layout is fixed.
Create a Problem Tracking Chart: From a properly formatted sheet, this feature will copy information from the current row and create a specifically formatted PowerPoint presentation. The user will be prompted to save the presentation. A suggested file name is derived from 3 columns containing the named ranges – PT_01, PT_02 & PT_03; however, the user can choose a different name or not to save the file at that time. If the presentation is saved, a hyper-link will be created to the saved file on the exported row. The hyper-link will be attached to the value that resides in the same column as the named range PT_01 in the exported row. A single Image can be transferred but its location on the Excel sheet but in the same column as the named range PT_07. This column on the sheet created by the Worksheet Tools – Forms function: Add Problem Tracking Sheet function is cleverly titled “Image”. If no images are required, the cell can be used for additional text.
Create a Problem Tracking Chart with Image Annotation: Performs as above with one exception regarding the image. Using this function transfers the contents of the cell – any image, call-out, shape, etc… as a single image. Any annotation on the image from the Excel file will transfer to the presentation; however, it will not be editable.
Create a Problem Tracking Chart from Template: Creates a PowerPoint slide from the current row from a EnP Tools problem tracking sheet using a user created template. The Excel sheet must be prepared using the tool “Prep PTC Sheet for PowerPoint Template” (see below). Also, a template file “Custom_EnP_PTC.pptx” must be in the Documents folder. The template file can be created using EnP Tools for PowerPoint under Utilities – Misc Tools: Convert to PTC Template. Images can be in any of the “mapped” Excel columns. The combination of the PowerPoint & Excel functions provides a significant level of flexibility in creating PowerPoint summary slides from Excel. Once a template is created, copying the Excel data to the template is faster than the functions above due to the fact that no rectangles are drawn and formatted.
Prep PTC Sheet for PowerPoint: Allows the user to select columns to be exported to a PowerPoint PTC slide. Opens a form to map specific column cells to the PowerPoint Problem Tracking Chart. This allows any worksheet to be used as a source to create a PowerPoint Problem Tracking Chart. The mapped cells are set up as named ranges allowing the text to be modified and the columns rearranged while maintaining the PowerPoint output to remain the same.
Prep PTC Sheet for PowerPoint Template: Allows the user to select up to 16 columns to be exported to a PowerPoint PTC slide utilizing the user created “Custom_EnP_PTC.pptx” template in the Documents folder. The template file can be created using EnP Tools for PowerPoint under Utilities – Misc Tools: Convert to PTC Template. Images can be in any of the “mapped” Excel columns.
Utilities: A number of misc tools including Picture Insert Tools. (video overview)
There are 2 categories:
1) Picture Insert Tools – utilities that assist in the insertion and placement of images and shapes.
2) Misc Tools
Add Custom List: From the Selected Range, creates a Custom List that can be used with AutoFill. Once you create a custom list, it is added to your computer registry, so that it is available for use in other workbooks. However, if you open the workbook on another computer or server, you do not see the custom list.
Add Sample Chart Data: Adds a block of random numbers with row and column headings to the selected Range. The selected range must be at least 3×3. The resulting sample data will have dates across the top starting from the current date and row labels – Data1, Data2, etc….
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.
Extract Notes: Combines the Workbook Notes as a text file in the Workbook folder. Info extracted – Sheet Name, Cell Address, Cell Value and Note. The text file is placed in the same folder as the Workbook.
e.g. TestFile – Notes.txt
Extract To EnP Clips: Extracts the Selected Shapes to a folder with the Workbook name in the EnPClips – Shape Extracts folder. The extracted shapes can be saved as BMP, GIF, JPG or PNG files.
1) The following Shape Types are not supported – msoShapeTypeMixed, msoComment, msoEmbeddedOLEObject, msoLinkedOLEObject, msoOLEControlObject, msoPlaceholder, msoTextEffect, msoMedia, msoScriptAnchor, msoCanvas, msoDiagram, msoInk, msoIgxGraphic, msoWebVideo, msoContentApp, msoGraphic, msoLinkedGraphic, mso3DModel, msoLinked3DModel
2) Checking the “Do not compress images in file” option provides maximum picture quality but may result in very large file sizes.
Password Tools: Tools to Set, Reset and Store Password Information. If the Workbook is opened Read-Only, the Set Passwords section is disabled as you cannot change the passwords in a Read-Only state. When the form is opened, the current Windows user name is displayed in the textboxes as a password suggestion. The User can type their own password or click the Generate Password button to generate a random 8 character password. Clicking the Clear button will delete the text in the corresponding textbox. Clicking the Set Password button at this time will Reset the password to nothing. If there are characters displayed in the textbox, clicking a Set Password button will set the appropriate password, save the workbook and add the workbook location, name , date, password type and password to the Password Record. View Password Records – opens a text file listing the files and passwords set by EnP Tools. This file is located in EnP Tools folder in the User’s Documents folder – EnP_PSWs.txt
Note: The password types are native to PowerPoint. Setting the Open password encrypts the workbook while setting a Modify password does not. It is not a security feature as it is only meant to protect from unintentional editing.
Set Protection: Lists the worksheets available in the workbook. Upon opening, any Password protected sheets are selected. The buttons allow the user to select specific sheets based on the Protection status. Sheets can be multi-selected using the Shift ot Control keys. The textbox is used to provide Password information for both protecting and unprotecting worksheets. If the textbox is empty, the sheets are protected with no Password when the Protect button is clicked. The “Allow Users to” options set the protection criteria. Clicking the Protect button protects the selected sheets and stores the Protection information and password in the EnP_PSWs file. If the password in the textbox does not match and existing password for a protected sheet, that sheet is skipped until the correct password is provided. The Unprotect button works similarly.
Create Protection Sheet Status: Creates a new sheet that summarizes the protection status of each sheet in the workbook.
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.
- 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.