Bill Jelen has published a volume of 277 Excel mysteries solved. Presented here are a smattering of tips from the book.

........................................................................................................................................................................................

An excel file repair tool: ExcelFIX.

........................................................................................................................................................................................

Many Excel toolbar icons will do the reverse action when you shift-click them. If you need more room on the formatting toolbar, you can remove the right-justify icon, because shift-clicking the left-justify icon will do the same thing. You can Sort Descending by shift-clicking the Sort Ascending button. Other pairs that can be accessed with the shift button include Increase/Decrease Decimal, Indent/Outdent. Perhaps my favorite shift trick is to hold down the shift key before you access the File menu. The Close option turns into Close All, allowing you to close all open workbooks without closing Excel. Turn a range on its side: Say that someone sent you a workbook with month names going down column A. You need to have these month names go across the columns of Row 1. The operation of turning a range on its side is called Transposing the data. Follow these steps:

• Select the data in column A
• Ctrl+C to copy to the clipboard
• Select cell B1
• Choose Edit – Paste Special. In the PasteSpecial dialog, place a checkmark in the bottom box for Transpose.
• Click OK – the data will be turned on it's side.

Note that you can not Cut & Transpose. You will have to copy the data and then separately erase the original data if necessary. Create Random Letters: Many people know how to use the =RAND() function to generate random numbers. It is also possible to create random letters. This formula uses the RANDBETWEEN function, which is only available in the analysis toolpack. To make sure that this function is available in your installation of Excel, go to Tools – Add-Ins. In the Add-Ins dialog, make sure that Analysis Toolpack is selected. The RandBetween function accepts two numeric parameters and will return a random integer between those integers inclusively. For example, =RANDBETWEEN(15,25) will return a random number from 15 to 25. To generate a random letter, you can utilize the fact that the capital letter A is known as character 65 in the ASCII character set. =CHAR(65) will return an A. The letters of the English alphabet run in sequence from CHAR(65) through CHAR(90). Thus, you can use the formula =CHAR(RANDBETWEEN(65,90)) to randomly generate a random letter from A through Z. Note that any of the random functions are volatile. Every time you enter a value in a cell or press the F9 key, the result of the random function will change. You might want to enter the random letters and then freeze them so that they will not be recalculated. In this case, select the range containing the random letter formulas. Copy with Ctrl+C. Use Edit – Paste Special – Values to change the random formulas to their current values.