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 To Height: Pastes the contents of the clipboard to the top left cell of the selected range and resizes it to the height of the range. Command also available as a “right click” menu item.

 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.

Print Range: Based on the selected range, prints the selection scaled to fit a single page.

Print Sheet: Based on the Page Setup, prints the current Sheet.

Print Sheets: Based on the Page Setup, prints the currently selected Sheets.

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.

Workbook Functions:

❶ 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.

Worksheet Functions:

① 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.

Data Prep: 42 Tools to assist in preparing data for processing including 3 built-in Excel functions – Text to Columns, Flash Fill & Spell Check.


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: &amp;lt;tag&amp;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+2010          Hyphen
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

Charting Tools

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

  1. Cycle Data Label Formats – 0 to 4 decimal places, $ and $0.00
  2. 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.
  3. 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 Pareto Chart: Based on user selected range, creates a simple Pareto Chart using frequency and cumulative frequency. Cell A1 is linked with the chart’s title.

Create Dot Chart: Based on user selected range, creates a vertical Dot Chart. The user must select 2 columns and at least 2 rows. The first row is considered a header row.

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.

Create Normalized Radar Chart: Based on the range selected, creates a Radar chart with data normalized to a scale of 10.

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) )
where
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

  1. Cycle Data Label Formats – 0 to 4 decimal places, $ and $0.00
  2. 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.

Label Line Last Data Point: Select the chart to modify. A label will be placed to the right of the last data point of any line series and the Legend (if any) will be removed.

Move and Size Chart: Select the chart to move and size. A dialog box prompts the user to select a range. The chart is moved and sized to fit the range.

Formatting Tools

   Change Case: Changes the Case of the selected Range – except cells with formulas – based on the user choice.

   Column to Multi-Column: Adds a new Sheet with information distributed into the number of required columns based on the selected cells in a column and the inputted number of columns.

Copy Rows Below: Copies and inserts the selected rows below the current selection. Rows, cells or ranges can be selected. This is particularly useful to add new rows to an SAP.

 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).

 Cycle Fill Colours: Cycles the fill colours of the selected shapes through Light Blue, Dark Blue, Light Grey, Orange and White.

  Delete Hidden Rows: Deletes all the hidden rows on the active sheet. Particularly useful when using auto filter.

  Fill Same: Fills blank cells with the value above in the selected range. Works best with values not formulas. Use the Formula to Value tool in Misc Tools to convert formulas to values.

 Format Range: Applies borders and alternating rows fills to the current Selection.

Hide Shapes: Tools to hide and unhide shapes (circles, textboxes, tables, etc…) throughout the Workbook. Shapes must be Tags first.

Add Hidden Tags: Adds the suffix “_Hide” to the Name of the selected shapes to enable the Hide Unhide functionality.

Hide Unhide Shapes: Hides or Unhides Shapes with Names that have the suffix “_Hide” throughout the Workbook.

Remove Hidden Tags: Removes the suffix “_Hide” where applied and Unhides those Shapes if Hidden throughout the Workbook.

Unhide All Shapes: Unhides all Shapes in the Workbook keeping any Names with the suffix “_Hide” assigned.

Highlight Alternate Rows: Highlights alternate rows within the Selected Range.

Highlight Duplicate Cells: Highlights cells within a selected range if there are duplicates.

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.

Highlight Formulas: Highlights cells with formulas in a selected range.

Highlight Max Min: Highlights the max and min values in a selected range.

Highlight Named Ranges: Highlights all named ranges in the workbook.

Highlight Unique: Highlights cells with unique values in a selected range. The highlighting is accomplished with conditional formatting.

  Highlight Remove: Sets the cell fill colour to none in the selected range.

Insert Empty Columns: Within the selected range, inserts an empty column after every nth column as specified by the user.

Insert Empty Rows: Within the selected range, inserts an empty row after every nth row as specified by the user.

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 Down: For a cell containing carriage returns, Splits the text into cells in newly inserted rows below.

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.

 Join and Merge: Based on the selected cells, merges the cells and text within the cells.

 Make Cells Square: Sets the cells in a worksheet to be square based on a row height of 15.

 Merge Same: Merges cells in a column with the same value in the selected range. Works best with values not formulas. Use the Formula to Value tool in Misc Tools to convert formulas to values.

 Reset Used Range: Resets the active area in the current worksheet. Typically used after many rows of data have been deleted.

 Forms

 Add Month Calendar: Inserts a new Sheet with an editable Calendar based on user input.


 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.

Examples:

The Custom Shapes menu has 3 specialized functions to add different shapes.

 Progress Pies can be used to report on the progress of a task or activity.

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. 

Misc Shapes

Add Rectangle: Adds a red Rectangle around the selected cells that can be used to indicate a focus area.

 Add Time Line: Adds or deletes a grouped shape – reverse “S” time line graphic.

 Rounded Rectangle: Adds a red Rounded Rectangle that can be used to indicate focus areas.

  Callout: Adds a red Rounded Callout that can be used to indicate focus areas.

 Approval Box: Adds a Approval sign off box at the current cell location.  The first blank cells are intended for the names and the second row for the dates.

