J-Walk & Associates, Inc.

The Utilities

The utilities are described below, by category. To view a screen shot, click the utility's title. For more information about a particular utility, download or view the PUP v6 documentation.

Range Tools

• Text Tools
  Lets you manipulate the text in cells in the following ways: (1) Add text to the beginning, end, or at a specified character position; Remove characters from the beginning, end, or at a specified character position; (3) Change the case of selected text; (4) Remove excess spaces from text; Enhancements: the ability to remove leading spaces or trailing spaces.
• Cell Comment Tools:
  Change the appearance of all comments, search and replace text within comments, remove the user name from comments, or create a listing of all comments.
• Change Sign of Value:
  Fix trailing negative signs (e.g, convert 12.43- to -12.43), make all cells positive, make all cells negative, or reverse the sign of all values.
• Force Values in Cells:
  When you import data, Excel sometimes fails to recognize numbers as numeric data. This utility forces Excel to recognize such data as numbers.
• Select by Value:
  Lets you select cells in a specified range (or an entire worksheet) that meet certain criteria -- for example, select all cells with a value between 12 and 24. Or, select all cells that contain text that ends in "ing." Or, you can select entire rows based on the contents of a column. This utility, for example, provides a quick way to select (and then delete) all empty rows.
• Select Cells by Format:
  Lets you select cells based on their formatting. For example, you can select all cells that contain a yellow background. Once selected, you can apply other formatting to the cells. Note: Excel 2002 and later also provides this feature.
• Go To Min or Max Value:
  This utility selects the minimum or maximum value in a selected range of cells.
• Cell Lock / Sheet Protect Utilities: 
  Makes it easy to lock and unlock specific types of cells in a worksheet (for example, lock all cells that contain formulas). Also simplifies protecting and unprotecting sheets.
• Range Randomize Utilities:
  Lets you: (1) Generate a series of consecutive integers, arranged randomly in a range; (2) Randomly rearrange the values in a range; or (3) Randomly select n cells from a range.
• Swap Ranges:
  This utility makes it easy to swap the contents of two cells or two identically-sized ranges.
• Delete Hyperlinks:
  Enables you to delete hyperlinks from a range, a worksheet, or an entire workbook.
• Insert Sequence Number:
  Makes it easy to insert a unique (and consecutive) value into a cell. You can define any number of sequences, specify a text prefix and/or suffix, and specify the number of digits for the number. For example: Invoice xxxx-2005 (where xxxx represents the sequence number).
• Export Range to File:
  Exports a range to a separate file (either an Excel workbook, a CSV text file, or an HTML file).
• Export Range as Graphic:
  Converts a range (and the graphic objects displayed in the range) to a graphic file (GIF, JPEG, TIF, or PNG format).
• Transform Vertical Range:
  Makes it easy to convert a single column of entries into rows with multiple columns.
• Combine Columns or Rows: 
  This utility combines data in multiple columns (or rows) into a single cell in the column (or row). The user decides what to do with the data that has been combined: Keep it, delete it, or create merged cells. As an option, you can specify one or more characters to insert between the combined data (for example, a space). Enhancements: Added the ability to insert a line break, and the ability to combine cells "as formatted".

Formula Tools

• Math Without Formulas:
  This utility enables you to change values in cells without using formulas. Very flexible, and lightning fast.
• Exact Formula Copy:
  Makes an exact copy of formulas. Range references are not changed.
• Convert Relative and Absolute References:
  Change cell references in formulas to absolute or relative.
• Unapply Names:
  Convert a name reference in a formula to its actual reference (either absolute or relative). For example, the utility converts a formula such as =SUM(Region1) to =SUM(A2:A98).
• Unit Conversion: 
  Easily convert measurements from one unit to another. Very flexible. Easily customizable if you purchase the VBA source code.
