Power Utility Pak v7

Add-in tools for Microsoft Excel 2007

PUP v7 Features

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

PUP augments Excel with more than 70 general purpose Excel utilities and 53 worksheet functions. You also get six non-serious amusements, for when you need a break from number-crunching.
Easy-to-use tab-and-ribbon user interface integrates seamlessly with Excel 2007.
Includes options to remember the last settings used in PUP dialog boxes, display the most recently used PUP dialog box (by pressing Ctrl+Shift+R), and display a graphic progress indicator for lengthy operations. You can also add frequently used PUP commands to your Quick Access Toolbar.
An auto-unload option for improved memory management (you specify the maximum number of utility files to keep in memory).
Single-level Undo is available for most of the operations.
The VBA code for PUP Worksheet functions can easily be inserted into any workbook (eliminating the need for external links). PUP v7 is not required to use these functions, so you can share your workbook with others who don't have a PUP license.
Detailed context-sensitive help .
And it's a great learning tool. The complete VBA source code is available for a very modest fee (US $20).

The Utilities

The utilities are described below, arranged by the Ribbon group in which they appear. Click a utility name to see a screen capture of its dialog box. For more information about a particular utility, download or view the PUP v7 documentation. If you're upgrading from PUP v6, see the list of removed features.

Cells and Ranges Group

Formula Tools

Convert Relative and Absolute References:
Change cell references in formulas to absolute or relative.
Unapply Names: CHANGED
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). Unfortunately, this utility is now less functional than in the past. It will not run if your workbook contains identical names (for example, two identical sheet-level names on different sheets). Working with identical names was far too error-prone.
Exact Formula Copy: IMPROVED
Makes an exact copy of formulas. Range references are not changed. This utility now handles a range that contains a mixture of array formulas and non-array formulas.
Error Condition Wizard: IMPROVED
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. This utility now includes an option to create the formula using the new Excel 2007 IFERROR function.
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.
Calculator:
A handy calculator. Paste the calculated results into a cell, or retrieve a value from a cell for additional calculations.
PUP Worksheet Function Library: IMPROVED
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 53 functions, including the popular XDATE functions. Removed two functions because they are not relevant for Excel 2007: CELLCOLORINDEX and CELLFONTCOLOR. Added five new functions: CELLCOLOR, CHAR2, CODE2, FONTCOLOR, and SAYIT. Click here for more information.

Modify Cells and Ranges

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

Math Without Formulas:
This utility enables you to change values in cells without using formulas. Very flexible, and lightning fast.

Delete Hyperlinks:
Enables you to delete hyperlinks from a range, a worksheet, or an entire workbook.

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.

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

Format

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.
Alternate Row Shading: IMPROVED
Applies user-specified shading to alternate rows in a range. The color choices are now the colors from the active workbook's document theme.
3D Cell Shading: IMPROVED
Applies an attractive three-dimensional border effect to a cell or range. When applying 3D formatting to a range, you now have the option of not applying a background color.

Convert

Unit Conversion:
Easily convert measurements from one unit to another. Very flexible. Easily customizable if you purchase the VBA source code.
Currency Conversion: IMPROVED
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). This utility uses a new (and more reliable) data source.

