6/11/2007
Microsoft Excel formulas
Following are the most asked questions of microsoft excel. I hope you'll find it worthwhile.
Excel settings:
Excel settings:
- 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.
Comments:
Links to this post:
<< Home
"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."
Right Click on Tab-> Select Tab Color
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."
Right Click on Tab-> Select Tab Color
Good day !.
might , perhaps curious to know how one can manage to receive high yields .
There is no initial capital needed You may commense earning with as small sum of money as 20-100 dollars.
AimTrust is what you thought of all the time
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.
Its head office is in Panama with structures around the world.
Do you want to become an affluent person?
That`s your choice That`s what you wish in the long run!
I feel good, I began to get real money with the help of this company,
and I invite you to do the same. It`s all about how to choose a correct partner who uses your money in a right way - that`s the AimTrust!.
I make 2G daily, and my first deposit was 1 grand only!
It`s easy to start , just click this link http://ofawalinob.freecities.com/eteryb.html
and go! Let`s take this option together to become rich
might , perhaps curious to know how one can manage to receive high yields .
There is no initial capital needed You may commense earning with as small sum of money as 20-100 dollars.
AimTrust is what you thought of all the time
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.
Its head office is in Panama with structures around the world.
Do you want to become an affluent person?
That`s your choice That`s what you wish in the long run!
I feel good, I began to get real money with the help of this company,
and I invite you to do the same. It`s all about how to choose a correct partner who uses your money in a right way - that`s the AimTrust!.
I make 2G daily, and my first deposit was 1 grand only!
It`s easy to start , just click this link http://ofawalinob.freecities.com/eteryb.html
and go! Let`s take this option together to become rich
Good day !.
You may , probably curious to know how one can make real money .
There is no initial capital needed You may begin to receive yields with as small sum of money as 20-100 dollars.
AimTrust is what you thought of all the time
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.
Its head office is in Panama with structures everywhere: In USA, Canada, Cyprus.
Do you want to become an affluent person?
That`s your chance That`s what you wish in the long run!
I feel good, I began to get income with the help of this company,
and I invite you to do the same. It`s all about how to select a correct partner utilizes your money in a right way - that`s it!.
I take now up to 2G every day, and my first investment was 500 dollars only!
It`s easy to get involved , just click this link http://xeqipijeva.digitalzones.com/muvafuba.html
and lucky you`re! Let`s take this option together to become rich
You may , probably curious to know how one can make real money .
There is no initial capital needed You may begin to receive yields with as small sum of money as 20-100 dollars.
AimTrust is what you thought of all the time
The company incorporates an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.
Its head office is in Panama with structures everywhere: In USA, Canada, Cyprus.
Do you want to become an affluent person?
That`s your chance That`s what you wish in the long run!
I feel good, I began to get income with the help of this company,
and I invite you to do the same. It`s all about how to select a correct partner utilizes your money in a right way - that`s it!.
I take now up to 2G every day, and my first investment was 500 dollars only!
It`s easy to get involved , just click this link http://xeqipijeva.digitalzones.com/muvafuba.html
and lucky you`re! Let`s take this option together to become rich
Hello!
You may probably be very interested to know how one can manage to receive high yields on investments.
There is no initial capital needed.
You may commense earning with a sum that usually is spent
for daily food, that's 20-100 dollars.
I have been participating in one company's work for several years,
and I'm ready to let you know my secrets at my blog.
Please visit my pages and send me private message to get the info.
P.S. I make 1000-2000 per day now.
http://theinvestblog.com [url=http://theinvestblog.com]Online Investment Blog[/url]
You may probably be very interested to know how one can manage to receive high yields on investments.
There is no initial capital needed.
You may commense earning with a sum that usually is spent
for daily food, that's 20-100 dollars.
I have been participating in one company's work for several years,
and I'm ready to let you know my secrets at my blog.
Please visit my pages and send me private message to get the info.
P.S. I make 1000-2000 per day now.
http://theinvestblog.com [url=http://theinvestblog.com]Online Investment Blog[/url]
Today I got emails inside with excel files. But I couldn't read its. Then I used the Google and nothing found. I was disappeared... But unexpectedly called me up a friend and told about some other things. He advised me this software - recover information in Excel. As he said it solved his problem for a minute and for free. My friend was quite right,because tool helped me too fast and easy. I hazard the remark that the software is easy to use.
Post a Comment
Subscribe to Post Comments [Atom]
Links to this post:
<< Home
Subscribe to Posts [Atom]


