Home

Power Utility Pak - The Spreadsheet Page

Contents

1. 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 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 PNG file formats usually produce the best quality output The dimensions of the exported file will match the chart dimensions To help identify a particular chart select it in the list and click Scroll to or Go to if a Chart sheet is active Page 55 e When exporting a chart on a chart sheet or on multiple chart sheets you can adjust the size of the chart s by resizing the workbook window Make sure the workbook s window isn t maximized then adjust the window size to match the desired chart size e The file names are based on the Chart object names or Chart sheet name with an underscore character substituted for each space e 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 e You can also change the name of an embedded Chart object manual
2. To close the dialog box after viewing the Calendar tab click Close Perpetual Calendar Tips If the active sheet is protected you cannot paste a calendar picture If you choose the Color option when pasting a calendar picture the colors and fonts are based on the active workbook s document theme However the colors and fonts will not change if you apply a different theme the workbook For some interesting visual effects select the calendar picture and use the tools in the Picture Tools Format Picture Styles gallery For additional visual effects create a transparent calendar and place it on top of a shape Then group the two objects and apply formatting 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 o All of the unused rows and columns are hidden o The row and column borders are hidden o 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 Also see the Insert A Live Calendar utility which creates a calendar in a worksheet range using formulas For an easy way to insert a date from a calendar display use the PUP Date Picker Insert a Live Calendar The Insert a Live Calendar utility creates a single month calendar i
3. 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 that refer directly to the formula cell same sheet only The name if any of the cell that contains the formula Using Formula Report Select Formula Report from the PUP v7 Auditing Report menu In the 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 Page 64 e Each formula is listed in standard format
4. C rr ncy Conversi n e save cect ccevece aaa Aa Aa E AE ataa iadaaa a di aaa ikia eaa adaa a aa aaia iaa aada ii a iaaa Select Dy ValU ees AEE A A AEE ORE E E E ls ea Select Gells DY FOME E AEA E E AEA SE ET EE A A G To Min or Max Valle aiii ad ia Group Workbooks and Sheets icon cia iii iii a decian 33 SS NN Interactive Hide Und is altiro Cell Lock Sheet Protect UtilitieS oonnnnncnnnnnnccinnnananinananonancnaninananananon cnc a aoisi a nn aran nn cr rana narran nn aran rra iari Insert Sequence NUMBERS ainda inca E Sousa fee bidesadasaian aces cst evans ian Suede ca dunia dan eds ani cavlaainddavieestaretos Toggle Setting AAA AAA ec Sos ea AAA AAA 1 bev eas be AE A Led vd dea aad Teun bad eae So on Seances etek Set SEO AF e aia de a O e a Os dey Rated DON ao e lo e lo o ses Reset the Last Cell Sort Sheets Save With Backup coooooccnccccccnncoooncnnnncnnnananonoss Create Workbook Contents Sheet Customize New Workbook Te a arr a a less en e s raaa ra Ea adh evos bos oade a aa ant eaees Cas ebadedsuaneneesd chads tee ues laden eas View Active Workbook s Folder cio eseteire r caebeveateedcoesuuventevechadee suadabvedneduussudeedaevesuseeaeatedee e Remove All Macross A a A A AS e rs Perpet akCalenda ui a ada delia iaa Insertia LiVe Calendar voii iia daa a E A OA AA A a EA PUP Date Picker A E AA EA A ene LAA EA AAA 0 IA IAE She RMN Ad A O Rc EIE EE dosed Leak tle AE Sak E E a deh tte Rea ee Seca lees B
5. Workbook Tools menu 2 Choose Yes to unhide all rows and columns in the worksheet Set Scroll Area Tips e The Set Scroll Area utility help prevent users from scrolling away from the active area of a worksheet Page 38 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 To remove a scroll area manually i e unhide the rows and columns 1 Click the select all button located at the intersection of the row and column headers 2 Right click any row number and choose Unhide to unhide all rows 3 Right click any column letter and choose Unhide to unhide all columns 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 e Activate a worksheet and choose Reset the Last Cell from the PUP v7 Workbooks and Sheets Worksheet Tools menu e The last cell is reset and you see a dialog box that tells you the location of the last cell Reset the Last Cell Tips e Ifthe last cell doesn t seem to be correct the problem could be blank cells tha
6. mm yyyy m mm mmm mmmm d ddd dddd yy yyyy serial number Choosing the serial number formatting option removes all date formatting from the cell and displays the date s serial number 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 lt lt _ Using Reminder Alarm E Select Reminder Alarm from the PUP v7 Worksheets and Sheets Date and Time menu Specify the time at which you want to be reminded You can set the alarm time in either of two ways o To change the hour select Hr and then click the arrow buttons to change the minutes select Min and then Aug 02 08 Sat 8 2 Aug 2 2 Aug 2008 2 8 08 2 8 2008 02 08 08 02 08 2008 02 August 2008 Saturday 2 August 2008 Sat 2 Aug 2008 02 Aug 08 Sat 2 8 2 Aug 2008 08 02 2008 08 02 Aug 02 Aug 2008 08 2008 8 08 Aug August 2 Sat Saturday 02 2008 39486 click the arrow buttons o 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 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 Select the type of alarm from the drop down list Choose OK Page 46 Reminder Alarm Tips e If Excel is closed bef
7. 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 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 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 expressio
8. CD ROMs are not available for PUP v7 This product is distributed only via the Internet This method ensures that you get the most up to date version of PUP Online ordering Ordering online is the most efficient method and is also the least expensive because you don t pay the processing fee Your credit card is approved online and you download the files immediately For online ordering go to this URL e http j walk com ss pup pup7 About J Walk amp Associates Inc Power Utility Pak was developed by John Walkenbach principal of J Walk and Associates Inc 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 about 50 spreadsheet books most of which have been translated into several foreign languages Page 82 Contact Information e Web site http j walk com ss e Email support j walk com End User License Agreement J WALK AND ASSOCIATES SOFTWARE LICENSE AGREEMENT POWER UTILITY PAK VERSION 7 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 7 PUP exists in two versions Trial Version and Fully Licensed Version LICENSE J Walk and Associates grants
9. Export Charts go 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 v7 Charts and Graphics Charting 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 items in the list 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 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
10. Ifyou 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 1 Choose Home Styles In the Style Gallery right click the ContentsHyperlink style and choose Modify 3 Make your formatting changes in the Style dialog box and click OK Y Customize 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 Many other options are available and you can even create sheet names based on text in a range of cells Page 41 Using Customize New Workbook To create a workbook with customized sheet names PWNER Choose Customize New Workbook from the PUP v7 Workbooks and Sheets Workbook Tools menu Specify the option upon which to base the worksheet names Month names Day names etc The right panel of the Customize a New Workbook dialog box changes depending on your choice in Step 2 Click OK to create the new workbook Customize New Workbook Tips The maximum number of sheets in the new workbook is 255 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 To create sheet names that contain text and a numerical sequence use the A Serie
11. PUP v7 Auditing Reports 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 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 Your security settings may not allow the detection and removal of VBA Project components If that s the case you will see a message when you run the VBA Project Summary Report utility To change your security setting Click the Office Button and choose Excel Options In the Excel Options dialog box select the Trust Center tab Click the Trust Center Settings button In the Trust Center dialog box click the Macro Settings tab Add a checkmark next to Trust access to the VBA project object model Click OK twice to return to your workbook Oy UT Ne Page 66 Workbook Link Finder The Workbook Link Finder utility helps you identify all li
12. 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 Page 34 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 v7 Workbooks and Sheets Worksheet 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 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 In the Select worksheets box select the worksheets that you want to modify Sheet names preceded
13. 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 Page 77 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 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 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 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 v7 Power Utility Pak Amusements Bomb Hunt command Hangman To
14. and Space The Object Align Size and Space utility works with a group of objects on a worksheet and makes it easy to adjust their alignment size or spacing It works with the following object types shapes pictures clipart SmartArt WordArt and embedded charts Page 58 Using Object Align Size and Space Select the graphic objects that you want to adjust You must select at least two objects Select Object Align Size and Space from the PUP v7 Chart and Graphic Object Tools menu In the Object Align Size and Space dialog box make the appropriate changes see below Click Close to close the dialog box E e Aligning objects 1 Inthe Object Align Size and Space dialog box click the Align tab 2 Select the dominant object from the drop down list The dominant object is used as the basis for the alignment of the other objects 3 Select the desired alignment option and click Apply 4 Ifthe results aren t what you expected select Undo in the Align tab Sizing objects 1 Inthe Object Align Size and Space dialog box click the Size tab 2 Select the dominant object from the drop down list The dominant object is used as the basis for sizing the other objects 3 Select the desired option from the Size options and click Apply 4 Ifthe results aren t what you expected select Undo in the Size tab Or Adjust the size of all objects interactively by using the spinners Spacing objects 1 Inthe Object Align Size an
15. 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 Array formulas are enclosed in braces The All Precedents and Direct Precedents columns contain a count of cell precedents in the same sheet only The All Dependents and Direct Dependents columns contain a count of cell precedents in the same sheet only The Locked and Hidden attributes have no effect unless the worksheet is protected If 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 1 Select Number Format Report from the PUP v7 Auditing Reports menu 2 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 The report includes only number formats that are used 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 Using Data Validation Report 1 Select Data Validation Report from the PUP v7 Auditing Reports menu 2 Inthe Data Validation Report dialog box select the option that corresponds to the scope
16. begin playing Hangman choose Hangman from the PUP v7 Power Utility Pak Amusements submenu 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 3 500 words It s possible but unlikely that the same word may be chosen twice in one session Tips e If 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 v7 Power Utility Pak 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 count double option Each incorrectly guessed vowel that is A E O or U adds two body parts e When the Sound option is in effect end of game messages will be spoken and also displayed in the message box Don t rely on the voice for proper pronunciation Depending on how your syste
17. by an asterisk denote protected sheets You must unprotect these sheets before you can change the Locked or Hidden status of cells Specify the type of cells that you want to modify by making one or more selections in the Cell Type section Select a Locked Status option If you don t want to change the Locked status choose No change Select a Hidden Status option If you don t want to change the Hidden status choose No change If you would like to see a message that displays the number of cells affected choose Show summary message Click Apply Pa a SP Note Refer to Cell Lock Sheet Protect Tips for more information about Locked and Hidden cells Protecting worksheets 1 Inthe Cell Lock Sheet Protect Utilities dialog box click the Sheet Protect Unprotect tab 2 Inthe Select worksheets box select the worksheets that you want to protect Sheet names preceded by an asterisk denote protected sheets 3 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 4 Ifyou want to protect the selected sheet s with a password check the Use password checkbox Click Protect 6 Ifyou chose Use password you will be prompted for the password You will need to type the password two times q Unprotecting worksheets 1 Inthe Cell Lock Sheet Protect Utilities dialog box click the Sheet Protect Unprotect tab 2 Inthe Se
18. 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 Formulas Calculation Calculation Options 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 In most case the solution is to exit Excel and re start it 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 utilities you might try unloading them by using the PUP Options command on the PUP v7 Power Utility Pak PUP menu Page 80 Also it s a good idea to keep track of which add in programs are installed Installing add ins that you never use consume memory and resources Figure out whether you really need these add in to be loaded at startup or contact your System Administrator if 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 inapp
19. if you haven t yet saved your file that is it has a name like Book1 Create Workbook Contents Sheet This utility adds a new worksheet which serves 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 Style Description Navigate to chart sheets Adds macros to your workbook Hyperlinks Clickable hyperlinks No No Buttons and Macros Clickable buttons Yes Yes Using Create Workbook Contents Sheet To create a workbook contents sheet 1 Activate the workbook that will contain the table of contents sheet 2 Select Create Workbook Contents Sheet from the PUP v7 Workbooks and Sheets Workbook Tools menu 3 Choose the Style Page 40 4 Specify a Name for the contents sheet This is the name that appears in the worksheet s tab Specify the Location in the workbook for the contents sheet 6 Specify the Number of columns for the hyperlinks or buttons If your workbook has many worksheets you may prefer to list the hyperlinks or buttons in two or more columns 7 Click OK and the contents sheet will be added to your workbook q Removing a contents sheet If you decide that you no longer need 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
20. menu Choose the Remove Functions tab of the PUP Worksheet 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 5 When you re finished removing functions click Close PUN Note You can also remove the functions manually by accessing the Visual Basic Editor window 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 Select PUP Function Library from the PUP v7 Cells and Ranges Formula Tools menu Choose the Add Functions tab of the PUP Function Worksheet 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 Selected button to add the VBA macro to your workbook Click Close to close the PUP Function Library dialog box 7 You can then insert a formula such as the following PURB au PARSENAME Al 3 The PARSENAME function definition is stored in your workbook file and is saved when you save your workbook If y
21. of the report 3 Click OK The report will be generated in a new workbook Data Validation Report Tips e Inthe 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 the cells that contain Conditional Formatting Page 65 Using Conditional Formatting Report 1 Select Conditional Formatting Report from the PUP v7 Auditing Reports menu 2 Inthe Conditional Formatting 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 Conditional Formatting Report Tips e In Excel 2007 a cell can have any number of conditional formatting conditions Each format condition is listed in a separate row in the report e If your workbook contains many cells with multiple conditional formatting conditions consider using the Conditional Formatting Report as the source data for a pivot table in order to generate additional summary information 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 1 Select VBA Project Summary Report from the
22. overview of how the product works e PUP v7 consists of dozens of add in files Most of these files use a PUP7 extension rather than an XLAM extension They are however standard Excel 2007 add in files The PUP7 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 e When the PUP7 XLAM add in is opened the Ribbon is updated with a new tab named PUP v7 The PUP7 XLAM file also controls the loading and unloading of the utilities i e the PUP7 files e PUP7 XLAM loads the utilities on an as needed basis so they won t use up memory and system resources if they are not used e PUP7 XLAM 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 7 Power Utility Pak PUP PUP Options e PUP 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 Page 81 e To view the current registry settings for PUP v7 choose PUP 7 Power Utility Pak PUP About PUP v7 Then click Registry Entries and respond Yes to the message box e To delete all of PUP v7 s registry settings choose PUP 7 Power Utility Pak PUP About PUP v7 Then click Registry Entries and respond No to
23. the Worksheet Map toolbar when the map workbook is closed If your security settings do not allow inserting macros you will see a dialog box to that effect e Ifyou save a workbook that contains a map the Worksheet Map toolbar will not be available when you re open the workbook Page 71 GROUP PUP BOOKMARKS The PUP Bookmarks group contains command that allow you to quickly access particular workbooks worksheets or ranges Create a PUP Bookmark Creating a bookmark Make sure the workbook that will be bookmarked has been saved Activate the workbook worksheet and range for the bookmark To create a bookmark for a chart sheet activate the chart sheet Click Create a Bookmark in the PUP v7 PUP Bookmarks group The Create Bookmark dialog box is displayed Enter a meaningful name for the bookmark to replace the generic name To create a hot key in the bookmark name precede the character with an ampersand The following bookmark name appears with the letter A underlined When the bookmark list is displayed press this key to quickly activate the bookmark Budget amp Assumptions Select the action to take when the bookmark is selected see the list of options below Click OK and the bookmark will be added to the list Option Description Activate the workbook and go to When the bookmark is selected the workbook will be opened or the specified sheet amp range activated the sheet will be activated and the range will be
24. transfer formatting If the 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 or press Ctrl Z immediately e Ifthe 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 e No links are created so the imported data will not be updated if the source file changes E ES a Page 53 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 contained 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 As an option you can choose to display files in all subdirectories as well Using Filename Lister Select Filename Lister from the PUP v7 Workbooks and Sheets Import Exports menu In the Filename Lister dialog box click Browse to display the Browse for Folder dialog box In the Browse For Folder dialog box select the directory that con
25. 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 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 Page 30 A common use for Select Cells by Format is to identify other 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 A Before using the Select Cells 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 Choose Select Cells by Format from the PUP v7 Cells and Ranges Select menu If necessary change the Range or the Base Cell settings The checkboxes in the Select Cells by Format dialog box show formatting attributes for the base cell If you change the base cell using the Base Cell range select
26. works with the current range selection The Text Tools 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 Inthe Character position drop down select the option that corresponds to the position where you want the text to be added 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 un Removing text by position 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 wa
27. 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 included 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 g
28. 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 This name is not deleted when you delete the contents sheet If you want to delete that name use Excel s Formulas Defined Names Name Manger command Create Workbook Contents Sheet Tips e If the results of this procedure are not what you expected select Undo or press 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 Ifyour 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 W O R K B 0 0 K C O N T E N T S To quickly return to the contents sheet press F5 and double click the W O R K B 0 0 K2 0 0 No Ti Es Ni TS 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 If 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 note be able use this option or you may receive a macro warning when the workbook is opened e
29. LIKE XDATE NUMBERFORMAT SUMVISIBLE PARSENAME XDATEADD SHEETCOUNT TOPAVERAGE REMOVESPACES XDATEDAY SHEETNAME SAYIT Page 12 Date amp Time Information Lookup amp Reference Stat Math Text XDATEDIF USER SCRAMBLE XDATEDOW XDATEMONTH XDATEYEAR XDATEYEARDIF When you use Excel s Insert Function dialog box these new functions appear in the User Defined category Notes If you add one or more function to an XLSX workbook you will need to re save the file using an XLSM extension which indicates a macro enabled workbook Depending on your security setting you may receive a warning when your workbook is opened after you ve added one or more PUP worksheet functions 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 In order to use this feature you may need to change a security setting If this change is required you will see a message when you attempt to insert a PUP function To change your security setting DN P
30. LM macros Unless they are hidden these sheets are visible in the workbook Sheets window not in the VB Editor Page 42 Using Remove All Macros Activate the workbook that contains the macros you want to delete Select Remove All Macros from the PUP v7 Workbooks and Sheets Workbook Tools menu The dialog box will display a list of macro related items found in the active workbook Select the item s to remove Click OK PONE A message will appear that displays a summary of the results Remove All Macros Tips e Your security settings may not allow the detection and removal of VBA Project components If that s the case you will see a message when you run the Remove All Macros utility To change your security setting Click the Office Button and choose Excel Options In the Excel Options dialog box select the Trust Center tab Click the Trust Center Settings button In the Trust Center dialog box click the Macro Settings tab Add a checkmark next to Trust access to the VBA project object model 6 Click OK twice to return to your workbook e These operations cannot be undone If there is any possibility that you may need the macros in the future 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 wit
31. ONE Click the Office Button and choose Excel Options In the Excel Options dialog box select the Trust Center tab Click the Trust Center Settings button In the Trust Center dialog box click the Macro Settings tab Add a checkmark next to Trust access to the VBA project object model Click OK twice to return to your workbook Adding PUP functions to your workbook PAN Activate the workbook in which you want to use the functions Select PUP Function Library from the PUP v7 Cells and Ranges Formula Tools menu Choose the Add Functions tab of the PUP Worksheet Function Library dialog box Choose an item from the Function category list and the available functions in that category are displayed in the list box If you choose All from the Function category list the list box displays all of the functions Select one or more functions from the list To select multiple functions press Ctrl and click the function names To select all of the functions click the All button To clear the selection in the list box click None Click Add Selected The VBA code that defines the selected function s is added to your workbook When you re finished adding functions click Close The functions are now available for use in that workbook Page 13 Removing PUP functions from your workbook Activate the worksheet that contains the PUP functions you want to remove Select PUP Function Library from the PUP v7 Cells and Ranges Formula Tools
32. ORE inn T e a Ea E E EE EE A E a e E aniei 64 NumberFormat Reporta iii Ai aia 65 Data Validation Report 65 Conditional Formatting Report 65 VBA Project Summary Report 66 Workbook Link Finder ierra a AAA A AAA E AA A aa aae ai EA EA EA aces 67 NE RN RNA 68 Compare Ranges can A oda arepa 69 Worksheet Ma pic corrio E EE dvees sss enaceacdsaNaesbuanads EESE 70 Group PUP Bookmarks s2 5scc5c0e cesta died A A A A A AA AAA 72 Create 3 PUP BOOK Mark coi di A A das Sam aah aa doterias 72 Editing PUA BOO kM a S E dans id l N 72 Activating a PUP BookMal Krnico iii o E Eaa ER a N 73 Group Power Utility Pak e aaa e a aaa a aaa ea a aa aa aa a e a a eao a onoi aa aasa 74 Enhanced Shortcut Menus Cell Range Column or ROW cssssscccceessssssseeeecceccesseeeeeccesseeeaeeeeececeeseaseeeeceeeeeesasseeeeeseneeeeaes 74 Enhanced Shortcut Menus Sheet Tab cccccccccccccsssssseeccceeesssssseeeeeeeeeeesseeeeeeeesseeeeaeeeeeeeseeesaaeeeeeeeseeesaaseeeeceseeesaseeeeeeseseeeaaes 74 Enhanced Shortcut Menus Workbook Title Bar ccccncnoooononnncconanonannnnnncnnnnannnonnnncnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnncncnnnnnnnnns 74 Enhanced ShorteutiMenus Excel Desktopi kera lavlabsdeerevevebaxcsslgcereven evans thoacevecuses she shvgescevea gods ba E Ra 75 PUPV Amuse men ES doit 75 Additional Information iii A ii AA dd 80 Downloading Program Updates cccccccccssssscccccessesssseeeseeeeesesseeeeeeeeee
33. PUP v7 Power Utility Pak group to toggle Shortcuts on and off Remember Settings e 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 e Use 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 e Use Ctrl Shift B to display bookmarks If checked pressing Ctrl Shift B displays the list of PUP bookmarks This setting is turned on by default Progress Indicators e 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 e 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 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 Quick Access e This tab simply describes how to a
34. PUP v7 command to your Quick Access Toolbar General Usage Tips e To access the PUP commands in the Ribbon using your keyboard press Alt or slash and you ll see boxes that contain letters Press the letters or use the arrow keys to access the controls For example to display the PUP InfoBox press Alt followed by U and I e You can work with the PUP dialog boxes using a mouse or the keyboard The dialog boxes adhere to common keyboard standards Use Tab to cycle among controls and use Alt underlined keys to access a particular control e The main PUP add in file pup7 xlam uses very little memory Additional memory is not used unless you invoke one of the utilities You can control how many PUP utility files are kept in memory by using the PUP Options dialog box e Ifyou 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 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 Formulas Page 6 Calculation Calculation Options command 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 workshe
35. Power Utility Pak Version 7 Add in Tools for Microsoft Excel 2007 J Walk amp Associates Inc Web Site http j walk com ss Technical Support support j walk com ES PUP v7 Contents Double click to jump to a section A O 4 What is the Power Utility Pak ou iii di Ae ia IAE AAA AA AAA diia dieta des 4 RO O A A O cs 4 aaea E aT a EE E A A ae 4 Removing PUP VAT EE E EEE AS AEE ARE O E AE TEN E OTEO 5 PUP VZ ODON A A aT 5 General Usage E TA E E A E RE RA N E AA A A A 6 Group Cells AMO E E TaT E E E EE E EE EA EEE EEE EE 8 Exact FOrmUla o a E EEE E aid 8 Convert Relative and Absolute References Wrap ply NAMES ui AA A a A aie aaa a a AE oiai e AAA AA Error Condition Wizard Cell Comment Tools Calculator a PUP Worksheet Function Ora V cobenntens ddecdcswlandleiacedes sencbkcdedseduebaancdedesonsdya lendsacdesse DeMegednoseoebere TEXE TOONS a Change Sien Of O cdiuwapdveenahac ceanaae uns tonakacs dates teuthe 11003 34 evege thadbea ag evtiadas tants iiaanno ona evans tet adnadiees tea Math Without A A A Delete HyperlinkKs tdt to it E A A ARA Range Randomize Utilities shes cess cesewnasds nas atrio chin cano doa densaabagsalbenees las toade dae dnepediben eda ael E Soeesadad dade ia SWAP RANGES NN TESTO Vertical Range ii A r thes A a AA Combine Columns or Rows Superscript Subscript Formatting Alternate Row Shading SDiCell Shading A ds Unit CONVO LESIONA AA a ARS
36. Replace with box Click Apply Adding or removing a user name PwWNR on In the Cell Comment Tools dialog box select the User Name tab Enter or edit the name in the User name box Choose an option either Remove user name from comments or Add user name to comments 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 Click Apply Page 11 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 text within comments is not case sensitive e You can also specify how comments are viewed by using commands in the Review Comments group e If your comments contain individual character formatting this formatting may be lost when using the Format Find Replace or User Name tabs Calculator The Calculator utility displays a simple four function calculator in a stay on top dialog box 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 Using Calculator Access the Calculator by choosing Calculator from the PUP v7 Cells and Range Formula Tools menu Use the mouse or keyboard to perform your calculation To use a value stored
37. Shading utility 2 Ifthe 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 3D Cell Shading The 3D Cell Shading utility adds a three dimensional button like 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 3D 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 2 Choose 3D Cell Shading from the PUP v7 Cells and Ranges Format menu 3 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 To keep the existing background color select don t apply gray shading If this option is not selected the cells will be formatted with a gray background color 6 Choose OK The selected cells are formatted with borders of the specified type 3D Cell Shading Tips e If the results aren t what you expected choose Undo or press Ctrl Z
38. You can also load Power Utility Pak by using the Open command on the Office Button menu open the file named pup7 xlam However if you use this method there is no direct way to unload the Power Utility Pak Therefore it s best to open it using the Add In dialog box described above Removing PUP v7 To remove the entire Power Utility Pak add in from memory 1 Display the Add ins dialog box Click the Office button and choose Excel Options In the Excel Options dialog box click the Add Ins tab In the drop down labeled Manage choose Excel Add ins Click Go 2 Inthe Add Ins dialog box remove the checkmark from the Power Utility Pak v7 item 3 Click OK The preceding steps unload the pup7 xlam 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 v7 s file All of the PUP v7 files are stored in a single directory on your computer After uninstalling the pup7 xlam add in as described above you can delete the entire PUP v7 directory By default this directory is c program files pup7 Removing PUP v7 s Registry entries PUP v7 stores its settings in the Windows Registry You can remove these setting at any time 1 Choose About PUP v7 from the PUP v7 Power Utility Pak PUP menu 2 Click the Registry Entries button 3 Click the No button in response to the message box A
39. a specified pattern designated using wildcard characters Cells that do not match a specified pattern designated using wildcard characters Page 29 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 used area of the 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 v7 Cells and Ranges Select menu Make your choice from the Selection type options 3 Ensure that the correct range is specified You can change the range while the Select by Value dialog box is displayed 4 Specify your criteria type in the Comparison dropdown Enter the value or values that describe your criteria 6 Click OK to select the matching cells or rows m an Pattern 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 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 e To select all cells or rows in
40. a workbook that contains one or more names Select Name Lister from the PUP v7 Auditing Utilities 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 Page 68 Name Lister Tips e The Name Manger in Excel 2007 has been greatly improved You may prefer to use that tool rather than the PUP Name Manager The Name Manger however does not display hidden names e 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 e When you choose the By sheet option the last option in the drop down list is no sheet When you select this option the list bo
41. activate the sheet Sorting sheets cannot be undone Save With Backup The Save With Backup utility performs two operations with a single command e It saves a backup copy of your workbook to a specified directory e It saves your workbook to its original location Normally saving a workbook to a backup location is a cumbersome procedure since Excel remembers the backup location as the workbook s new 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 v7 Workbooks and Sheets Workbook Tools menu 3 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 lf 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 N Save With Backup Tips e If 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 directly to a floppy disk e You won t be able to use this utility
42. 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 e The data used by this utility is stored in the Windows Registry When creating a sequence specify O 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 e 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 2008 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 2008 The next number in this sequence would appear as INVOICE 0099 2008 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 Full Screen mode Page Breaks Windows in taskbar Formula View Selection Pane Display Zeros Page 37 Using Toggle Settings 1 Choose Toggle Settings from the PUP v7 Workbooks and Sheets Worksheet Tools menu 2 Inthe Toggle Settings dialog box adjust th
43. atch Printing Wizard Print Multiple Selection Wizard a soe densa di occas sedeueeca dh cdeese ee aae d o DETA No dar AEE ENEE Aae E Oaa SARA AStA RA ENEE ETOL ES Ea aa Eaa 48 Copy Page Setup onai tai sii a a a aa e i aa aaia iaa aeii aa asia aeia aai a iia 49 Font Rere Ne EET a o AA aan e a o Bas aA 50 EXPOrt Range to i I E A AREE E AA EEE E A A R 50 EXPort RaNgeras Graphic EA E E E E EEEE 51 Save Sheet as HTML 52 Insert File at Cursor 53 Filename LISESL acoso dais a a iia dano adrian incre edilicia dia Lerida iS 54 Group Charts and Graphics wi ccccccssiececccccccccctelececesecctecetelececedeccdecesesdcecockescesseesececeshcescsusesavecetecsvesscessdecetuesdestosrsdecstassdeessensdes 55 EXD Ort CAIUS A EA TEE EE ETT TE TTA 55 Chart Data Labeler 56 Resize Lael Sra dl cob a 57 Convert Chart TOP iGture es Ei a 57 CRArt REDO eee ce ces EA RA A AA Tuo daa A Faded Fea ld EA AAA i a a 58 Object AligniSize and Spaces A aa o 58 OBJECT REPO EA EA sav cvansas dedes ATE sues toanedeses tocbescesaededsvadeadatavsoebbuvsieedes S tuankdbvss ves iets a hischsatewiantaetesiaves 60 GrOUP AUGITING EAEE cocdicvesccctecececescucdcccscceccseaecedscesscecdcesscecdcensceddceuscecdcesscecdecedcedecesdcnddcenscecdceudcncdcesdceddcesdcecseesdsess 61 PUPA ATOBOX il A A A AA iaa 61 Workbook SUMMALY Repot snn miaren a A aia aena a e ia a ta ai 62 D te Repo a ere ce T ENE A OEE REENE A EEE E OE R EET EEEE satay EET E EEEE E 63 FOFMULAIRE D
44. ccurate Bug fixes and updated files are available free of charge from the following web site e http www j walk com ss pup pup7 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 e support j walk com 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 Questions asked via e mail are typically answered within 24 hours often within the same day e Ifyou 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 Solutions to Common Problems If you re having a problem with PUP v7 check the following information The PUP v7 ribbon tab is not displayed Probable causes e Power Utility Pak is not installed Refer to Installing PUP v7 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 example when selecting a PUP utility you may experience a delay before the
45. ce that show 2 Threes At least one die that shows 3 The sum of the number of dice that show 3 Fours At least one die that shows 4 The sum of the number of dice that show 4 Fives At least one die that shows 5 The sum of the number of dice that show 5 Sixes At least one die that shows 6 The sum of the number of dice that show 6 Three of a Kind At least three of the dice match The sum of all the dice Four of a Kind At least four of the dice match The sum of all the dice Page 76 Scoring Category Condition Points Full House Two of one kind and three of another kind 25 points Small Straight At least four consecutive numbers 30 points Large Straight Five consecutive numbers 40 points Five of a Kind All of the dice match 50 points Chance Any combination of dice The sum of all the dice Bonus Points 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 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 scorin
46. ciates Inc All rights reserved Page 84
47. ck OK Convert Relative and Absolute References Tips e Ifthe results of this utility are not what you expected choose Undo or press Ctrl Z PONE Unapply Names The Unapply Name utility works with formula cells and converts named references to their actual cell addresses something that surprisingly Excel is not able to do This utility has a limitation however It will not work if the workbook has any duplicated names For example if the workbook contains two sheet level names such as Sheet1 Total and Sheet2 Total you cannot use the Unapply Names utility In addition it will not work if the workbook has a sheet level name that s the same as a workbook level name In either case you will see a message when you attempt to use this utility 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 1 Choose Unapply Names from the PUP v7 Cells and Ranges Formula Tools menu Verify that the Range box contains the desired range Or change the range if desired 3 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 4 Specify the type of reference Absolute references or Relative references to be
48. cker disappears unexpectedly choose PUP v7 Workbooks and Sheets Date and Time Date Picker again PUP Date Picker Tips To reverse the effect of the most recent action choose Undo or press Ctrl z Click the Today button to select the current date If you re using the PUP Enhanced Shortcut Menus option right clicking a cell will display a PUP Date Picker menu item which displays the PUP Date Picker Excel does not support dates prior to January 1 1900 In addition days of the week prior to March 1 1900 are incorrect because of a bug in Excel If the column is not wide enough to display the formatted date the column is widened automatically The PUP Date Picker remembers the screen location that it was in the last time you closed it It also remembers the most recently used date Following is a list of the available date formats and an example of each This table uses slash as the default date separator Note The slashes will be replaced by your local date separator character Date Format Example mmm d yyyy Aug 2 2008 m d yy 8 2 08 m d yyyy 8 2 2008 mm dd yy 08 02 08 mm dd yyyy 08 02 2008 mmmm dd yyyy August 02 2008 dddd mmmm d yyyy Saturday August 2 2008 ddd mmm d yyyy Sat Aug 2 2008 Page 45 mmm dd yy ddd m d mmm d d mmm yyyy d m yy d m yyyy dd mm yy dd mm yyyy dd mmmm yyyy dddd d mmmm yyyy ddd d mmm yyyy dd mmm yy ddd d m d mmm yyyy mm dd yyyy mm dd mmm yy mmm yyyy
49. colors used In some cases changing the background color may improve the quality of the image 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 Save Sheet as HTML The Save Sheet as HTML utility exports the data on the active worksheet to an HTML file with a single table An HTML file is readable in a Web browser Options allow most but not all of the cell formatting to be preserved Using Save Sheet as HTML Before using this utility activate the worksheet you want to export PA er Choose Save Sheet as HTML from the PUP v7 Workbooks and Sheets Import Export menu Specify the formatting options Choose OK A file selection dialog box appears Specify a filename and location and click Save ar O K 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 is not translated To export a specific range of cells not the entire sheet use PUP s Export Range to File command If you select the Open the file after exporting the data option the exported file will be opened in your default Web brow
50. ct Show summary Make the desired changes see descriptions below Click Close to close the Cell Comment Tools dialog box Note Changes made by this utility cannot be undone View or listing comments In the Cell Comment Tools dialog box select the View List tab Select the appropriate viewing option in the View comments frame This affects all comments regardless of the Scope setting 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 Formatting comments 9 In the Cell Comment Tools dialog box select the Format tab Click Specify the comment cell to select a cell that contains a comment A range selection dialog box will appear 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 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 NN In the Cell Comment Tools dialog box select the Find Replace tab Enter the text to be replaced in the Find text in comments box Enter the replacement text in the
51. d Space dialog box click the Space tab 2 Select the desired option from the Spacing options and click Apply 3 Ifthe results aren t what you expected select Undo in the Space tab e Adjust the spacing of all objects interactively by using the spinners Object Align Size and Space Tips e After you close the Object Align Size and Space dialog box you can reverse the changes you made by choosing Undo or by pressing Ctrl Z 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 and Space dialog box e Select multiple objects by pressing the Ctrl or Shift key as you click each object Or use the Selection pane available in the Format Arrange group when a graphic object is selected e 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 Page 59 Object Report The Object Report utility creates a report that describes all graphic objects on on a worksheet 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 object report consists of 10 columns and displays the following information for each object Information Description Name The name of the object Z Order The index of the object Left The left position of t
52. d protection is not a security feature Worksheet password are easily cracked 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 IN Select the cell that will contain the sequence number Choose Insert Sequence Number from the PUP v7 Workbooks and Sheets Workbook Tools menu The dialog box displays a list of all sequences that you have added Select the desired sequence from the list 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 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 Click Close to close the dialog box Sequence Data The Insert Sequence Number dialog box displays a list of all defined sequences For each sequence the following information is listed Sequence The name of the sequence Inc The value by which each sequence number will be incremented Previous The sequence number that was last inserted Date Used The date on which the last sequence number was inserted Creating a New Sequence When the Insert Sequence Number dialog box fi
53. data labels in the chart Copy label If selected the data labels will use the following formatting attributes from the data label formatting 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 Warn before If selected you will get a warning message if the selected data series already contains overwriting labels data labels Warn if range size If selected you will get a warning message if the number of cells in the Label range does does not match not match the number of data points in the selected series Position 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 Page 56 Chart Data Labeler Tips e Ifthe results aren t what you expected choose Undo or press 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 in a different workbook 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 labe
54. deo poker choose Video Poker from the PUP v7 Power Utility Pak Amusements menu 1 Click Deal to receive five cards 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 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 Page 75 e Click New Game to reset the score to 0 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 v7 Power Utility Pak 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 dice game choose Dice Game from the PUP v7 Power Utility Pak Amusements menu How to Play The rules for the dice game are similar to those for Triple Yahtzee Following is a brief sum
55. 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 combining 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 v7 Cells and Ranges Modify Cells and Ranges 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 3 Specify an option Combine columns or Combine rows 4 Specify an option for the With the data in section 5 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 Page 22 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
56. e Bookmark in the PUP v7 PUP Bookmarks group or press Ctrl Shift B The bookmarks are displayed in a menu Click a bookmark name to activate the bookmark Or use the arrow keys to select the bookmark and press Enter to activate it e ifthe bookmark name displays an underlined hot key press the key to quickly activate the bookmark When you create a bookmark precede the letter that will be designated the hot key with an ampersand amp e Ifthe workbook is already open it will be activated and the bookmarked sheet and range if any will be selected e Ifthe workbook is not open it will be opened and the bookmarked sheet and range if any will be selected PUP Bookmarks Tips e PUP Bookmarks are more versatile that Excel s built in recently used file list You can create a bookmark that points to a particular sheet or a specific range of cells e The shortcut key combination to display the PUP Bookmark list is Ctrl Shift B The list appears next to the active cell Use the arrow keys to select the bookmark then press Enter If the bookmark name has a hot key an underlined letter you can just press the hot key to activate the bookmark The Ctrl Shift B key combination work even when the PUP v7 ribbon tab is not displayed e Remember that you can create multiple bookmarks for a single workbook For example you might have a large workbook that contains many sheets and ranges You can create bookmarks so you can quickly activate a particu
57. e dialog box Page 33 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 Closing workbooks e Use Close File to close 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 Click Sheets gt gt to expand the dialog box and display a list of sheets for the selected workbook Use Hide or Unhide to change the hidden status of the selected sheet Use Unhide all to unhide all sheets in the selected workbook 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 Activating a workbook or sheet e Use Activate to activate the selected workbook e Ifthe 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 Double 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 Cell Lock
58. e 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 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 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
59. e settings as desired You will see the result immediately 3 Choose Close to close the dialog box Toggle Settings Tips e The Selection Pane toggles the display of a task pane that lets you select and hide objects on a worksheet e Click Apply to all sheets to apply the selected worksheet settings to all worksheets in the active workbook These settings are o Grid Lines Vertical Scrollbar Horizontal Scrollbar Row Column Headers Page Breaks Formula View Display Zeros e Click Defaults to change the settings to Excel s default settings 000000 Note These settings can also be changed in the Excel Options dialog box However 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 Setting a scroll area To specify a scroll area for a a worksheet Select the range of cells for the scroll area Choose Set Scroll Area from the PUP v7 Workbooks and Sheets Workbook Tools menu Verify that the Scroll area range box displays the desired scroll area range Or change it if desired Choose OK PUNE Removing a scroll area To remove a scroll area that was applied with this utility 1 Choose Set Scroll Area from the PUP v7 Workbooks and Sheets
60. ect all cells that match the base cell s number formatting attribute Click OK If Display Summary is selected a message box will tell you how many cells qualify Click OK to select the cells 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 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 PonP Select any cell that has the formatting described above Choose Select Cells by Format from the PUP v7 Cells and Ranges Select menu Because only a single cell was selected the Range box shows the address of the entire used area of the worksheet The Base Cell box shows the address of the selected cell Page 31 5 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 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 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 Yo
61. eeeseeeeeeeeeseeeeeeeeeeeseeessaeeeeeeeseeeeaaseeeeceseseseaaeeeeeeseeeeeaaes 80 Obtaining Technical UPPER 80 Solutions to Common Problems 80 How PUP V7 Works ccssscccccesessssseeeeeeeeeeees 81 The PUP v7 Source Code 82 About the Tall Mer O eR E lL Real ee Satis ve Sods Tan E ane teodade es 82 HOW tosOnrder PUBIS A ata Aa 82 About JiWalk 8 Associates ING csccic a aa sdeavanecebeaass conteapcceonsadecdeaseeSo webs caeds dds aaa a aaa deve raa EEAS EEA Erea 82 End User License Agreement mension aeien iri aea saeibic AAA LORA ea a AAA dada tetris 83 OVERVIEW What is the Power Utility Pak Power Utility Pak Version 7 or PUP v7 is a collection of add ins for Microsoft Excel 2007 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 v7 consists of several components General Purpose Excel Utilities A set of 60 general purpose utilities that add new features to Excel Access these utilities from the ribbon when you click the PUP v7 tab Note that the PUP v7 tab is present only when PUP v7 is installed New worksheet functions A set of 54 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 Enhanced shortcut menus optional When this option is in e
62. ells Direct Dependents Precedent Cells Direct Precedents 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 The number of conditional formatting conditions if any 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 This number includes only the formulas on the same sheet 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 Item
63. es 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 This utility does not attempt to determine if your formulas already have an error condition If you choose the Use the Excel 2007 IFERROR function option in step 1 you cannot specify the type of error in Step 2 If you choose the Use the Excel 2007 IFERROR function option in Step 1 your workbook will not be compatible with versions prior to Excel 2007 Cell Comment Tools The Cell Comment Tools utility provides several ways to work with comments attached to cells You can Change the way comments are displayed Generate a listing of all comments Change the formatting of all comments so the formatting matches a specified comment Find and replace text contained in comments Add or remove the user name from all comments Page 10 Using Cell Comment Tools Before using the Cell Comment Tools utility activate a worksheet that contains at least one cell comment 3 Y Choose Cell Comment Tools from the PUP v7 Cells and Ranges Formula Tools 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 If you would like to see a summary of the comments that were changed for each operation sele
64. et 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 PUP utility are reversible If the results of an operation are not what you expected choose Undo or press Ctrl Z In some cases you ll want to use a utility repeatedly For a few of the utilities Text Tools Calculator and Insert Sequence Number the dialog box remains onscreen after you perform the operation Identify the stay on top dialog boxes by their smaller title bars 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 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 v7 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 t
65. exchange rates and create this file e The exchange rate data is obtained from the Currency Source web site Their disclaimer The values on this site are gathered from the International Monetary Fund and or the Federal Reserve Bank of New York according to their availability Values and dates are believed to be reliable but this site makes no warranties regarding these values fitness for a particular purpose accuracy or availability Page 27 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 Choose Currency Conversion from the PUP v7 Cells and Ranges Convert menu In the Currency Conversion dialog box Select the Convert a Single Value tab 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 Enter 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 NP UNE 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 Conver
66. f 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 v7 Workbooks and Sheets Printing 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 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 files 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 Page 47 Step 4 This step has some additional options described below Option Description Quiet Mode If selected screen u
67. ffect many of Excel s shortcut menus display additional commands PUP v7 Bookmarks optional This feature gives you quick access to frequently used workbooks sheets or ranges PUP v7 Amusements Several games and amusements that you can check out when you need a break from number crunching Guaranteed to reduce your productivity Requirements PUP v7 works with Microsoft Excel 2007 for Windows PUP v7 does not work with earlier versions and it does not work with any version of Excel for Macintosh Installing PUP v7 You can choose to have the PUP v7 add in loaded automatically whenever you start Excel Or you can simply load it when you need it To install the Power Utility Pak 1 2 3 Display the Add ins dialog box e Click the Office button and choose Excel Options e Inthe Excel Options dialog box click the Add Ins tab e In the drop down labeled Manage choose Excel Add ins e Click Go In the Add Ins dialog box place a checkmark next to Power Utility Pak v7 Click OK The Power Utility Pak will be installed and Excel will display a new menu PUP v7 Tips If Power Utility Pak v7 does not appear as one of your choices in the Add Ins dialog box use the Browse button to locate pup7 xlam By default the pup7 xlam add in file is in a directory named C Program Files pup7 However the product can be installed in any directory A quick way to display Excel s add ins dialog box is to press Alt TI Page 4 e
68. fter performing these steps PUP v7 will be returned to its default settings 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 v7 Options To display the Options dialog box for Power Utility choose PUP Options from the PUP v7 Power Utility Pak PUP menu The PUP v7 Options dialog box contains four tabs described below Utility Management e 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 e List Click this button to display a list of PUP utilities that are currently open Page 5 e Unload All Click this button to unload all of the PUP utility files This will unload the utility files but the PUP v7 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 The PUP Shortcut Menus file not included in the count of utility files This file is not unloaded when you click Unload All Use the PUP Shortcuts checkbox in the
69. g button Options e Click the Options button to expand or contract the dialog box e Ifyou 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 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 Dice Game from the PUP v7 Power Utility Pak Amusements menu e Ifyou 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 v7 Power Utility Pak 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 The 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
70. ge the current print area if any 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 the PUP Date Picker which is used for entering or formatting dates Displays the PUP InfoBox which displays information about the active cell e Press Shift F10 to display the shortcut menu without using a mouse Enhanced Shortcut Menus Sheet Tab The new shortcut commands listed below appear when you right click a sheet tab Menu item Sort Sheet Tabs Delete Empty Sheets Synchronize Sheets Activate a Sheet Interactive Hide Unhide Description Displays the PUP v7 Sort Sheets dialog box See Sort Sheets Deletes all empty worksheets in the active workbook Synchronizes all worksheets so they have the same upper left cell and the same range selection See Synchronize Sheets Displays a list of all sheets in the active workbook Activate a sheet by selecting it from the list Hide or unhide workbooks or sheets interactively See Interactive Hide Unhide Enhanced Shortcut Menus Workbook Title Bar The new shortcut menu commands listed below appear when you right click a workbook s title bar Page 74 Note If the workbook window is maximized the title bar will not be visible You can acces
71. gns value e g 143 44 This option converts those cells to normal negative values e g Page 16 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 all This option makes all negative values positive and makes all positive values values negative Using Force Values In Cells To force Excel to recognize number values as numbers Start by selecting the range Choose Force Values In Cells from the PUP v7 Cells and Range Modify Cells and Range menu Verify that the Range box displays the correct range or change it if necessary Click OK POM Force Values In Cells Tips e Ifthe results aren t what you expected choose Undo or press 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 Math Without Formulas 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 Addition Subtraction Multiplication Division Exponentiation Rounding Apply an Excel function 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 eithe
72. h only blank lines The Remove All Macros utility will remove all empty VBA modules and delete all blank lines from code modules e If your workbook is in a macro enabled format for example XLSM you can re save it as an XLSX file after you remove the macro components TP ON Perpetual Calendar The Perpetual Calendar utility has the following features e Displays a handy pop up calendar for any month from January 1900 through December 2200 e Inserts a picture of any calendar month into your worksheet e Creates a calendar workbook consisting of up to 12 consecutive months each on a separate worksheet Using Perpetual Calendar 1 Select Perpetual Calendar from the PUP v7 Workbooks and Sheets Date and Time menu 2 Inthe Perpetual Calendar dialog box use the left spinner to select the month and the right spinner to select the year The calendar is displayed in the Calendar tab Click This month to display the current month 3 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 4 To insert a picture of the selected month click the Picture tab choose a style and then click Paste The picture will be pasted and the dialog box will be closed 5 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 Page 43 6
73. hat 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 The picture can be manipulated with the tools in the Picture Tools Format tab Chart Report The Chart Report utility 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 A simple 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 Choose Chart Report from the PUP v7 Chart and Graphics Chart Tools menu Specify the report type simple or detailed Specify the location for the report in a new worksheet or in a new workbook If you would like the report to include a small image of the chart select Include chart thumbnail Click OK to generate the report Chart Report Tips e 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 e For details on the specific information included in the report consult the Help system for Excel WV PwWwnr Object Align Size
74. he object Top The top position of the object Width The width of the object Height The height of the object Type The type of object AutoShape Type The AutoShape type if the object s type is AutoShape Top Left Cell The address of cell that corresponds to the objects upper left corner Hidden TRUE if the object is hidden FALSE if the object is visible Using Object Report To create an object report start by selecting the workbook that contains the objects for the report 1 Choose Object Report from the PUP v7 Charts and Graphic Object Tools menu 2 Inthe Object Report dialog box select the option that corresponds to the scope of the report 3 Click OK to generate the report Object Report Tips e The report can be sorted by any of its columns e Keep in mind that Excel allows two or more objects to have the same name Page 60 GROUP AUDITING The Auditing group contains command that can help document your work or identify potential errors PUP InfoBox The PUP InfoBox provides a convenient way to get quick information about the active cell The InfoBox updates as you move around the worksheet and always displays information about the active cell The PUP InfoBox displays the following information about the active cell Notes Cell Information Value Displayed As Cell Type Number Format Formula Name Style Horiz Alignment Protection Format Conditions Data Validation Cell Comment Dependent C
75. heet that you want to operate on 1 Choose Delete Hyperlinks from the PUP v7 Cells and Ranges Modify Cells and Ranges menu 2 Specify the scope Entire workbook Active worksheet or the specified Range 3 Click OK to delete the specified hyperlinks Delete Hyperlinks Tips e Ifthe results aren t what you expected choose Undo or press Ctrl Z immediately This will restore the deleted hyperlinks e To prevent Excel from creating automatic hyperlinks display the Excel Options dialog box Click the Proofing tab and then click the AutoCorrect Options button Then select the AutoFormat As You Type tab and clear the checkmark from Internet and network paths with hyperlinks You can also prevent Excel from creating a hyperlink by preceding your cell entry with an apostrophe If Excel creates an unwanted hyperlink press Ctrl Z and the hyperlink will be removed Range Randomize Utilities The Range Randomize Utilities has 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 of cells 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 Cho
76. her 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 or press Ctrl Z immediately This will restore the previous formatting of the selected cells Using Excel 2007 s table feature is an alternate way to apply alternate row shading This alternate row shading style in a table is completely independent of the method used by PUP Generally the Conditional formatting option is a better choice because the alternate row shading will be 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 Page 24 be deleted You can however add additional conditional formatting criteria after you ve applied alternate row shading e The Conditional formatting option cannot be used if the workbook is shared 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
77. hoose 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 Page 28 Currency Conversion Tips e If you re converting a range of data and the results of this operation are not what you expected choose Undo or press Ctrl Z e The Results range can be on the same worksheet a different worksheet or in a different workbook 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 xlsx workbook The date and time of this data is displayed in the Currency Conversion dialog box e Ifyou retrieve new currency exchange rates be aware that these rates may be delayed In general consider these rates to be approximate rather than exact e For other types of conversions see Unit Conversion How it works The Currency Conversion utility makes use of a workbook named currency rates xlsx which is located in your pup7 directory This workbook contains a Web Query that retrieves the exchange rate information from the Currency Source web site The Currency Conversion utility always uses the data stored in the currency rates xlsx file When you update the exchange
78. id producing a poor quality graphic unshaded cells should be colored with a white background 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 v7 Workbooks and Sheets Import Export menu Select a graphics file format GIF JPG TIF or PNG Click Specify Filename In the Save As dialog box specify a filename and location and click Save Export Range as Graphic Tips 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 The Export Range as Graphic utility makes it easy to create attractive textual graphic files for Web pages You can apply any type of formatting to the range before exporting it 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 If your worksheet uses a background picture added with the Page Layout Page Setup Background command the image may not be rendered correctly in the exported graphic file The quality of the image varies depending on the file format and the
79. immediately This will restore the previous formatting of the selected cells e 3D 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 Usually applying the same color shading to all of the surrounding cells will maximize the 3D effect e You can change the interior color of the cells after the special formatting has been applied The 3D effect will remain but the background will be a different color 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 millimeters You can use the Unit Conversion utility to convert these values to inches When working with a range of data the converted values can e Replace the existing values Page 25 e Be inserted into a new range as values e Beinserted into a new range as formulas which refer to cells in the original range The Unit Conversion utility supports the following measurement units Angle Bits amp Distance Energy Force Liquid Mass Surface Temperature Time Volume Bytes Cubic Degree Bit Foot BTU Dyne Cup Caret Acre Celsius Day Foot RE Calorie Cubic Grad Kilobit Inch IT Newton Gallon Grain Hectare Fahrenhei
80. in a cell activate the cell and click Get Click Paste to paste the result to the selected cell PON The Calculator remains onscreen after pasting and you can continue to work in Excel while it is displayed Calculator Tips The C key clears the calculator display The CE key clears the current entry If you use the calculator from the keyboard use the asterisk key for multiplication If a range of cells is selected clicking Paste inserts the result into the active cell in the range If the active worksheet is protected you will not be able to paste the results If you accidentally paste a value choose Undo or press Ctrl Z to restore the previous value The colors used in the calculator are from the active workbook s document theme 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 53 PUP worksheet functions are arranged into five categories Date amp Time Information Lookup amp Reference Stat amp Math Text DAYSINMONTH CELLCOLOR CREDITCARD COUNTAVISIBLE CHAR2 MONTHWEEK CELLHASFORMULA HINTERPOLATE COUNTBETWEEN CODE2 TIMEXX CELLTYPE LASTINCOLUMN MAXALLSHEETS CONTAINS TIMEXX_ADD EXCELDIR LASTINROW MINALLSHEETS DOLLARTEXT TIMEXX_SUBTRACT FILEEXISTS SHEETOFFSET SELECTONE EXTRACTELEMENT TIMEXX_SUM FILENAME UNIQUEITEMS STATICRAND INSERTSTRING WHICHDAY FONTCOLOR VINTERPOLATE SUMALLSHEETS IS
81. ingle 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 v7 Cells and Ranges Modify Cells and Ranges menu 2 Verify that the Data to be transformed range box contains the address of the input data or change 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 Ifthe records in the input column are delimited by a blank cell choose Blank cell delimits records If the records are not delimited by a blank cell use the spinner to specify the number of rows per record as displayed in the rows per record option 5 Click OK Transform Vertical Range Tips If the results of this utility are not what you expected choose Undo 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
82. insert a formatted date into a cell by picking the date from a calendar display and choosing from a list of common date formats Alternatively you can insert a date without the formatting or apply the formatting without changing the date Using PUP Date Picker Da a Notes Select Date Picker from the PUP v7 Workbooks and Sheets Date and Time menu Select the cell that you want to modify Use the arrow controls at the top of the calendar display to select the desired month and year Change the month by clicking the left arrows and change the year by clicking the right arrows Click on the date in the calendar The selected date appears in a different color If desired select a Date format from the drop down list on the right Click one of the three colored buttons to insert the date and or format into the active cell o D Inserts the date only If the cell is already formatted as a date the formatting will not be changed If the cell is not formatted as a date it will use the default date format o F Applies the selected format to the active cell but does not change the date o D amp F Inserts the date and the selected format The PUP Date Picker remains visible until you close it by clicking Close 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 v7 Options dialog box If the PUP Date Pi
83. ition 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 0 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 As an option you can choose to convert the formula so it uses the Excel 2007 IFERROR function IFERROR 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 Only the formula cells will be affected wn NOW SP Choose Error Condition Wizard from the PUP v7 Cells and Ranges Formula Tools menu In Step 1 verify that the range is correct If you would like the formula to use the Excel 2007 IFERROR function choose the Use the Excel 2007 IFERROR function option Click Next In Step 2 specify the type of error that you want to trap Click Next In Step 3 specify what you would like to display instead of the error Click Finish Error Condition Wizard Tips If the results of this utility are not what you expected choose Undo or press Ctrl Z In Step 3 the information at the bottom of the dialog box chang
84. iven 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 Page 83 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 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 7 2001 2007 by J Walk and Asso
85. l and then drag it to its desired position e Ifyou select a single cell for the Label range the Chart Data Labeler utility will use additional cells below the specified cell e If 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 Charts 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 v7 Charts and Graphics menu 2 The Resize Charts dialog box displays the dimensions of the selected chart You can change either or both of these dimensions 3 To make all charts on a sheet the same size select the Make all charts on the active sheet this size checkbox 4 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
86. lar section of the workbook when needed e Ifyou activate a bookmark in which the workbook s window is hidden the workbook s window will be unhidden e ifthe 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 Activate Bookmark command This is normal behavior when a workbook is opened via a VBA macro Page 73 GROUP POWER UTILITY PAK The Power Utility Pak group contains commands that control how PUP v7 works plus a selection of non serious diversions 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 Toggle Page Break Display Toggle Word Wrap Change Case Description Toggles the display of page breaks that normally appear after printing or previewing a worksheet 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 The shortcut menu for a cell or range also includes the items listed below Menu item Print Selection Center Across Selection Synchronize Sheets PUP Date Picker PUP InfoBox Tip Description Prints the selected range This command does not chan
87. lect worksheets box select the worksheets that you want to unprotect Sheet names preceded by an asterisk denote protected sheets Click Unprotect 4 Ifa password is required to unprotect the sheet s you will be prompted to enter the password w Page 35 Cell Lock Sheet Protect Utilities Tips This utility works only with worksheets To protect or unprotect Chart sheets activate the chart sheet and choose Review Protect Sheet 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 right click the range selection and choose Format Cells from the shortcut 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 ofa 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 of the used area are never changed If 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 Passwor
88. ly Select the chart and use the Chart Name box in the Chart Tools Layout Properties group Chart Data Labeler The Chart Data Labeler utility makes it easy to add labels to chart data points Importantly the labels can be stored in any arbitrary range of cells 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 Activate the chart to which you want to add data labels The chart can be an embedded chart or a chart sheet Select Chart Data Labeler from the PUP v7 Charts and Graphic Tools Chart Tools menu 3 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 6 Click OK to add the labels to your chart as Option Description Create links to the If selected the labels will be linked to the range If you modify the text in the labels in the labels 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
89. m 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 Page 78 Sudoku Puzzle Generator Choose PUP v7 Power Utility Pak Amusements Sudoku Puzzle Generator to create a Sudoku puzzle in a new workbook The dialog box lets you specify the number of clues placed on the 81 cell grid a value from 12 to 60 Generally puzzles with fewer clues are more difficult to solve How to Play Sudoku involves placing numbers in squares using basic rules of logic and deduction The objective of the game is to fill all the blank squares in a puzzle with the correct numbers subject to the following constraints e Every row of 9 numbers must include all digits 1 through 9 in any order e Every column of 9 numbers must include all digits 1 through 9 in any order e Every 3 X 3 subsection of the 9 X 9 square must include all digits 1 through 9 To solve the puzzle just type the numbers into the cells If a row or column contains a duplicate number you ll see a red arrow to remind you If one of the 3 X 3 blocks contains a duplicate entry the numbers in that block are displayed in red When you solve the puzzle the word S O L V E D appears at the top The worksheet is protected to prevent you from accidentally typing over the pr
90. mary 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 1x column of scoring buttons Scoring Category Condition Points Ones At least one die that shows 1 The sum of the number of dice that show 1 Twos At least one die that shows 2 The sum of the number of di
91. n a range of cells The calendar dates are calculated using an array formula that references the date at the top of the calendar If you change that date the formula recalculates and displays the calendar for the new month Using Insert a Live Calendar To insert a calendar into a range start by selecting a cell in an empty area of your worksheet The calendar uses 8 rows and 7 columns and these cells must be empty SETE Choose Insert a Live Calendar from the PUP v7 Workbooks and Sheets Date and Time menu If you would like the column widths to adjust automatically select the Adjust column widths options To apply background colors to the calendar select the Apply colors option Click OK and the calendar is inserted Insert a Live Calendar Tips If you choose the Apply colors option the colors are from the active workbook s document theme If you apply a new theme the colors in the calendar will change The dates are calculated with a single multicell array formula You can copy the entire calendar range and paste it to another location To change the month displayed change the date in the top row of the calendar a 7 column merged cell The formulas use only the month and the year from that date so you can enter any date for the month into this cell To force the calendar to always display the current month enter this formula into the top cell TODAY Page 44 PUP Date Picker The PUP Date Picker makes it easy to
92. n below uses Excel s ROUNDUP function to round each cell up to two decimal places ROUNDUP 2 Math Without Formulas Tips e If the results of the operation are not what you expected choose Undo 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 might consider the imported values to be text not numbers To convert these values 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 e Insome case performing a mathematical operation may result in an error An error may occur when working with very large numbers or using a function with an inappropriate 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 dialog box with the Add Subtract Multiply or Divide option but Math Without Formulas is much more versatile and easier to use Page 19 Delete Hyperlinks The Delete Hyperlinks utility converts hyperlinks in cells to plain text With this utility you can delete the hyperlinks in e An entire workbook e The active worksheet e A specified range of cells Using Delete Hyperlinks Before using Delete Hyperlinks select the range or works
93. n t generate a link report click Close to close the Link Finder dialog box 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 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 It is possible for a workbook to contain links to files that are not listed when you view Excel s Edit Links dialog box 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 Page 67 Workbook Link Finder Tips 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 If you can t locate a link identified by Workbook Link Finder keep in mind that the object that contains the link may be hidden If some of the information is too wide to be displayed completely increase the
94. ne 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 v7 Auditing Utilities 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 4 Click Next 5 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 Page 69 Compare Ranges Tips e If you re comparing two ranges the ranges can be in different worksheets or in different workbooks e Ifyou re comparing two worksheets the sheets can be in different workbooks 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 A worksheet map uses the following codes Map Code Description G
95. 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 protected worksheets To include the missing information in the report unprotect the sheets and generate the report again e Your security settings may not allow the VBA module report to be generated If that s the case you will see a message when you run the Workbook Summary Report utility To change your security setting Click the Office Button and choose Excel Options In the Excel Options dialog box select the Trust Center tab Click the Trust Center Settings button In the Trust Center dialog box click the Macro Settings tab Add a checkmark next to T
96. ngle sheet workbook that contains a drop down list of all of the fonts 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 v7 Workbooks and Sheets Printing menu A new 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 file is based on an Excel template font reference xltm which is stored in your pup7 directory To change the color and default font apply a different document them to the workbook use Page Layout Themes Themes e To view the VBA macros in this template press Alt F11 and select the VBA project for font reference N 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 A standard Excel workbook file with one worksheet workbook Text file A text file with values separated by a comma This is a common file format used by many applications CSV Simple An HTML file readable in a web browser Options allow most but not all of the formatting to be preserved HTML The file is significantly smaller than that produced by the Complex HTML
97. nks 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 in Excel s Edit Links dialog box To display this dialog box click the Office button and choose Prepare Edit Links to Files This command is not available if the active workbook contains no links 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 in the controls contained on Excel 5 95 dialog sheets links 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 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 Y Notes Activate a workbook that contains links to one or more other files Select Workbook Link Finder from the PUP v7 Auditing Utilities 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 Select a link in the Links list box and more information about the link will display below the list box To create a report that describes all links click Report This button closes the dialog box and creates a report in a new workbook If you do
98. nt the characters removed If you want to limit the operation to selected cells that contain text select Skip non text cells If this option is not checked cells that contain values will also be modified U e wn e Page 15 6 Click Apply to modify the selected cells Removing spaces 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 Pon Note Selecting All Excess Spaces is equivalent to applying Excel s TRIM function to the cells Deleting characters by type 1 Select the cells that you want to modify In the Text Tools dialog box select the Delete characters option 3 Inthe Remove all drop down select the option that corresponds to the type of characters that you want to remove 4 Click Apply to modify the selected cells IN Note Selecting Non printing characters is equivalent to applying Excel s CLEAN function to the cells Text Tools Tips e 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 e After you click Apply you can choose Undo or press Ctrl Z to reverse the effects Note that only one level of undo is available e Text Tools works only with non empty cells Therefore you can select enti
99. o cells on the active sheet that use the active cell directly Direct Precedents Draws tracer arrows to cells on the active sheet that are used directly by the formula in the active cell 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 000000 Note There is some overhead involved in updating the PUP InfoBox to reflect the current active cell For maximum performance you will not want to keep this InfoBox displayed at all times 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 Workbook Summary Summary information about the workbook Name Report A listing of all names defined in the workbook Worksheet Report Information about each worksheet in the workbook VBA Module Report A listing of the number of VBA procedures contained in each VB Component within the workbook Using Workbook Summary Report 1 To create a workbook summary report activate the workbook Then select Workbook Summary Report from the PUP v7 Auditing Reports menu 2 Inthe Workbook Summary Report dialog box select the components to be included in the report 3 Click OK Page 62 The report is created in a
100. o swap Or you can make a multiple selection and select both ranges Select Swap Ranges from the PUP v7 Cells and Ranges Modify Cells and Ranges menu 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 the number of rows and columns for the two ranges must be identical Click OK to swap the two ranges Swap Ranges Tips N If the results of this operation are not what you expected choose Undo or press Ctrl Z The formatting is also swapped Formulas in the two ranges remain intact and continue to refer to the original cells 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 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 Select the first range Press and hold down the Ctrl key Select the second range Page 21 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 s
101. o test this product under all possible scenarios so it is quite likely that 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 help file for information on getting the problem resolved Page 7 GROUP CELLS AND RANGES The Cells and Ranges group contains command that work with data in cells 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 v7 Cells and Ranges Formula Tools menu Verify that the Copy from box contains the desired range Or change the range if desired Specify the Copy to location a single cell which represents the upper left cell of the destination range 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 or press Ctrl Z e The Copy from and Copy to ranges need not be in
102. on Tips If the results of this operation are not what you expected choose Undo or press Ctrl Z In some cases Excel CONVERT function is a better alternative to using this utility The Results range can be on the same worksheet a different worksheet or in a different workbook 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 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 To convert between world currencies see Currency Conversion NP NE 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 convert a single 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 and display the results in another range 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
103. operators Page 70 e The number of exponentiation operators e The number of concatenation amp operators Note e Because each formula cell is scanned separately it can take significantly 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 Excel 2007 does not support toolbars so this utility displays a toolbar like dialog box when the map is displayed e To view a particular cell or range in the original workbook select the range of cells in the map workbook then click the Activate Selected Cell Range button on the Worksheet Map toolbar e To return to the map workbook click the Return to the Worksheet Map button on the Worksheet Map toolbar To display information about the map codes click Key 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 Ifyou 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 If you find that this utility is running very slowly try setting Excel s calculation mode to Manual e The map workbook normally contains a simple macro that deletes
104. option Complex An HTML file readable in a web browser This is the format produced when you use Excel s Save as Web Page HTML 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 Choose Export Range to File from the PUP v7 Workbooks and Sheets Import Export menu Page 50 2 Inthe Export Range to File dialog box verify that the Range to export is correct Select one of the four file formats 4 The right section of the dialog box varies depending on the file format you select Specify additional options and choose OK 5 A file selection dialog box appears Specify a filename and location and click Save Export Range to File Tips e The contents of hidden rows or columns are not exported unless you choose the Complex HTML option e Ifyou export the range as an Excel workbook and don t select the Convert formulas to values checkbox the exported file may contain link formulas e Ifyou export the range as a text file and you select the Save values as shown onscreen option the values may lose precision if the cells are not formatted to display all decimal places e Ifyou 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 e If you choose the Com
105. or the checkboxes display information for the new base cell Place a checkmark next to each formatting attribute that you want to match In order to be selected cells must meet all of the formatting criteria that are checked To avoid selecting blank cells choose Don t select empty cells If you would like to see a summary of the number of cells selected choose Display summary Click OK to select the matching cells Select Cells by Format Tips Remember In order to be selected a cell must match all of the formatting criteria that are checked If Don t select empty cells is checked blank cells will not be selected even if they match the specified formatting attributes You may prefer to use Excel s built in command to identify cells that contain a particular format press Ctrl F to display the Find and Replace dialog box Usage 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 IN Select any cell that has percentage formatting with no decimal places Choose Select Cells by Format from the PUP v7 Cells and Ranges Select menu Because only a single cell was selected the Range box shows the address for the entire used area of the 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 sel
106. ore 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 when Excel is in any of the following modes as indicated on the status bar Ready Copy Cut or Find e 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 e Thealarmis 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 e 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 e Ifyou change your system s clock when a reminder alarm is set the alarm time will not be reliable e The alarm sound is stored in a file named alarm wav located in the pup7 directory If you prefer a different sound you can substitute a different WAV file The file however must be named alarm wav Batch Printing Wizard The Batch Printing Wizard prints the contents o
107. ose Range Randomize Utilities from the PUP v7 Cells and Ranges Formula 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 Randomize Utilities from the PUP v7 Cells and Ranges Formula Tools menu Page 20 2 3 4 Choose the Select cell s at random option Specify the No of cells to select value Click OK Inserting a sequence of integers in random order Pa Mae Choose Range Randomize Utilities from the PUP v7 Cells and Ranges Formula Tools menu Select the Insert a sequence of integers in random order option Specify a Beginning with value and an Increment value Click OK Range Randomize Utilities Tips 9 If the results of this utility are not what you expected choose Undo or press Ctrl Z This utility is useful for randomizing the rows in a list Select the cells in a blank column to the left or right of your list Insert a sequence of integers use the Insert a sequence of integers in random order option Sort the database using this new column as the sort key 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 auu ss cs ss K Using Swap Ranges Start by selecting one of the ranges t
108. our workbook uses an XLSX extension you will need to save it with an XLSM extension 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 e 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 e To get more information about a particular function select the function in the list and click the Function help hyperlink e 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 Function dialog box click the fx icon to the left of the formula bar 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 v7 directory e Some of the functions do not updated automatically For example the functions that ret
109. ovided clue values To unprotect the sheet choose Review Changes Unprotect Sheet To view the solution activate the sheet named Solution and place a checkmark in the View the solution checkbox To view only a single square don t click the View the solution checkbox Rather select a single cell and view the number in the formula bar Random Number Generator Random Number Generator simply generates a uniform random number within a numeric 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 v7 Power Utility Pak Amusements menu 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 PUN 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 background color of the dialog box uses a color from the active workbook s document theme 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 entertained for at least 10 minutes Page 79 ADDITIONAL INFORMATION Downloading Program Updates The goal is to make every component of PUP v7 error free and a
110. pdating is turned off The files that are opened and printed will not be visible Generate a summary If selected a report will be generated The report describes what was printed and report 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 These options determine if links and remote references are updated when each file is references 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 a summary report Step 4 This will let you know if any errors occurred while printing If 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 wiza
111. plex HTML option you can specify a title for the HTML document by choosing Use this title and entering the title text e 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 however that formulas which refer to cells outside of the exported range will be converted to values e 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 a 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 e To export an entire worksheet use PUP s Save Sheet as HTML command 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 Interchange Format JPEG Joint Photographic Experts Group TIFF Tagged Image File Format PNG Portable Network Graphics Page 51 Using Export Range as Graphic au Pw Before using Export Range as Graphic make sure that the range to be exported uses a color for its background To avo
112. pper 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 Ifthe results of this utility are not what you expected choose Undo or press Ctrl Z e Ifthe 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 and the cell will no longer function as a numeric value qn 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 Page 23 The formatting can be done using either of two techniques 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 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 Using Alternate Row Shading Before using the Alternate Row Shading utility
113. r 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 Select the cells that you want to work with Choose Math Without Formulas from the PUP v7 Cells and Ranges Modify Cells and Ranges menu Verify that the range displayed is the range you intend to modify To choose a mathematical operation select the appropriate item from the Operation list PE WONGS Page 17 Depending on the Operation selected you then enter an operand select a function or enter an expression The Operations are Operation Description Addition Adds the operand to each cell in the range Subtraction Subtracts the operand from each cell in the range Multiplication Multiplies each cell in the range by the operand Division Divides each cell in the range by the operand Exponentiation Raises each cell in the range to the power specified as the operand Rounding Rounds each cell in the range to the number of decimal places specified as the operand Function Lets you choose a function to apply to each cell in the range Only single argument functions are available Custom 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
114. ranges instead of one large range Copy Page Setup The Copy Page Setup utility makes it easy to transfer page setup settings from one sheet to other sheets in the same workbook Page setup setting include items such as headers footers margins page orientation and so on aeaa sss S ON Using Copy Page Setup 2 Activate the worksheet or chart sheet that contains the page setup setting that you want to copy Choose Copy Page Setup from the PUP v7 Workbooks and Sheets Printing menu The To list box lists all other non hidden sheets in the workbook Select the sheets that will receive the page setup settings from the active sheet 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 If the active worksheet contains any images in the header or footer select Include pictures in headers footer if you d like to transfer the images to the other sheets Click OK and the page setup settings are copied Page 49 Copy Page Setup Tips e Hidden sheets are not listed in the To list If you need to copy the settings to a hidden sheet unhide the sheet before using this utility e Copying page setup settings can sometimes be slow so be patient After you click OK the dialog box displays the name of each sheet as it s being processed Font Reference Sheet The Font Reference Sheet utility displays a si
115. rate data that workbook is opened the query is refreshed and the file is saved with the new data If you re curious you can open the currency rates xlsx workbook and examine the rate information 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 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 Equal to Not equal to Greater than Less than Greater than or equal to Less than or equal to Logical TRUE Logical FALSE Similar to pattern Not similar to pattern Cells that contain a value that is not between two specified values Cells that contain a value equal to a specified value Cells that contain a value not equal to a specified value Cells that contain a value greater than a specified value Cells that contain a value less than a specified value Cells that contain a value greater than or equal to a specified value Cells that contain a value less than or equal to a specified value Cells that contain TRUE Cells that contain FALSE Cells that match
116. rd automatically creates linked picture objects on a new worksheet These objects 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 1 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 v7 Workbooks and Sheets Printing menu 3 Use the Next and Back buttons to make your choices in the three steps or click Finish to accept the defaults IS 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 e To remove a range item from the list select the range item and click Remove e To change the order of the ranges items in the list select a range item and click the Up or Down arrows e When Activate the selected range is selected the selected range item is selected in the workbook Page 48 Step 2 Step 3 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 choic
117. re rows or columns Note however that you cannot use Text Tools to add text to empty cells e 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 e 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 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 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 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 sometimes displays negative signs to the right of a si
118. reen with F The cell contains a formula Green with a numeric value The cell contains a formula The number 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 Using Worksheet Map 1 Activate the workbook that you want to map 2 Select Workbook Map from the PUP v7 Auditing Utilities menu 3 Inthe Workbook Map dialog box select the scope for the map either Active sheet only or All sheets in the active workbook 4 Ifyou 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 are generated in a new workbook In addition a navigational toolbar dialog box named Worksheet Map is 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 The number of sets of parentheses in the formula The number of plus operators The number of minus operators The number of multiplication operators The number of division
119. ropriate or a utility simply doesn t perform as it should The first step is to check the PUP v7 Program Updates web site e http www j walk com ss pup pup7 The site may contain an updated version of the particular utility file which you can download If an updated version is not available please report the problem 1 Make a list of the exact steps required to reproduce the problem 2 Send an e mail to J Walk amp Associates at e supportOj walk com The problem will be corrected and an updated file will be posted at the PUP v7 Program Updates site The text in dialog boxes is cut off If the text in some of the PUP v7 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 How PUP v7 Works Power Utility Pak was developed using the programming tools that are supplied with every copy of Excel the Visual Basic for Applications VBA macro language One of the primary design goals of PUP v7 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
120. rst 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 Field Name Description Page 36 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 e Ifyou change your mind after inserting a sequence number select Undo or press Ctrl Z This will restore the previous contents of the active cell and will not affect the next sequence number e You can create as many sequence definitions as you need e In most cases you will use 1 as the Increment value You can however specify any number you like including a negative number e f you ve defined a sequence that you no longer use select it in the Insert Sequence Number dialog box and click Delete e If you need to change the parameters for a sequence delete the existing sequence
121. rust access to the VBA project object model Click OK twice to return to your workbook NS IN Date Report The Date Report utility creates a useful report that describes all date cells in a worksheet or workbook Using Date Report 1 Select Date Report from the PUP v7 Auditing Reports 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 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 EOMONTH MONTH NETWORKDAYS NOW TODAY WEEKDAY WORKDAY YEAR and YEARFRAC e The rows of the date report can be sorted by any column Page 63 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
122. s you can specify a Metric prefix by using the drop down lists below the From and To list boxes 7 Enter the value to be converted in Input box on the left side of the dialog box Au PwWnr Page 26 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 Choose Unit Conversion from the PUP v7 Cells and Ranges Convert menu In the Unit Conversion dialog box select the Convert a Range tab Verify that the Source range is correct and change it if necessary 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 6 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 7 Select the Unit category 8 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 9 Click OK Unit Conversi
123. s marked with an asterisk can be clicked to display a dialog box or additional information Page 61 Using the PUP InfoBox 1 Select PUP InfoBox from the PUP v7 Auditing menu The PUP InfoBox dialog box will be displayed 2 While the PUP InfoBox 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 3 When you are finished using the PUP InfoBox click the Close button X in the dialog box s title bar Notes e The PUP InfoBox 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 v7 Options dialog box If the PUP InfoBox toolbar disappears unexpectedly just select PUP InfoBox from the PUP v7 Auditing menu again PUP InfoBox Tips e Items that are underlined can be clicked o Number Format Displays the Number Format tab of the Format Cells dialog box Horiz Alignment Displays the Alignment tab of the Format Cells dialog box Protection Displays the protection tab of the Format Cells dialog box Format Conditions Displays the Conditional Formatting Rule Manager dialog box Data Validation Displays the Data Validation dialog box Direct Dependents Draws tracer arrows t
124. s option This enables you to create sheet names such as Division 1 Division 2 etc If you ve created any custom lists using Excel s Edit Custom Lists button located in the Popular tab of the Excel Options dialog box these lists appear when you choose the Custom List option For optimal control over the sheet names place your sheet names in a range and use the Data in a range option View Active Workbook s Folder PUP s View Active Workbook s Folder command displays a Windows explorer window for the active workbook s directory nr lt Using View Active Workbook s Folder To view the folder for the active workbook choose PUP v7 Workbooks and Sheets Workbook Tools View Active Workbook s Folder View Active Workbook s Folder Tips Many uses find that it s easier to open Excel workbooks directly from an Explorer window This utility is for those users Remove All Macros The Remove All Macros utility will remove any or all of the following elements from the active 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 with VBA Excel 5 95 Dialog Sheets that store custom dialog boxes Unless they are hidden these sheets are visible in the Sheets workbook window not in the VB Editor Excel 4 XLM Macro Sheets that store X
125. s 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 Description Toggle Vertical Scroll Bar Toggles the display of the vertical scroll bar Toggle Horizontal Scroll Bar Toggles the display of the horizontal scroll bar Toggle Grid Lines Toggles the display of the ell grid lines Toggle Row and Column Borders Toggles the display of the row and column headers Toggle Sheet Tabs Toggles the display of the workbook sheet tabs Toggle R1C1 Reference Style Toggles the reference style for formulas between A1 and R1C1 Hide Window Hides the active window 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 Description Minimize All Windows Minimizes all worksheet windows Tile Windows Equivalent to the Arrange command on the Windows menu with the Tiled option Activate a Window Displays a list of all windows Interactive Hide Unhide Hide or unhide workbooks or sheets interactively See Interactive Hide Unhide PUP v7 Amusements Just for fun 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 vi
126. select the range of cells to which you want to apply the formatting Choose Alternate Row Shading from the PUP v7 Cells and Range Format 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 The color choices represent the colors in the active workbook s document theme Specify a value for the Shade every setting To keep existing background colors for non shaded rows select the Leave existing color for non shaded row 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 y Notes Select the range that contains alternate row shading Choose Alternate Row Shading from the PUP v7 Cells and Ranges Format 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 If you choose the Conditional formatting option the range may not contain any ot
127. selected Activate the workbook and go to When the bookmark is selected the workbook will be opened or the specified sheet 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 Editing PUP Bookmarks To edit your bookmark list click Edit Bookmarks in the PUP v7 PUP Bookmarks group This displays the Edit Bookmarks dialog box When you select a bookmark item from the list its workbook sheet and range are displayed at the bottom of the list box Note To change the order of the bookmark names select an item in the Bookmark names list and use the up and down arrows buttons to move the selected item For example you may prefer that more frequently used bookmarks appear at the top of the list Use Rename to change the name of the selected bookmark 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 to delete all defined bookmark names Click Close to close the Edit Bookmarks dialog box 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 Page 72 Activating a PUP Bookmark To activate a bookmark click Activat
128. ser Page 52 e Only the data in cells is saved Charts and other objects are ignored e 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 1to 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 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 Cursor Activate the cell that will be the upper left cell of the data in the imported file Select Insert File at Cursor from the PUP v7 Workbooks and Sheets Import Export menu Ensure that the Beginning at this cell reference is correct or change it Click Browse to locate the file 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
129. t Hour Inch Calorie Pound Gallon Leap Cubic Minut M bit Met G Sq Foot Kel ae Ban ERSA Th mic force Imp ae amoa Svin Year Meter Pee Nautical Electron Ounce Cubic Radian Gigabit mile volt Liter Avdp Sq Inch Minute Yard Statut O S Revolution Terabit a wee Erg Ounce ae q Second mile Troy Meter Foot Ounce Pound S d Petabit Yard Sq Mil Y econ etabi ar Hound imp Avdp q Mile ear Exabit H powr Pint Pound Sq Yard hr Troy a BYTE Joule Pint Imp Stone Watt KILOBYTE A Quart Ton hour MEGABYTE ual Imp GIGABYTE Tablespoon TERABYTE Teaspoon PETABYTE EXABYTE Notes e Metric units can be scaled by specifying a prefix For example decimeter millimeter kilometer etc e In some cases minor rounding errors will be apparent Using Unit Conversion The Unit Conversion utility can be used in either of two modes e Convert a single value 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 Choose Unit Conversion from the PUP v7 Cells and Ranges Convert 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 unit
130. t are formatted Select entire rows or entire columns beyond your data area and delete the rows or columns Then use the Reset Last Cell utility again Sort Sheets The Sort Sheets utility provides a flexible way to sort the sheets in your workbook Using Sort Sheets 1 Activate the workbook that contains the sheets to be sorted 2 Select Sort Sheets from the PUP v7 Workbooks and Sheets Workbook Tools menu The list box will display all sheets in the active workbook in their current order 3 Click the Alpha Sort button to arrange the sheet list in alphabetical order optional Use the Move up and Move down buttons to change the position of the selected sheet 5 When you re satisfied with the position of the sheets in the list box click OK gt Note The order of the sheets listed in 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 Page 39 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 Sheet2 e When the View sheet option is enabled selecting a sheet in the sheet list will
131. t 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 In addition each sheet will have the same active cell or range selection as in the original worksheet Using Synchronize Sheets e Activate a worksheet and select a cell or range e Choose Synchronize Sheets from the PUP v7 Workbooks and Sheets Workbook Tools menu e A dialog box will appear that tells you the how many worksheets were synchronized Synchronize Sheets Tips e This operation cannot be undone e If you re using the Enhanced Shortcut Menus option the Synchronize Sheets command also appears on the shortcut menu that appears when you right click a cell or a sheet tab 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 Operations Change the hidden status of any open workbook Save workbooks Rename workbooks Close workbooks Activate a workbook Sheet Operations Change the hidden status of any sheet in any open workbooks Delete sheets Rename sheets Activate a sheet Using Interactive Hide Unhide 1 Select Interactive Hide Unhide from the PUP v7 Workbooks and Sheets Worksheet Tools menu 2 Use the controls in the dialog box to work with various items see below 3 Click Close to close th
132. tains the files you want to list If you would like to includes files in the subdirectories choose Include files in subdirectories If you would like the filenames to be displayed as hyperlinks choose Create hyperlinks Click OK Au PWN Pr The filename list is created in a new worksheet If you choose the Include files in subdirectories option the list contains four columns Directory File Name Size and Date Time If you don t choose the Include files in subdirectories option the list contains three columns File Name Size and Date Time 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 Page 54 GROUP CHARTS AND GRAPHICS The Charts and Graphics group contains command that work with charts and other objects on a worksheet 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
133. the Width based on the Height displayed Click the bottom Calc button to calculate the Height based on the Width displayed If you re resizing multiple charts the aspect ratio that s maintained is based on the chart that is selected when the utility is invoked 5 Click Apply to change the size of the selected chart 6 If the size is satisfactory click Close to dismiss the Resize Charts dialog box Resize Charts Tips e Ifthe results aren t what you expected choose Undo or press Ctrl Z immediately Convert Chart To Picture The Convert Chart To Picture utility creates a static picture object from a single chart or all charts on a worksheet The original chart remains intact Page 57 Using Convert Chart To Picture Start by selecting that embedded chart that you want to convert to a picture 1 Choose Convert Chart to Picture from the PUP v7 Chart and Graphics Chart Tools menu 2 To convert all charts choose Convert all charts on the active worksheet 3 Click OK to convert the chart or charts to a picture or pictures The picture is placed directly on top of the original chart offset slightly to the right and downward Use your mouse to drag the picture 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 only with charts that embedded on a worksheet It does not work with Chart sheets Keep in mind t
134. the message box This will restore PUP v7 to its default state Be aware that this action will delete any Sequences and PUP Bookmarks that you have defined The PUP v7 Source Code Licensed users of PUP v7 may purchase the complete VBA source code for a nominal fee Examining this usually 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 see the RibbonX code that creates the PUP v7 Ribbon You can adapt these techniques to your own work The source code files are available from J Walk and Associates for US 20 00 Please note that the license does not allow you to use this code for commercial products About the Trial Version The Trial Version of Power Utility Pak 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 v7 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 PUP v7 using any of the following methods e Online Safe and efficient using a secure server Requires a credit card No processing fee e Mail Use check money order or credit card 5 00 processing fee applies e Fax Requires a credit card 5 00 processing fee CD ROMs
135. the same worksheet or in the same workbook If you specify a range that contains a multi cell array formula and the range does not include the entire array the copied formulas will result in an error OP NA Convert Relative and 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 Absolute references for example SAS1 Relative references for example A1 Column absolute references for example A1 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 DS 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 and Absolute References Before using Convert Relative and 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 Page 8 Choose Convert Relative and Absolute References from the PUP v7 Cells and Ranges Formula Tools menu Verify that the Range box contains the desired range Or change the range if necessary Select the appropriate Convert option Cli
136. 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 perform the specified operation To ignore cells that contain formulas select Skip formula cells Normally you will want to keep this option checked Click OK Math Without Formulas Additional Details 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 146 0125 899 105 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 1 3 24 60 60 154 97 11 Page 18 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 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
137. ting a range of values Before using the Currency Conversion utility select the range of cells that contains the value to be converted Choose Currency Conversion from the PUP v7 Cells and Ranges Convert menu In the Currency Conversion dialog box Select the Convert a Range tab Verify that the Source range is correct and change it if necessary 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 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 7 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 9 Click OK Wi PwWwnr oo Updating the exchange rates The Currency Conversion dialog box displays the date and time 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 f 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 c
138. u 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 v7 Cells and Ranges Select menu In the Go To Min or Max Value dialog box xelect Minimum value or Maximum value To select only the first matching cell choose First cell only To select all matching cells choose All cells Specify the type of cells to search formulas values or both in the Look in box Click OK N 2 A NA 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 tells you how many cells were found Go To Min or Max Value Tips e If 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 Page 32 GROUP WORKBOOKS AND SHEETS The Workbooks and Sheets group contains command that work with workbooks or worksheets 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 lef
139. urn color information about a cell do not update when you change the color of the cell used as the argument To update these functions press Ctri Alt F9 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 Page 14 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 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 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 v7 Cells and Ranges Modify Cells and Ranges menu The Text Tools utility always
140. used for the substituted cell or range addresses 5 Click OK Y A message box will tell you the number of formulas that were modified Unapply Names Tips e Ifthe results of this utility are not what you expected choose Undo or press Ctrl Z e Insome cases it s not possible to unapply names so check the results carefully It s always a good idea to save your workbook before using this utility e When you create a formula by pointing to a cell or range Excel always inserts the name for a named cell or range reference whether you like it or not In some situation this can cause a problem if you later copy the formula The Unapply Names utility can convert these names to cell references e 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 a very slow process For faster processing select only the names that you know are used in the formulas in the selected range e 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 access the Excel Options dialog box and select the Advanced tab Scroll down and remove the checkmark from Transition formula entry Note however that changing this mode will also convert all of the names used in your formulas to standard references so you don t even need to use this utility Page 9 Error Cond
141. what you expected choose Undo 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 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 not a value or a formula In addition to superscript and subscript formatting this utility can apply the following changes to individual characters ina cell Toggle the case of alpha characters Toggle font Bold attribute Toggle font Italic attribute Change the font size Using Superscript Subscript Formatting Before using Superscript Subscript Formatting select the cell that you want to format The cell must not contain a formula or a value 1 Choose Superscript Subscript Formatting from the PUP v7 Cells and Ranges Formatting menu 2 The Superscript Subscript Formatting dialog box displays the contents of the cell 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 selected character s vertical position Superscript Normal or Subscript toggle the case of using the U
142. width of the Workbook Link Finder dialog box by dragging the control on the right side of the dialog box Excel provides some built in tools for working with links Click the Office button and choose Prepare Edit Links to Files This command is not available if the active workbook contains no links The PUP 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 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 defined at the workbook level names Sheet level Names defined at the worksheet level These names are preceded by the worksheet s name and names an exclamation point e g Sheet1 MyName Hidden names Names that are hidden and do not appear in Excel s Name Manager dialog box Linked names Names that are defined in terms of a range in a different workbook Erroneous Names that contain an erroneous reference These names often cause a phantom link problem names Excel prompts you to update links 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 n Activate
143. x will display the names that aren t defined in terms of a cell or range that is named formulas e 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 e 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 e You can always delete names listed in the Erroneous names category without causing any problems 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 different 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 If checked cells will be considered different if they use different number formats formatting Using Compare Ranges If you re comparing two ranges start by selecting o

Download Pdf Manuals

Related Search

Related Contents

Shark S3501C User's Manual  Samsung Galaxy Fame Lite Felhasználói kézikönyv  WDP3500 Hydraulic Sump Pump    CMD600 - Sensor  BeatMaker 2 User`s Manual  User manual  nspifationssaon  

Copyright © All rights reserved. Failed to retrieve file