Power Utility Pak v7.2

The Spreadsheet Page - PUP v7 Utilities

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.

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. 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.
  • 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 ConversionIMPROVED
    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.
© Copyright 2017, J-Walk & Associates, Inc.
Privacy Policy