Power Utility Pak v6

Add-in tools for Microsoft Excel

PUP v6 Features

PUP v6 contains the most comprehensive set of Excel add-in tools available. This document describes the features available in PUP v6.

General Features

Includes more than 70 general purpose Excel utilities and 50 worksheet functions.
An option to remember the last settings used in PUP dialog boxes.
An option to display the most recently used PUP dialog box by pressing Ctrl+Shift+R.
An option to display a graphic progress indicator for lengthy operations.
Undo is available for most of the operations.
An Auto-unload option for improved memory management (user specifies the maximum number of utility files to keep in memory).
Some of the utilities feature a "stay on top" dialog box, so you can continue working without dismissing the dialog box.
Programmers: The complete VBA source code is available for a very modest fee (US $20).
The VBA code for PUP Worksheet functions can be easily inserted into any workbook (eliminating the need for external links).
Detailed context-sensitive help .
Easy-to-use menu system.
The ability to create a PUP Toolbar.
The ability to quickly access the PUP file folder, view all of the PUP registry settings, and delete all of the registry settings.

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: IMPROVED
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: NEW
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: NEW
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: IMPROVED
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: IMPROVED
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: NEW
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: NEW
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: IMPROVED
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: IMPROVED
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: NEW
Makes it easy to make a chart an exact size, or make all charts the same size.
Convert Chart To Picture: NEW
Makes a copy of a chart as a static picture. This is useful for saving charts that depict various what-if scenarios.
Chart Report: NEW
Produces a detailed report that documents a chart, or produces a report that documents all charts.
Object Align, Size & Space: NEW
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: NEW
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: IMPROVED
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: IMPROVED
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: NEW
Creates a report that describes all cells that use Data Validation.
Conditional Formatting Report:NEW
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: IMPROVED
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: NEW
Displays a toolbar that makes it easy to identify the FaceID property value for toolbar images.

Enhanced Shortcut Menus IMPROVED

As an option, you can choose to display enhanced shortcut menus. Improvement: No longer uses a custom image to identify PUP commands. This solves a potential problem of overwriting the Clipboard when Excel is opened.

Worksheet Cell, Range, Column, or Row: IMPROVED
Adds the following menu items: Print selection, Toggle Word Wrap, Change Case, Format Number, Center Across Selection, Quick Chart, Synchronize Sheets. Enhancement: Added Toggle PUP Infobox Toolbar.
Chart: IMPROVED
Adds the following menu items: Export Chart as Graphic, Chart Data Labeler. Enhancements: Added Convert Chart To Picture and Chart Report.
Excel Desktop:
Adds the following menu items: Minimize All Windows, Tile Windows, Activate a Window, Interactive Hide/Unhide
Sheet Tab:
Adds the following menu items: Sort Sheet Tabs, Delete Empty Sheets, Activate a Sheet, Interactive Hide/Unhide, Hide Sheet.
Workbook Title Bar:
Adds the following menu items: Toggle Vertical Scroll Bar, Toggle Horizontal Scroll Bar, Toggle Grid Lines, Toggle Row and Column Headers, Toggle Sheet Tabs, Toggle R1C1 Reference Style, Hide 'Windows.

PUP Bookmarks

PUP Bookmarks: A handy and easily accessible toolbar that lets you create bookmarks so you can quickly return to a particular workbook, worksheet, or range. If the workbook is not open, it opens for you. The bookmarks you create are saved between sessions. An excellent alternative to Excel's "most recently used file list."

Amusements:

Need a break from crunching numbers? PUP v6 includes:

Video Poker:
Choose from Joker's Wild or Jacks or Better. You can also change the payoffs for the various poker hands.
Dice Game:
Like Triple Yahtzee. You have an option to have four rolls per turn instead of three (your scores will sky-rocket).
Bomb Hunt:
Works just like Microsoft's Minesweeper.
Hangman: IMPROVED
The classic word-guessing game. Includes 700 additional words.
Random Number Generator:
Choose a random number between two specified values. Animated display of the numbers being chosen. Great for office raffles and drawings.