Cross Over Connector – Horizontal

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.

 Yamataka Tools: A set of tools to help build Yamataka / Waterfall charts from simple rectangles:

 Add Shapes and Connectors: Displays the MS Office Autoshapes gallery. Right clicking on a shape and selecting “Lock Drawing Mode” will allow the User to draw multiple versions of the selected shape.

 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.

 Size Proportional: Proportionately sets the height of the selected shapes in relation to the height of the first selected shape and its text while setting the width to be the same as the first shape.

 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.

 Move Text Above:   Creates a text box above the selected rectangle with the rectangle’s text and then deletes the original text in the rectangle.

 Erase Shapes: Erases All Shapes (pictures, graphs, textboxes, autoshapes, etc…) in the selected range.

Size and Arrange Shapes: A number of functions to size and arrange selected shapes. (video overview)

 Same Size: Adjusts the size of the selected Shapes to the size of the last selected Shape.

 Same Height: Adjusts the height of the selected Shapes to the height of the last selected Shape.

 Same Width: Adjusts the width of the selected Shapes to the width of the last selected Shape.

  Bottom Edge Align: Aligns the top edge of the selected Shapes to the bottom edge of the last selected Shape.

 Top Edge Align: Aligns the bottom edge of the selected Shapes to the top edge of the last selected shape.

 Right Edge Align: Aligns the left edge of the selected Shapes to the right edge of the last selected Shape.

 Left Edge Align: Aligns the right edge of the selected Shapes to the left edge of the last selected Shape.

Spacing Vertical: Distributes the Selected Shapes, in order of Selection, below the Last Selected Shape by the number of Points entered.

Spacing Horizontal: Distributes the Selected Shapes, in order of Selection, to the right of the Last Selected Shape by the number of Points entered.

Misc

Grow And Shrink: Expand and Contract the Selected Shapes along the non-rotated height and width axes.

Line Snap: Makes the Selected Lines or Connectors horizontal or vertical based on their slope.

Size Get: Gets the size of the selected shape. To be used in conjunction with the Size Set feature to match sizes.

Size Set: Sets the size of the selected shape. To be used in conjunction with the Size Get feature to match sizes.

Stretch Down: Stretches the bottom edge of the first of two selected shapes to the top edge of the second shape.

Stretch Up: Stretches the top edge of the first of two selected shapes to the bottom edge of the second shape.

Stretch Up: Stretches the top edge of the first of two selected shapes to the bottom edge of the second shape.

Stretch Up: Stretches the top edge of the first of two selected shapes to the bottom edge of the second shape.

Switch: Switches the Positions of the 2 Selected Shapes.

 PowerPoint: Macros providing functionality between Excel and PowerPoint (video overview)

 Range as Picture to PPT – Fit Height: Copies the selected range, pastes it as a picture into a new Slide resized if its Height is greater than Slide Height.

 Range as Picture to PPT – Fit Width: Copies the selected range, pastes it as a picture into a new Slide resized if its Width is greater than Slide Width.

 Range as Picture to PPT – Fit Slide: Copies the selected range, pastes it as a picture into a new Slide resized if required based on its Height or Width whichever fits best.

 Embed Selection on Slide: Copies the selected range, embeds it as an Excel file into a new Slide resized if required based on its Height or Width whichever fits best.

 Chart as Picture to PPT – Fit Slide: Copies the selected Chart, pastes it as a picture into a new Slide resized if required based on its Height or Width whichever fits best.

 Embed Chart on Slide: Copies the selected Chart, embeds it as an Excel file into a new Slide resized if required based on its Height or Width whichever fits best.

 Selected Range Insert Table: Creates a Slide with a Table populated from the selected range but does not fit Table to Slide. Slow based on range size.

 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

 Picture Insert Tools – utilities that assist in the insertion and placement of images and shapes.

 Insert Across a Row: Allows users to multi-select pictures to insert across a row of cells. Upon insert the pictures are sized to the cells.

 Insert and Size to Range: Allows users to insert and resize a picture to a selected range.

 Insert Down a Column: Allows users to multi-select pictures to insert down a column of cells. Upon insert the pictures are sized to the cells.

 Size Cell for Photos: Sizes selected rows and columns to a standard height and width photos.

 Size To Cell: Sizes selected picture to cell beneath.

 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 Random Numbers: Adds random numbers between 0 and 100 to the selected range.

Add Random Word: Add a random word from the Lorem Ipsum text to the selected range.

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.

Copy Row by Colour: From the Selection, copies rows to a new sheet if the rows have any colour filled cells not Conditionally Formatted.

 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.

Create File List: From the selected folder, creates a file list on a new worksheet. Note: the function is not recursive i.e. sub-folders are ignored. The Open hyperlink opens the file.

 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 Tools: Tools to extract and save Media and Notes from Workbooks.

Extract Media to Folders: Extracts copies of the embedded image files and places them in folders. Windows Explorer is opened to the targeted folder.

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.
Notes:
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.

Text To Cells: Places the Selected Shape’s Text in separate cells starting in the cell beneath the Shape’s top left corner and deletes the Shape.

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

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.

 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.

Shapes Stats: Provides the dimensions and position of the selected shape in both points and inches.  A “point” is a unit of measurement that is equal to 1/72 of an inch.

Show Calculator: Opens the MS Calculator.

Toggle Lorem Ipsum: Toggles the selected Shapes’ text between a Lorem Ipsum string and the original text. A simple way to temporarily “hide” confidential information for printing.