• Currency Conversion: 
  Easily convert currencies from one unit to another, using exchange rates downloaded from the Internet. Supports more than 50 world currencies. You can convert a single amount interactively, or convert a range of values. You can update the exchange rate data at any time via the Internet (it takes only a few seconds).
• Error Condition Wizard:
  Makes it easy to add an error condition to a formula or group of formulas. This is useful when you want to avoid displaying error values such as $DIV/0! The wizard walks you through the steps to create the formula you need.
• PUP Worksheet Function Library:
  This utility adds one or more VBA function macros to your workbook. These macros provide you with new worksheet functions that you can use in your formulas. Choose from a list of 50 functions, including the popular XDATE functions.

Formatting Tools

• Superscript/Subscript Formatting: 
  Simplifies the task of applying superscript or subscript formatting to characters in a cell. In addition, you can toggle the Bold and Italic attributes and the case (upper or lower) for alpha characters -- perfect for those who work with chemical formulas.
• Insert Symbol:
  Makes it easy to insert a special character from any installed font. Note: This is primarily for Excel 2000 users; Excel 2002 and later provides a much better implementation of this feature.
• 3D Cell Shading:
  Applies an attractive three-dimensional border effect to a cell or range.
• Alternate Row Shading:
  Applies user-specified shading to alternate rows in a range. Improvements: Avoids the error when attempting to apply conditional formatting to a shared workbook
• Font Reference Sheet:  
  Displays a handy interactive workbook that lets you view the characters in any font installed on your system.

Worksheet Tools

• Toggle Settings:
  Quickly toggle any of the following settings: gridlines, horizontal scrollbar, vertical scrollbar, row and column headers, page breaks, formula view, sheet tabs, status bar, formula bar, full screen display, comment visibility, and object visibility. It's all done interactively, so you can see the effect before you commit to it.
• Set Scroll Area:
  Makes it very easy to set the scrollable range in a worksheet to prevent users from scrolling outside of the appropriate area and getting lost. This utility works by hiding unneeded rows and columns.

• Interactive Zooming:
  Adjust the worksheet zoom level interactively, for the active sheet or for all sheets. Improvements: Smooth zooming, "stay on top" dialog so you can select other sheets or workbooks.
• Save Sheet as HTML:
  Saves the active sheet in HTML format, with many user-specified options. Unlike Excel, it does not produce a bloated file.
• Calculator:
  A handy calculator. Paste the calculated results into a cell, or retrieve a value from a cell for additional calculations.
• Synchronize Sheets:
  Forces all worksheets in the active workbook to display the same range selection and upper-left cell as the active sheet.
• Reset the Last Cell:
  Forces Excel to reset the "last cell" -- the cell that is activated when you press Ctrl+End. Excel has always had a problem keeping track of this.

Workbook Tools

• Insert File at Cursor:
  Insert a complete worksheet, range, or text file into the current worksheet, at the cursor position. Includes an option to copy values only (no formulas), and an option to ignore formatting.
• Interactive Hide / Unhide: 
  Makes it easy to change the hidden status of all open workbooks, or the sheets contained in those workbooks. 
• Sort Sheets:
  A fast and flexible way to rearrange the order of the sheets in a workbook.
• Save With Backup:
  Makes a backup copy of the active workbook at a specified location.
• Create Workbook Contents Sheet:
  This utility adds a new worksheet, which functions as a table of contents for quick navigation to other sheets in the workbook. Choose between hyperlinks, or buttons and macros.
• Customize A New Workbook:
  Creates a new workbook with specially-named sheet tabs. For example, you can create a workbook with sheets named Division-1, Division-2, etc. Or, specify a range that contains your sheet names.
• Filename Lister:
  Many people are surprised to discover that Windows provides no direct way to print a list of the filenames contained in a directory. The Filename Lister utility creates a list of filenames in a worksheet. Improvement: Option to create hyperlinks.

Chart & Graphics Tools

• Export Charts:
  Quickly converts Excel charts to stand-alone GIF, JPEG, TIF, or PNG files.
