Home

PUP v6 User`s Manual - The Spreadsheet Page

1. 2 Discard the cards you don t want by clicking them You can discard between zero and five cards The Keep All button discards no cards The Discard All button discards all cards 3 Click Get New Cards to replace the discarded cards with new ones Your winnings if any will be displayed Options e Click the Options button to expand or contract the dialog box e Choose the amount of your bet from 1 to 5 points This represents the amount bet on each hand e Choose your game Jacks or Better or Joker s Wild Each game has its own payoff schedule Click Payoffs to see the payoff amounts for various hands e Click Chart to display a historical chart of your winnings e Click New Game to reset the score to 0 Changing the payoffs If you d like to change the amounts paid for each hand open the file named video poker payoff xls this file is located in the directory where PUP v6 is installed Make the changes then save and close the file The change will take effect the next time the Video Poker game is opened Video Poker Tips e If for some reason you need to hide the fact that you re playing Video Poker click the Hide button Or you can just press Escape To resume the game where you left off select the PUP v6 Amusements Video Poker command e Note that Jack s or Better requires a pair of Jacks or better to win The minimum winning hand for Joker s Wild is a pair of Aces Dice Game To begin playing the
2. 4 Click Delete After performing these steps the PUP Date Picker toolbar will be re created the next time it is invoked Reminder Alarm The Reminder Alarm utility lets you specify a time of day and pops up a reminder message and or sounds an alarm at the specified time Using Reminder Alarm 1 Select Reminder Alarm from the PUP v6 Date amp Time Tools menu 2 Specify the time at which you want to be reminded You can set the alarm time in either of two ways e To change the hour select Hr and then click the arrow buttons to change the minutes select Min and then click the arrow buttons e To specify an alarm time in terms of minutes from now enter the number of minutes in the Minutes from now box and click Set to update the alarm display 3 If desired select Display alarm time in the title bar If this option is selected Excel s title bar will display the time that the alarm is set for 4 Select the type of alarm from the drop down list 5 Choose OK Reminder Alarm Tips e f Excel is closed before the alarm goes off the alarm will be canceled e f Excel is not active when the alarm goes off it will be activated or it s icon will flash in the Windows task bar If you chose an audible alarm it will sound even if Excel is not active e Be aware that the alarm is not 100 reliable For example the alarm cannot be displayed or sounded if Excel is busy e g running a macro The alarm goes off only 7
3. By downloading or receiving on disk or CD ROM the software you agree to abide by the following provisions SOFTWARE VERSIONS Power Utility Pak Version 6 PUP exists in two versions Trial Version and Fully Licensed Version LICENSE J Walk and Associates grants you a license to use and copy the software program s and documentation subject to the limitations described here The license for the Trial Version expires 30 days after it has been installed The license for the Fully Licensed Version does not expire DISTRIBUTION OF SOFTWARE Any and all distribution of the Fully Licensed Version is prohibited Not for profit distribution of Trial Version is permissible on condition that the software is not altered in any way and is distributed in its entirety Electronic transfer renting leasing loaning selling or distributing of the Trial Version for profit in any form including but not limited to bulletin board distribution magnetic or optical medium distribution is not permitted without the written consent of J Walk and Associates Furthermore modification or alteration of the software including but not limited to decompiling disassembling reverse engineering or creation of works arising from the software is prohibited DISTRIBUTION EXCEPTION The PUP FUNCTION LIBRARY component of PUP copies VBA macros to an Excel workbook file The PUP license allows such workbooks to be distributed to others However the VBA macros may not be include
4. Inthe Number Format Report dialog box select the option that corresponds to the scope of the report 3 Click OK The report will be generated in a new workbook Number Format Report Tips e Keep in mind that the report includes only used number formats Your workbook may contain custom number formats that are not used in any cells Data Validation Report This utility generates a report that describes all the cells that contain Data Validation 82 PUP v6 User s Manual Using Data Validation Report 1 Select Data Validation Report from the PUP v6 Auditing Tools menu 2 Inthe Data Validation Report dialog box select the option that corresponds to the scope of the report 3 Click OK The report will be generated in a new workbook Data Validation Report Tips e In the report some cells may be empty That s because the Data Validation options are not available for some Types For example the In cell Dropdown option is valid only when the Data Validation Type is List Conditional Formatting Report This utility generates a report that describes all the cells that contain Conditional Formatting Using Conditional Formatting Report 1 Select Conditional Formatting Report from the PUP v6 Auditing Tools menu 2 Inthe Conditional Formatting Report dialog box select the option that corresponds to the scope of the report 3 Click OK The report will be generated in a new workbook Conditi
5. LASTINROW SHEETOFFSET UNIQUEITEMS VINTERPOLATE Statistical amp Math COUNTAVISIBLE COUNTBETWEEN MAXALLSHEETS MINALLSHEETS SELECTONE STATICRAND SUMALLSHEETS SUMVISIBLE TOPAVERAGE Text CONTAINS DOLLARTEXT EXTRACTELEMENT INSERTSTRING ISLIKE PARSENAME REMOVESPACES SCRAMBLE When you use Excel s Insert Function dialog box these new functions appear in the User Defined category Notes Depending on your security setting you may receive a warning when your workbook is opened This warning appears for all workbooks that contain macros If you choose to disable macros when you see the warning you will not be able to use the PUP worksheet functions in your formulas Keep in mind that the functions are available only within the workbook that was active when you added the functions If you need to use the same functions in a different workbook you ll need to add the functions to that workbook as well Using the PUP Worksheet Function Library The PUP Worksheet Function library allows you to add and remove PUP functions from your workbook Adding PUP functions to your workbook 1 2 3 4 Activate the workbook in which you want to use the functions Select PUP Worksheet Function Library from the PUP v6 Formulas menu Choose the Add Functions tab of the Insert PUP Function Library dialog box Choose an item from the Function category list and the available functions in that category are displayed in the l
6. characters removed If you want to limit the operations to selected cell that contain text select Skip non text cells If this option is not checked cells that contain values will also be modified Click Apply to modify the selected cells Removing spaces 1 2 3 4 Select the cells that you want to modify In the Text Tools dialog box select the Remove spaces option In the Remove drop down select the option that corresponds to the spaces that you want to remove Click Apply to modify the selected cells Note Selecting All Excess Spaces is equivalent to applying Excel s TRIM function to the cells Deleting characters by type 1 2 3 4 Select the cells that you want to modify In the Text Tools dialog box select the Delete characters option In the Remove all drop down select the option that corresponds to the type of characters that you want to remove Click Apply to modify the selected cells Note Selecting Non printing characters is equivalent to applying Excel s CLEAN function to the cells Text Tools Tips The Text Tools dialog box remains open when you click Apply so you can make other modifications to the selected cells Or you can select another range of cells to work with After you click Apply you can choose Undo from the Edit menu or press Ctrl Z to reverse the effects Note that only one level of undo is available Text Tools works only with non empty cells Therefore you can
7. pay the shipping handling fee Your credit card is approved online and you download the files immediately For online ordering go to this URL http j walk com ss pup pup6 Fax or mail orders For fax or mail orders use the PUP v6 Order Form 115 PUP v6 User s Manual PUP Version 6 Order Form The most efficient way to order PUP v6 is via the Internet If you prefer you can print this order form and send it by mail or fax Name Company Address Address City State Zip Phone No copies 39 95 per copy Source code 20 00 per copy Subtotal Sales Tax Shipping amp handling 5 00 order Amount enclosed Note The s h fee applies regardless of the delivery method Send me PUP v6 via regular mail Send me download instructions via e mail Your email address If paying with a credit card Credit Card Number Expiration Date Signature Arizona residents only Please add 7 5 to your total order for sales tax If paying by check or money order funds must be in U S dollars No exceptions If ordering five or more licenses contact J Walk amp Associates for discount information Mail Fax J Walk amp Associates 520 531 1632 P O Box 68797 Tucson AZ 85737 U S A 116 PUP v6 User s Manual End User License Agreement J WALK AND ASSOCIATES SOFTWARE LICENSE AGREEMENT POWER UTILITY PAK VERSION 6
8. 1x column of scoring buttons 105 Scoring Category Ones Twos Threes Fours Fives Sixes Three of a Kind Four of a Kind Full House Small Straight Large Straight Five of a Kind Chance Bonus Points Condition At least one die that shows 1 At least one die that shows 2 At least one die that shows 3 At least one die that shows 4 At least one die that shows 5 At least one die that shows 6 At least three of the dice match At least four of the dice match Two of one kind and three of another kind At least four consecutive numbers Five consecutive numbers All of the dice match Any combination of dice PUP v6 User s Manual Points The sum of the number of dice that show 1 The sum of the number of dice that show 2 The sum of the number of dice that show 3 The sum of the number of dice that show 4 The sum of the number of dice that show 5 The sum of the number of dice that show 6 The sum of all the dice The sum of all the dice 25 points 30 points 40 points 50 points The sum of all the dice In addition to the points described above you receive bonus points if the sum of the Ones through Sixes buttons is e Greater than or equal to 63 1x column 35 bonus points e Greater than or equal to 126 2x column 70 bonus points e Greater than or equal to 189 3x column 105 bonus points In other words you will achieve bonus points if you assign at least three
9. 3 Choose Close to close the dialog box Toggle Settings Tips The Task Pane toggle option is not available if you re running Excel 2000 Click Defaults to change the settings to Excel s default settings Click Apply to all sheets to apply the selected worksheet settings to all worksheets in the active workbook These settings are Grid Lines Vertical Scrollbar Horizontal Scrollbar Row amp Column Headers Page Breaks Formula View Display Zeros These settings can also be changed in Excel s Options dialog box choose Options from the Tools menu Changes made in the Options dialog box are not visible until you close the dialog box Set Scroll Area The Set Scroll Area utility makes it easy to hide all rows and columns except those in a specific range Hiding unused rows and columns is useful if you want to limit scrolling to a specific range of cells Using Set Scroll Area Adding a scroll area 1 2 3 4 Select the range of cells for the scroll area Choose Set Scroll Area from the PUP v6 Worksheet Tools menu Verify that the Scroll area range box displays the desired scroll area range Or change it if desired Choose OK Removing a scroll area To remove a scroll area applied with this utility 1 Choose Set Scroll Area from the PUP v6 Worksheet Tools menu 2 Choose Yes to unhide all rows and columns in the worksheet 49 PUP v6 User s Manual Set Scroll Area Tips The Set Scroll Area
10. Chart sheet The file name for each chart is derived from the name of the Chart object or the Chart sheet To rename a chart or Chart sheet select the chart name and click Rename You will then be prompted for a new name for the Chart object or Chart sheet This option is available only when a single chart is selected in the list Select the Export format for the exported charts If you would like a warning if the chart file already exists select Warn if file exists The dialog box displays the directory name in which the files will be saved Use Change directory to change this directory 10 Click OK to begin exporting the files Export Charts Tips If a worksheet is active when you invoke the Export Charts utility the dialog box displays a list of all chart objects on the active worksheet If a chart sheet is active 61 PUP v6 User s Manual when you invoke the Export Charts utility the dialog box displays a list of all chart sheets in the active workbook The Export Charts utility uses the graphic export filters supplied with Excel or Microsoft Office If you are unable to export to a particular file format re run the Setup program for Excel or Microsoft Office The GIF and JPEG file formats are commonly used Typically the GIF format will produce a much smaller file than JPEG The dimensions of the exported file will match the chart dimensions To help identify a particular chart select it in the list and clic
11. ContentsHyperlink from the Style name drop down list e Click Modify e Make your formatting changes and click OK e Click OK to close the Style dialog box Customize a New Workbook The Customize New Workbook utility creates a new workbook with customized worksheet names For example you can create a workbook with 12 worksheets each with a name that corresponds to a month January February etc Using Customize a New Workbook To create a workbook with customized sheet names 1 Choose Customize New Workbook from the PUP v6 Workbook Tools menu 2 Specify the option upon which to base the worksheet names Month names Day names etc 3 The right panel of the Customize a New Workbook dialog box changes depending on your choice in Step 2 Make your choice s 4 Click OK to create the new workbook Customize a New Workbook Tips e When creating a workbook in which the sheet names correspond to months or days you can choose to abbreviate those names Jan instead of January for example e To create sheet names that contain text and a numerical sequence use the A Series option This enables you to create sheet names such as Division 1 Division 2 etc e f you ve created any custom lists using Excel s Tools Options command and the Custom Lists tab these lists appear when you choose the Custom List option e For optimal control over the sheet names place your sheet names in a range and use the Data in a range
12. Example 1 Your worksheet has many cells that display percentage with no decimal places You want to change all cells that have a percentage format to display two decimal places 16 PUP v6 User s Manual 1 Select any cell that has percentage formatting with no decimal places Choose Select Cells by Format from the PUP v6 Range Tools menu Because only a single cell was selected the Range box shows 1 65536 i e the utility will operate on the entire worksheet The Base Cell box shows the address of the selected cell Place a checkmark next to Format and remove any other checkmarks In other words the utility will select all cells that match the base cell s number formatting attribute 6 Click OK If Display Summary is selected a message box will tell you how many cells qualify Click OK to select the cells 8 When the dialog box closes all of the cells that contain the base cell s number format will be selected You can then change the number format for the selected cells Select Cells by Format Usage Example 2 Your worksheet has many title cells that contain the following formatting attributes Arial font size 14 Bold center aligned You would like to change the formatting of all such cells 1 Select any cell that has the current formatting Choose Select Cells by Format from the PUP v6 Range Tools menu Because only a single cell was selected the Range box shows 1 65536 i e the utility will oper
13. Excel XLS workbook file with one worksheet Text file CSV A text file with values separated by a comma This is a common file format used by many applications Simple HTML An HTML file readable in a web browser Options allow most but not all of the formatting to be preserved The file is significantly smaller than that produced by the Complex HTML option Complex HTML An HTML file readable in a web browser This is the format produced when you use Excel s Save as Web Page command All of the formatting is preserved and the file can be opened in Excel with no loss of information Using Export Range to File Before using Export Range to File select the range of cells that you want to export 1 2 3 4 Choose Export Range to File from the PUP v6 Range Tools menu In the Export Range to File dialog box verify that the Range to export is correct Select one of the four file formats The right section of the dialog box varies depending on the file format you select Specify additional options and choose OK A file selection dialog box appears Specify a filename and location and click Save Export Range to File Tips The contents of hidden rows or columns are not exported unless you choose the Complex HTML option If you export the range as an Excel workbook and don t select the Convert formulas to values checkbox the exported file may contain link formulas If you export the range as a text file and you select th
14. Sequence Data The Insert Sequence Number dialog box displays a list of all defined sequences For each sequence the following information is listed e Sequence The name of the sequence e Inc The value by which each sequence number will be incremented e Previous The sequence number that was last inserted 22 PUP v6 User s Manual e Date Used The date on which the last sequence number was inserted Creating a New Sequence When the Insert Sequence Number dialog box first appears it will display none defined You must define a sequence before you can use it To define a new sequence click New Sequence to display the Create New Sequence dialog box This dialog box contains five fields 23 PUP v6 User s Manual Field Name Description Sequence name A descriptive name to help you identify the sequence Start number The value for the first number in the sequence Increment The value by which each sequence number will be incremented No of digits The number of digits to use for the number If the number uses fewer digits it will be preceded by leading zeros Prefix optional Text that will appear before the sequence number Suffix optional Text that will appear after the sequence number After you ve specified the fields for your sequence click OK to return to the Insert Sequence Number dialog box Insert Sequence Number Tips If you change your mind after inserting a sequence number select Undo from the Edi
15. When you are finished click Close Changing the Locked or Hidden status of cells 1 Inthe Cell Lock Sheet Protect Utilities dialog box click the Cell Lock Hide tab 2 Inthe Select worksheets box select the worksheets that you want to modify Sheet names preceded by an asterisk denote protected sheets You must unprotect these sheets before you can change the Locked or Hidden status of cells 3 Specify the type of cells that you want to modify by making one or more selections in the Cell Type section 4 Select a Locked Status option If you don t want to change the Locked status choose No change 5 Select a Hidden Status option If you don t want to change the Hidden status choose No change 6 If you would like to see a message that displays the number of cells affected choose Show summary message 18 T PUP v6 User s Manual Click Apply Note Refer to Cell Lock Sheet Protect Tips for more information about Locked and Hidden cells Protecting worksheets 1 In the Cell Lock Sheet Protect Utilities dialog box click the Sheet Protect Unprotect tab In the Select worksheets box select the worksheets that you want to protect Sheet names preceded by an asterisk denote protected sheets Select the options that denote actions that will be allowed when the worksheet is protected If you re using Excel 2000 most of these options will not be available If you want to protect the selected sheet
16. a formula The value indicates the relative complexity of the formula Yellow with V The cell contains a numeric value or a date Red with E The cell contains an error Black with T The cell contains text Gray with L The cell contains a logical value TRUE or FALSE White with The cell is empty 87 PUP v6 User s Manual Using Worksheet Map 1 Activate the workbook that you want to map Select Workbook Map from the PUP v6 Auditing Tools menu In the Workbook Map dialog box select the scope for the map either Active sheet only or All sheets in the active workbook 4 If you would like the map to indicate the complexity of the formulas select Include formula complexity values see below 5 Click OK to create the map s The map s will be generated in a new workbook In addition a new toolbar named Worksheet Map will be displayed About formula complexity values When the Include formula complexity values checkbox is not checked the map will display an F for each formula cell When this option is selected the map will display a numeric value that provides some indication of the complexity of the formula This value is the sum of e The number of sets of parentheses in the formula e The number of plus operators e The number of minus operators e The number of multiplication operators e The number of division operators e The number of exponentiation operators e The number
17. a summary report Step 4 This will let you know if any errors occurred while printing e f a workbook to be printed contains automatic macros you may want to specify the Disable Auto macros option Step 4 For example you may print a workbook that has a Workbook_Open macro that displays a prompt when the file is opened Disabling automatic macros will eliminate this prompt Print Multiple Selection Wizard Normally when you print a multiple selection Excel inserts a page break between each range in your selection The Print Multiple Selection Wizard lets you print non contiguous ranges without a page break between each range Print Multiple Selection wizard automatically creates linked picture objects on a new worksheet These object are essentially snapshots of the ranges you specify Because they are linked to the ranges they will reflect any changes you make to the original ranges Using Print Multiple Selection Wizard Start by selecting the first range or a multiple selection of ranges that you want to print This step is optional Select Print Multiple Selection Wizard from the PUP v6 Printing Tools menu Use the Next and Back buttons to make your choices in the three steps or click Finish to accept the defaults Additional details are provided below Step 1 e To add additional range items to the list click the Add button This displays another dialog box in which you specify the range to add 69 Step 3 P
18. all comments so the formatting matches a specified comment e Find and replace text contained in comments e Add or remove the user name from all comments Using Cell Comment Tools Before using the Cell Comment Tools utility activate a worksheet that contains at least one cell comment 1 Choose Cell Comment Tools from the PUP v6 Range Tools menu Select the appropriate tab in the Cell Comment Tools dialog box Select the Scope for the operation Choose Active sheet to work with comments on the active sheet only Choose All sheets to work with all comments in the active workbook 4 If you would like to see a summary of the comments that were changed for each operation select Show summary 5 Make the desired changes see descriptions below 6 Click Exit to close the Cell Comment Tools dialog box Note Changes made by this utility cannot be undone View or listing comments 1 Inthe Cell Comment Tools dialog box select the View List tab 2 Select the appropriate viewing option in the View comments frame This affects all comments regardless of the Scope setting 3 To create a list of comments choose the location of the comment list by selecting In a new workbook or In a new sheet Then click Create comment list to create a list of all cell comments 11 PUP v6 User s Manual Formatting comments 1 Inthe Cell Comment Tools dialog box select the Format tab 2 Click Specify the comment cell to select a ce
19. and filename in the header or footer of a printed page The Add Path to Header or Footer utility inserts a VBA macro procedure that is executed automatically whenever you print or preview your workbook This VBA procedure inserts the workbook s full path and file name into the header or footer position you specify Using Add Path to Header or Footer Adding the VBA procedure To add the VBA macro procedure to your workbook 1 Select Add Path to Header or Footer from the PUP v6 Printing Tools menu Select the Add Path tab and specify the page location and style of the header or footer Click the Format tab and specify the font size and formatting for the header or footer text 4 Click OK A VBA macro procedure will be added to your workbook This procedure which is executed before the workbook is printed or previewed adds the workbook s full path and file name to the specified header or footer position This also applies to new sheets that you add later Removing the VBA procedure It is not possible to override this automatic behavior for individual sheets Changing the header or footer manually will have no effect To restore normal functionality 1 Select Add Path to Header or Footer from the PUP v6 Printing Tools menu 2 Select the Remove Path tab 3 Click Remove If you re familiar with VBA you can also edit the VBA code manually The code inserted by this utility resides in the code module for the ThisWorkbook o
20. case there s an excellent chance that you ll learn something about VBA that you didn t know before What can t you do with it You may not use the PUP v6 source code to create commercial applications And you may not use the PUP v6 source code to create add ins that are used within your company The PUP license is actually very simple Anyone who uses PUP v6 must be a licensed user And this includes using applications that are derived from PUP v6 If you have any questions about using PUP source code please contact sales j walk com 114 PUP v6 User s Manual The PUP v6 Trial Version The Trial Version of PUP v6 is intended for evaluation purposes only The Trial Version is fully functional and is not crippled in any way You can use the Trial Version for 30 days After 30 days you must delete the files or purchase the licensed version How to Order PUP v6 The price for a single user Power Utility Pak license is US 39 95 Discount pricing is available for five or more license You can purchase Power Utility Pak Version 6 using any of the following methods e Online Safe and efficient using a secure server Requires a credit card No shipping and handling fee e Mail Use check money order or credit card 5 00 shipping and handling fee e Fax Requires a credit card 5 00 shipping and handling fee Online ordering Ordering online is the most efficient method and is also the least expensive because you don t
21. chart amp graphic tools deal with charts and worksheet graphic objects Export Charts The Export Charts utility lets you quickly save any number of charts as separate graphics files The charts that you export can be embedded on a worksheet or stored in separate chart sheets The utility supports the following graphic file formats File Format Description GIF Graphics Interchange Format JPEG Joint Photographic Experts Group TIFF Tagged Image File Format PNG Portable Network Graphics Note The Export Charts utility uses the graphic export filters supplied with Excel or Microsoft Office All of these file formats may not be installed on your system Using Export Charts 1 Activate the worksheet than contains the charts to export Or activate a Chart sheet to export the chart stored there Select Export Charts from the PUP v6 Chart Tools menu If the active sheet is a worksheet the Export Charts dialog box lists the embedded charts on the active sheet If a Chart sheet is active the dialog box lists all Chart sheets Select the charts that you want to export To select multiple charts press Ctrl while you click To identify a particular chart in the list select the chart name and click Scroll to This will scroll the worksheet such that the selected chart is displayed in the upper left corner of the window If you re working with Chart sheets the button will display Go to and clicking it will activate the selected
22. chart sheets hidden sheets will not be changed Excel 5 95 dialog sheets cannot be zoomed The amount of zooming in a worksheet or chart does not affect how it is printed To adjust the printed output use the Page Setup command on the File menu If you use this utility when a chart sheet is active it will automatically turn off the Size With Window option a command under the View menu If you re having problems with Out of Memory errors keeping all sheets zoomed to 100 may reduce this problem Save Sheet as HTML The Save Sheet as HTML utility exports the active worksheet to an HTML file An HTML file is readable in a browser Options allow most but not all of the formatting to be preserved 50 PUP v6 User s Manual Compared to using Excel s File Save as Web Page command this utility creates a significantly smaller HTML file Using Save Sheet as HTML Before using this utility activate the worksheet you want to export 1 2 3 4 Choose Save Sheet as HTML from the PUP v6 Worksheet Tools menu Specify the formatting options Choose OK A file selection dialog box appears Specify a filename and location and click Save Save Sheet as HTML Tips Data in hidden rows or columns is not exported If the worksheet is displaying gridlines the HTML table will have borders If the worksheet is not displaying gridlines the HTML table will not have borders Borders applied to cells or ranges are not trans
23. contains the desired range Or change the range if necessary Select the appropriate Convert option Click OK Convert Relative amp Absolute References Tips e If the results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z 33 PUP v6 User s Manual Unapply Names The Unapply Name utility works with formula cells and converts named references to their actual cell addresses Using Unapply Names Before using Unapply Names select the range of cells that contain formulas that you want to convert The selection can include formula cells and non formula cells Only the formula cells that contain the names you specify will be modified 5 Choose Unapply Names from the PUP v6 Formula Tools menu Verify that the Range box contains the desired range Or change the range if desired Select the names to be replaced in the Name s to replace box Use the All button to select all names in the Name s to replace list box or use the None button to deselect all names Specify the type of reference Absolute references or Relative references to be used for the substituted cell or range addresses Click OK A message box will tell you the number of formulas that were modified Unapply Names Tips If the results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z When you create a formula by pointing to a cell or range Excel always inserts
24. dice game choose Dice Game from the PUP v6 Amusements menu How to Play The rules for the dice game are similar to those for Triple Yahtzee Following is a brief summary 104 PUP v6 User s Manual The goal is to obtain a high score Each turn consists of three rolls of five dice Use the Roll Dice button to roll the dice After the first and second roll you can discard any number of dice by clicking the appropriate die The next roll will replace the discarded dice A complete game consists of 39 turns Basic Scoring After the third roll click one of the unused buttons in the Scoring section There are 39 scoring buttons arranged in three columns x1 x2 and x3 The scoring button you click corresponds to the dice that are showing For example if you have four of a kind click a button in the Four of a Kind category You may however prefer to assign it to a different category As you gain experience playing the game strategies will emerge In the later stages of the game you may be forced to assign the dice to a button that results in a score of 0 Once a scoring button is clicked the value appears on the button and you cannot assign any subsequent turns to that button Clicking a scoring button in the x1 column gives a normal score Clicking a button in the x2 column gives double the normal score Clicking a button in the x3 column gives triple the score The table below summarizes the scoring points shown are for the
25. dice to each of the Ones through Sixes categories Five of a Kind Bonus Points If you ve used all three of the Five of a Kind scoring buttons additional Five of a Kind rolls will result in 100 points each This is in addition to the score resulting from clicking a scoring button 106 PUP v6 User s Manual Options e Click the Options button to expand or contract the dialog box e f you choose the Four rolls checkbox each turn consists of four rolls of the dice This option can result in significantly higher scores e Click High Scores to view the highest scores for the game Initially this will be empty If after finishing a game it is among the top five scores that score will be entered as a high score Dice Game Tips e If for some reason you need to hide the fact that you re playing Dice Game click the Hide button Or you can just press Escape To resume the game where you left off select the PUP v6 Amusements Dice Game command e f you accidentally click the wrong button while placing your score click Undo You can then select another scoring button Bomb Hunt To begin playing Bomb Hunt choose Bomb Hunt from the PUP v6 Amusements menu Bomb Hunt is patterned after the Minesweeper game winmine exe that s included with Microsoft Windows If you know how to play Minesweeper you ll find that Bomb Hunt is very similar The object of the game is to discover the bombs hidden in the playing field Th
26. exit Excel and re start Windows If possible don t run any other applications while you use Excel This problem is not caused by the Power Utility Pak per se However if you ve loaded many of the PUP v6 utilities you might try unloading them by using the PUP Options command on the PUP v6 menu You may find that restarting Windows several times per day solves this type of problem Upgrading to a more recent version of Windows such as Windows 2000 or Windows XP may also help Also it s a good idea to keep tabs on which add in programs are installed use the Add Ins command on the Tools menu In addition you may have add ins in your XLStart directory These add ins are loaded every time Excel starts and may not even be listed in the Add Ins dialog box Add ins consume memory and resources and there is no need to load them if you don t use them Look to see whether you really need these programs to be loaded at startup or contact your System Administrator if you you re unfamiliar with this process A utility or function isn t working correctly Although every effort was made to ensure that the Power Utility Pak is error free it s possible that you may encounter an error under some situation For example you may see an error message that seems inappropriate or a utility simply doesn t perform as it should The first step is to check the PUP v6 Program Updates web site http www j walk com ss pup pup6 The site may contain an updated
27. in mind that the object that contains the link may be hidden e In some cases a link shown in the Links list box may be too long to display completely In such a case move the mouse pointer out of the list box and then point to the item in the list box The complete text will display in a pop up window e Excel provides some built in tools for working with links Select Links from Excel s Edit menu 84 PUP v6 User s Manual The PUP v6 Name Lister utility is also helpful for tracking down and deleting erroneous names For chart links the Workbook Link Finder has a limitation It cannot identify links contained in Secondary Value Axis titles or Secondary Category Axis titles 85 PUP v6 User s Manual Name Lister The Name Lister utility lets you view all names defined in a workbook sorted by the following categories Category Description All names Every name defined in the active workbook Workbook level names Names defined at the workbook level Sheet level names Names defined at the worksheet level These names are preceded by the worksheet s name and an exclamation point e g Sheet1 MyName Hidden names Names that are hidden and do not appear in Excel s Define Name dialog box Linked names Names that are defined in terms of a range on a different workbook Erroneous names Names that contain an erroneous reference These names often cause a phantom link problem Excel prompts you to update links
28. installed on your system It also shows the first 255 characters from that font Using Font Reference Sheet To create a font reference sheet 1 Select Font Reference Sheet from the PUP v6 Formatting Tools menu 2 Anew single sheet workbook is displayed 3 Use the controls at the top to change the font the size or the bold property In addition you can type some sample text into cell D2 Font Reference Sheet Tips e In order for the drop down lists to work macros must be enabled e The actual file is an Excel template font reference xlt which is stored in your pup6 directory e To view the VBA macros in this template press Alt F11 and select the VBA project for font reference Toggle Settings The Toggle Settings utility makes it easy to adjust a variety of worksheet and application settings interactively You can change any of the following settings and see the effect immediately Sheet Settings Other Settings Comments and Objects Cell grid lines Sheet tabs Comment display Vertical scrollbar Status bar Object display Horizontal scrollbar Formula bar Row and column headers Page Breaks Formula View Full Screen mode Windows in taskbar Task Pane not available in Excel 2000 48 PUP v6 User s Manual Using Toggle Settings 1 2 Choose Toggle Settings from the PUP v6 Worksheet Tools menu In the Toggle Settings dialog box adjust the settings as desired You will see the result immediately
29. it if necessary 3 Inthe Output range field specify a cell for the upper left cell of the transformed data This cell can be on the same worksheet in a different worksheet or in a different workbook 4 If the records in the input column are delimited by a blank cell choose Blank cell delimits records If the records are not delineated by a blank cell use the spinner to specify the number of rows per record 5 Click OK 27 PUP v6 User s Manual Transform Vertical Range Tips e If the results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z e The original column of data remains intact e The Data to be transformed range and the Output range need not be in the same worksheet or in the same workbook e The minimum number of rows per record is 2 and the maximum is 256 Combine Columns or Rows The Combine Columns or Rows utility combines data in multiple columns or multiple rows into a single cell For example you can combine the data in columns A F and put it all into column A You decide 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 Using Combine Columns or Rows Start by selecting the data that you want to combine If you re combining columns the selection should be at least two columns wide If you re com
30. mmmm d yyyy ddd mmm d yyyy mmm dd yy ddd m d mmm d dmmm yyyy d m yy d m yyyy dd mm yy dd mm yyyy dd mmmm yyyy dddd d mmmn yyyy ddd d mmm yyyy dd mmm yy ddd d m dmmm yyyy mm dd yyyy mm dd mmm yy mmm yyyy mm yyyy m mm mmm mmmm ddd Example Aug 2 2005 8 2 05 8 2 2005 08 02 05 08 02 2005 August 02 2005 Tuesday August 2 2005 Tue Aug 2 2005 Aug 02 05 Fri 8 2 Aug 2 2 Aug 2005 2 8 05 2 8 2005 02 08 05 02 08 2005 02 August 2005 Tuesday 2 August 2005 Tue 2 Aug 2005 02 Aug 05 Tue 2 8 2 Aug 2002 08 05 2222 08 05 Aug 02 Aug 2005 08 2005 8 08 Aug August 2 Tue 76 PUP v6 User s Manual dddd Tuewday yy 05 yyyy 2005 serial number 38566 e Choosing the serial number formatting option removes all date formatting from the cell and displays the date s serial number In other words the cell is no longer considered a date cell Therefore you cannot use the PUP Date Picker toolbar to apply a different date format you must use Excel s Format Cells dialog box e f you change your system s regional settings to a locale that uses a different date separator the PUP Date Picker toolbar s format list will continue to display the previous date separator To update the toolbar to display the current regional date separator 1 Select View Toolbars Customize 2 Inthe Customize dialog box select the Toolbars tab 3 Scroll down and select PUP Date Picker
31. operators such as and parentheses and Excel worksheet functions For example to divide the number 12 by each cell in the selected range enter the following expression 12 The expression below uses Excel s ROUNDUP function to round each cell up to two decimal places ROUNDUP 2 Exact Formula Copy Normally when you copy and paste a cell that contains a formula the relative cell references are adjusted in the copied formula The Exact Formula Copy utility makes an exact copy of a range Relative references in the copied formulas are not adjusted Using Exact Formula Copy Before using Exact Formula Copy select the range of cells that contain the information that you want to copy The selection can include formula cells and non formula cells All cells will be copied Choose Exact Formula Copy from the PUP v6 Formula Tools menu 2 Verify that the Copy from box contains the desired range Or change the range if desired 3 Specify the Copy to location a single cell which represents the upper left cell of the destination range 4 If you also want to copy the cell formatting place a checkmark next to Copy formatting Click OK Exact Formula Copy Tips e Ifthe results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z 32 PUP v6 User s Manual e The Copy from and Copy to ranges need not be in the same worksheet or in the same workbook e This
32. option 59 PUP v6 User s Manual Filename Lister Many people are surprised to discover that Windows provides no direct way to print a list of filenames in a directory The Filename Lister utility solves that problem The Filename Lister utility generates a list of files in a specified directory The list is created in a new workbook In addition to the filenames the list includes the file size and the date and time of each file The list also displays the names of the subdirectories in the specified directory but files within the subdirectories are not listed Using Filename Lister 1 Select Filename Lister from the PUP v6 Workbook Tools menu 2 Inthe Filename Lister dialog box click Browse to display the Browse for Folder dialog box In the Browse For Folder dialog box select the directory If you would like the file extensions listed separately from the filenames choose Separate filename and extension 5 If you would like the filenames to be displayed as hyperlinks choose Create hyperlinks 6 Click OK The filename list will be created in a new worksheet It will consist of three columns or four columns if you selected the Separate filename and extension option Filename Lister Tips e The list can be sorted by any of the columns e The size column shows the file size in bytes To convert to Kbytes divide the value by 1 024 60 PUP v6 User s Manual The Utilities Chart amp Graphic Tools The
33. select entire rows or columns Note however that you cannot use Text Tools to add text to empty cells If Skip non text cells is checked the utility works only on cells in the selection that contain text as opposed to values If left unchecked the utility works with cells that contain either text or values Blank cells and cells that contain formulas are always skipped When removing spaces from text the All excess spaces option converts multiple spaces to a single space and removes all spaces from the beginning and end of the text 10 PUP v6 User s Manual e The Text Tools utility is appropriate only for text in worksheet cells For example you cannot use the Text Tools utility to make a chart label upper case e The Text Tools utility works only with text entered directly into cells and formulas are ignored For example the Text Tools utility will have no effect on a cell that has a formula such as UPPER A1 even though that formula displays text in its cell e f you re using PUP v6 s Enhanced Shortcut Menus option the shortcut menu for a range selection includes a command to change the case of text e To modify value and formula cells you can use the Math Without Formulas utility Cell Comment Tools The Cell Comment Tools utility provides several ways to work with comments contained in cells You can e Change the way comments are displayed e Generate a listing of all comments e Change the formatting of
34. sheet by selecting it from the list Interactive Hide Unhide Hide or unhide workbooks or sheets interactively See Interactive Hide Unhide Hide Sheet Hides the active sheet Equivalent to the Hide command on the Format Sheet menu 99 PUP v6 User s Manual Enhanced Shortcut Menus Workbook Title Bar The new shortcut menu commands listed below appear when you right click a workbook s title bar Note If the workbook window is maximized the title bar will not be visible You can access the Workbook Title Bar shortcut menu by right clicking the Excel icon at the far left of the Menu bar directly to the left of the File menu Menu item Toggle Vertical Scroll Bar Toggle Horizontal Scroll Bar Toggle Grid Lines Toggle Row and Column Headers Toggle Sheet Tabs Toggle R1C1 Reference Style Hide Window Description Toggles the display of the vertical scroll bar Equivalent to the Options command on the Tools menu with the View tab selected Vertical scroll bar checkbox Toggles the display of the horizontal scroll bar Equivalent to the Options command on the Tools menu with the View tab selected Horizontal scroll bar checkbox Toggles the display of the grid lines Equivalent to the Options command on the Tools menu with the View tab selected Gridlines checkbox Toggles the display of the row and column headers Equivalent to the Options command on the Tools menu with the View tab sele
35. sheet tab and select the range The ranges are printed in the order you select them You can change the order of the ranges in Step 1 by using the arrow buttons In Step 2 specify the Print setting option print settings include headers footers margins page orientation and so on If you select Use the print settings from the current workbook there will be a delay since copying print settings is notoriously slow in Excel For maximum control over the output select Activate the worksheet but don t print it in Step 3 Use Excel s print preview feature to ensure that the output appears as you want it You can adjust the positioning of the linked pictures Since the ranges are stored as linked picture objects changes you make to the original ranges will also appear in the linked pictures Excel imposes a limit on the size of linked pictures If you select a range that exceeds this limit you will see a message to that effect Proceed by selecting multiple ranges instead of one large range 70 PUP v6 User s Manual Copy Page Setup The Copy Page Setup utility makes it easy to transfer page settings from one sheet to other sheets in the same workbook Using Copy Page Setup 1 Activate the worksheet or chart sheet that contains the page setup setting that you want to copy Select Copy Page Setup from the PUP v6 Printing Tools menu In the Page Setup dialog box verify the sheet listed as Copy the Page Setup setti
36. that refer directly to the formula cell same sheet only The name if any of the cell that contains the formula 81 PUP v6 User s Manual Using Formula Report 1 Select Formula Report from the PUP v6 Auditing Tools menu 2 Inthe Formula Report dialog box select the scope for the formula report either Active sheet only or All sheets in the active workbook 3 Click OK The report will be created in a new worksheet Formula Report Tips e The rows of the formula report can be sorted by any column For example you can sort by the Locked column to quickly identify the formulas which are not locked e Each formula is listed in standard format and in R1C1 format R1C1 format is useful for identifying copied formulas Sorting the report using the RC Formula column will place all copied formulas together in the list e The All Precedents and Direct Precedents columns contain a count of cell precedents in the same sheet only e The All Dependents and Direct Dependents columns contain a count of cell precedents in the same sheet only e The Locked and Hidden attributes have no effect unless the worksheet is protected e f your workbook contains many formulas generating a formula report may take a few minutes Number Format Report This utility generates a report that describes the number formats used in a worksheet Using Number Format Report Select Number Format Report from the PUP v6 Auditing Tools menu 2
37. the following versions of Microsoft Excel e Excel 2003 for Windows e Excel 2002 for Windows e Excel 2000 for Windows PUP v6 does not work with Excel 97 or earlier versions and it does not work with any version of Excel for Macintosh A previous version PUP 2000 is available for Excel 97 users Installing PUP v6 Installing PUP v6 for the first time is a two step process 1 Unpack the files from the downloaded exe file or from the PUP v6 CD ROM 2 Start Excel and install the pup6 xla add in file These steps are described below Unpacking the files You will acquire PUP v6 by downloading it from the Internet or on a CD ROM In either case you will have a exe executable file that when executed unpacks the PUP v6 files Double click the exe file to execute it and extract the files PUP v6 User s Manual Installing the add in After the files are unpacked you can choose to have the PUP v6 add in loaded automatically whenever you start Excel Or you can simply load it when you need it To install the Power Utility Pak add in from within Excel 1 Choose Add Ins from the Tools menu 2 Inthe Add Ins dialog box place a checkmark next to Power Utility Pak v6 3 Click OK The Power Utility Pak will be installed and Excel will display a new menu PUP v6 Tips e lf Power Utility Pak v6 does not appear as one of your choices in the Add Ins dialog box use the Browse button to locate pup6 xla By default the pup6
38. the name for a named cell or range reference whether you like it or not This can cause a problem if you later copy the formula The Unapply Names utility can convert these names to cell references For every formula in your selection this utility checks every name that s selected in the Name s to replace list box If your workbook has many names this can be very slow For faster processing select only the name that you know are used in the formulas in the selected range If you use Excel s Accept labels in formulas option be aware that labels used in your formulas are not actual names Therefore this utility will not convert these labels to cell or range references To view or change this setting choose Options from the Tools menu and select the Calculation tab The Unapply Names utility will not work if your worksheet uses Transition Formula Entry i e a 1 2 3 compatible mode To change this setting choose Options from the Tools menu and select the Transition tab Note however that changing this mode will also convert all of the names used in your formulas to standard references 34 PUP v6 User s Manual Unit Conversion The Unit Conversion utility makes it easy to convert values between common measurement units without using formulas or functions You can enter a value directly or you can quickly convert a range of cells from one unit to another For example you might have a range of cells that contain values mill
39. the number of rows and columns for the two ranges must be identical 3 Click OK to swap the two ranges Swap Ranges Tips e If the results of this operation are not what you expected choose Undo from the Edit menu or press Ctrl Z e The original formatting is also swapped e Formulas in the two ranges remain intact and continue to refer to the original cells e The cells or ranges can reside on the same sheet on different sheets or in different workbooks Swapping entire rows or columns is also supported The only restriction is that the two ranges may not have any cells in common e If you make a two area multiple selection before invoking the Swap Ranges utility both of the range address will appear in the Swap Ranges dialog box To make a multiple selection 1 Select the first range 2 Press and hold down the Ctrl key 3 Select the second range Delete Hyperlinks The Delete Hyperlinks utility converts hyperlinks in cells to plain text With this utility you can delete the hyperlinks in e Anentire workbook e The active worksheet e Aspecified range of cells 21 PUP v6 User s Manual Using Delete Hyperlinks Before using Delete Hyperlinks select the range or worksheet that you want to operate on 1 Choose Delete Hyperlinks from the PUP v6 Range Tools menu 2 Specify the scope Entire workbook Active worksheet or the specified Range 3 Click OK to delete the specified hyperlinks Delete Hyp
40. two operations with a single command e t saves a backup copy of your workbook to a specified directory e It saves your worksheet to its original location Normally saving a workbook to a backup location is a cumbersome procedure because Excel remembers the backup location as the workbook s location When you save your file again it is saved to the backup location unless you remember to use the Save As command on the File menu to redirect the save back to its original location Using Save With Backup 1 Activate the workbook to be saved and backed up Select Save With Backup from the PUP v6 Workbook Tools menu Specify the location for the backup file use Browse to specify a drive and directory The location can be a removable device a network directory or a different directory on your hard drive 4 If you d like the backup file to use bak for the file extension select the Use BAK extension checkbox This option will not affect the file name at the original location 5 Click OK to save the workbook to the backup location and then back to its original location Save With Backup Tips e f you last saved your file to a floppy disk this utility will not let you make a backup It s not a good practice to save files to a floppy disk except for backup purposes e You won t be able to use this utility if you haven t yet saved your file that is it has a name like Book1 e Saving to a floppy disk is much slower
41. utility help prevent users from scrolling away from the active area of a worksheet and getting lost You can select entire rows or columns for the scroll area A scroll area cannot consist of multiple noncontiguous ranges This utility does not use VBA s ScrollArea property Rather it works by hiding rows and columns Interactive Zooming The Interactive Zooming utility lets you magnify or reduce your worksheet or chart sheet interactively and apply the same zoom factor to all other worksheets or chart sheets Using Interactive Zooming 1 2 Choose Interactive Zooming from the PUP v6 Worksheet Tools menu Select a zoom percent from the list of presets or use the scrollbar to fine tune the zoom percent Click Fit selection to automatically zoom such that the current range selection fills the entire window Click Apply to all worksheets to apply the current zoom factor to all other worksheets in the active workbook If a chart sheet is active this button will read Apply to all chart sheets and clicking it will apply the current zoom factor to all other chart sheets in the active workbook Click Close to change the zoom level Interactive Zooming Tips The Interactive Zooming dialog box is a stay on top dialog box This means that you can select other sheets while the dialog box remains on screen The minimum zoom percentage is 10 and the maximum is 400 When you click Apply to all worksheets or Apply to all
42. when you open the workbook but no links seem to exist By sheet Names that refer to cells on a specific sheet you select the sheet of interest Using Name Lister 1 2 Activate a workbook that contains one or more names Select Name Lister from the PUP v6 Auditing Tools menu In the Name Lister dialog box select a name category from the list of option buttons The names of that type if any will display in the Names in list box When By sheet is selected use the drop down box to select a sheet The list box will display the names defined on the selected sheet Select a name in the Names in list box and the name s definition will display below the list box To create a summary listing of all names in the workbook click Report The report will be generated in a new workbook To select a cell or range that corresponds to the selected name click Go to To delete the selected name click Delete To delete all of the names shown in the list box click Delete All If the All Names category option is not selected the Delete All button will delete only the names shown in the selected name category 86 PUP v6 User s Manual Name Lister Tips When you click Go to be aware that not all names refer to cells or ranges In some cases Clicking this button will display a message indicating that the name cannot be activated When you choose the By sheet option the last option in the drop down list is no sheet When you
43. xla add in file is in a directory named C Program Files pup6 However the product can be installed in any directory e The Add Ins command does not appear on the Tools menu if a workbook is not open e You can also load Power Utility Pak by using the Open command on the File menu open the file named pup6 xla However if you use this method there is no direct way to unload the file Therefore it s best to use the method described above Removing PUP v6 To remove the entire Power Utility Pak add in from memory 1 Choose Add Ins from the Tools menu The Add Ins command does not appear on the Tools menu if a workbook is not open 2 Inthe Add Ins dialog box remove the checkmark from the Power Utility Pak v6 item 3 Click OK The preceding steps unload the pup6 xla add in file and all of the utilities that are currently loaded If you exit Excel without reinstalling the add in Power Utility Pak will not be loaded automatically the next time you start Excel Removing PUP v6 s files All of the PUP v6 files are stored in a single directory on your computer After uninstalling the pup6 xla add in as described above you can delete the entire PUP v6 directory By default this directory is c program files pup6 Removing PUP v6 s Registry entries PUP v6 stores its settings in the Windows Registry You can remove these setting at any time 1 Choose About Power Utility Pak from the PUP v6 menu 2 Click the Registry Entries bu
44. you use that information to deduce the location of the bombs e If for some reason you need to hide the fact that you re playing Bomb Hunt click the Hide button Or you can just press Escape To resume the game where you left off select the PUP v6 Amusements Bomb Hunt command Hangman To begin playing Hangman choose Hangman from the PUP v6 Amusements menu How to Play Hangman is a classic word guessing game The goal is to guess the word by choosing letters If you guess a letter correctly all occurrences of the letter are displayed If you guess a letter that s not contained in the word a new body part is added to the unfortunate gentleman on the left When you ve guessed all of the letters the word is fully exposed and you win If the skeletal remains of the body are completely revealed you lose Words are chosen randomly from a list of 4 200 words It s possible but unlikely that the same word may be chosen twice in one session Hangman Tips e f for some reason you need to hide the fact that you re playing Hangman click the Hide button Or you can just press Escape To resume the game where you left off select the PUP v6 Amusements Hangman command e By default words of random length are chosen You can work with words of a specified length by choosing from the Word length dropdown Typically shorter words are more difficult to solve e To make the game more challenging choose the Incorrect vowels
45. 7 PUP v6 User s Manual when Excel is in any of the following modes as indicated on the status bar Ready Copy Cut or Find You can set more than one alarm If more than one alarm is set only the alarm set last will display in the title bar and subsequent alarm times will not be displayed in the title bar The alarm is not actually set until you click OK the Set button simply changes the time display in the dialog box To set multiple alarm times you need to run the utility one time for each alarm If the current time displayed when the utility is invoked is not accurate this means that your system s clock is wrong Use the Windows Control Panel to change your system s clock If you change your system s clock when a reminder alarm is set the alarm time will not be reliable The alarm sound is stored in a file named alarm wav located in the PUP v6 directory If you prefer a different sound you can substitute a different WAV file The file however must be named alarm wav 78 PUP v6 User s Manual The Utilities Auditing Tools The utilities in the category deal with locating and fixing problems in your workbook Workbook Summary Report The Workbook Summary Report utility produces a report with key information about the active workbook The report is created in a new workbook You can choose to include any or all of the following information e Workbook Summary Summary information about the workbook e Nam
46. Conversion The Unit Conversion utility can be used in either of two modes Convert a single value Convert a range of cells You can either overwrite the original data or specify a range for the converted values Converting a single value 1 oa F WON 7 Choose Unit Conversion from the PUP v6 Formula Tools menu In the Unit Conversion dialog box select the Convert a Single Value tab Use the Units category drop down to select the unit category Use the From list box to select the measurement unit that you are converting from Use the To list box to select the measurement unit that you are converting to If either of the units selected in Step 4 or Step 5 are metric units you can specify a Metric prefix by using the drop down lists below the From and To list boxes Enter the value to be converted in Input box on the left side of the dialog box The converted value will appear in the Converted value box The information is updated automatically whenever you change the value or make a change in any of the other drop down lists or list boxes Click Cancel to close the dialog box Converting a range of values Before using the Unit Conversion utility select the range of cells that contains the value to be converted 1 2 3 4 Choose Unit Conversion from the PUP v6 Formula Tools menu In the Unit Conversion dialog box select the Convert a Range tab Verify that the Source range is correct and change it if nec
47. Power Utility Pak Version 6 Add in Tools for Microsoft Excel User s Manual PUP v6 Copyright 2005 J Walk amp Associates Inc PUP v6 User s Manual Contents PUP Version 6 Introduction and Overview cccccceeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeees 5 REQUIFEMENINS aeie aer naear aena aaa aa sav aa a aa Aaaa te ae AE aa aeaaea a naaeesderdeeaseeatacen 5 installing PUF Voros aAa ENEA ER EEA ENNER EARE ESTARE E AEREE ERE E NARAR 5 REMOVING PUP V6 sirean aaran rR ENEE ARARA EANA ENER E EAR EEA ASEARA NEEE A ARRENA EAS 6 PUP yO Optoma iin r a e Sane eee ace ec dele ccc ce ccc a ae ta en Deed eee cee e aE E ceed cee aaa aaaea EDan 7 General Usage TIPS curieri enaena OANE SKEARE b KNER O CENONE NAANA KAATA OA eN KA a RESEN 8 The Utilities Range Tools ssssssssnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnna 9 Text TOONS iiidid ancara e naaa aaa apa t naa aariaa aada inaen aateitigiens 9 Cell Comment TOOIlS sweetie cieceesesceccetce cae means inier anaana anen aaa an eaaa ceedeesdeesucententeieeese diceceeenerieess 11 Change Sign of Values asssssueseennnnennnnnnnnnnnnnnnnnnunnnnnnnnnnnnnnnnnnnnnnnunnnnnnnnnnnnnnnnnannnnnnnnnnnnnnn nnmn ne 12 Force Values in Cele a rra a C aa e aaa e ara a aaraa ae aE Ea Eaa aa raaa Eae aiaa eSak 14 Select by Value van sasarane neare naaraan aAa ANENA AEAEE EANET Anaa E A TARRENI A ERAR 14 Select Cells by Formats cic i2secceccccieteescetceeeesdtcce
48. This utility enables you to change values in cells without using formulas You can apply any of the following mathematical operations to each cell in a range e Addition e Subtraction e Multiplication e Division e Exponentiation e Rounding e Apply an Excel function e Apply a custom expression The mathematical operation is performed using a value function or custom expression you specify Depending on the option you choose the cells values are either changed or replaced by formulas Using Math Without Formulas Start by selecting the range of cells that you want to modify The selection can consist of a single cell a range a discontinuous range entire rows or columns or the entire worksheet 1 Choose Math Without Formulas from the PUP v6 Formula Tools menu 2 Verify that the range displayed is the range you intend to modify 3 Tochoose a mathematical operation select the appropriate item from the Operation list 4 Depending on the Operation selected you then enter an operand select a function or enter an expression The Operations are described in the following table 29 Operation Addition Subtraction Multiplication Division Exponentiation Rounding Function Custom PUP v6 User s Manual Description Adds the operand to each cell in the range Subtracts the operand from each cell in the range Multiplies each cell in the range by the operand Divides each cell in the ra
49. UP Function Library dialog box You re looking for a function that manipulates text so choose Text from the Function category list Click the PARSENAME item to select it At this stage you could select additional functions from the list by pressing Ctrl and then clicking the other function names Click the Add button to add the VBA macro to your workbook Click Close to close the PUP Function Library dialog box You can then insert a formula such as the following PARSENAME A1 3 The PARSENAWME function definition is stored in your workbook file and is saved when you save your workbook If you need to use the PARSENAME function in a different workbook you ll need to re run the PUP Worksheet Library utility PUP Worksheet Function Library Tips When adding functions to your workbook you can select multiple functions in the list box by pressing the Ctrl key while you click the function names To select a group Shift Click the first function scroll down and then Shift Click the last function These techniques also work when removing functions using the Remove Functions tab To get more information about a particular function select the function in the list and click the Function help hyperlink After you ve added PUP worksheet functions to your workbook you can use the functions in your formulas The easiest way to insert a function is to use Excel s Insert 41 PUP v6 User s Manual Function dialog box sel
50. UP v6 User s Manual To remove a range item from the list select the range item and click Remove To change the order of the ranges items in the list select a range item and click the Up or Down arrows When Activate the selected range is selected the selected range item is selected in the workbook The linked picture items are inserted on a new worksheet The print settings for this new worksheet are determined by you choice in Step 2 If you choose Specify new print settings you can click Page Setup to select the print settings you desire If you select the Add a blank row between ranges option the linked picture objects will have a space equal to one row height Your choice in Step 3 determines what will happen when you click Finish The worksheet that contains the linked picture objects will be activated printed or printed and deleted Print Multiple Selection Wizard Tips To select multiple ranges with a mouse press Ctrl while you select the ranges with your mouse You can do this before you invoke the utility or while adding a range after clicking the Add button To select multiple ranges with the keyboard press F8 and select a range Then Press Shift F8 to move to the beginning of the next range Press F8 again to select the range Repeat this until all ranges are selected You can add ranges from any worksheet in the active workbook To add a range from a different worksheet click the Add button click a different
51. Undo from the Edit menu or press Ctrl Z In some cases you ll want to use a utility repeatedly For a few of the utilities Text Tools Calculator Insert Sequence Number and Interactive Zooming the dialog box remains onscreen after you perform the operation For other utilities you can use the Ctrl Shift R key combination to display the PUP dialog box that was most recently used In addition PUP dialog boxes by default remember the last settings used These features are enabled and disabled in the PUP Options dialog box You may prefer to use a floating toolbar rather than the PUP v6 menu To create a toolbar choose Create a PUP Toolbar from the PUP v6 menu Some Excel applications may have a macro that resets the menu bar In such a case the PUP v6 menu will disappear To re build the PUP v6 menu press Ctrl Shfit U When a PUP utility is running it s possible to break out of the procedure by pressing Ctrl Break Therefore if your system appears to hang during a lengthy operation you can press Ctrl Break to stop the procedure This will result in a Code execution has been interrupted dialog box Choose Continue to keep the procedure running or End to stop the procedure Keep in mind that PUP v6 was thoroughly tested using a wide variety of hardware It was also tested with several non English language versions of Excel It was impossible of course to test this product under all possible scenarios so it is quite likely t
52. Worksheet TO0IS vossiseastees sands ccccncesss sconce snardzsssacessaeneniearaearteeeans 48 Font Reference Sheet ccs sscceccccecesassecccasects aaaea nea anena aa cadeesntavance rescue ecieciade cateventeven 48 Toggle Settings arepke a ieie a Ea RREAN en AEA Naaa TE Eaa Aana EEE 48 Set SCroll T E E T E E E E T A 49 interactive ZOOMING wiv aieecesceciceesecceececcteecevesctendescecteresscunteesctdtesusccduecesendieeesesceteesstiteeeseoetteees 50 Save Sheet as HTML a ce aaar a aaa aaa aaa a aaa aa aa aaaea aa cevesnenctacdanrexisyes 50 CALCU ACN ATE T O A TT 51 Syvachr nize Sheets rere aR aA AARAA ENAA Ea KAARE AANE REAT AARAA R AAR ESPANA NANNA E AE EARE WAANR 52 Reset the Last Cell a e e er ea cies aaa a ae r aaaea ae aa e aa aa ea e AAAA A aaaea aaa amaa aN Daaa 53 The Utilities Workbook oolS cccceeeeeeeeeeeeeeeeeeeeeeeeeeeeeneeeeeeeeeeeseeseneneeees 54 PAET E E 54 Interactive Hide Unhide risisiisininninneunncanannnnnni ienaa ninaman enanar cadeuescanetedeedeesdasteecesereceedeesteues 55 SOM SNC visiirisi anaana ae aaaea E Naana aaa E aa E Sa eaa aa aaaea ao aaaea S Tanina cern ees 56 Save With Backup i i esis em mae aaae n aaen aea e cuties aa Aaaa E oaaae eaaa da raae a eraai da rakaa ieit 57 Create Workbook Contents Sheet cccccccesseeeeeeceeeeeeeeeeeaeeeseeeeeseesseaeeeseeeseseeesaeeeeeeeeneees 58 Customize a New Workbook cccecceeeeeeeeeeeeeeeeee ance cece ee seneeeaeee sees ens easeaeeeseeesese
53. a workbook Using Interactive Hide Unhide 1 Select Interactive Hide Unhide from the PUP v6 Workbook Tools menu 2 Use the controls in the dialog box 3 Click Exit to close the dialog box 4 Click Activate to activate the selected workbook and optionally the selected worksheet Hiding and unhiding workbooks e Use Hide or Unhide to change the hidden status of the selected workbook e Use Hide all to hide all workbooks e Use Hide all but to hide all workbooks except the selected workbook Saving workbooks e Use Save to save the selected workbook e Use Save as to save the selected workbook with a different filename Hiding and unhiding sheets e Click Sheets gt gt to expand the dialog box and display a list of sheets for the selected workbook e Use Hide or Unhide to change the hidden status of the selected sheet e Use Unhide all to unhide all sheets in the selected workbook e Use Hide all but to hide all sheets except the selected sheet Deleting and renaming sheets e Use Delete to delete the selected sheet e Use Rename to change the name of the selected sheet Note These operations cannot be undone 55 PUP v6 User s Manual Activating a workbook or sheet e Use Activate to activate the selected workbook e f the dialog box is expanded to show sheets Activate will also activate the selected sheet Interactive Hide Unhide Tips e Double click a workbook name to change its hidden status e Doubl
54. ackground colors for non shaded rows select the Leave existing color for non shaded rows option If this box is unchecked non shaded rows will have the standard white background Click OK Removing alternate row shading To remove alternate row shading 1 Notes Select the range that contains alternate row shading Choose Alternate Row Shading from the PUP v6 Formatting Tools menu In the Operation tab of the Alternate Row Shading dialog box verify that the Range box displays the correct range Or change it if desired Choose the Remove existing alternate row shading option If the range contains any conditional formatting you will be asked if you want to remove only the conditional formatting or the conditional formatting and other standard background shading Click OK to close the dialog box If you choose the Conditional formatting option the range may not contain any other conditional formatting If any cells in the selected range contain conditional formatting you will be asked if you want to delete the conditional formatting If you select the Remove existing alternate row shading option the Options tab is not available Alternate Row Shading Tips If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immediately This will restore the previous formatting of the selected cells Generally the Conditional formatting option is a better choice because the alternate row shading will b
55. alysis ToolPak add in is installed Currency Conversion The Currency Conversion utility makes it easy to convert between dozens of world currencies using current exchange rates downloaded from the Internet You can enter a value directly or you can quickly convert a range of cells from one currency to another For example you might have a range of cells that contain values in U S Dollar You can use the Currency Conversion utility to convert these values to the EURO currency Notes e The first time you use this utility you will be prompted to create a workbook file that will hold the currency exchange ranges Your system must be connected to the Internet in order to retrieve the current currency exchange rates and create this file e The exchange rate data is obtained from the MSN MoneyCentral Investor web site The quotes are delayed by at least 20 minutes Using Currency Conversion The Currency Conversion utility can be used in either of two modes e Convert a single amount e Convert a range of cells You can either overwrite the original data or specify a range for the converted values Converting a single value 1 Choose Currency Conversion from the PUP v6 Formula Tools menu In the Currency Conversion dialog box select the Convert a Single Value tab 2 3 Use the Convert from list box to select the currency that you are converting from 4 Use the To list box to select the currency that you are converting to 5 En
56. ate on the entire worksheet The Base Cell box shows the address of the selected cell Place a checkmark next to the following items Size Name Bold and Color Index Remove the checkmark from all other items In other words the utility will select all cells that match the base cell in these four formatting attributes Click OK 7 If Display Summary is selected a message box will tell you how many cells qualify Click OK to select the cells 8 When the dialog box closes you can then apply other formatting to the selected cells Go To Min or Max Value This utility selects the minimum or maximum value in a selected range of cells You can use Excel s MIN or MAX function to identify these values but this utility helps you locate the cells that contain these values As an option you can search in formula cells value cells or both Using Go To Min or Max Value Start by selecting the range to search To search the entire worksheet select a single cell Choose Go To Min or Max Value from the PUP v6 Range Tools menu Select Minimum value or Maximum value To select only the first matching cell choose First cell only To select all matching cells choose All cells 17 PUP v6 User s Manual 4 Specify the type of cells to search formulas values or both in the Look in box 5 Click OK The qualifying cell s will be selected If you selected All cells and more than one cell is found you ll see a message that t
57. atic picture object from a chart The original chart remains intact If desired the picture can be grayscale and you have the option of converting the chart to a Picture or a Bitmap image 64 PUP v6 User s Manual Using Convert Chart to Picture Start by selecting that chart that you want to convert to a picture e Choose Convert Chart To Picture from the PUP v6 Chart amp Graphic Tools menu e In the Convert Chart To Picture dialog box specify your options e Click OK to convert the chart to a picture The picture is placed directly over the original chart Use your mouse to drag it to a different location Convert Chart to Picture Tips e There is no Undo for this utility If the picture isn t what you expected just delete it e This utility works with embedded charts and with charts on a Chart sheet e Keep in mind that a picture is essentially a dead chart It is no longer associated with your data so changing the data will not affect the appearance of the picture e Creating a picture from a chart is useful if you d like to keep charted results of various what if scenarios e Experiment with the Picture vs Bitmap option to determine which is best for your needs One key difference is that a Picture unlike a Bitmap can be ungrouped Right click the picture and select Grouping Ungroup After doing so you can then work with the different elements contained in the picture Chart Report The Chart Report ut
58. atting select the cell that you want to format The cell must not contain a formula 1 Choose Superscript Subscript Formatting from the PUP v6 Formatting Tools menu 2 Use the arrow buttons to select a character The selected character has a red border around it 3 Use the other controls to change the formatting of the selected character You can specify the character s vertical position Superscript Normal or Subscript toggle the case of using the Upper case checkbox for alpha characters only toggle the Bold and Italic font attribute and change the font s Size 4 Repeat Steps 2 3 to change the formatting of other characters If In cell preview is checked the cell will display the formatting changes as you make them 6 Click OK to apply the formatting to the selected cell Superscript Subscript Formatting Tips e If the results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z e If the active cell contains a value you will be asked if you want to convert the value to text If you reply Yes a single apostrophe will be inserted as a prefix 43 PUP v6 User s Manual Insert Symbol The Insert Symbol utility lets you insert any character from any installed font You select the font character and size from a graphic display You can insert the selected character into an empty cell or into any character position of a non empty cell This utility does not work w
59. bar Press Ctrl Shift U to rebuild the PUP v6 menu e f you use another add in that insists on resetting the menu bar you can create a PUP v6 toolbar and access the PUP utilities from the toolbar The PUP toolbar doesn t work The most likely cause is that you ve moved your PUP v6 files To solve this problem select Create a PUP Toolbar from the PUP v6 menu This will delete the existing toolbar and create a new one Running a PUP utility is very slow If you re working with a very large or complex workbook with lots of formulas you may find that the performance of some utilities is slow because the workbook is recalculated frequently For 110 PUP v6 User s Manual example when selecting a PUP utility you may experience a delay before the dialog box is even displayed Or generating reports may take an unnecessarily long time To speed things up try setting Excel s calculation mode to Manual use the Options command on the Tools menu and choose the Calculation tab When you re finished with the operation return the Calculation setting to Automatic to ensure that your formulas return the correct result Excel is acting strangely Unfortunately it s not uncommon to encounter a situation in which Excel stops working properly For example the video display may be incorrect or Excel may suddenly stop responding The most common cause of general strangeness on the part of Excel is not enough system resources The solution is to
60. bining rows the selection should be at least two rows high This utility does not work with a multiple selection 1 Select Combine Columns or Rows from the PUP v6 Range Tools menu 2 Verify that the correct range is selected If not you can change it while the Combine Columns or Rows dialog box is displayed Specify an option Combine columns or Combine rows Specify an option for the With the data in section Specify an option for the Data separator section If you choose Insert this text you can enter any number of characters in the text box 6 If you would like to use the formatted values for the cells check Use formatted values 7 Click OK to combine the columns or rows Note When you choose the Combine columns or Combine rows option the dialog box displays a graphic that illustrates the type of combining that will take place This graphic does not reflect your actual data and it does not reflect the options you ve selected Combine Columns or Rows Tips e If the results of this operation are not what you expected choose Undo from the Edit menu or press Ctrl Z e When you choose Merge the combined cells the cells in each row if you chose the Combine columns option or the cells in each column if you chose the Combine rows option are merged together into a single cell 28 PUP v6 User s Manual The Utilities Formula Tools The Formula Tools are used to work with formulas Math Without Formulas
61. bject Add Path to Header or Footer Tips e f your workbook will be used exclusive in Excel 2002 or later this utility is no longer necessary Excel 2002 and later versions allows you to specify a file s path in the header or footer To do so Select Page Setup from the File menu Select the Header Footer tab Click Custom Header or Custom Footer Use the Path amp File icon to insert the code into the desired location a kf WN gt If your workbook already contains a macro inserted by the PUP Add Path to Header or Footer utility you ll want to remove the macro e f you save your workbook with a different name or to a different location no additional effort is required The new path and file name will be used for printed output 72 PUP v6 User s Manual e The Delete all existing path name headers and footers option if selected will deleted path names inserted by this utility This option is useful if you decide to display the path name in a different header or footer position e Depending on your security settings you may be prompted to enable or disable macros when the workbook is opened If you choose to disable macros the path printed in the header or footer may not be correct e If you are using Excel 2002 or later your security settings may not allow the Add Path to Header or Footer utility to modify your VBA Project To change your security setting 1 Select Tools Macro Security 2 Click the Trust
62. count double option Each incorrectly guessed vowel that is A E O or U adds two body parts e If you re using Excel 2002 or later you will see another option Sound When this option is in effect end of game messages will be spoken and also displayed in the message box Depending on how your system is set up you may hear an additional sound a system sound when the message box is displayed To turn off this system sound use the Windows Control Panel and remove the wav file associated with Asterisk e The game has a cheat code that will reveal the current word 108 PUP v6 User s Manual Random Number Generator This program simply generates a uniform random number within a number range you specify Every number within the range has an equal probability of being selected To display the Random Number Generator choose Random Number Generator from the PUP v6 Amusements menu 1 2 3 4 Enter the upper and lower values Click Start or press S and the numbers flash by Click Stop or press S again and the randomly selected number is displayed To close the Random Number Generator dialog box press Esc The value displayed in the lower left corner is the total number of random values calculated during the time interval between clicking Start and Stop The Random Number Generator is perfect for office raffles and drawings easier than picking a number out of a hat and it will keep a 5 year old child ente
63. cted Row amp column headers checkbox Toggles the display of the workbook sheet tabs Equivalent to the Options command on the Tools menu with the View tab selected Sheet tabs checkbox Toggles the reference style for formulas Equivalent to the Options command on the Tools menu with the General tab selected R1C1 reference style checkbox Hides the active window Equivalent to the Hide command on the Window menu 100 PUP v6 User s Manual Enhanced Shortcut Menus Excel Desktop The new shortcut menu commands listed below appear when you right click the Excel desktop area The Excel desktop area is the area behind the workbook windows The desktop area is not visible if the workbook window is maximized Menu item Minimize All Windows Tile Windows Activate a Window Interactive Hide Unhide Description Minimizes all worksheet windows Equivalent to the Arrange command on the Windows menu with the Tiled option Displays a list of all windows Hide or unhide workbooks or sheets interactively See Interactive Hide Unhide 101 PUP v6 User s Manual PUP Bookmarks As an option PUP v6 can display a new toolbar that lets you store bookmarks that enable you to quickly activate a particular workbook worksheet or range If you work with many different files or with large multi sheet workbooks this feature can save you a great deal of time You can create bookmarks for any number of workbook
64. d as part of any other commercial product BACKUP COPIES You may make any reasonable number of backup copies of PUP MULTI SYSTEM INSTALLATION You may install PUP on multiple computer systems subject to the condition that only one such system will be in use at any given time For example you may install the software on an office system and a home system DISASSEMBLY You may not reverse assemble or reverse compile any component of PUP VBA SOURCE CODE The complete VBA source code for PUP is available to licensed users for a nominal fee This code is intended primarily for instructional purposes The VBA code may not be incorporated into other commercial products COPYRIGHT PUP and ancillary material are copyrighted by J Walk and Associates Do not delete the copyright notice trademarks or protective notice from your copy or any copy you make WARRANTY J Walk and Associates does not warrant that the software will be free from errors or will meet your specific requirements The software is made available to you as is However if a problem is discovered and reported J Walk and Associates will take reasonable measures to correct the problem LIMITATIONS AND LIABILITY J Walk and Associates makes no warranty or condition either express or implied including but not limited to any implied warranties of merchantability and fitness for a particular purpose regarding the software In no event shall J Walk and Associates or its suppliers be
65. displayed Select the cell in which you want to enter a date Use the arrow buttons on the PUP Date Picker toolbar to select the desired month and year If desired select a date format from the drop down list at the bottom of the PUP Date Picker toolbar Click the button that corresponds to the day of the month The date in the selected date format is inserted into the active cell The PUP Date Picker toolbar remains visible until you close it or until its corresponding utility file is closed automatically The utility file can be closed automatically if you have specified a maximum number of utility files to keep open in the PUP v6 Options dialog box If the PUP Date Picker toolbar disappears unexpectedly just select Toggle PUP Date Picker Toolbar from the PUP v6 Date amp Time Tools menu again The PUP Date Picker toolbar remains visible until you close it To close the toolbar click the x in the toolbar s title bar or select Toggle PUP Date Picker Toolbar from the PUP v6 Date amp Time Tools menu PUP Date Picker Toolbar Tips Clicking a day button inserts the date into the active cell even if the cell is not empty If you accidentally insert a date into a cell and overwrite existing information select Undo from the Edit menu or press Ctrl Z To quickly change to the current year click the year button To change to the current month click the month name To insert the current date click anywhere in the line that d
66. e e You can also specify how comments are viewed by using Excel s Options dialog box View tab e If your comments contain individual character formatting this formatting may be lost when using the Format Find Replace or User Name tabs Change Sign of Values This utility makes it easy to change the sign positive or negative of values within a range It allows the following operations Operation Description Fix trailing negative Data imported from other sources often displays negative signs to the 12 signs PUP v6 User s Manual right of a value e g 143 44 This option converts those cells to normal negative values e g 143 44 Change all negative This option makes all negative values positive values to positive Change all positive This option makes all positive values negative values to negative Reverse the sign of This option makes all negative values positive and makes all positive all values values negative Using Change Sign of Values Start by selecting the range of cells that you want to alter The selection can consist of value cells and formula cells but formula cells will be ignored 1 2 3 4 Select Change Sign of Values from the PUP v6 Range Tools menu Verify that the correct range is specified Or you can change it Choose an Operation Click OK Change Sign of Values Tips If the results of this operation are not what you expected choose Undo from the Edit menu or p
67. e Report A listing of all names defined in the workbook e Worksheet Report Information about each worksheet in the workbook e VBA Module Report A listing of the number of VBA procedures contained in each VB Component within the workbook Using Workbook Summary Report To create a workbook summary report 1 Activate the workbook Then select Workbook Summary Report from the PUP v6 Auditing Tools menu 2 Inthe Workbook Summary Report dialog box select the components to be included in the report 3 Click OK The report is created in a new workbook Workbook Summary Report Tips e The Workbook Summary page of the report is in outline form Use the outline controls on the left side of the worksheet to expand or collapse the information e The Name Report page categorizes the defined names in terms of scope workbook level or worksheet level and visibility hidden or visible You can use PUP s Name Lister utility to work with names e The Worksheet Report page is in the form of a database table and it can be sorted by any field e The VBA Module Report page lists all modules even if they are empty Every Excel sheet has an associated code module named Sheet1 Sheet2 etc and the workbook itself has an associated code module named ThisWorkbook These modules always exist even if they are empty e When you specify the Summarize each worksheet option some of the summary information will not be generated for
68. e Save values as shown onscreen option the values may lose precision if the cells are not formatted to display all decimal places If you select the Open the file after exporting the data option the exported file will be opened If you chose an HTML format the file will be opened in your default browser If you choose the Complex HTML option you can specify a title for the HTML document by choosing Use this title and entering the title text Be aware that the Complex HTML option will generate a much larger file than the Simple HTML option However all of the formatting is retained and the file can be opened in Excel with no loss of information Even the formulas remain intact Note 25 PUP v6 User s Manual however that formulas which refer to cells outside of the exported range will be converted to values When you choose the Preserve font name size color option for a Simple HTML export the font size may not be duplicated precisely Fonts are assigned to the HTML font size attribute based on the following table Font size range HTML Font Size lt 10 HTML font size 1 10 to 12 HTML font size 2 12 1 to 14 HTML font size 3 14 1 to 24 HTML font size 4 24 1 to 32 HTML font size 5 32 1 to 47 HTML font size 6 gt 47 HTML font size 7 Export Range as Graphic The Export Range as Graphic utility exports a range of cells as a graphic image It supports the following graphic file formats File Format Description GIF Graphics I
69. e applied automatically if you insert new rows into the range If the range already has some type of conditional formatting applied you should choose the Standard formatting option otherwise your conditional formatting criteria will be deleted You can however add additional conditional formatting criteria after you ve applied alternate row shading The Conditional formatting option cannot be used if the workbook is shared 46 PUP v6 User s Manual e When you choose the Remove existing alternate row shading option the following operations occur 1 If the range does not contain any conditional formatting all background shading from the selected range will be removed This includes background shading that was not applied by the Alternate Row Shading utility 2 If the range contains conditional formatting you will be asked if you want to remove the conditional formatting only or the conditional formatting and any other standard background shading In either case only the conditional formatting applied by the Alternate Row Shading utility will be deleted Other conditional formatting information that is contained in the range will remain intact 47 PUP v6 User s Manual The Utilities Worksheet Tools The utilities in the worksheet tools category deal with manipulating worksheets Font Reference Sheet The Font Reference Sheet utility displays a single sheet workbook that contains a drop down list of all of the fonts
70. e click a sheet name to change its hidden status e You cannot hide all sheets in a workbook You can however hide an entire workbook Sort Sheets The Sort Sheets utility provides a flexible way to sort the sheets in your workbook Using Sort Sheets 1 Select Sort Sheets from the PUP v6 Workbook Tools menu The list box will display all sheets in the active workbook in their current order 2 Click the Alpha Sort button to arrange the sheet list in alphabetical order optional 3 Use the Move up and Move down buttons to change the position of the selected sheet 4 When you re satisfied with the position of the sheets in the list box click OK Note The dialog box provides a preview of how the sheets will be sorted The sheets are not actually sorted until you click OK Sort Sheets Tips e Because sheet names can vary widely the Alpha sort button may not always provide the type of sorting you had in mind e If Reverse order is selected the sheet list will be sorted in reverse alphabetical order e Choose Strict alphanumeric to override the intelligent sheet sorting If this option is enabled the sorting will be strict but may not be logical For example Sheet12 will appear before Sheetz2 e When the View sheet option is chosen selecting a sheet in the sheet list will activate the sheet e Sorting sheets cannot be undone 56 PUP v6 User s Manual Save With Backup The Save With Backup utility performs
71. e for Select Cells by Format is to identify cells that are formatted like a particular cell Once the cells are selected you can work with them as a group For example you can apply different formatting to the selected cells Using Select Cells by Format 1 Before using the Select Rows by Format utility select the range that you want to work with If you want to work with all cells on the worksheet select a single cell that contains formatting attributes that you want to search for This is considered the base cell 2 Choose Select Cells by Format from the PUP v6 Range Tools menu The checkboxes in the Select Cells by Format dialog box show the formatting attributes for the base cell Place a checkmark next to each formatting attribute that you want to match To choose a different base cell use the Base Cell range selector To avoid selecting blank cells choose Don t select empty cells 6 If you would like to see a summary of the number of cells selected choose Display summary 7 Click OK to select the matching cells Select Cells by Format Tips e f Don t select empty cells is checked blank cells will not be selected even if they match the specified formatting attributes e f you re using Excel 2002 or later you may prefer to use Excel s built in command to identify cells that contain a particular format use Edit Find then click Options gt gt in the Find and Replace dialog box Select Cells by Format Usage
72. e game is over you win when you have discovered all of the bombs by marking their cells The game is also over you lose when you accidentally uncover a bomb How to play Clicking a button exposes it as either a bomb a number or a blank If it s a bomb the game is over If clicking a button reveals a number the number tells you how many bombs are in the surrounding cells If clicking reveals a blank that means that there are no bombs in the eight surrounding cells In this case the program automatically reveals the surrounding cells Marking a bomb If you think a location contains a bomb right click it to mark it If you change your mind after marking a location as a bomb right click it again The button will display a question mark to remind you that you thought a bomb might be there Right clicking again returns the button to its normal state 107 PUP v6 User s Manual Winning You win the game if you 1 Correctly identify each bomb by marking it and 2 Do not incorrectly mark any position as a bomb Changing the parameters You can change the size of the playing field and select the number of bombs by clicking the Parameters button The default configuration is an 8 x 8 field with 10 bombs Bomb Hunt Tips e Bomb Hunt requires lots of strategy and a little bit of luck The luck comes in the initial moves when you re pretty much operating blind You ll get better with practice Once you ve uncovered a few numbers
73. e that the currency data was last updated To update the exchange rate data 1 Click Get latest rates 2 Another confirmation dialog box will appear If your system is connected to the Internet click the Refresh button The new data will be retrieved Notes e If you ve created formulas for a range of values these formulas will not be updated automatically e If your system is always connected to the Internet you can choose the My PC is always connected to the Internet option in Step 2 If this option is in effect the confirmation dialog box will no longer appear Currency Conversion Tips e f you re converting a range of data and the results of this operation are not what you expected choose Undo from the Edit menu or press Ctrl Z e The Results range can be on the same worksheet a different worksheet or ina different workbook e When you start a new Excel session there will be a slight delay the first time you use the Currency Conversion utility as the data is read from the local workbook file that stores the exchange rate data e The exchange rates are not updated automatically Unless you specifically request updated rates the rates used are those stored in the local currency rates xls workbook The date and time of this data is displayed in the Currency Conversion dialog box 38 PUP v6 User s Manual e f you retrieve new currency exchange rates be aware that these rates are delayed by at least 20 minut
74. e the chart to which you want to add data labels The chart can be an embedded chart or a chart sheet 2 Select Chart Data Labeler from the PUP v6 Chart Tools menu If the chart contains more than one data series select the series name from the Chart data series list box These series names correspond to the names in the chart s legend 4 Use the Label range control to specify the range that holds the labels to be added to the chart Specify options see below Click OK to add the labels to your chart Option Create links to the labels Copy label formatting Warn before overwriting labels Warn if range size does not match Position Description If selected the labels will be linked to the range If you modify the text in the labels in the range the changes will automatically be reflected in the chart s data labels However changing the formatting of the range will not affect the formatting of the data labels in the chart If selected the data labels will use the following formatting attributes from the data label range font font size font style bold and italic and text color However changing the formatting of the range will not affect the data labels in the chart If selected you will get a warning message if the selected data series already contains data labels If selected you will get a warning if the number of cells in the Label range does not match the number of data points in the selected
75. eaaeaneeseeeeneees 59 Filename Lister sanee aana aa Ea aana canes fugebedanctendvastachadadsstusvansectudacedexevessienar 60 The Utilities Chart amp Graphic Tools sssssssesnnsnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnna 61 Export CM a A TT A T O AE AT 61 Chart Data Labeler a a a a aaa E Aa eaa aa ae da an Ea eaa a apia raa 63 ELAP aA aT 1 a e REEE A O A AT 64 Convert Chart to Picture viccic sais cicccesessensneseessndevanunsiccnessasesssuceeecactenievandncleeattenerdvdanceecartenasses 64 Chart Report iin nc nnn sinned in en cineca mini d 65 Object Align Size amp Space cceeeceeeeeeseneeeeeeeeeeeeeeeeeeeeceeeeeeseeeeeeeseeeeeeeseeeeeeeseeeeeeeeseeeneeseeenes 66 Object Hide UNWIdeG ceces ccccvesdccccessecteeveseteesekectcee vas decdeanseeceevaecetesabeneceevaneeteuersseceevsaseterayesteeed 67 The Utilities Printing TOOIS cccccceceeeceeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeenees 68 Batch Printing Wizard ine cccccccccecestececcsee tenes ceceeeestecceesstecceesstedenesstecerensteneersteceueeesteterssetecerreen 68 Print Multiple Selection Wizard sisieun niana ennaa riean nsa aeaaaee see er EEEa Ea ania Eiis 69 COpy Page Set p aisir Eare NEAN E NENE AAEE Tey KEA OEKE EAA sonseenvanseceneveserivasseters 71 Add Path to Header r FOOtel reer aeea aa ea aaa aaa a aaan h etaar anaa raaa iiaa 72 The Utilities Date and Time Tools ccccssceeeeeesseeeeeseeseeeeeeeeeeeeeeeeeeeenensaees 74 Perpetual Calendar 2
76. eaaeeeseseeesesseaaneeseeeeeseeseeaees 29 Exact Formula Copy isccccccesesccecectscecceseccceesticceeeeseeccesestecterestetereestecbeeesteneerestocterssetersestecer est 32 Convert Relative amp Absolute References cccceeeeseeeneeceeeeeeeeseeaeeeseeeseeeeeseneeeeeeeneees 33 Unidpply N MS a r eects ba tecsececth aa E a rr aer Ea scunchececwsbeQsaduseb ise swanves vouweeceeseebvsobouete 34 Umit Convers O ae a a chee e a a ensue as aaa Era E a aaa a anara EEEE SA EE 35 Currency Conversioniss iiicccestenteiestecccerscdoecnesdcteeenstcncetestuteceusdtuedvesduneeenstaucdertinaesustanceverdecces 37 Error Condition WIZard 02020 cccssseecnncelenstszeaaeseseseedtadeqnescmandeenzs eqsunsusndcedzsvaqstcerantsedasvoanecer 39 PUP Worksheet Function Library ccccccssececeeseneeeeeeeeeeeeeeeneeeeseeneeeeseeneeenseeneeenseeeeeeesenneeenss 40 The Utilities Formatting Tools sssssssnsunnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn 43 Superscript Subscript Formatting cccccccecsseeeceeeeeeeeeeeeeeeeeeeeeeeeeseaeeeseeeseseeeeeaeeeeeeeeneees 43 sort SYMON 2 feescees cee dace cds sacs ck ca svacde Fesecnes es deaced tn a EE ARESE A A ARRA 44 3 D Coll Shading anneanne anaana eaaa aAA ANANA AN a AAAA Ga WAEN a a AANA aE NAER ANANE NA WA ANA TEAS 45 Alternate Row Shading s ssssssseseunsnrnnnnnnnunnnnnunnnnnnnnnnnunnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn nennen 45 PUP v6 User s Manual The Utilities
77. ecscececeesseteeessceenecvecacterseccueecvscteerssecneecvstaneerdencnaeysuecteeresecueecdensteers 89 Toggle PUP InfoBox Toolbar ceeccceseeeeeeneeeeeeeeeeeseeeeeeeseeeseeesesesneeseseseeeseseeneeeeeseeeeeseenenen 90 The Utilities VBA and Macro TOols ccccceceeeeeeeeeeeeeeeeeeeeeeneeeeeeeeeeeeeeeenneeees 94 VBA Project Summary REPOST ceccceeeeeeeeseeeeeeeeeeeeeeeeeeeeeseseeeseseeeeeseseseeesesesneeensssneesnneeneees 94 Remove All Macros iietscvesscsseccts lcecidess naet annerer are a eneued devsveusas ene e Eaa Eaa aone aian aaeeea 94 Toggle Face ID Toolbar iss s eane eeaeee a ae meae canned ae eaaa amaaa eana ai aaeain n Rai aeia 95 Enhanced Shortcut MenuS sssssssssssssnsnnnrernnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn nennen nnmnnn 97 Enhanced Shortcut Menus Cell Range Column or ROW ccccceseesseeeeeeeeeeeeeeseeenes 98 Enhanced Shortcut Menus Chat ccccccccsceeceeceeeeeeeeseeeeeeeeeeeeeseeaeeeseeeeeseseseaneeseseeneeeseeanes 99 Enhanced Shortcut Menus Sheet Tabb ccccceeeesseeeeeeeeeeeeeeeaeeeeeeeeeseeeseaeeeeeeeenseeseeanes 99 Enhanced Shortcut Menus Workbook Title Bar cccceceseeseeeeeeeeeeeeeeeeeneeeeeeeeeeees 100 Enhanced Shortcut Menus Excel DeSktop cccccscesseeeeeceeeeeeeeeeeeeeeeeeesnseeeeneneeeeeneeeees 101 PUP BOG IIMGEN S55 2g sce Sawa sia innin Sid denn Seca dies Sead sce cee basa cee ees 102 PUP v6 Amusements ac icc Sapa sass 2 we ope ass ee cee
78. ect Function from the Insert menu Although the PUP functions are arranged into categories they will always be listed in the User Defined category in Excel s Insert Function dialog box e When using Excel s Insert Function dialog box to insert a PUP worksheet function you can get help on the function by clicking the Help on this function hyperlink The help is contained in a file named pupfunctions chm and this file should be located in your PUP v6 directory e Keep in mind that the functions are available only within the workbook that was active when you added the functions If you need to use the same functions in a different workbook you ll need to add the functions to that workbook as well 42 PUP v6 User s Manual The Utilities Formatting Tools The formatting tools are used to apply formatting or work with special characters Superscript Subscript Formatting The Superscript Subscript Formatting utility simplifies the task of applying superscript or subscript formatting to individual characters in a cell The utility works with the active cell which must contain a text string In addition to superscript and subscript formatting this utility can apply the following changes to individual characters in a cell e Toggle the case of alpha characters e Toggle font Bold attribute e Toggle font Italic attribute e Change the font size Using Superscript Subscript Formatting Before using Superscript Subscript Form
79. ed Sources tab 3 Place a checkmark next to Trust access to Visual Basic Project 73 PUP v6 User s Manual The Utilities Date and Time Tools The utilities in this category work with dates and times Perpetual Calendar The Perpetual Calendar utility has the following features Displays a handy pop up calendar for any month from January 1900 through December 2200 Inserts a picture of any calendar month into your worksheet Creates a calendar workbook consisting of up to 12 consecutive months each ona separate worksheet Creates a simple appointment calendar that uses cell comments to store appointments Using Perpetual Calendar 1 2 Select Perpetual Calendar from the PUP v6 Date amp Time Tools menu In the Perpetual Calendar dialog box use the left spinner to select the month and the right spinner to select the year If you prefer to use Monday as the first day of the week choose Week starts on Monday If this option is not selected weeks begin with Sunday To insert a picture of the selected month click the Picture tab choose Color or Black amp White then click Paste The picture will be pasted and the dialog box will be closed To create a calendar workbook click the Workbook tab specify the Number of months and click Create The calendar workbook will be created and the dialog box will be closed To create a simple appointment calendar click the Appointments tab specify the Numbe
80. ed their time as beta testers for PUP v6 e Scott Bable Colin Banfield T th B la Steve Crow lan Huitson Kevin DeWhitt Doug Klippert Ken Lepper Adrienne Quinlan Eric Renaud and Dennis Wallertin Collectively they identified dozens of bugs and contributed many useful suggestions 118
81. eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeaeeeeees 118 Contact Inf rmate aaa r a aa cect ccaaceneesenstet deze cvneeverse ae cusecvneresastenceencetns 118 Acknowledgements iy 25c cc cccecedsscececesecouecscecuctentvcende ds cuctensecescesdesectenduceste uecciesesheoseeisestctenzee 118 PUP v6 User s Manual PUP Version 6 Introduction and Overview Power Utility Pak Version 6 known as PUP v6 is a collection of add ins for Microsoft Excel for Windows These add ins are designed to save time make your job easier and enable you to perform actions that might otherwise be impossible PUP v6 consists of several components e General Purpose Excel Utilities A set of 65 general purpose utilities that add new features to Excel Access these utilities from Excel s PUP v6 menu a new menu that is added when the Power Utility Pak is installed e New worksheet functions A set of 50 new functions for use in your formulas These function s are written in VBA and you can add the function code to your workbook using the PUP Worksheet Function Library utility VBA programming knowledge is not required e Enhanced shortcut menus optional When this option is in effect many of Excel s shortcut menus display additional commands e PUP Bookmarks toolbar optional This feature gives you quick access to frequently used workbooks sheets or ranges e PUP Amusements Guaranteed to reduce your productivity Requirements PUP v6 works with
82. ells you how many cells were found Go To Min or Max Value Tips e f more than one cell is found they are all selected that is a multiple selection To identify the selected cells use the sheet s scroll bars Look for the highlighted row and column borders to identify the selected cells Cell Lock Sheet Protect Utilities The Cell Lock Sheet Protect Utilities contains a number of tools to make it easier to work with locked cells hidden cells and sheet protection The utility performs the following functions e Lock unlock or hide unhide cells based on their contents formula cells cells that contain a value cells that contain text or blank cells e Change the protection status of one sheet or a number of sheets Note The Hide property does not actually hide the cell Rather a hidden cell s content is not displayed in the formula bar Using Cell Lock Sheet Protect Utilities Choose Cell Lock Sheet Protect Utilities from the PUP v6 Range Tools menu The Cell Lock Sheet Protect dialog box has two tabs e Cell Lock Unlock Contains commands to lock unlock or hide unhide cells e Sheet Protect Unprotect Contains commands to protect unprotect worksheets The Select Worksheets box lets you select any number of worksheets The operation that you perform is applied to all selected worksheets After performing an operation the Cell Lock Sheet Protect dialog box remains open so you can perform additional tasks
83. erent if they do not contain the same contents Check formulas vs values If checked cells will be considered different if one of the cells contains a formula and the other does not Compare numeric formatting If checked cells will be considered different if they use different number formats Using Compare Ranges If you re comparing two ranges start by selecting one of the ranges to be compared If you re comparing two entire worksheets activate one of the sheets to be compared 1 Select Compare Ranges from the PUP v6 Auditing Tools menu 2 Inthe Compare Ranges dialog box select the Range option if you re comparing two ranges If you re comparing two entire worksheets select the Worksheets option 3 Specify the comparison options and the maximum number of differences to be identified either No limit or a number that you specify in the Stop at box Click Next If you re comparing two ranges specify the First range and the Second range These two ranges should contain the same number of rows and columns Or If you re comparing two worksheets specify the First worksheet and the Second worksheet 6 Click Finish The comparison report will be created in a new workbook Compare Ranges Tips e f you re comparing two ranges the ranges can be in different worksheets or in different workbooks e f you re comparing two worksheets the sheets can be in different workbooks 89 PUP v6 User s Manual Togg
84. erlinks Tips e If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immediately This will restore the deleted hyperlinks e To prevent Excel from creating automatic hyperlinks choose Tools AutoCorrect Options Then select the AutoFormat As You Type tab and clear the checkmark from Internet and network paths with hyperlinks This applies to Excel 2002 and later versions e You can also prevent Excel from creating a hyperlink by preceding your cell entry with an apostrophe e f Excel creates an unwanted hyperlink press Ctrl Z and the hyperlink will be removed Insert Sequence Number The Insert Sequence Number utility makes it easy to insert a unique consecutive value into a cell This utility may be useful for preparing invoices in which you need a new identifying number for each invoice Using Insert Sequence Number 1 Choose Insert Sequence Number from the PUP v6 Range Tools menu Select the cell that will contain the sequence number Select the sequence from the list of sequences When you select a sequence in the list you will see a preview of the data that will be inserted This preview includes the specified optional prefix and suffix for the sequence 4 Click Insert Next to insert the sequence number into the active cell The Insert Sequence Number dialog box remains open so you can insert the next sequence number into a different cell 5 Click Close to close the dialog box
85. ern Matching If you choose the Similar to pattern option you can make use of wildcard characters The following table shows the characters allowed and what they match Character Matches Any single character i Zero or more characters Any single digit 0 9 Examples of pattern matching e To select all cells or rows in which the cell contains text that begins with the letter J choose the Similar to pattern option and enter J in the This pattern box 15 PUP v6 User s Manual e To select all cells or rows in which the cell contains values that end in 4 choose Similar to pattern and enter 4 in the This pattern box e To select all cells or rows in which the cell contains the letter Z choose Similar to pattern and enter Z in the This pattern box Select by Value Tips e Selections made using the Similar to pattern option are not case sensitive Therefore using A in the This pattern box selects the same cells as using a e After the utility has selected the cells or rows that meet your criterion you can give this range a name by using Define on the Insert Name menu or press Ctrl F3 After naming the range you can quickly select those same cells or rows again by using Go To on the Edit menu or the F5 shortcut key Select Cells by Format The Select Cells by Format utility selects a group of cells based on their formatting In most cases the result is a noncontiguous selection of cells Acommon us
86. es e For other types of conversions see Unit Conversion How it works The Currency Conversion utility makes use of a workbook named currency rates xls which is created the first time you use this utility and is located in your PUP v6 directory This workbook contains a Web Query that retrieves the exchange rate information from the MSN MoneyCentral Investor web site When you update the exchange rate data that workbook is opened the query is refreshed and the file is saved with the new data The Currency Conversion utility will then use the updated data If you re curious you can open the currency rates xls workbook but you should not make any changes to it Doing so may cause the Currency Conversion utility to return incorrect information or even crash your system Error Condition Wizard The Error Condition Wizard makes it easy to modify existing formulas so they don t display errors For example consider the following formula which calculates a percentage change D4 C4 C4 If cell C4 is empty or contains 0 the formula will display a DIV O error Use the Error Condition Wizard to convert the formula to the following which displays a blank when C4 contains 0 IF ISERROR D4 C4 C4 D4 C4 C4 Using Error Condition Wizard Before using the Error Condition Wizard select the range of cells that contains the formulas you want to convert The selection can include formula cells and non formula cells O
87. essary If you select Overwrite source data the data in Source range will be replaced by the converted data To put the results into a different range uncheck Overwrite source data and specify the Results range a single cell is sufficient If you would like the result in the form of formulas select Create conversion formulas This option is not available when the Overwrite source data option is specified Select the Unit category Select the From unit and the To unit If working with metric units you can also choose a Metric prefix The conversion factor for the two units will be displayed on the lower left of the dialog box Click OK Unit Conversion Tips If the results of this operation are not what you expected choose Undo from the Edit menu or press Ctrl Z 36 PUP v6 User s Manual e The Results range can be on the same worksheet a different worksheet or in a different workbook e If the measurement unit is a metric unit Meter Gram or Liter you can select a metric prefix for the From or To units For example to convert miles to kilometers choose Miles as the From unit then chose Meter as the To unit and select Kilo as the Metric prefix e The Bits amp Bytes category does not use the Metric prefix boxes e g a kilobyte is 1 024 bytes not 1 000 bytes e Using the Unit Conversion utility is often a good alternative to using Excel s CONVERT function because the CONVERT function requires that the An
88. expressions that use cell addresses and named cells e C4 uses the contents of C4 on the active sheet e Interest uses the contents of a cell named Interest e C4 2 uses the contents of C4 multiplied by 2 e Sheet1 C4 uses the contents of C4 on Sheet1 Rounding To round values in the selected cells choose Rounding from the Operation list Then specify the number of decimal places For example to round values to two decimal places use an operand of 2 Negative decimal places are allowed If you specify a negative decimal place rounding will occur to the left of the decimal point For example 15 469 87 rounded to 2 decimal places is 15 500 31 PUP v6 User s Manual Using Functions If you choose Function from the Operation list you will be able to choose from a list of 35 worksheet functions The functions listed are standard Excel functions that take a single numeric argument For example to calculate the square root of all selected cells choose the SQRT function from the list Using Custom Expressions If you choose Custom from the Operation list you will be able to enter an expression to be applied to each cell in the selection This option provides more flexibility and enables you to perform calculations not possible using simple operands and functions When you enter your expression use a question mark to represent the cell The expression can contain values single cell address single cell range names
89. f the used area are never changed When you protect a sheet you can choose to protect any of the following contents objects or scenarios If you use Excel 2002 or later you have many additional protection options 19 PUP v6 User s Manual e f you select the Go to selected sheet the selected sheet will be activated so you can see its contents If more than one sheet is selected in the Select worksheets list the last lower in the list selected sheet in the list is activated e Password protection does not ensure complete security Password cracking utilities are widely available Range Randomize Utilities The Range Randomize Utilities include the following features e Rearrange a range of cells in random order e Randomly select one or more cells from a range you choose the number to select e Insert a sequence of integers into a range in random order You choose the beginning value and the increment Using Range Randomize Utilities Before using the Range Randomize utilities select the range of cells that you want to work with Or you can select the range when the Range Randomize Utilities dialog box is displayed Rearranging cells 1 Choose Range Randomize Utilities from the PUP v6 Range Tools menu 2 Select the Rearrange the selected cells in random order option 3 Click OK Note This option will not work if any of the selected cells contain a formula Selecting cells at random 1 Choose Range Rando
90. g has been applied simply by selecting the cells and using the Color tool on the Formatting toolbar The 3D effect will remain but the background will be a different color Alternate Row Shading The Alternate Row Shading utility applies cell shading to every nth row in the selection similar to the effect of printing on computer forms This type of shading may make it easier to read printed output The formatting can be done using either of two techniques e Conditional Formatting This option uses Excel s Conditional Formatting feature The advantage is that the shading is dynamic If you insert new rows within the shaded range the alternate row shading will be applied automatically e Standard Formatting This option use standard formatting If you insert new rows within the shaded range you will need to re apply the alternate row shading 45 PUP v6 User s Manual Using Alternate Row Shading Before using the Alternate Row Shading utility select the range of cells to which you want to apply the formatting Choose Alternate Row Shading from the PUP v6 Formatting Tools menu In the Operation tab of the Alternate Row Shading dialog box verify that the Range box displays the range to be formatted Or change it if desired Select the option that represents the operation to be performed Click the Options tab and select the desired color for the shading Specify a value for the Shade every setting To keep existing b
91. harts The Resize Charts utility makes it easy to precisely adjust the size of one or more embedded charts It s particularly useful for making all charts on a sheet exactly the same size Using Resize Charts Start by selecting an embedded chart This utility does not work with a chart on a Chart sheet 1 Choose Resize Charts from the PUP v6 Chart amp Graphic Tools menu 2 The Resize Charts dialog box dimensions of the selected Chart You can change either or both of these dimensions 3 Click Apply to change the size of the selected chart 4 If the size is satisfactory click Close to dismiss the Resize Charts dialog box Resize Chart Tips e If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immediately e To make all charts on a sheet the same size select the Make all charts on the active sheet this size checkbox before you click Apply e To ensure that the chart retains its original aspect ratio i e the ratio between the width and the height select the Lock aspect ratio checkbox When this option is in effect two Calc buttons are visible Click the top Calc button to calculate the Width based on the Height displayed Click the bottom Calc button to calculate the Height based on the Width displayed The aspect ration that s maintained is the aspect ratio of the selected chart when the utility was invoked Convert Chart to Picture The Convert Chart To Picture utility creates a st
92. hat there will be situations in which the product does not perform as it should If you discover a problem with a PUP utility consult the technical support section of this document for information on getting the problem resolved PUP v6 User s Manual The Utilities Range Tools The Range Tools are used to manipulate and work with data contained in ranges Text Tools The Text Tools utility provides several ways to manipulate text in selected cells without using formulas You can e Change the case of the text UPPER CASE lower case Proper Case Sentence case and tOGGLE cASE e Add new characters to the text at the beginning at the end or at a specified position e Remove characters from the text by position from the beginning from the end or beginning at a specified position e Remove spaces from the text all extra spaces leading spaces trailing spaces or all spaces e Remove characters of a particular type from the text non printing characters alpha characters non numeric characters non numeric and non alpha characters or numeric characters Note This utility will not preserve individual character formatting in your cells e g superscript formatting Formatting applied to entire cells is not affected Using Text Tools To display the Text Tools dialog box choose Text Tools from the PUP v6 Range Tools menu The Text Tools utility always works with the current range selection The Text Tool
93. his number includes only the formulas on the same sheet 90 Dir Dependents Precedent Cells Dir Precedents PUP v6 User s Manual The number of formulas that use the cell directly This number includes only the formulas on the same sheet If the cell contains a formula the number of cells used by the formula either directly or indirectly This number includes only the formulas on the same sheet If the cell contains a formula the number of cells used directly by the formula This number includes only the formulas on the same sheet When Range Selection mode is in effect the PUP InfoBox toolbar displays the following information about the selected range of cells Range Information Total Cells Empty Cells Total Formulas Formulas Values Formulas Text Formulas Logical Formulas Errors Total Constants Constants Value Constants Text Constants Logical Unique Entries Sum Average Description The number of cells selected The number of empty cells The number of selected cells that contain a formula The number of formula cells that return a value The number of formula cells that return text The number of formula cells that return a logical TRUE or FALSE The number of formula cells that return an error The number of selected cells that contain a constant that is not a formula and not empty The number of constant cells that contain a value The number of constant ce
94. iately This will return the objects to their original sizes and positions and override any Undo operations you may have performed in the Object Align Size amp Space dialog box Select multiple objects by pressing the Ctrl or Shift key as you click each object or by using the Select Object tool on the Drawing toolbar When Jiggle selected object is selected the dominant object will move slightly when it s selected in the list to help you identify the object Object Hide Unhide The Object Hide Unhide utility provides a way to hide or unhide objects on a worksheet Excel does not provide this functionality without using a VBA macro Using Object Hide Unhide To hide or unhide object on a sheet start by activating the worksheet Choose Object Hide Unhide from the PUP v6 Chart amp Graphic Tools menu The Object Hide Unhide dialog box displays a list of all objects on the sheet excluding cell comments To hide an object select its name from the list and then click Hide object To unhide a hidden object select its name from the list and click Unhide object To hide all objects click Hide All To unhide all objects click Unhide All Click Close to close the Object Hide Unhide dialog box Object Hide Unhide Tips This utility does not have an Undo option To restore the hidden attribute of objects re run the utility In many cases you won t know the name of the objects on your sheet To identify a particu
95. ility creates a new worksheet that contains useful information about a chart or all charts You can use this utility to create a type of documentation for your charts You can choose from two report options e Asimple report for all charts in the workbook e A detailed report for a single chart Using Chart Report If you are creating a report for a single chart start by selecting that chart To create a report for all charts select any chart e Choose Chart Report from the PUP v5 Chart amp Graphic Tools menu e Specify the report type simple or detailed e Specify the location for the report in a new worksheet or in a new workbook e f you would like the report to include a small image of the chart select Include chart thumbnail e Click OK to generate the report 65 PUP v6 User s Manual Chart Report Tips The Chart Report is a snapshot of the current chart If you make changes to the chart the report is not updated to reflect those changes For details on the specific information included in the report consult the Help system for Excel Object Align Size amp Space The Object Align Size amp Space utility works with a group of objects on a worksheet and makes it easy to adjust their alignment size or spacing Using Object Align Size amp Space 1 N Select the graphic objects that you want to adjust You must select at least two objects Select Object Align Size amp Space f
96. ill be added to the list Option Description 102 PUP v6 User s Manual Activate the workbook and When the bookmark is selected the workbook will be opened go to the specified sheet amp or activated the sheet will be activated and the range will be range selected Activate the workbook and When the bookmark is selected the workbook will be opened go to the specified sheet or activated and the sheet will be activated There will be no change to the selected cell or range Activate the workbook When the bookmark is selected the workbook will be opened or activated There will be no change to the active sheet or to the selected cell or range Activating a bookmark Choose a bookmark from the drop down list in the PUP Bookmarks toolbar If the workbook is already open the bookmarked sheet and range will be selected If the workbook is not open it will be opened Editing your bookmark list To edit your bookmark list click Edit on the PUP v6 Bookmark toolbar This displays the Edit Bookmarks dialog box When you select a bookmark item from the list it s workbook sheet and range are displayed at the bottom of the list box To rearrange the bookmark items select an item in the Bookmark names list and use the up and down arrows buttons to move the selected item Use Delete to delete the selected bookmark name This deletes the bookmark only The file that it refers to is not deleted Use Delete All t
97. imeters You can use the Unit Conversion utility to convert these values to inches Replace the existing values When working with a range of data the converted values can Replace the existing values Be inserted into a new range as values Be inserted into a new range as formulas which refer to cells in the original range The Unit Conversion utility supports the following measurement units Notes Angle Degree Grad Minute Radian Revolution Second Bits amp Bytes Bit Kilobit Megabit Gigabit Terabit Petabit Exabit BYTE KILOBYTE MEGABYTE GIGABYTE TERABYTE PETABYTE EXABYTE Distance Foot Inch Meter Nautical mile Statute mile Yard Energy BTU Calorie IT Calorie Th mic Electron volt Erg Foot pound H powr hr Joule Watt hour Force Dyne Newton Pound force Liquid Cup Gallon Gallon Imp Liter Ounce Ounce Imp Pint Pint Imp Quart Quart Imp Tablespoon Teaspoon Mass Caret Grain Gram Ounce Avdp Ounce Troy Pound Avdp Pound Troy Stone Ton Surface Acre Hectare Sq Foot Sq Inch Sq Meter Sq Mile Sq Yard Temperature Celsius Fahrenheit Kelvin Time Day Hour Leap Year Minute Second Year Volume Cubic Foot Cubic Inch Cubic Meter Cubic Yard Metric units can be scaled by specifying a prefix For example decimeter millimeter kilometer etc In some cases minor rounding errors will be apparent 35 PUP v6 User s Manual Using Unit
98. ions VBA macro language One of the primary design goals of PUP v6 was simplicity Consequently it does not use any custom DLLs or non standard controls and it does not make any changes to your system settings Here s a basic overview of how the product works PUP v6 consists of dozens of add in files Most of these files use a PUP extension rather than an XLA extension They are however standard Excel add in files The PUP extension is used so these files won t appear in the Browse dialog box when the Browse button is clicked from Excel s Add Ins dialog box When the pup6 xlaadd in is opened VBA code builds the PUP v6 menu and it controls loading and unloading of the utilities The pup6 xla add in loads the utilities on an as needed basis so they won t use up memory and system resources if they are not used The pup6 xla add in keeps track of which utilities are in memory and you can specify the maximum number of utility files to keep in memory You control this in the PUP Options dialog box choose PUP Options from the PUP v6 menu PUP v6 stores its settings in the Windows Registry database This is also where it stores the last used dialog box settings The path is HKEY CURRENT _USER Software VB and VBA Program Settings Power Utility Pak To view the current registry settings for PUP v6 Select About Power Utility Pak from the PUP v6 menu Then click Registry Setting and respond Yes to the message box To delete all
99. isplays the single letter day abbreviations If the column is not wide enough to display the formatted date the column is widened automatically If the active cell already contains a date you can change the date format of the that date by selecting a new format from the drop down list at the bottom of the PUP Date Picker toolbar To change the format for more than one date cell select the cells and choose a date format from the drop down list If the Move selection after Enter option is in effect the active cell will change appropriately after the date is inserted To change this setting choose Options from Excel s Tools menu and select the Edit tab 75 PUP v6 User s Manual When you move the mouse pointer over the days in the calendar the tooltip displays the day of the week and the date for each day button Note that the format of the date displayed is not necessarily the format that will be inserted into the active cell Excel does not support dates prior to 1 January 1900 Therefore the PUP Date Picker toolbar will not display years prior to 1900 You can move the PUP Date Picker toolbar but it cannot be docked or customized Following is a list of the available date formats and an example of each This table is uses slash as the default date separator Note The slashes will be replaced by your local date separator character Date Format mmm d yyyy m d yy m d yyyy mm dd yy mm dd yyyy mmmm dd yyyy dddd
100. ist box If you choose All from the Function category list the list box displays all of the functions 40 6 7 PUP v6 User s Manual Select one or more functions from the list To select multiple functions press Ctrl To select all of the functions click the All button To clear the selection in the list box click None Click Add The VBA code that defines the selected functions is added to your workbook When you re finished adding functions click Close The functions are now available for use in that workbook Removing PUP functions from your workbook 1 2 3 4 5 Activate the worksheet that contains the PUP functions you want to remove Select PUP Worksheet Function Library from the PUP v6 Formulas menu Choose the Remove Functions tab of the Insert PUP Function Library dialog box Select one or more functions from the list and click OK The VBA code that defines the selected functions is removed from your workbook If the list box is empty no PUP worksheet functions are defined in your workbook When you re finished removing functions click Close PUP Worksheet Library Usage Example Assume that your worksheet contains a list of names and you would like to be able to extract the last name e g extract Smith from John Q Smith PUP s PARSENAME worksheet function will do just that 1 Select PUP Worksheet Library from the PUP v6 Formulas menu Choose the Add Functions tab of the Insert P
101. ith cells that contain formulas Numeric cells will be converted to text by preceding the value with an apostrophe Using Insert Symbol Before using Insert Symbol select the cell that you want to work with This can be an empty cell or a non formula cell that contains text 1 2 Choose Insert Symbol from the PUP v6 Formatting Tools menu Select the font from the Font list Click on a character from the graphic display For a better view of the character under the mouse pointer select Magnify character Specify a Font size by clicking the spinner control The Font size preview window shows the actual size of the current character up to 60 points If the active cell is not empty use the horizontal arrow buttons to select a character position for the inserted character The Character position window displays up to six characters The vertical arrow points to the position at which the character will be inserted Click Insert to insert the character into the active cell If you need to insert additional characters repeat the preceding steps as necessary When you are finished click Close Insert Symbol Tips If you are using Excel 2002 or later you may prefer to use the Symbol command on the Insert menu Excel s version is much more versatile If the results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z If the active cell contains a value you will be asked if you want to c
102. k Scroll to or Go to if a Chart sheet is active When exporting a chart on a chart sheet or on multiple chart sheets you can adjust the size of the chart s by selecting Sized with Window from the chart sheet s View menu make sure the workbook window is not maximized The file names are based on the Chart object names or Chart sheet name with an underscore character substituted for each space Use the Rename button to change the name of a Chart object or a Chart sheet This will also change the name of the exported file name The Rename button is available only when a single chart in the list is selected You can also change the name of an embedded Chart object manually Ctrl Click the chart type the new name in the Name box located at the left of Excel s edit line and press Enter If you re using the Enhanced Shortcut Menus option you can right click on a chart and choose Export Chart As Graphic from the shortcut menu 62 PUP v6 User s Manual Chart Data Labeler The Chart Data Labeler utility makes it easy to add labels from an arbitrary range of cells to chart data points Although Excel lets you add data labels to a chart it does not allow you to select the range for the labels The Chart Data Labeler utility overcomes this limitation In addition data labels that you add to a chart can be linked to a range and even use the same formatting as the cells in the range Using Chart Data Labeler 1 Activat
103. lar object select the checkbox labeled Scroll sheet to show object in upper left corner If this option is checked the selected object in the list will appear in the upper left corner of the workbook window This utility does not work with cell comment objects If you no longer need an object it is better to delete it than hide it Hidden objects remain in the workbook and take up memory and increase the file size 67 PUP v6 User s Manual The Utilities Printing Tools The utilities in this category deal with printing Batch Printing Wizard The Batch Printing Wizard prints the contents of selected files Excel workbooks or text files with a single command The utility loads each file you select prints what you want and then closes the file Using Batch Printing Wizard Select Batch Printing Wizard from the PUP v6 Printing Tools menu The Batch Printing Wizard displays a series of four boxes Use Back and Next to indicate your choices and click Finish to commence the printing Additional details are provided below Step 1 In this step choose the option that describes the location of the files to be printed all files in the same directory or files in multiple directories Step 2 The choices in Step 2 are determined by your choice in Step 1 All of the files are in the same directory 1 Click Browse to specify the directory 2 Specify the Type of files or enter a File specification The number of matching fi
104. lated To export a specific range of cells not the entire sheet use PUP s Export Range to File utility If you select the Open the file after exporting the data option the exported file will be opened in your default browser Only the data in cells is saved Charts and other objects are ignored Cell formatting applied as a result of conditional formatting is not applied When you choose the Preserve font name size color option the font size may not be duplicated precisely Fonts are assigned to the HTML font size attribute based on the following table Font size range HTML Font Size lt 10 points HTML font size 1 10 to 12 HTML font size 2 12 1 to 14 HTML font size 3 14 1 to 24 HTML font size 4 24 1 to 32 HTML font size 5 32 1 to 47 HTML font size 6 gt 47 HTML font size 7 Calculator The Calculator utility displays a simple four function calculator in an always on top window The calculator can be accessed using the mouse or keyboard and the result can be pasted into a cell In addition the value of a cell can be retrieved and used in a calculation 51 PUP v6 User s Manual Using Calculator 1 Access the Calculator by choosing Calculator from the PUP v6 Worksheet Tools menu 2 Use the mouse or keyboard to perform your calculation 3 To use a value stored in a cell activate the cell and click Get 4 Click Paste to paste the result to the selected cell The Calculator remains onscreen after pasting a
105. le PUP InfoBox Toolbar The PUP InfoBox toolbar provides a convenient way to get quick information about the active cell or arange of cells The toolbar has two modes e Active Cell mode e Range Selection mode When Active Cell mode is in effect The PUP InfoBox toolbar displays the following information about the active cell Cell Information Value Displayed As Cell Type Number Format Formula Name Style Horiz Alignment Protection Conditional Fmt Data Validation Cell Comment Dependent Cells Description The underlying value of the cell The contents of the cell as it is displayed that is with number formatting applied A description of the cell s contents Either Blank Value Text Logical Date Time or Error The number format code applied to the cell The cell s formula if any Use the Show formulas in R1C1 notation checkbox to change the way in which formulas are shown The cell s name if any If the cell is part of a named range that name is not displayed The cell s named style if any By default cells use the Normal style The cell s horizontal alignment setting The protection status of the cell Either Locked Hidden or Locked Hidden True if the cell uses conditional formatting Displays the number of data validation conditions if any The text of the cell s comment if any The number of formulas that use the cell either directly or indirectly T
106. les is indicated The files to be printed are in multiple directories 1 Click Add Files as needed to locate the files The file s will be added to the Files to print list To select multiple files hold down the Ctrl key as you click the file names Hold down Shift to select a group of contiguous file names 2 Click Remove to remove a selected file or click Remove All to remove all files from the list Step 3 In this step you specify which part s of the workbooks you would like to print Step 4 This step has some additional options described below Option Description 68 PUP v6 User s Manual Quiet Mode If selected screen updating is turned off The files that are opened and printed will not be visible Generate a summary report If selected a report will be generated The report describes what was printed and also lists any errors that occurred Disable Auto macros If selected automatic macros will not be executed in the workbooks that are opened Password These options determine what happens when a file requires a password to be opened If you specify a password it must be the same password for each file in the list Links amp remove references These options determine if links and remote references are updated when each file is opened Batch Printing Wizard Tips e The specified workbooks are opened and printed using the print specifications stored in the workbooks e It s a good idea to request
107. liable for any indirect incidental or consequential damages loss of profits loss of use or data or interruption of business whether the alleged damages are labeled in tort contract or indemnity Power Utility Pak Version 6 2001 2005 by J Walk and Associates Inc All rights reserved 117 PUP v6 User s Manual About J Walk and Associates Inc Power Utility Pak was developed by John Walkenbach principal of J Walk and Associates Inc J Walk and Associates is a small consulting firm based in Tucson Arizona Walkenbach has been using spreadsheets since the early days of VisiCalc He has written approximately 300 articles and reviews and his work has appeared in publications such as PC World Windows PC Computing and InfoWorld In addition he s the best selling author of more than 30 spreadsheet books most of which have been translated into several foreign languages Contact Information http j walk com ss support j walk com Acknowledgements Power Utility Pak started out simple The first version released in 1994 has evolved into a powerful set of tools that is used by people throughout the world Many of the VBA programming techniques used in PUP are based on information posted in the Excel newsgroups These newsgroups are perhaps the best source of information for all things related to Excel Thanks to those who take the time to share their knowledge Beta Testers Special thanks to the beta testers who volunteer
108. ll that contains a comment A range selection dialog box will appear 3 Select a comment cell and click OK This will apply the formatting of the selected cell to all other comments The formatting that is copied includes font font size text color background color and comment size and shape 4 In many cases the user name portion of a comment are bold Copying the formatting will make the entire comment bold To ensure that bold formatting is not applied to the entire comment text choose the Force non bold formatting option Finding and replacing text within comments 1 Inthe Cell Comment Tools dialog box select the Find Replace tab 2 Enter the text to be replaced in the Find text in comments box 3 Enter the replacement text in the Replace with box 4 Click Apply Adding or removing a user name 1 Inthe Cell Comment Tools dialog box select the User Name tab 2 Enter or edit the name in the User name box 3 Choose an option either Remove user name from comments or Add user name to comments 4 If you are adding a user name and you would like the name to be in bold type select Make name bold If you are removing a user name and you don t want the remaining text of the comment to be bold choose Force non bold formatting 5 Click Apply Cell Comment Tools Tips e The Cell Comment Tools dialog box will not be displayed if the active workbook contains no cell comments e Finding and replacing is not case sensitiv
109. lls that contain text The number of constant cells that contain a logical TRUE or FALSE The number of unique cells The sum of the values in the selected range The average of the values in the selected range 91 PUP v6 User s Manual Minimum The smallest value in the selected range Maximum The largest value in the selected range Notes e The information displayed for a selected range includes cells in hidden rows and columns e Range information cannot be displayed if the selected range contains merged cells Using the PUP InfoBox Toolbar 1 Select Toggle PUP InfoBox Toolbar from the PUP v6 Auditing Tools menu The toolbar will be displayed 2 To view information about the active cell select Active Cell from the drop down list 3 To view information about the selected range of cells select Range Selection from the drop down list 4 To temporarily turn off cell or range monitoring select Turn Off from the drop down list When the PUP InfoBox Toolbar is active you may experience a decrease in performance because of the overhead involved in updating the toolbar 5 While the PUP InfoBox toolbar is displayed you can continue to use Excel select cells and ranges enter and edit data etc In most cases changes you make will be reflected in the contents of the toolbar If the information is not updated click Refresh to force an update Notes e The PUP InfoBox toolbar remains visible until you clo
110. ls that contain a value not equal to a specified value Greater than Cells that contain a value greater than a specified value Less than Cells that contain a value less than a specified value pie than or Cells that contain a value greater than or equal to a specified value equal to 14 Less than or equal to Logical TRUE Logical FALSE Similar to pattern Not similar to pattern PUP v6 User s Manual Cells that contain a value less than or equal to a specified value Cells that contain TRUE Cells that contain FALSE Cells that match a specified pattern designated using wildcard characters Cells that do not match a specified pattern designated using wildcard characters Using Select by Value Before using the Select Value utility select the range that you want to operate on If you select a single cell e The entire worksheet will be examined if you choose the Select Cells option e The entire column of the cell will be examined if you choose the Select Rows based on a single column option After selecting the range 1 Choose Select by Value from the PUP v6 Range Tools menu 2 Make your choice from the Selection type options Ensure that the correct range is specified You can change the range while the Select by Value dialog box is displayed Specify your criteria type in the Comparison dropdown Enter the value or values that describe your criteria Click OK to select the matching cells or rows Patt
111. mize Utilities from the PUP v6 Range Tools menu 2 Choose the Select cell s at random option 3 Specify the No of cells to select value 4 Click OK Inserting a sequence of integers in random order 1 Choose Range Randomize Utilities from the PUP v6 Range Tools menu 2 Select the Insert a sequence of integers in random order option 3 Specify a Beginning with value and an Increment value 4 Click OK Range Randomize Utilities Tips e If the results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z e This utility is useful for randomizing the records in a database 1 Select the cells in a blank column to the left or right of your database 20 PUP v6 User s Manual 2 Insert a sequence of integers use the Insert a sequence of integers in random order option 3 Sort the database using this new column Swap Ranges The Swap Ranges utility makes it easy to swap the contents of two cells or two ranges If swapping ranges the ranges must be of identical size i e the same number of rows and columns Using Swap Ranges Start by selecting one of the ranges to swap Or you can make a multiple selection and select both ranges 1 Select Swap Ranges from the PUP v6 Formula Tools menu 2 Verify that the two range selection boxes contain the addresses of the ranges you want to swap Note that labels indicate the number of rows and columns for each range selection box
112. mp Macro Tools menu 2 Inthe VBA Project Summary Report dialog box select the workbook that will be reported on The list does not include 1 VBA projects that are protected with a password 2 VBA projects in which macros have been disabled and 3 VBA Projects contained in workbooks that have never been saved 3 To generate a report of all UserForm controls place a checkmark next to List UserForm controls 4 Click OK The report will be created in a new workbook VBA Project Summary Report Tips e The VBA Project Summary Report lists all modules even if they are empty e Every Excel sheet has an associated code module named Sheet1 Sheet2 etc and the workbook itself has an associated code module named ThisWorkbook These modules always exist even if they are empty e If you are using Excel 2002 or later your security settings may not allow the VBA Project Summary Report to examine your VBA Project To change your security setting 1 Select Tools Macro Security 2 Click the Trusted Sources tab 3 Place a checkmark next to Trust access to Visual Basic Project Remove All Macros The Remove All Macros utility will remove any or all of the following elements from a workbook Item Description VBA Macros All VBA macros in the workbook including those found in VBA modules class modules and code modules for workbook objects e g Sheet1 ThisWorkbook UserForms All UserForms i e custom dialog boxes created wi
113. nd is not available on the shortcut menus for Column or Row selections Applies Center Across Selection formatting to the selected cells This command is not available on the shortcut menus for Column or Row selections Forces all sheets in workbook to have the same range selection and upper left cell as the active sheet See Synchronize Sheets Displays or hides the PUP Date Picker Toolbar which is used for entering or formatting dates Displays or hides the PUP InfoBox Toolbar which is useful for auditing worksheets e Press Shift F10 to display the shortcut menu without using a mouse 98 PUP v6 User s Manual Enhanced Shortcut Menus Chart The new shortcut menu commands listed below appear when you right click an embedded chart or an object on a Chart sheet Menu item Description Export Chart as Graphic Runs the PUP v6 Export Charts utility Convert Chart to Picture Runs the PUP v6 Convert Chart to Picture utility Chart Data Labeler Runs the PUP v6 Chart Data Labeler Utility Chart Report Runs the PUP v6 Chart Report utility Enhanced Shortcut Menus Sheet Tab The new shortcut commands listed below appear when you right click a sheet tab Menu item Description Sort Sheet Tabs Displays the PUP v6 Sort Sheets dialog box See Sort Sheets Delete Empty Sheets Deletes all empty worksheets in the active workbook Activate a Sheet Displays a list of all sheets in the active workbook Activate a
114. nd you can continue to work in Excel while it is displayed Calculator Tips e The C key clears the calculator display The CE key clears the current entry e f you use the calculator from the keyboard use the asterisk key for multiplication e fa range of cells is selected clicking Paste inserts the result into the upper left cell in the range e If the active worksheet is protected you will not be able to paste the results e If you accidentally paste a value use Undo from the Edit menu or Ctrl Z to restore the previous value Synchronize Sheets The Synchronize Sheet utility forces all sheets in workbook to have the same range selection and upper left cell as the active sheet For example if cell D32 is in the upper left corner of the active worksheet s window the Synchronize Sheets utility will loop through all other worksheets in the workbook and place cell D32 in the upper left corner If a multi cell is range is selected when you run this utility the same range will be selected in the other worksheets Using Synchronize Sheets 1 Activate a worksheet and select a cell or range 2 Choose Synchronize Sheets from the PUP v6 Worksheet Tools menu 3 A message box will appear that tells you the how many worksheets were synchronized Synchronize Sheets Tips e This operation cannot be undone e f you re using the Enhanced Shortcut Menus option the Synchronize Sheets command will also appear on the shor
115. nge by the operand Raises each cell in the range to the power specified as the operand Rounds each cell in the range to the number of decimal places specified as the operand Lets you choose a function to apply to each cell in the range Only single argument functions are available Lets you create a formula like expression which is evaluated for each of the selected cells The expression can include multi argument Excel functions If Create formulas is checked the selected cells will be converted to formulas If a cell already contains a formula and Skip formula cells is not checked the formula will be modified To ignore cells that contain formulas select Skip formula cells Normally you will want to keep this option checked Click OK Math Without Formulas Tips If the results of the operation are not what you expected choose Undo from the Edit menu Or press Ctrl Z This utility is also useful for converting non number values to actual values The phenomenon of non number values is common when importing data from another source For some reason Excel may consider the imported values to be text not numbers To convert these cells to actual numbers use the Multiply operation and specify 1 as the operand This will effectively force Excel to treat the contents of these cells as numeric values In some case performing a mathematical operation may result in an error An error may occur when working with ve
116. ngs from is correct or change it to a different sheet The To list box lists all other non hidden sheets in the workbook Select the sheets that will receive the page setup settings By default all sheets are selected You can unselect sheets by clicking them in the list The All button selects all sheets in the list and the None button deselects all sheets in the list Click Next and you ll see a summary of what will be copied Click Finish to display Excel s standard Page Setup dialog box At this point you can make changes to the page setup settings if necessary Click OK to copy the settings Copy Page Setup Tips Hidden sheets are not listed If you need to copy the settings to a hidden sheet you will need to unhide the sheet before using this utility Page setup settings can be copied only to similar sheets In other words you cannot copy settings from a worksheet to a chart sheet or vice versa The To list box displays the sheets that are the same type as the sheet selected in the Copy the Page Setup settings from box The settings that are copied are the settings that appear in Excel s Page Setup dialog box select Page Setup from the File menu The only settings that cannot be transferred are Print area and Print titles both are located on the Sheet tab of the Page Setup dialog box 71 PUP v6 User s Manual Add Path to Header or Footer Prior to Excel 2002 there was no direct way to print a workbook s path
117. nly the formula cells will be affected 1 Choose Error Condition Wizard from the PUP v6 Range Tools menu 2 In Step 1 verify that the range is correct Click Next 3 In Step 2 specify the type of error that you want to trap Click Next 4 In Step 3 specify what you would like to display instead of the error Click Finish Error Condition Wizard Tips e If the results of this utility are not what you expected choose Undo from the Edit menu or press Ctrl Z e In Step 3 the information at the bottom of the dialog box changes depending on the option you select For example if you choose to display text the dialog box displays a text box so you can enter the text to display e This utility does not attempt to determine if your formulas already have an error condition 39 PUP v6 User s Manual PUP Worksheet Function Library This utility adds one or more VBA macros to your workbook These macros provide you with new worksheet functions that you can use in your formulas The 50 new functions are arranged into five categories Date amp Time DAYSINMONTH MONTHWEEK TIMEXX TIMEXX_ADD TIMEXX_SUBTRACT TIMEXX_SUM WHICHDAY XDATE XDATEADD XDATEDAY XDATEDIF XDATEDOW XDATEMONTH XDATEYEAR XDATEYEARDIF Information CELLCOLORINDEX CELLFONTCOLOR CELLHASFORMULA CELLTYPE EXCELDIR FILEEXISTS FILENAME NUMBERFORMAT SHEETCOUNT SHEETNAME USER Lookup amp Reference CREDITCARD HINTERPOLATE LASTINCOLUMN
118. nterchange Format JPEG Joint Photographic Experts Group TIFF Tagged Image File Format PNG Portable Network Graphics Using Export Range as Graphic 1 2a kw N Before using Export Range as Graphic select the range of cells that you want to export as a graphic image Or you can select the range while the Export Range as Graphic dialog box is displayed Choose Export Range as Graphic from the PUP v6 Range Tools menu Select a graphics file format GIF JPG TIF or PNG Select a copy format either Bitmap or Picture Click Specify Filename In the Save As dialog box specify a filename and location and click Save 26 PUP v6 User s Manual Export Range as Graphic Tips e The default file name for the exported graphic file is based on the sheet name and range you selected For example if you export range C5 H12 on Sheet1 using the GIF format the default file name will be sheet1 C5 H12 gif You can change this to a different name in the Save As dialog box e Experiment with the Bitmap and Picture cell copy options to obtain the best results This refers only to the method of copying the range within Excel The exported graphic file will always be a bitmap image e f your sheet displays cell gridlines they will appear in the graphic image If you don t want gridlines to appear select Options on the Tools menu click the View tab and remove the checkmark from Gridlines e The Export Range as Graphic utility makes it eas
119. o delete all defined bookmark names Use Rename to change the name of the selected bookmark Click Close to close the Edit Bookmarks dialog box Note You cannot modify the definition of a bookmark To change the worksheet worksheet or range for a bookmark you need to delete the old bookmark and create a new one PUP Bookmarks Tips Remember that you can create multiple bookmarks for a single workbook For example you might have a large budgeting workbook that contains many sheets and ranges You can create bookmarks so you can quickly activate a particular section of the workbook If the bookmarked workbook s window is hidden it will be unhidden when the bookmark is selected If the bookmarked workbook s window is minimized it will be restored when the bookmark is selected If your security settings display a warning when a workbook that contains macros is opened this warning will not appear when the workbook is opened using the PUP Bookmarks toolbar This is normal behavior when a workbook is opened via a VBA macro PUP v6 Amusements 103 PUP v6 User s Manual This section describes some additional non serious programs that are included with PUP 2000 Video Poker Video Poker works much like the video poker games found in most casinos The goal is to get a good poker hand and accumulate points To begin playing video poker choose Video Poker from the PUP v6 Amusements menu 1 Click Deal to receive five cards
120. of PUP v6 s registry settings Select About Power Utility Pak from the PUP v6 menu Then click Registry Setting and respond No to the message box This will restore PUP v6 to its default state Be aware that this action will delete any Sequences and PUP Bookmarks that you have defined 113 PUP v6 User s Manual The PUP v6 Source Code Licensed users of PUP v6 may purchase the complete VBA source code for a nominal fee Examining this well documented code is an excellent way to improve your VBA programming skills With these files you ll learn exactly how each utility and function was written and you can adapt these techniques to your own work What do you get If you purchase the PUP v6 Developer s Package 59 95 you receive an end user license for PUP 2000 plus a Microsoft Word document that describes how to access all of the VBA source code for PUP v6 For those who like details You ll have complete access to e 78 pro quality Excel add in files e Approximately 1 140 VBA procedures e More than 23 000 lines of VBA code e About 80 finely tuned UserForms e 7 class modules What can you do with it The VBA source code is available primarily for educational purposes The utilities in PUP v6 cover a wide range of applications Studying a particular utility can provide you with new insights for developing your own Excel applications Or you might want to customize a particular PUP utility to better meet your needs In either
121. of concatenation amp operators Note Because each formula cell is scanned separately it may take a bit longer to generate a map when the Include formula complexity values option is selected However using this option may make it easier to spot an errant formula among a group of formulas that should be identical Using the Worksheet Map toolbar e To view a particular cell or range in the original workbook select the range of cells in the map workbook then click the Go to button on the Worksheet Map toolbar e To return to the map workbook click the Return to Map button on the Workbook Map toolbar e To display help including a map key that describes the map codes click Help on the Worksheet Map toolbar Worksheet Map Tips e The Worksheet Map utility is particularly useful when you receive a workbook created by someone else This tool can provide an overview of how the worksheets are organized e f you choose the All sheets in the active workbook option a map is generated for each visible worksheet in the workbook maps are not created for hidden sheets 88 PUP v6 User s Manual Compare Ranges The Compare Ranges utility performs a cell by cell comparison of two ranges or two entire worksheets and generates a report of the differences found You can select among three options that determine if two cells are different Comparison Options Result Compare cell contents If checked cells will be considered diff
122. onal Formatting Report Tips e Acellcan have as many as three Conditional Formatting conditions Each is listed separately in the report Condition1 Condition2 and Condition3 Workbook Link Finder The Workbook Link Finder utility helps you identify all links in a workbook even the common phantom links that are difficult to track down You can identify any of the following types of links Link Type Description Linked files The items that appear when you select Links from the Edit menu Formula links Formulas that refer to cells in a different workbook Name links Names that are defined using a different workbook XL5 95 dialog box links Links in the controls contained on Excel 5 95 dialog sheets 83 PUP v6 User s Manual Chart links Links contained in a chart s SERIES formula titles data labels or in shapes contained in the chart Object links Links contained in drawing objects or shapes on worksheets OLE links Links from embedded objects This utility also has an option to create a report of all links Using Workbook Link Finder Activate a workbook that contains links to one or more other files Select Workbook Link Finder from the PUP v6 Auditing Tools menu In the Workbook Link Finder dialog box select a Link type The links of that type if any will display in the Links list box 4 Selecta link in the Links list box and more information about the link will display below the list box 5 Tocreate a repo
123. onvert the value to text If you reply Yes a single apostrophe will be inserted as a prefix 44 PUP v6 User s Manual 3 D Cell Shading The 3D Cell Shading utility adds an attractive three dimensional look to a cell or range of cells You can choose from a raised look or a depressed look and you can also select the line thickness Using 3 D Cell Shading 1 Before using the 3 D Cell Shading utility select the range of cells to which you want to apply the formatting This can consist of a single range or a multiple selection Choose 3 D Cell Shading from the PUP v6 Formatting Tools menu Select the desired type of shading using the Shading type and Lines options a preview appears in the center of the dialog box 4 To erase any existing border in the selection select Erase existing borders 5 Choose OK The selected cells are formatted with a gray background and borders of the specified type 3 D Cell Shading Tips e If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immediately This will restore the previous formatting of the selected cells e 3 D Cell Shading also works with multiple selections For example you can select a noncontiguous group of cells and the formatting will be applied to each group separately e Usually applying a light gray shading to all of the surrounding cells will maximize the 3D effect e You can change the interior color after the special formattin
124. ouse or the keyboard The dialog boxes adhere to common keyboard standards Use the Tab key to cycle among control and use Altt underlined keys to access a particular control The main PUP v6 add in pup6 xla uses very little memory Additional memory is not used unless you invoke one of the utilities You control how many utility files are kept in memory by using the PUP Options dialog select PUP v6 PUP Options If you re working with a very large or complex workbook with lots of formulas you may find that the performance is slow because the workbook is recalculated frequently For example when selecting a PUP utility you may experience a delay before the dialog box is even displayed Or generating reports may take an unnecessarily long time To speed things up try setting Excel s calculation mode to Manual use the Tools Options command and choose the Calculation tab in the Options dialog box When you re finished with the operation return the Calculation setting to Automatic to ensure that your formulas return the correct result Many of the PUP utilities do not work if the worksheet is protected If that s the case you will see a warning when you attempt the operation Some of the PUP utilities modify a range of cells Be aware that these operations also affect cells that are in hidden rows or columns In most cases the effects of using a utility are reversible If the results of an operation are not what you expected choose
125. protected worksheets To include the missing information in the report unprotect the sheets and generate the report again 79 PUP v6 User s Manual e f you are using Excel 2002 or later your security settings may not allow the VBA project report to be generated To change your security setting 4 Select Tools Macro Security 5 Click the Trusted Sources tab 6 Place a checkmark next to Trust access to Visual Basic Project Date Report The Date Report utility creates a useful report that describes all date cells in a worksheet or workbook This report may help you identify potential year 2000 problems Using Date Report 1 Select Date Report from the PUP v6 Auditing Tools menu 2 Inthe Date Report dialog box select the scope for the date report either Active sheet only or All sheets in the active workbook 3 Click OK The report will be created in a new worksheet Date Report Tips e The date report is useful for identifying dates that use the wrong century When you enter a date using a 2 digit year Excel has its own rules for determining which century is used e The date report includes all cells that contain dates It also includes non date cells that use any of the following date related functions DATE DATEDIF DATEVALUE DAY DAYS360 EDATE EOQMONTH MONTH NETWORKDAYS NOW TODAY WEEKDAY WORKDAY YEAR and YEARFRAC e The rows of the date report can be sorted by any column 80 PUP v6 User
126. r of months and click Create The appointment calendar workbook will be created and the dialog box will be closed Note The first sheet of the appointment calendar workbook contains instructions To close the dialog box after viewing the Calendar tab click Close Perpetual Calendar Tips Use the This month button to display the current month and year If the active sheet is protected you cannot paste a calendar picture When creating a calendar workbook with the Workbook tab choose Include previous next month dates if you d like to dates from the previous and next month to fill in the blanks before the first day and after the last day When a calendar workbook is created All of the unused rows and columns are hidden The row and column borders are hidden The day numbers are in text boxes and the sheets are protected so the dates cannot be selected or modified You can however enter text into the calendar 74 PUP v6 User s Manual For an easy way to insert a formatted date from a calendar display use the PUP Date Picker toolbar Toggle PUP Date Picker Toolbar The PUP Date Picker toolbar makes it easy to insert a date into a cell by picking the date from a calendar display You can also select the date format from a list of common date formats Using the PUP Date Picker Toolbar 1 Notes Select Toggle PUP Date Picker Toolbar from the PUP v6 Date amp Time Tools menu The PUP Date Picker toolbar will be
127. ress Ctrl Z Keep in mind that formula cells are not changed by this utility 13 PUP v6 User s Manual Force Values in Cells The Force Values In Cells utility is useful in situations in which Excel does not recognize numeric data as values This sometimes occurs when data is imported from other sources Using Force Values in Cells To force Excel to recognize number values as numbers 1 Start by selecting the range 2 Choose Force Values In Cells from the PUP v6 Range Tools menu 3 Verify that the Range box displays the correct range or change it if necessary 4 Click OK Force Values in Cells Tips e If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immediately This will restore the previous formatting of the selected cells e This utility will also convert values in cells that are preceded by an apostrophe Select by Value The Select by Value utility selects cells or entire rows based on criteria you specify After the cells or rows are selected you can work with them as a group For example you can apply formatting to the cells or delete the selected rows The types of comparison criteria are Comparison What it selects Empty Empty cells Between Cells that contain a value that is between two specified values Not Between Cells that contain a value that is not between two specified values Equal to Cells that contain a value equal to a specified value Not equal to Cel
128. rom the PUP v6 Chart amp Object Tools menu In the Object Align Size amp Space dialog box make the appropriate changes Click Exit to close the Object Properties dialog box Aligning objects 1 2 3 4 In the Object Align Size amp Space dialog box click the Align tab Select the dominant object from the drop down list The dominant object is used as the basis for the alignment of the other objects Select the desired alignment option and click Apply If the results aren t what you expected select Undo in the Align tab Sizing objects 1 2 In the Object Align Size amp Space dialog box click the Size tab Select the dominant object from the drop down list The dominant object is used as the basis for sizing the other objects Select the desired option from the Size options and click Apply If the results aren t what you expected select Undo in the Size tab Adjust the size of all objects interactively by using the spinners Spacing objects 1 2 3 Or In the Object Align Size amp Space dialog box click the Space tab Select the desired option from the Spacing options and click Apply If the results aren t what you expected select Undo in the Space tab 66 PUP v6 User s Manual Adjust the spacing of all objects interactively by using the spinners Object Align Size amp Space Tips If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immed
129. rt that describes all links click Report This button closes the dialog box and creates a report in a new workbook 6 If you don t generate a link report click Close to close the Link Finder dialog box Notes e When you run Workbook Link Finder you will be presented with a list of all linked workbooks that are currently open Before you can use this utility you must close all of the workbooks that are used as a link source in the workbook being checked e You will be warned If the workbook has any protected sheets The Workbook Link Finder is not able to identify links contained on protected sheets e tis possible for a workbook to contain links to files that are not listed when you choose Links from Excel s Edit menu This can occur if a name is defined using an external reference but the name is not actually used in a formula In such a case the Workbook Link Finder will locate the linked names when you select Name links but it may not list the linked file when you select Linked files Workbook Link Finder Tips e When you open a workbook Excel may prompt you to update links In some cases you may think that the workbook doesn t contain any links This type of phantom link is often very difficult to identify Use the Workbook Link Finder to track down links Often a phantom link is caused by an erroneous name link Check this by using the Name links option e f you can t locate a link identified by Workbook Link Finder keep
130. rtained for at least 10 minutes 109 PUP v6 User s Manual Technical Support This section contains topics that deal with technical support for PUP v6 Obtaining Technical Support e Registered users are entitled to unlimited free technical support for the Power Utility Pak This assistance is available directly from the author Use this email address support j walk com e Questions asked via e mail are typically answered within 24 hours often within the same day e f you discover a bug or problem with a utility or worksheet function please report it The problem will be corrected and an updated file will be posted at the Program Updates site Downloading Program Updates The goal is to make every component of PUP v6 error free and accurate Bug fixes and updated files are available free of charge from the following web site http www j walk com ss pup pup6 If you discover a problem that s not addressed in an update file please report it using the e mail address below Describe the problem and list the steps required to reproduce it The problem will be corrected and an updated file will be posted at the Program Updates site support j walk com Solutions to Common Problems If you re having a problem with PUP v6 check the following information The PUP v6 menu is not displayed Probable causes e Power Utility Pak is not installed Refer to Installing PUP v6 e Another Excel application has reset the menu
131. ry large numbers or using a function with an inappropriate 30 PUP v6 User s Manual argument If an error occurs the cell may display VALUE or some other error message e Some of the functionality of Math Without Formulas is available through Excel s Paste Special command with the Add Subtract Multiply or Divide option but Math Without Formulas is much more versatile and easier to use Math Without Formulas Additional Details This section contains additional information about Math Without Formulas Specifying an operand The operand is the value used in the mathematical operation The operand can consist of any of the following a simple value an expression a single cell reference a name defined for a single cell The operand value may consist of any valid number Following are examples of valid operands e 146 e 0125 e 899 e 105 e 1E 05 If you enter an expression for the operand the expression is evaluated and the resulting value is used as the operand for the selected operation The expression must result in a numeric value Following are examples of valid expressions e 1 3 e 24 60 60 e 154 97 11 You can also use a single cell address or name for the operand or in an expression If the cell contains a non numerical value you ll get an error message If you use a name the name must refer to a single cell only Using a multi cell range name will generate an error Following are examples of valid
132. s cccecis esie rrea aean eaaa a ea eaae aeae aa a aaa aoaiina eaaeo 74 Toggle PUP Date Picker Toolbar ssssussssnnnnennnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn nnn 75 Reminder Alain sicion innana cvetncanedd aaa a aaraa aaa a aa eaaa Kaaa E aa Aaaa aaan si cnetereniesesaedene 77 The Utilities Auditing Tools sssssssnsnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn 79 Workbook Summary Report s ussssnsnsennnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn nnmnnn na 79 Date RE Lol a T A A T 80 eE o a aaa A T a Sen ch eden sent teceeties aaa sentteenes Crvczareneteanareeeevasoucteey 81 Number Format REPOT 05 2 c050 casseaieccvacsecescscanndeeueceedusveqsucneececeszs ensnsesaedeeansvesiucerentsennsvoazecer 82 Data Validation REPO Mie ic c cscceccecceenssctecivesceteseencttececeedenetancteetecasersetenentedeceasrentecciatesencerenecten 82 PUP v6 User s Manual Conditional Formatting ReEPort ccccssseeccssneeeeeeseneeeeeeeeeeeeeeeeeeeeseseeeeesseeeeeeseseeeeeeseeeeseesseenes 83 Workbook Link Finder s cc cc c ctiveceesst i thcectecien aed anaie aeaaaee aE bt cdaeedsvnyeerchscdavedtareeeetancdey 83 Name LISt bcs fees sccasecctceass scnacunas ct cvusususneuensefteaiesseaan atteassstecnteiectcduvasaiancacnattearsavacniyaceacsuvaseceras 86 Worksheet MaDe e e Te cece cd us estos dec cb ue siete aie Ge n E ae aaa ahaaa aa a aa aaaea ENa EEEa 87 Compare Ranges aia cissec
133. s and each workbook can have any number of bookmarks When you create a new bookmark you assign a meaningful name which appears in the drop down list in the PUP Bookmarks toolbar To activate a bookmark select it from the list The bookmarked sheet and range will be activated If the file is not already open it will be opened for you Displaying PUP Bookmarks To toggle the PUP Bookmarks toolbar choose PUP Bookmarks from the PUP v6 menu e When the PUP Bookmarks menu item is checked the PUP Bookmarks toolbar is displayed and ready for use e When the PUP Bookmarks menu item is not checked the PUP Bookmarks toolbar is not displayed The PUP Bookmarks toolbar is created on the fly when you enable the PUP Bookmarks feature and it is destroyed when you disable the PUP Bookmarks feature or close PUP v6 Therefore you cannot customize this toolbar Using PUP Bookmarks To use the PUP Bookmark option the PUP Bookmarks toolbar must be displayed Creating a bookmark Make sure the workbook that will be bookmarked has been saved 2 Activate the workbook worksheet and range for the bookmark To create a bookmark for a chart sheet activate the chart sheet 3 Click Create In the PUP Bookmarks toolbar The Create Bookmark dialog box lists the bookmarks currently defined 4 Enter a meaningful name for the bookmark Select the action to take when the bookmark is selected see the list of options below Click OK and the bookmark w
134. s with a password check the Use password checkbox Click Protect If you chose Use password you will be prompted for the password You will need to type the password two times Unprotecting worksheets 1 In the Cell Lock Sheet Protect Utilities dialog box click the Sheet Protect Unprotect tab In the Select worksheets box select the worksheets that you want to unprotect Sheet names preceded by an asterisk denote protected sheets Click Unprotect If a password is required to unprotect the sheet s you will be prompted to enter the password Cell Lock Sheet Protect Utilities Tips This utility works only with worksheets To protect or unprotect Chart sheets activate the chart sheet and choose Protection from the Tools menu By default all cells are Locked and not Hidden The status of the Hidden and Locked attributes can be viewed or changed in the Format Cells dialog box choose Cells from the Format menu and click the Protection tab Locking or hiding cells has no effect unless the sheet is protected When the sheet is protected a locked cell cannot be changed and the contents of a hidden cell cannot be viewed in the Formula bar A typical scenario is to unlock all input cells and lock all other cells before protecting a worksheet This allows the user to enter data but formulas and text cannot be changed This utility affects only the used area of the worksheet Therefore empty cells outside o
135. s Manual Formula Report The Formula Report utility creates a useful report that describes all formulas in a worksheet or workbook The report is generated in a new workbook and the report for each worksheet appears in its own sheet The sheet names in the report correspond to the sheet names in the original workbook The formula report consists of 14 columns and displays the following information for each formula cell Information Address Row Column Result Display Number Format Formula RC Formula Locked Hidden All Precedents Direct Precedents All Dependents Direct Dependents Name Description The cell address of the formula The row number of the formula The column number of the formula The unformatted result returned by the formula The result of the formula as displayed in the cell that is with number formatting applied The number format string for the cell The formula standard format The formula in R1C1 format TRUE if the cell s Locked attribute is set this has no effect unless the sheet is protected TRUE if the cell s Hidden attribute is set this has no effect unless the sheet is protected The number of cells used by the formula either directly or indirectly same sheet only The number of cells used directly by the formula same sheet only The number of cells that refer to the formula cell either directly or indirectly same sheet only The number of cells
136. s dialog box is a stay on top dialog box This means that you can easily select different cells to work with and continue working in Excel while the dialog box remains on screen The cell selection can consist of a discrete range entire rows or columns a multiple selection or even the entire worksheet When you are finished using Text Tools click Close Changing the case of text 1 Inthe Text Tools dialog box select the Change case operation 2 Select the appropriate Change to option to convert the selected text 3 Click Apply to modify the selected cells Adding text 1 Select the cells that you want to modify 2 Inthe Text Tools dialog box select the Add text operation 3 Enter the text you want to add in the Text to add box 4 In the Character position drop down select the option that corresponds to the position where you want the text to be added 5 If you want to limit the operations to selected cell that contain text select Skip non text cells If this option is not checked cells that contain values will also be modified 6 Click Apply to modify the selected cells PUP v6 User s Manual Removing text by position 1 2 3 4 6 Select the cells that you want to modify In the Text Tools dialog box select the Remove by position option In the Characters to remove drop down choose the number of characters to be deleted In the Character position drop down choose the position from which you want the
137. s to Visual Basic Project Toggle Face ID Toolbar The Toggle FacelDs Toolbar utility is useful for those who modify Excel s user interface specifically the images used on toolbars and menus It displays a toolbar that makes it easy to identify the numeric FacelD value for each toolbar image that s available Using Toggle Face ID Toolbar To hide or display the CommandBar FacelD Images toolbar e Choose Toggle FacelDs Toolbar from the PUP v6 VBA amp Macro Tools menu e If the CommandBar FacelD Images toolbar is not visible it will be displayed If the CommandBar FacelD Images toolbar is visible it will be hidden e The images are displayed in groups of 100 Use the drop down list at the bottom of the toolbar to show a different set of 100 images e When you hover your mouse pointer over an image the FacelD value for that image will appear 95 PUP v6 User s Manual Toggle Face ID Toolbar Tips e The Commandbar FacelDs Toolbar is created on the fly whenever it is requested 96 PUP v6 User s Manual Enhanced Shortcut Menus As an option the Power Utility Pak can add new commands to Excel s shortcut menus A shortcut menu appears when you right click an object Enhanced shortcut menu options are displayed with a P icon The Enhanced Shortcut Menus command on the PUP v6 menu serves as a toggle When this menu item has a checkmark enhanced shortcut menus are enabled When the checkmark is not present enhanced
138. sa ee eee ec 103 VIO POKEN aici cede cddece EA 104 Dice Game wi aa a aaa a E teva ieen Does ce een weed eeepc AE Taa Eeee raa eiar eter 104 Bomb HU Int a a aeaa vtec Seed a eae Aa a a anda vdeenun deena ea a aaan e Aaea Ea ea 107 Hangmiamsinien aana nea Eaa E E eaea e NEE A EENE N AER 108 Random Number Generator ccscesceccececeeeeseeeneeeeeeeeeeeeseaeeeseeeeeeeesseaeeeseseseeeeeeeeeeeeeeeeneees 109 Technical SUpport nunana aes 110 Obtaining Technical Supports enaere raae ree E aria ee aE arar reaa E Eaa at haaa aun kN aina 110 Downloading Program Updates assssnusseunnnnennnnnnnnnnnnunnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn ennnen 110 Solutions to Common Problems ccceceeeeeeeeeeeeeeeeeeeeeeeaneeceeeeeseeseaeeeseseeeseeseeaneeeeeees 110 Technical IMPOR MANION wicesdessccs sissdtseaccsstencensdees pensancnscahsssasaseasen gaascsssacaseuanscmecaae 113 HOW PUP VG WO KS ate raa aaa Sevesedaxs coants Seteetndassnanit cecevetexs suanicderevenshvencnuscereveesyesd 113 The PUP vO Source Codes ivvecciciceceetidececessncee neinna nee e Geedeeeseestbeeecentvesvesseboeets Penahanan aapa a rini 114 The PUP v Trial Version i ec ehenicienennnnenneninictiene 115 How to Order PUP VG inai siciiecs aoada eaa ana aaaea nna aa aaia o da keta aa aeaaaee iaa daa Eiaa aia 115 End User License Agreement ssssssssssssnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn 117 About J Walk and Associates INC ccccccceeee
139. se it or until its corresponding utility file is closed automatically The utility file can be closed automatically if you have specified a maximum number of utility files to keep open in the PUP v6 Options dialog box If the PUP InfoBox toolbar disappears unexpectedly just select Toggle PUP InfoBox Toolbar from the PUP v6 Auditing Tools menu again e To close the PUP InfoBox toolbar click the X in the title bar Or select Toggle PUP InfoBox Toolbar from the PUP v6 Auditing Tools menu PUP InfoBox Toolbar Tips e When Active Cell mode is in effect the toolbar contains a number of clickable buttons along the left side e To change the number formatting of the active cell click Number Format This displays the Number Format tab of Excel s Format Cells dialog box e To change the named Style of the active cell click Style to display Excel s Style dialog box e To change the protection status of the active cell click Protection This displays the Protection tab of Excel s Format Cells dialog box e To view or modify the conditional formatting for the active cell click Conditional Fmt This displays Excel s Conditional Formatting dialog box 92 PUP v6 User s Manual e To view or modify the data validation for the active cell click Data Validation This displays Excel s Data Validation dialog box e To view tracer arrows to formulas that use the active cell directly click Dir Dependents e To view tracer arrows to cells
140. select this option the list box will display the names that aren t defined in terms of a cell or range that is named formulas Be careful when deleting a name A name that s used in a formula will cause the formula to return an error You can use PUP s Unapply Names utility to replace names in formulas with their corresponding range references but you must do this before you delete the name The Delete and the Delete All button are not available if the workbook s structure is protected To delete a name in a protected workbook you must unprotect the workbook Deleting names cannot be undone If you delete a name that is used in a formula the formula will return an error You can always delete names listed in the Erroneous names category without causing any problems The Report button produces a more useful listing than Excel s Paste List option available from Excel s Paste Name dialog box which is displayed by the Insert Name Paste command Worksheet Map The Worksheet Map utility creates a handy color coded map that summarizes the contents of a worksheet A worksheet map can help you spot errors in your worksheet for example a non formula among a group of formulas You can generate a worksheet map for a single worksheet or for each worksheet in a workbook The worksheet map uses the following codes Map Code Description Green with F The cell contains a formula Green with a numeric value The cell contains
141. series Specify the position of the data labels relative to the series marker bar column or other series item Not all positions are appropriate for every series type If you choose an invalid position the default position for the series type is used Chart Data Labeler Tips e If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immediately Exception If the data labels originally contained links the links cannot be recreated when the operation is undone e The Label range for the chart labels can be in a different worksheet or workbook 63 PUP v6 User s Manual e After the data labels are added you can change their position by using Excel s normal procedures Select the data series labels and the press Ctrl 1 e To change the position of a single label select the data series labels click the single label and then drag it to its desired position e f you select a single cell for the Label range the Chart Data Labeler utility will use additional cells below the specified cell e f you re using the Enhanced Shortcut Menus option you can right click on a chart and choose Chart Data Labeler from the shortcut menu Caution The data labels are not linked to the specific data points For example if you delete a row that contains a data point for the chart the data labels will no longer be correct You will need to re run the Chart Data Labeler utility to re apply the labels Resize C
142. sert Name Define command Create Workbook Contents Sheet Tips e If the results of this procedure are not what you expected select Undo from the Edit menu or Ctrl Z This will delete the contents sheet If you chose the Create buttons and macros style it will also delete the macros created for the buttons e Hidden sheets in the workbook are not included in the contents sheet e f your workbook has many sheets choosing a larger number for the Number of columns option will allow more hyperlinks or buttons to be visible without scrolling e Cell A1 of the contents sheet is named 58 PUP v6 User s Manual We Os RKB 0O Ke CON oTr EsN ToS To quickly return to the contents sheet press F5 and double click the W O R K B O 0 K C O N T E N T S item in the Go To dialog box This name will always appear near the top of the list in the Go To dialog box e f you choose the Create buttons and macros option VBA macro code is added to your workbook This code resides in the code module for the Sheet object that corresponds to the contents worksheet This code module contains a simple macro for each button Depending on your security settings you may receive a macro warning when the workbook is opened e f you choose the Create hyperlinks option a new named style is created You can change the formatting of the hyperlinks by modifying the ContentsHyperlink style To do so e Choose Style from Excel s Format menu e Select
143. shortcut menus are disabled Displaying Enhanced Shortcut Menus To toggle enhanced shortcut menus choose Enhanced Shortcut Menus from the PUP v6 menu e When Enhanced Shortcut Menus is checked the Power Utility Pak enhanced shortcut menus are enabled e When Enhanced Shortcut Menus is not checked the Power Utility Pak enhanced shortcut menus are disabled Commands on the Enhanced Shortcut Menus When PUP s Enhanced Shortcut Menu option is turned on enhanced shortcut menus are available when you right click any of the following items e Cell Range Column or Row e Chart e Sheet Tab e Workbook Title Bar e Excel Desktop These commands are described in the following sections 97 PUP v6 User s Manual Enhanced Shortcut Menus Cell Range Column or Row The new shortcut menu commands listed below appear when you right click a cell range column or row Menu item Print Selection Toggle Word Wrap Change Case Quick Chart Center Across Selection Synchronize Sheets Toggle PUP Date Picker Toolbar Toggle PUP InfoBox Toolbar Tip Description Prints the selected range This command does not change the current print area if any Toggles the Wrap Text setting for the current selection The toggling is based on the active cell in the selection Changes the case of text in the selected cells to UPPER lower or Proper Creates a default chart using the selected range This comma
144. ssceccenevsocetesuncncesivsqudegtscencessderesteseecscenersesneess 16 G T Mit or Max Value ccs ic iciece fe aidesendensecte cared dana doce rrea epee dusuvencendevisd casa annpeneeeessdusdunncengeersaenae 17 Cell Lock Sheet Protect Utilities c cccceccececeseee cece ee eeeeeeaeeeeeeeeeseeseeaeeeseeeseeeeeeeaeeeeeeeeneees 18 Range Randomize Utilitie 0 0 0 ccesseee cess eeeeeeeeneeeeeeeneeeeseeeeeeeseeeeeeeseeneeeaseceeeeeseeeeeenseeneeenss 20 WAP Range Saien ia acetic eae decodes la ea cd a es eva she EN S EAA EER EAE EAN RAT rN 21 Delete Hyperlinks nosia senenin teaceceetusecdcc cestecetesseteatcescectecsstcdaceescecaeessacecerenduetersstcdeeresd 21 Insert Sequence NUMER a a a ted a a a aa a ch a Ta aeaa e aA Aarra aE aaia rei 22 Export Range to Filet iccicccceefoecaczentissiceceevncbechesestecvesuctestneescdicedsabspiesesuesiesuctecinessecieesssbetivesd 25 Export Range as Graphics ieic caisvasecveciccsscesaseaneuniiecnsveannsoeececeateves2useniedeeansvetes davenceeazsve3zezee 26 Transform Vertical Range cccceseescecsseeeeeeeseeeseeeeeeeseeeeneeseseseeeseseeeeeseseeeeeseseneeeeesesnenseeseneees 27 Combine ColUmns or ROWS ccccceceeeseee cece eeeeeeee ance eeeeeeseneeeaeeeseeeeeseeeseaeeeseeeseseeaeeaeeeseeeeneees 28 The Utilities Formula TOONS scsssssccassensconsscscessscentccatentansscateestconttes aestpeascenteeel 29 Math Without Formulas 0 ccccceeeceseeeceeeeeeeeeeeeeee seen eeeeeaseeeee sees seee
145. t menu or Ctrl Z This will restore the previous contents of the active cell and will not affect the next sequence number You can create as many sequence definitions as you need In most cases you will use 1 as the Increment value You can however specify any number you like including a negative number If you ve defined a sequence that you no longer use select it in the Insert Sequence Number dialog box and click Delete If you need to change the parameters for a sequence delete the existing sequence and create a new sequence with the same name You ll probably want to use the current sequence number as the Start number for the new sequence When creating a sequence specify 0 as the No of digits if you don t want any leading zeros Note that the full sequence number will always be displayed even if it exceeds the No of digits setting Use the Prefix and Suffix fields to add text information to your sequence numbers For example you can create a sequence that appears as INVOICE 0098 2002 In this example the sequence number is 98 The No of digits is 4 The Prefix is defined as INVOICE and the Suffix is defined as 2002 The next number in this sequence would appear as INVOICE 0099 2002 24 PUP v6 User s Manual Export Range to File The Export Range to File utility exports a range of cells to a new file The new file can be any of the following File Format Description Excel workbook A standard
146. t unloaded when you click Unload All Use the PUP v6 menu to toggle these utilities on and off Remember Settings Remember the last used settings in PUP dialog boxes If checked PUP s dialog boxes will be displayed with the settings you used most recently This setting is turned on by default Uses Ctrl Shift R to display the most recently used PUP dialog box If checked the Ctrl Shift R key combination will display the dialog box for the PUP utility that you used most recently Note that pressing this key combination this will not actually repeat the operation automatically it will only display the dialog box This setting is turned on by default Progress Indicators Show progress indicators for lengthy operations Many of the PUP utilities process cells within a loop If you would like to see a progress indicator while the cells are being processed choose this option This setting is turned on by default Progress indicator threshold You can also specify a threshold which is the minimum number of cells to process that will trigger the progress indicator display There is some overhead involved in PUP v6 User s Manual showing a progress indicator so the operations may take a bit more time The optimal Progress indicator threshold setting will depend on the speed of your system General Usage Tips Following are some general points to keep in mind as you use PUP v6 You can work with the PUP dialog boxes using a m
147. tcut menu that appears when you right click a cell range column or row 52 PUP v6 User s Manual Reset the Last Cell The Reset the Last Cell utility resets the last cell in a worksheet The last cell is defined as the cell at the intersection of the last row and last column that contains data or formatting To activate the last cell press Ctrl End Note If you delete information from a worksheet Excel does not always automatically update the last cell Using Reset the Last Cell 1 Activate a worksheet and choose Reset the Last Cell from the PUP v6 Worksheet Tools menu 2 Adialog box will appear that tells you the location of the last cell Reset the Last Cell Tips e If the last cell doesn t seem to be correct the problem could be blank cells that are formatted Select entire rows or entire columns and choose Delete from the Edit menu Then use the Reset Last Cell utility again 53 PUP v6 User s Manual The Utilities Workbook Tools The workbook tools deal with workbooks Insert File at Cursor The Insert File at Cursor utility performs the following operations 1 Opens a specified file 2 Inserts the contents of the file beginning at the current cursor position the active cell 3 Closes the file These operations are all performed behind the scenes thereby simplifying a rather tedious operation The file to be inserted can be an Excel workbook or a text file Using Insert File at C
148. ter the value in the upper text box on the left side of the dialog box This box will be labeled with the currency that s selected in the Convert from list box The converted value will appear in the lower text box which is labeled with the currency that s selected in the To list box The information is updated automatically whenever you change the value or make a currency change in either of the list boxes 37 PUP v6 User s Manual Converting a range of values Before using the Currency Conversion utility select the range of cells that contains the value to be converted 1 Choose Currency Conversion from the PUP v6 Formula Tools menu 2 Inthe Currency Conversion dialog box select the Convert a Range tab 3 Verify that the Source range is correct and change it if necessary 4 If you select Overwrite source data the data in Source range will be replaced by the converted data 5 To put the results into a different range uncheck Overwrite source data and specify the Results range a single cell is sufficient 6 If you would like the result in the form of formulas select Create formulas This option is not available when the Overwrite source data option is specified Use the Convert from list box to select the currency that you are converting from Use the To list box to select the currency that you are converting to Click OK Updating the exchange rates The Currency Conversion dialog box displays the date and tim
149. th VBA Excel 5 95 Dialog Sheets Sheets that store custom dialog boxes Unless they are hidden these sheets are visible in the workbook window Excel 4 XLM Macro Sheets Sheets that store XLM macros Unless they are hidden these 94 PUP v6 User s Manual sheets are visible in the workbook window Using Remove All Macros 1 Activate the workbook that contains the macros you want to delete 2 Select Remove All Macros from the PUP v6 Workbook Tools menu The dialog box will display a list of macro related items found in the workbook Select the item s to remove 4 Click OK A message will appear that displays a summary of the results Remove All Macros Tips e These operations cannot be undone If there is any possibility that you may need the macros make a backup copy of your workbook before using this utility e In some cases you may receive a macro warning when you open a workbook even though no macros exist in the workbook Excel displays this warning if the workbook contains an empty VBA module or a code module with only blank lines The Remove All Macros utility will remove all empty VBA modules and delete all blank lines from code modules e f you are using Excel 2002 or later your security settings may not allow the detection and removal of VBA Project components To change your security setting 1 Select Tools Macro Security 2 Click the Trusted Sources tab 3 Place a checkmark next to Trust acces
150. than saving to your hard drive so be patient 57 PUP v6 User s Manual 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 You have two choices for the style of the table of contents sheet These are summarized below Navigate to Adds macros to Style Description chart sheets your workbook Hyperlinks Clickable No No hyperlinks Buttons and Clickable buttons Yes Yes Macros Using Create Workbook Contents Sheet Activate the workbook that will contain the table of contents sheet Select Create Workbook Contents Sheet from the PUP v6 Workbook Tools menu Choose the Style Specify a Name for the contents sheet Specify the Location in the workbook for the contents sheet Specify the Number of columns for the hyperlinks or buttons TILO O SO Ne Click OK and the contents sheet will be added to your workbook Removing a contents sheet If you decide that you no longer want the contents sheet you can simply delete the sheet If the contents sheets used the Buttons and Macros option the macros will also be deleted Cell A1 of the contents worksheet has the following range name W O R K B 0 0 K C O N T E N T S This name is created in order to make it easy to return to the contents sheet see Tips below This name is not deleted when you delete the contents sheet If you want to delete that name use Excel s In
151. tton 3 Click the No button in response to the message box After performing these steps PUP v6 will be returned to its default settings PUP v6 User s Manual You can also delete the registry settings manually using the Windows regedit exe program The registry key is HKEY CURRENT USER Software VB and VBA Program Settings Power Utility Pak PUP v6 Options To display the dialog box for Power Utility Pak options choose PUP Options from the PUP v6 menu The PUP Options dialog box contains three tabs described below Utility Management Maximum number of utility files to keep in memory The various utilities that make up the Power Utility Pak are stored in number of separate files These files are loaded on an as needed basis To conserve memory you can specify the maximum number of PUP utility files to keep in memory The default setting is three 3 utility files but you can specify any number between 1 and 12 or no limit List Click this button to display a list of PUP utilities that are currently open Unload All Click this button to unload all of the PUP utility files This will unload the utility files but the PUP v6 add in will remain in memory When a utility file is unloaded it will be opened again if needed Notes e The utility files are unloaded on a first in first out basis e Enhanced Shortcut Menus and PUP Bookmarks are not included in the count of utility files and are no
152. ursor Activate the cell that will be the upper left cell of the imported file Select Insert File at Cursor from the PUP v6 Workbook Tools menu Ensure that the Beginning at this cell reference is correct or change it Click Browse to locate the file OT oe gt oN E gt If you want to retrieve only the values i e no formulas select Values only no formulas If you want to retain the existing formatting select Do not transfer formatting 7 Ifthe selected file contains more than one worksheet you will be prompted for the worksheet to insert Insert File at Cursor Tips e The Insert File at Cursor utility will overwrite existing data without warning If the results aren t what you expected choose Undo from the Edit menu or Ctrl Z immediately e f the imported data contains formulas and you did not select the Values only option double check the formulas to ensure that the references are correct e Columns widths are not adjusted to accommodate the imported data You ll need to make such adjustments manually 54 PUP v6 User s Manual Interactive Hide Unhide The Interactive Hide Unhide utility provides a variety of tools that make it easy to hide workbook windows and sheets The utility has following features Workbook Operation Sheet Operations Change the hidden status Change the hidden status Save workbooks Delete sheets Rename workbooks Rename sheets Close workbooks Activate a sheet Activate
153. used directly by the formula in the active cell click Dir Precedents e When Range Selection mode is in effect the toolbar contains a number of clickable buttons along the left side Click any of these buttons to select the corresponding cells within the current range selection e Total Formulas e Formulas Values e Formulas Text e Formulas Logical e Total Constants e Constants Values e Constants Text e Constants Logical e The Show formulas in R1C1 notation option when checked displays formulas using a different notation Unlike standard A1 notation R1C1 notation makes it very easy to identify cells that contain the same formula Note There is some overhead involved in updating the toolbar to reflect the current active cell or range selection For maximum performance you will not want to keep this toolbar active at all times To temporarily stop the monitoring select Turn Off from the drop down list To close the PUP InfoBox toolbar click the X in the title bar Or select Toggle PUP InfoBox Toolbar from the PUP v6 Auditing Tools menu 93 PUP v6 User s Manual The Utilities VBA and Macro Tools VBA Project Summary Report The VBA Project Summary Report utility generates a listing of all VBA procedures contained in a workbook Optionally the report includes a list of the controls contained in all UserForms Using VBA Project Summary Report Select VBA Project Summary Report from the PUP v6 VBA a
154. utility has some limitations when copying a range that contains array formulas If you re making an exact copy of a multi cell range formula the Copy from range must consist of the entire array and no other cells This utility cannot be used to make an exact copy of a range that contains non identical array formulas Convert Relative amp Absolute References The Convert Relative amp Absolute References utility works with formulas and converts the cell references in the formulas to one of the following reference types e Absolute references for example A 1 e Relative references for example A1 e Column absolute references for example A1 e Row absolute references for example A 1 The formula listed below for example contains relative references SUM A16 D124 If you convert these to absolute references the formula will appear as SUM A 16 D 124 Relative and absolute referencing is a factor only if the cells are copied If a formula that uses absolute referencing is copied the cell references do not change Using Convert Relative amp Absolute References Before using Convert Relative amp Absolute References select the range of cells that contain formulas that you want to convert The selection can include formula cells and non formula cells Only the formula cells will be affected 1 Choose Convert Relative amp Absolute References from the PUP v6 Formula Tools menu Verify that the Range box
155. version of the particular utility file which you can download If an updated version is not available please report the problem 1 Make alist of the exact steps required to reproduce the problem 2 Send an e mail to J Walk amp Associates at support j walk com The problem will be corrected and an updated file will be posted at the PUP v6 Program Updates site Error message Can t Create Embedded Object If you receive an error message Can t Create Embedded Object the most likely cause is a problem with your Excel installation Try uninstalling and then re installing Excel 111 PUP v6 User s Manual The text in dialog boxes is cut off If the text in some of the PUP v6 dialog boxes appears cut off or is not displayed correctly you re probably using the Large Fonts system option Unfortunately this setting is not fully compatible with Excel custom dialog boxes Activate the Windows Control Panel and access the Display Properties dialog box Click the Settings tab and change the Font Size setting to Small Fonts Be aware that changing this setting will affect the appearance of your other programs 112 PUP v6 User s Manual Technical Information This section provides some technical information about PUP v6 and describes how to obtain the VBA source code How PUP v6 Works Power Utility Pak was developed using the programming tools that are supplied with every copy of Excel the Visual Basic for Applicat
156. y to create attractive textual graphic files for Web pages You can apply any type of formatting to the range before exporting it e Graphic objects contained in the range to be exported will also appear in the exported graphic file For example you can create a Word Art graphic and export the underlying cells the Word Art image will also be exported e f your worksheet uses a background picture added with the Format Sheet Background command the image may not be rendered correctly in the exported graphic file e The Export Range as Graphic utility uses the graphic export filters supplied with Excel or Microsoft Office If you are unable to export to a particular file format re run the Setup program for Excel or Microsoft Office and make sure that the export filter is installed Transform Vertical Range The Transform Vertical Range utility converts a single column range into a multiple column range For example you may have a single column of data in the form of mailing labels and each label consists of five rows of information This utility can covert the single column of data into a five column database table Using Transform Vertical Range Before using Transform Vertical range select the data to be transformed It must be in a single column 1 Choose Transform Vertical Range from the PUP v6 Range Tools menu 2 Verify that the Data to be transformed range box contains the address of the input data or change

PUP v6 User`s Manual - The Spreadsheet Page

Contents

Download Pdf Manuals

Related Search

Related Contents