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.
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.
Total Comments 2
Comments
-
Funny Mirrors
Posted 04-16-2013 at 06:12 PM by Egitty -
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]Posted 04-17-2013 at 11:19 AM by rebekahj23