Welcome to City-Data.com Forum!
U.S. CitiesCity-Data Forum Index
Go Back   City-Data Forum > Blogs > KC_Sleuth
 [Register]
Please register to participate in our discussions with 2 million other members - it's free and quick! Some forums can only be seen by registered members. After you create your account, you'll be able to customize options and access all our 15,000 new posts/day with fewer ads.
Rate this Entry

Microsoft Excel Tricks/Tips Part 1

Posted 04-15-2013 at 11:14 PM by KC_Sleuth


Many of us have to use Microsoft Excel on a daily basis, and there are some excellent techniques one can pick up with a lot of Excel experience. As a numbers guy and data analysis guru, I think Excel is a very powerful program, and have decided to list some of the features I use on a daily basis in this blog. Maybe it can help some people.

1) The VLOOKUP function is one of the most useful formulas when working with multiple data sets. It allows you to introduce data points from one data set into another based on some record-identifying key. Here is an excellent tutorial on how to use the VLOOKUP function.

2) The IFERROR and ISERROR functions are excellent for clearly identifying situations in which a formula or function has resulted in an error or #N/A value. These functions can also be written so that it can dump into another formula in case of the error. For example, the formula

=IFERROR(VLOOKUP(Sheet1!A2,Sheet2!A:C,3,FALSE),"No Value")

would return a "No Value" string if the VLOOKUP function could not be completed. A great tutorial on IFERROR and ISERROR can be found here and here.

3) IF, AND, OR: IF statements in Excel are fairly simple formulas. The arguments in an IF statement are: =IF(condition,instructions if true,instructions if false). For instance, assume the condition you want to test is A3=2. If cell A3 = 2, and you write the IF statement to say =IF(A3=2,"Yes","No"), the formula will return "Yes" if A3=2, and "No" if not. You can nest IF statements - where you tell the formula to perform another function if the IF statement returns true and/or false. Here is a good tutorial on nesting IFs. The AND and OR statements can be used within an IF statement to define multiple conditions: either both occurring (AND) or one or the other occurring (OR). Here is a great rundown of nested IFs, AND, and OR statements.

4) Paste Special. If you have a column of numbers generated from formulas that you want to convert to fixed numbers, you can copy the entire column, right-click and select "Paste Special". A dialogue box will open, and you can select "Values". This also works with converting between multiple cell formats (date, number, text, etc.) The Paste Special-Multiply format can be used when you want to convert a field stored as text to numbers. simply type the number 1 in an empty cell, copy it, and select the field you want to convert. Then, right click and select "Paste Special" and "Multiply". This will convert the field to number format.

In my next blog entry, I will go over pivot tables, text-to-columns and data importing, date formulas, and statistical formulas.
Posted in Uncategorized
Views 1110 Comments 2
Total Comments 2

Comments

  1. Old Comment

    Funny Mirrors

    WOW
    This is amazing! You have to check this out!!
    Funny Mirrors
    permalink
    Posted 04-16-2013 at 06:12 PM by Egitty Egitty is offline
  2. Old Comment

    rebekahjappah@hotmail.com

    [email]rebekahjappah@hotmail.com[/email]
    Hello,
    I wish you best Compliment of the season,with hope that you are physically and healthy alright,l do believe that this mail will reach in good condition. My name is Miss Rebekah and i got your email in [url]www.city-data.com[/url] and i think we can make it together,please i would like you to contact me back through my email address and i will tell you more about myself and also send you my photo,as soon as you contact me back,
    Hopping for your lovely reply soonest,
    Rebekah.
    [email]rebekahjappah@hotmail.com[/email]
    permalink
    Posted 04-17-2013 at 11:19 AM by rebekahj23 rebekahj23 is offline
 

All times are GMT -6. The time now is 05:59 AM.

© 2005-2024, Advameg, Inc. · Please obey Forum Rules · Terms of Use and Privacy Policy · Bug Bounty

City-Data.com - Contact Us - Archive 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37 - Top