• Chart Data Labeler:
  Lets you apply labels (contained in a range) to a chart data series -- a feature that is surprisingly missing from Excel. Enhancement: Now ignores data that is hidden as a result of autofiltering.
• Resize Charts:
  Makes it easy to make a chart an exact size, or make all charts the same size.
• Convert Chart To Picture:
  Makes a copy of a chart as a static picture. This is useful for saving charts that depict various what-if scenarios.
• Chart Report:
  Produces a detailed report that documents a chart, or produces a report that documents all charts.
• Object Align, Size & Space: 
  Back by popular demand. This utility was removed from PUP v5, but now it's back. It provides an easy way to precisely align, size, and space a group of objects or embedded charts.
• Object Hide / Unhide:
  An interactive way to hide or unhide charts and other objects on a worksheet.

Printing Tools

• Batch Printing Wizard:
  Print any number of workbooks unattended. The workbooks can be in a specified directory or across multiple directories.
• Print Multiple Selection Wizard:
  Lets you print non-contiguous ranges of cells without page breaks between the separate ranges.
• Copy Page Setup:
  Makes it easy to copy print setting from one worksheet to other worksheets in the same workbook.
• Add Path to Header or Footer:
  Creates a VBA procedure that prints the workbook's full path in the page header or footer -- something that's normally not possible to do. You can also control the font and size.  Note: This utility is primarily for workbooks that will be used in versions prior to Excel 2002. (Excel 2002 and later supports the file path in headers and footers).

Date & Time Tools

• Perpetual Calendar:
  This multi-purpose tool (1) Displays a handy calendar for any month and year; (2) Inserts a graphic image of any calendar month; (3) Creates a new workbook with up to 12 sheets, each displaying a calendar month -- perfect for schedules. Enhancement: (4) The ability to create a simple appointment calendar that use cell comments to store appointments.
• PUP Date Picker Toolbar:
  Displays a handy toolbar that makes it easy to insert a formatted date into a cell, or change the formatting of an existing date (or a range of dates).
• Reminder Alarm:
  Displays a reminder at a specified time of day, or after a specified period of time has elapsed.

Auditing Tools

• Workbook Summary Report:
  Creates a useful report that summarizes many aspects of a workbook.
• Date Report:
  Creates a useful report that summarizes cells that contain dates. This can help you identify potential date-related problems.
• Formula Report:
  Creates a useful report that lists all formulas in a worksheet or workbook. Enhancement: The report includes the cell name for the formula, if any.
• Number Format Report:
  Creates a report that lists all number formats used in a worksheet or in a workbook.
• Workbook Link Finder:
  Lets you track down workbook links. More accurate link identification.
• Data Validation Report:
  Creates a report that describes all cells that use Data Validation.
• Conditional Formatting Report:
  Creates a report that describes all cells that use Conditional Formatting
• Worksheet Map: 
  Creates a handy map that visually identifies cells of various types. Includes a new option to code the formula cells by complexity.
• Compare Ranges:
  Lets you easily identify the differences between two worksheets or ranges.
• Name Lister:
  Lets you works with names in ways that are otherwise not possible.
• PUP InfoBox Toolbar:
  Displays a handy toolbar that lists key information about a selected cell or selected range. Long-time users of Excel may find a similarity to the old InfoBox window option. Enhancements: Displays Alignment for a cell selection, and displays the number of unique entries for a range selection. In addition, you can click buttons to select a subset of the selected range (e.g., all formulas that return a value).

VBA & Macro Tools

• VBA Project Summary Report:
  Creates a summary report of the VBA procedures in a workbook. As an option, the report can include details for all controls contained in the UserForms.
• Remove All Macros:
  This utility will remove any or all of the following: VBA modules, UserForms, Excel 5/95 dialog sheets, or Excel 4 XLM macro sheets.
• Toggle FaceID Toolbar:
  Displays a toolbar that makes it easy to identify the FaceID property value for toolbar images.