Select

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: IMPROVED
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. Added a new option to select cells based on their style (something that Excel can't do).
Go To Min or Max Value:
This utility selects the minimum or maximum value in a selected range of cells.

Workbooks and Sheets Group

Worksheet Tools

Interactive Hide / Unhide:
Makes it easy to change the hidden status of all open workbooks, or the sheets contained in those workbooks.
Synchronize Sheets:
Forces all worksheets in the active workbook to display the same range selection and upper-left cell as the

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

Toggle Settings: CHANGED
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. The Toggle Settings dialog box no longer has an option to show/hide the Task Pane. It does, however, have an option to show/hide the Selection and Visibility pane.
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.

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

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.
View Activeworkbook's Folder: NEW
This simple, but very useful command, opens an Explorer window that shows the files in the active workbook's directory.

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.

Date and Time

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. The calendar picture is now rendered using the active workbook's theme colors and font.
Insert a Live Calendar: NEW
Inserts a small calendar into an 8-row by 7-column range. The dates are calculated using formulas, so you can enter a new month and year in the top cell and the calendar updates automatically.
Date Picker: CHANGED
Makes it easy to insert a formatted date into a cell, or change the formatting of an existing date. The Date Picker is now a "stay-on-top" dialog box rather than a floating toolbar. However, it has the same functionality as the toolbar version. In fact, it has more functionality: you can choose to enter the date with formatting, the date only, or the formatting only.
Reminder Alarm:
Displays a reminder at a specified time of day, or after a specified period of time has elapsed.

Printing

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: IMPROVED
Makes it easy to copy print setting from one worksheet to other worksheets in the same workbook. This utility has been completely rewritten, and now copies header/footer images.

Font Reference Sheet: NEW
Displays a handy interactive workbook that lets you view the characters in any font installed on your system.

Import / Export

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

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.

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.
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, with an option to create hyperlinks. When importing a list of files, you can now specify that the list include the files in all subdirectories.

Charts and Graphics Group

Charting Tools

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:
Resize Charts:
Makes it easy to make a chart an exact size, or make all charts the same size.
Convert Chart To Picture: CHANGED
Makes a copy of a chart as a static picture. This is useful for saving charts that depict various what-if scenarios. The grayscale option is no longer available (because it looked terrible).
Export Charts:
Quickly converts Excel charts to stand-alone GIF, JPEG, TIF, or PNG files.
Chart Report:
Produces a detailed report that documents a chart, or produces a report that documents all charts.

Object Tools

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 Report: NEW
Creates a report that describes all graphic objects on a worksheet. Useful for locating hidden objects.

Auditing Group

Miscellaneous

PUP InfoBox: CHANGED
Displays a handy stay-on-top (and resizable) dialog box that lists key information about a selected cell. This utility no longer includes the option to display information about the selected range. For some reason, this option consistently caused Excel 2007 to crash.

Reports

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.
Workbook Summary Report:
Creates a useful report that summarizes many aspects of a workbook.
Number Format Report:
Creates a report that lists all number formats used in a worksheet or in a workbook.
Data Validation Report:
Creates a report that describes all cells that use Data Validation.
Conditional Formatting Report: IMPROVED
Creates a report that describes all cells that use Conditional Formatting. The report format has been modified so it's a sortable table. It also includes the new Excel 2007 conditional formatting options.
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.

Utilities

Workbook Link Finder:
Lets you track down workbook links. More accurate link identification.
Name Lister:
Lets you works with names in ways that are otherwise not possible.
Compare Ranges:
Lets you easily identify the differences between two worksheets or ranges.
Worksheet Map: CHANGED
Creates a handy map that visually identifies cells of various types. Includes a new option to code the formula cells by complexity. The floating toolbar (used to navigate between the map and the workbook) has been replaced by a stay-on-top dialog box.

PUP Bookmarks Group

Bookmark Tools

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 (and much more flexible) alternative to Excel's most recently used file list.

Create a Bookmark: CHANGED
This command is available at the top level of the Ribbon, making it easier than ever to create a new bookmark.
Edit Bookmarks: CHANGED
This command is available at the top level of the Ribbon, making it easier than ever to manage your bookmarks.
Activate Bookmarks: CHANGED
This command is available at the top level of the Ribbon, making it easier than ever to activate a bookmark. A new option lets you use Ctrl+Shift+B to display a bookmark list at the cursor location.

Power Utility Pak Group

PUP Shortcut Menus

As an option, you can choose to display enhanced shortcut menus. You can quickly turn this feature on and off from the PUP v7 Ribbon.

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, toggle PUP InfoBox, and toggle page break display (new)
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.

Amusements:

Video Poker: IMPROVED
Choose from Joker's Wild or Jacks or Better. You can also change the payoffs for the various poker hands. The win/loss chart is now displayed in a pop-up dialog box.
Dice Game:
Similar to Triple Yahtzee. With 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:
The classic word-guessing game. Includes 4,200 words.
Sudoku Puzzle Generator: NEW
Generates a 9x9 Sudoku puzzle (with solution).
Random Number Generator:
Choose a random number between two specified values. Animated display of the numbers being chosen. Great for office raffles and drawings.

Removed Features

Following is a list of features that were present in PUP v6, but have been removed from PUP v7.

Create a PUP Toolbar
Support for custom toolbars in Excel 2007 is extremely limited. You can, however, easily add frequently-used PUP commands to your Quick Access Toolbar.
Chart Shortcut Menus
Excel 2007 no longer supports custom shortcut menus for charts, so if you choose the PUP Enhanced Shortcut menu options, right-clicking a chart will have no new menu items.
Toggle FaceID Toolbar
This feature, intended for Excel developers, is no longer useful in Excel 2007.
Interactive Zoom
Zooming a worksheet is now a simple matter, using the Zoom controls in the Excel status bar.
Object Hide/Unhide
Excel's new Selection and Visibility pane makes this utility obsolete.
Insert Symbol
Excel's Insert Symbol tool is much better.
Add Path to Header or Footer
This utility is obsolete because Excel has this feature.
PUP Worksheet Functions
Removed two functions because they are not relevant for Excel 2007: CELLCOLORINDEX and CELLFONTCOLOR.