Making the Difference

MENU OPTION - AVOID CALC ERRORS   (back to contents) What does it do? "Avoid Calculation Errors" removes calculation error messages arising such as "#DIV/0!" , "#VALUE!" , "#N/A" , etc. replacing the result with a zero "0". XLSpower does this by embedding the core formula within =IF(ISERROR(core formula)=TRUE,0,(SUM(core formula)) "Remove If(ISERROR)" option will merely remove the wrapper functions from around the core formula. Why is this useful? Excel error displays can arise for a variety of reasons - on some occasions it is desirable to replace the error message with a zero. e.g. where a formula results in a value being divided by 0 or blank - or a LOOKUP function cannot find a value. The problem can be resolved by manually keying in the required formula but the process is time consuming and prone to keying errors! XLSpower "Avoid Calculation Errors" can quickly eliminate the error messages in just 2 keystrokes. How does it work? Select/highlight a range of data/formula where the unwanted error values are being displayed. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+A". If you have the "Formula Bar" on display you will see that the original formula is now elongated to be =IF(ISERROR(core formula)=TRUE,0,(SUM(core formula)) The additional formula can be removed quickly with XLSpower's "Remove IF(ISERROR)". Note "Avoid Calculation Errors" can be applied to all cells in a range irrespective of whether an error return is being displayed or not.

MENU OPTION - BORDERS - CELL BORDERS and OUTER BORDER LINE   (back to contents) What does it do? For a range of selected cells - for each cell, the border color will be formatted to either black or white (white being useful for dark colored cell backgrounds). The "Outer Border Lines" option only colors the outer boundary of the selected area with a black line. Why is this useful? Speeds up formatting. To do this using normal Excel functionality requires the use of mouse actions. With XLSpower you only need 3 keystrokes! How does it work? Select/highlight the range of cells to be formatted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+C" (once, twice or three times). Note: formatting can be undone using "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+U"

MENU OPTION - BORDERS - SHADE UNLOCKED CELLS   (back to contents) What does it do? Where a worksheet is to be protected - cells that need to be unprotected to allow updates need to be flagged as not "locked". See Excel Menu - Format/Cells/Protection. "Shade Unlocked Cells" - gray shades these cells. Why is this useful? It is useful to see at a glance which cells are not set to "Locked" status. By selecting a range of the worksheet - and using "Shade Unlocked Cells" the cells are immediately shaded gray and easily identified. Note "Shade Unlocked Cells" will only operate prior to the worksheet being protected. How does it work? Select/highlight the range of cells to be formatted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+S". Note: formatting can be undone using "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+U" N.B. avoid selecting the whole worksheet as this will result in 17 million cells having to be searched - taking up a lot of needless time ! Use "CTRL" + "Break" keys to stop the process.

MENU OPTION - CELL BACKGROUND COLOR   (back to contents) What does it do? For a range of selected cells - the background color is changed. Why is this useful? Speeds up formatting - matching blends of the same color are grouped together. The background color is easily changed using only 3 to 5 keystrokes! How does it work? Select/highlight the range of cells to be formatted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+starting letter of the color required". Note The formatting can be undone using "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+U".

MENU OPTION - SHADE BLANK CELLS   (back to contents) What does it do? For a range of selected cells - cell contents that are blank are shaded gray. Why is this useful? Speeds up formatting. Where a range of data has intermittent blank values - the appearance of the layout can be enhanced by shading the blank value cells. This feature only requires 3 keystrokes! How does it work? Select/highlight the range of cells to be formatted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+S". Note The formatting can be undone using "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+B" and "ALT+S" (twice).

MENU OPTION - CONVERT FORMULAS TO ABSOLUTE VALUES   (back to contents) What does it do? "Convert Formulas to Absolute Values" adds the $ sign to cell references contained within formulas. e.g. =SUM(A1:A22) becomes =SUM($A$1:$A$22) This means that when a formula is copied to another location - the formula retains the original cell references. Why is this useful? Formulas can be converted to absolute values by manually typing in the $ sign - or you can use Excel's shortcut F4 key. However this needs to be done on a cell by cell basis if the formulas are different in each cell. Where you have a range of cells containing different formulas that you wish to change to absolute formulas - then each cell formula has to be individually updated. The process can be speeded up using Excel's "Find and Replace" option but this can also become long winded. "Convert Formulas to Absolute Values" removes the hassle. A whole range of formulas can be converted to absolute formulas with just 2 keystrokes! How does it work? Select/highlight a range including the formulas you wish to change to absolute values. Note "Convert Formulas to Absolute Values" can be applied to all cells in a range - only the cells containing formulas will be updated. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+Z". If you have the "Formula Bar" displayed you will see that the original formula now includes the "$" sign with the cell references. Note the "$" sign can be removed quickly with XLSpower's :-

MENU OPTION - COPY VISIBLE   (back to contents) What does it do? For a range of selected data that includes hidden rows and columns "Copy Visible" will copy only the visible rows and columns. Why is this useful? Where a user wants to copy only the visible range (e.g. copying data from filtered results) to a new range/worksheet - the standard copy function will copy the entire range including hidden rows and columns. Excel provides a facility to select the visible range only but this requires 4 keystrokes and a mouse click on "OK" plus copying is then required. "Copy Visible Only" does the whole process in just 2 keystrokes. How does it work? Select/highlight the range of visible data to be copied. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+V" to copy. The copied data can then be pasted to another range/worksheet as required. You find it helpful to use "Paste Value and Format"

MENU OPTION - DIAGONALY COPY   (back to contents) What does it do? Simply diagonally copies a single row of data downwards offsetting the copy by one column to the right per row. Why is this useful? Certain calculations need to run on an offset basis for each row. Using "Convert to Absolute Values" in conjunction with "Copy Diagonally" can avoid lengthy work arounds to achieve the same result. How does it work? Select/highlight a range (on a single row) that you wish copy downwards diagonally. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+D" to copy diagonally downwards. CAUTION! Generally you need to select only a single row of data to copy downwards. "Diagonal Copy" will allow more than one row to be selected but this should only be used for a single column of data. N.B. Beware that the copy downwards and to the left will overwrite existing data in the copy range. You need to check that the range to be filled is empty!

MENU OPTION - FILE NAME and PATH into Cell   (back to contents) The XLSpower menu serves as a useful reminder to insert the File Name and Path details on to your worksheets. What does it do? Simply puts the file name and path of the current workbook into the selected cell. Why is this useful? Include the "File Name and Path" cell within print ranges. Then it is easy to check back from the print out to the source workbook on your PC. A good place to insert "file name and path details" is to the top left of a print range e.g. cell A1. This feature is especially useful where you have many versions of a particular workbook and several different print outs. How does it work? Select/highlight a cell where you want the file name/path to be inserted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+F" to insert the File Name and Path into the cell. CAUTION! If the cell where you want to put the file name and path already has contents - "File Name and Path" will automatically insert a new row in order to insert the File Name and Path details. N.B. You must already have saved the file with a specific name, "File Path & Name" will not operate with a new workbook that only has the default name (e.g.Book1.xls).

MENU OPTION - XLSpower FUNCTION   (back to contents) XLSpower comes with the "Pick" function. What does it do and why is this useful? Within Excel's function list - menu Insert/Function, you will find the "Pick" function - just use it in the way described below. The function is useful because you can display a parameter along with a description. Change the parameter associated with the text and results from related formulas change.

MENU OPTION - HIDE ROWS with BLANKS/ZERO's   (back to contents) What does it do? For a single column - "Hide Rows with Blanks/Zero's" will hide rows that contain blanks and/or zeros. Why is this useful? Where you have many rows of data - it is often convenient to display only those rows that contain non zero data or blanks. How does it work? Select/highlight a range of data within a single column. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+H". Choose whether you want rows containing zeros and/or blanks to be hidden by checking the appropriate boxes - then choose "OK". CAUTION! "HideRows" will not allow you to select a complete column - this is to avoid "HideRows" needlessly checking 65k rows. N.B. If you wish to use this on multiple columns containing rows/blanks - merely insert a new column that totals across the other columns. Then run "HideRows" against the "Total" column.

MENU OPTION - MATRIX SUM   (back to contents) What does it do? This feature provides an alternative to Excel's built in AUTOSUM function. With AUTOSUM the totals are displayed to the bottom row and right of the selected numbers. Matrix Sum places the additions across the top and left of the data. Why is this useful? Where you have many columns and rows of numbers - it is often easier to see the totals at the beginning of rows and tops of columns. How does it work? Select/highlight a range of numeric data. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+M" to run the Matrix Sum routine. The resultant screen will give you the totals as follows:- NOTE! An additional column and row are always inserted to allow for the totals to be entered and to avoid overwriting existing data.

MENU OPTION - PASTE VALUE and FORMAT   (back to contents) What does it do? "Paste Value and Format" will paste the value and format attributes of a copied range of data. Why is this useful? Where a user wants to snapshot the results from a worksheet - to another range/worksheet, the standard copy/paste routine will paste formulas as well. Excel provides the facility to paste only values and formats but this requires two operations involving 6 keystrokes and 2 "OK" mouse clicks. "Paste Value and Format" will do the job in just 2 keystrokes. How does it work? Copy the data you require to the clipboard - you might want to use "Copy Visible Only". Having copied the data select/highlight the cell representing the top left area where you wish the data to be pasted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+P".

MENU OPTION - REMOVE REPEAT CELL VALUES BELOW   (back to contents) What does it do? "Remove Repeat Cell Values Below" will remove repeating cell values from within columns to just leave unique occurrences of a value. Why is this useful? This feature can help the appearance of reports - where only the summary labels are required. This is best explained by illustrating the following example. Data in a workbook might look like this:- Using "Remove Repeat Cell Values into Cells Below" produces a result like this:- How does it work? Select/highlight a range of data to remove repeat values. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+R" and "ALT+R" again. CAUTION! Be careful not to inadvertently include within your selected range of data, values that are repeated that you do not wish to be removed. Note: the process can be undone using "Repeat Cell Values Below".

MENU OPTION - REPEAT CELL VALUES BELOW   (back to contents) What does it do? "Repeat Cell Values into Cells Below" will cascade values downward. Why is this useful? This feature is useful to convert summary data to detail - to use in a database or lookup range. This is best explained by the illustrating the following example Data in a workbook might look like this:- Using "Repeat Cell Values into Cells Below" produces a result like this:- How does it work? Select/highlight a range of data to cascade downwards. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+R". Note: the process can be reversed using "Remove Repeat Cell Values Below"

MENU OPTION - NUMERIC VALUES REVERSE SIGN +/-   (back to contents) What does it do? For numeric values contained within a selection range - positive values are converted to negative values, negative values are converted to positive values. Why is this useful? The need to reverse the sign can arise where formula(s) have been set up to expect a range of numeric values which are signed the opposite way round. To change the values manually, a new range has to be set up, the data copied and then multiplied by -1. The results (value only) then need to be copied back to their original positions. "Numeric Values - Reverse Sign" does this in just 2 key strokes! How does it work? Select/highlight a range of data that includes values where you want the signs for numeric values to be reversed. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+N" to reverse the sign. Note The operation is easily reversed by just running "Numeric Values - Reverse Sign" again.

MENU OPTION - TEXT BULLET   (back to contents) What does it do? Puts a small round bullet in front of text. Why is this useful? Improves formatting - "Bullet Text" provides a quick solution. How does it work? Select/highlight the range of cells with text to be formatted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+T" and "ALT+B" and "ENTER". Note formatting can be undone using "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+T" and "ALT+B" (twice) and "ENTER".

MENU OPTION - TEXT INDENT   (back to contents) What does it do? Indents text by 3 spaces. Why is this useful? Speeds up formatting. Where words are being used in Excel - for display purposes it is often desirable to indent sub-headings. This can be done by introducing extra columns - but the additional column may interfere with other layouts on the worksheet. "Indent Text With 3 Spaces" provides a quick solution. Note if you want to nest indents within indents just repeatedly run "Indent Text With 3 Spaces". How does it work? Select/highlight the range of cells with text to be formatted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+T" and "ALT+I". Note formatting can be undone using "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+T" and "ALT+R"

MENU OPTION - TEXT - NUMBER to TEXT FORMAT   (back to contents) What does it do? Converts values in "Number Format" to "Text Format". Why is this useful? Under certain conditions values need to be treated as text - e.g. This can arise where a "LOOKUP" function is used (refer to Excel's Help for more details on LOOKUP functions). Once a number has been entered into Excel it usually assumes the default "Number Format" the value will continue to remain as a number even if the cell is reformatted to "Text". A "LOOKUP" function will not work where a number in "Text Format" is being compared with a value in "Number Format". Reformatting the cell to "Text Format" does not remedy the situation. "Convert Number to Text Format" cures the problem. How does it work? Select/highlight the range of cells where numbers need to be converted to "Text Format". Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+T" and "ALT+C".

MENU OPTION - TEXT FORMATS   (back to contents) What does it do? Text formatting. Why is this useful? Speeds up formatting. e.g. combine a dark cell background color with "Text White". This can be done using just 3 keystrokes! How does it work? Select/highlight the range of cells with text to be formatted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+T" and "ALT+T" (once or twice) and "ENTER". Note formatting can be undone using "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+T" and "ALT+U"

MENU OPTION - UNHIDE ROWS and COLUMNS   (back to contents) What does it do? "Unhide Rows and Columns" will unhide all rows and columns selected. Why is this useful? To unhide both rows and columns using standard Excel menus requires 6 keystrokes - rows and columns have to be separately revealed! "Unhide Rows and Columns" unhides both as a single operation requiring only 2 keystrokes. How does it work? Select/highlight a range of data to unhide rows and columns - OR select all cells by clicking on the top left gray box to the left of the "A" column of the worksheet. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+U".

MENU OPTION - DATE INSERT TODAY   (back to contents) The XLSpower menu serves as a useful reminder to insert the date on to your worksheets. What does it do? Simply puts the date, when the current workbook is created, into the selected cell. Why is this useful? Include the "Date" cell within print ranges. Then it is easy to tell when the worksheet was created. How does it work? Select/highlight a cell where you want the date to be inserted. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+D" to insert the date into the cell. CAUTION! If the cell where you want to put the date already has contents in it - "Date Today Insert" will insert a new row in order to enter the date.

MENU OPTION - SWAP DATA/FORMULA/FORMATS   (back to contents) What does it do? "Swap Data" works on two selections of data (each with the same number of columns and rows). "Swap Data" then Cuts and Pastes the first selection of data to a temporary worksheet. The second selection is then cut and pasted into the gap left by the first selection. Finally the first selection is pasted into the space left by the second selection area and the temporary worksheet is removed. Why is this useful? This is a quick way to swap data/formula/formats around without the need for a more lengthy copying and pasting process. How does it work? Select two ranges of data of equal rows and columns. Select the first range then press the "Control" key and then select the second range. Then use keystrokes "ALT+X" (to invoke the XLSpower drop down menu) followed by "ALT+S" to insert swap the two selections around. CAUTION! If selections have ranges that contain formulas, the formulas will change on a relative cell basis just as in a normal "Copy" and "Paste" operation. If you want the formulas to retain their original cell references you will need to convert the formulas to absolute values. You can do this manually or you can use XLSpower's "Convert Formulas to Absolute Values".

©2003 All rights reserved