6/11/2007

Microsoft Excel formulas

Following are the most asked questions of microsoft excel. I hope you'll find it worthwhile.

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.

19 comments:

Anonymous said...

"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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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]

Alex said...

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.

Anonymous said...

I was very pleased to find this great site. I wanted to thank
you for ones time just for this fantastic read!! I definitely appreciated every bit of it
and i also have you book marked to check out new information in your site.


my web-site :: password hacking

Anonymous said...

What's Taking place i am new to this, I stumbled upon this I've found It
absolutely helpful and it has aided me out loads.
I'm hoping to contribute & aid other users like its helped me. Great job.

Also visit my webpage funny animal pictures free

Anonymous said...

Everyone loves what you guys are up too. Such clever work and exposure!
Keep up the amazing works guys I've incorporated you guys to blogroll.

Review my site: Hack Into Twitter User Accounts To Get Data Back

Anonymous said...

What's up, of course this article is actually nice and I have learned lot of things from it on the topic of blogging. thanks.

Also visit my homepage Get Free Youtube Views

Anonymous said...

I like the valuable information you provide in your articles.
I'll bookmark your weblog and test again right here frequently. I am fairly sure I will learn many new stuff proper right here! Best of luck for the following!

My blog post :: free minecraft

Anonymous said...

What's up, I wish for to subscribe for this blog to get latest updates, so where can i do it please help out.

my page: dragonvale free download for android

Anonymous said...

Woah! I'm really digging the template/theme of this site. It's simple, yet
effective. A lot of times it's very difficult to get that "perfect balance" between user friendliness and appearance. I must say that you've done a great job with this.
Additionally, the blog loads extremely fast for me on Chrome.
Excellent Blog!

Also visit my weblog: forgot my password

Anonymous said...

When someone writes an post he/she retains the plan of a user in his/her mind that how a user can know it.

Thus that's why this paragraph is amazing. Thanks!

my web page - psn code generator

Anonymous said...

I have learn several excellent stuff here. Certainly worth bookmarking for revisiting.

I surprise how a lot attempt you place to make one of these
fantastic informative site.

My blog post rar password cracker

Anonymous said...

I'm really enjoying the design and layout of your website. It's a
very easy on the eyes which makes it much more enjoyable for me to come here and visit more often.
Did you hire out a designer to create your theme?
Exceptional work!

Have a look at my blog; bypass sharecash surveys

Anonymous said...

Hurrah! Finally I got a website from where I know how
to truly get useful facts concerning my study and knowledge.



Here is my page :: hacking tips

Anonymous said...

Have you ever considered about adding a little bit more than just your articles?
I mean, what you say is important and all. However think about if you added some great visuals or videos to give your posts more,
"pop"! Your content is excellent but with pics and videos,
this site could definitely be one of the
very best in its niche. Amazing blog!

my weblog; uncompress file

Anonymous said...

I just couldn't go away your site prior to suggesting that I actually loved the standard info a person supply for your visitors? Is gonna be back regularly in order to check up on new posts

Here is my page ... sharecash downloader 2012

Anonymous said...

An impressive share! I have just forwarded this onto a co-worker who had been doing a little homework on this.

And he actually ordered me dinner due to the
fact that I stumbled upon it for him... lol. So let me reword this.
... Thank YOU for the meal!! But yeah, thanx for spending the
time to talk about this matter here on your site.


my web site ... cityville wiki