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.
Convert Relative and Absolute References:
Change cell references in formulas to absolute or relative.
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.
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. 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
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.
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.
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".
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.
Easily convert measurements from one unit to another. Very flexible. Easily customizable if you purchase the VBA source code.
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 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).
To Min or Max Value:
This utility selects the minimum or maximum value in a selected range of cells.
Interactive Hide / Unhide:
Makes it easy to change the hidden status of all open workbooks, or the sheets contained in those workbooks.
Forces all worksheets in the active workbook to display the same range selection and upper-left cell as the
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).
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.
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
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.
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.
Displays a reminder at a specified time of day, or after a specified period of time has elapsed.
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.
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.
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:
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).
Quickly converts Excel charts to stand-alone GIF, JPEG, TIF, or PNG files.
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.
Creates a report that describes all graphic objects on a worksheet. Useful for locating hidden objects.
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.
Creates a useful report that summarizes cells that contain dates. This can help you identify potential date-related problems.
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.
Workbook Link Finder:
Lets you track down workbook links. More accurate link identification.
Lets you works with names in ways that are otherwise not possible.
Lets you easily identify the differences between two worksheets or ranges.
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.
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.
This command is available at the top level of the Ribbon, making it easier than ever to manage your bookmarks.
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.