Tech That!

• When I enter a value, it appears with two decimal places. For example, when I enter 154 it shows up as 1.54. What's wrong?
  Somehow Excel's fixed-decimal mode was turned on. To return to normal select Tools » Options » Edit » remove the check mark from the Fixed decimal option.
• Can I change the color of the worksheet tabs in my workbook?
  No. It would certainly be helpful to be able to color-code your worksheet tabs. Microsoft hasn't implemented this feature (please check this feature in Microsoft Office Live. I heard its available in this new version of office, though not sure) which has been available in 1-2-3 and Quattro Pro for quite a while.
• I saved my workbook with a password, but Excel doesn't recognize it and won't let me open the file. Am I out of luck?
  Passwords are case sensitive. So if you originally entered your password as Xyzzy then typing xyzzy won't work. If you are entering the password correctly then it's time to start looking for a password recovery utility. Several utilities exist and some of them are free. Google search for Excel password recovery and you'll find several products that will come to the rescue. These products might raise some alarms for the security minded.
  Bottom line?
  Excel password protection isn't as secure as you might expect.
• How can I increase the number of rows or columns in a worksheet?
  Every workbook in Excel has 255 columns and 65,526 rows. These values are fixed and cannot be changed. If you need more rows use Microsoft Access or latest version of Excel also known as Microsoft Excel Online.

Formulas and Functions:

• Is there a function that returns the name of the worksheet?
  Excel's CELL function comes close. The following formula displays the workbook's full path along with the worksheet name:
  =CELL("filename")
  For example this formula might return something like:
  C:\Windows\Desktop\[Budget.xls]Sheet2
  Returning only the sheet name requires a more complex formula:
  =MID(CELL("filename"), FIND("]",CELL("filename"))+1, LEN(CELL("filename"))-FIND("]", CELL("filename")))
• I have a price list stored in a worksheet and I need to increase all prices by 5 percent. Can I do this without re-entering all the prices?
  Excel provides two ways traditional technique which goes something like this...
  Insert or find a blank column near the prices.
  In that column's first cell enter a formula to multiply the price in that row by 1.05.
  Copy the formula down the column.
  Then select and copy the entire column of formulas, select the original prices and choose Edit » Paste Special » select Values to overwrite the original prices with the formulas results.
  And finally delete the column of formulas.
  Another more efficient approach also uses the Paste Special dialog box.
  To increase a range of values (prices in this example) by 5 percent enter 1.05 into any blank cell.
  Select the cell and choose Edit » Copy.
  Then select the range of values and choose Edit » Paste Special.
  Choose the Multiply option and click OK.
  Then delete the cell that contains the 1.05.
• I've created some clever formulas and I don't want anyone else to see them. Is it possible to hide the formulas but display the results?
  
  
  
  Every cell has two key properties: locked and hidden.
  A locked cell can't be changed and the contents of a hidden cell don't appear in the formula bar when the cell is selected.
  By default every cell is locked and not hidden. But it's important to remember that these attributes have no effect unless the worksheet itself is protected.
  To change the attributes select the appropriate cell or range and then choose Format » Cells » Protection » Locked or Hidden (or both).
  Unlock cells that accept user input and lock formula and other cells that should stay unchanged (such as titles).
  To prevent others from seeing your formulas lock and hide the formula cells. The results of the formulas will be visible but the formulas will not.
  
  
  
  To protect the worksheet choose Tools » Protection » Protect Sheet. Make sure the Contents box is checked. Enter a password to prevent others from unprotecting the sheet. Locked cells in a protected sheet cannot be edited and changes are disabled.
• Can I write a formula that returns the number of distinct entries in a range?
  Say we're hunting for a formula that, given the range 100, 99, 98, 100, 98, 100, 98, would return 3. This type of counting requires an array formula. For example counts the number of distinct entries in the range A1:D100.
  =SUM(1/COUNTIF(A1:D100, A1:D100))
  When you enter this formula you must press Ctrl-Shift-Enter.
  Pressing only Enter will give you the wrong result.
  Excel will place brackets around the formula to remind you that you've created an array formula.
  The preceding formula works fine in many cases but it will return an error if the range contains any blank cells.
  The formula below with Ctrl-Shift-Enter is more complex but it will handle a range that contains a blank cell.
  =SUM(IF(COUNTIF(A1:D100,A1:D100)=0, "", 1/COUNTIF(A1:D100,A1:D100)))

Thanks Ayub for sending me this great finding. Also thanks Imageshack for hosting the above images for free.

Labels: Excel, Microsoft

# posted by Rupesh @ 9:55 AM