Excel Tips - Tip 15 (Using IF Command )

Hello,

Here is the Fifteenth tip for you. Hope this help you.

Using IF Command is very easy and effective.

In many cases we have to give multiple IF Command for a single outcome (Best example for Multiple IF command is Income Tax Slab)

Keeping the month of Income tax return in mind, I am hereby using the same example for explaining Multiple IF Command. Hope it will help you in ease your work.

In case of any query feel free to contact me any time. If any of your friends is also interested for the said excel Tips, then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w=1

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Please do share your valuable feedback. Keep Sharing.

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 14 (Using IF Command with COUNTIF Formulae)

Hello,

After all the appreciation and positive feedback, I am pleased to continue my tips.

Here is the Fourteenth tip for you. Hope this help you.

Using IF Command with COUNTIF Formulae is very easy and effective.

Points to remember while using IF Command with COUNTIF()

1. IF Command is used for the outcome in True or False.

2. Gives command for “True” value and “False” Value without Double quote i.e. (“)

3. COUNTIF() counts values based on a single criteria and COUNTIFS() Count values based on a Multiple criteria.

4. Criteria could be a number, expression, cell reference, text, or a formula

5. Criteria which are text or mathematical/logical symbols (such as =, +,-, /,*) should be in double quotes i.e. (“”)

In case of any query feel free to contact me any time. If any of your friends is also interested for the said excel Tips, then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w=1

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Please do share your valuable feedback. Keep Sharing.

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

 +91-9045773456

Excel Tips - Tip 13 (Using COUNTIF)

Hello,

After all the appreciation and positive feedback, I am pleased to continue my tips.

Here is the Thirteenth tip for you. Hope this help you.

Using COUNTIF Formulae is very easy and effective.

Points to remember while using COUNTIF()

1. COUNTIF() counts values based on a single criteria and COUNTIFS() Count values based on a Multiple criteria.

2. Criteria could be a number, expression, cell reference, text, or a formula

3. Criteria which are text or mathematical/logical symbols (such as =, +,-, /,*) should be in double quotes i.e. (“”)

If any of your friends is also interested for the said excel Tips, then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w=1

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Please do share your valuable feedback. Keep Sharing.

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 12 (Using Vlookup)

Hello,

Hope my excel tips are helping you out.

Here is the Twelfth tip for you. Hope this help you.

Using “VLOOKUP” Formulae is very easy and effective and it is used very frequently in every industry.

Steps to use it:

1. Use =Lookup( in the cell where you want the desired outcome.

2. Select the range of that column against which you want the desired outcome. After selecting the column, press comma (,) [As shown in my table, selected the “Name” column]

3. Now select the table from where you want to pick the desired outcome. After selecting the column, press comma (,)[As shown in my table, selected from the “Actual Data” table]

4. Count the number of columns in between the desired outcome and type the same. [As shown in my “Actual Data” table, there are 4 columns in between “Name” and “English”]. After that, press comma (,)

5. Now use “F4” to freeze the formulae [As shown in table] , [the $ sign means that the formulae is freeze]

6. Now you can copy and paste the said formulae to maximum upto your desired need.

If any of your friends is also interested for the said excel Tips, then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w=1

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Please do share your valuable feedback. Keep Sharing.

Thanks & Regards,

CA Mohit Bansal

Excel enthusiast

+91-9045773456

Excel Tips - Tip 11 (How to Clean Text )

Hello,

Hope my excel tips are helping you out.

Here is the Eleventh tip for you. Hope this help you.

If ever we download or import any file in which the text found in improper format, then the Clean Syntax will help in the clearing the text in proper way.

What Does It Do?

This function removes any nonprintable characters from text.

For example, please check the images.

How to use it ?

=CLEAN(TextToBeCleaned)

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/alreadyresponded?c=0&w=1

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 10 (How to calculate exact Age)

Hello,

Hope my excel tips are helping you out.

Here is the Tenth tip for you. Hope this help you.

As this is the season of Filing Income tax return and depositing of taxes, we often have to find the age of the assessee to calculate the exact amount of tax as per Income Tax Act, 1961 .

How to Calculate Age with Formulae :

You can calculate a persons age based on their birthday and todays date

The calculation uses the DATEDIF() function..

Birth date :	01-Jan-60		Formulae Used
Years lived :	55	=DATEDIF(C8,TODAY(),"y")
and the months :	8	=DATEDIF(C8,TODAY(),"ym")
and the days :	7	=DATEDIF(C8,TODAY(),"md")

You can put this all together in one calculation, by using my earlier tip ‘using & Key’ and the formulae is

="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

And the outcome is 

Age is 55 Years, 8 Months and 7 Days

Note : The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000.

Hope the Tip is useful to you and the same is clear.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/alreadyresponded?c=0&w=1

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Please do share your valuable feedback. Keep Sharing.

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 9 (Creating Chart)

Hello,

Hope my excel tips are helping you out.

Here is the Ninth tip for you. Hope this help you.

If you want to create a chart for any table, then You can create a chart quickly without having to use the chart button on the toolbar. The Shortcut for the same is by pressing the function key F11 while inside a range of data.

Here are example for the table :

How to Use it :

Click anywhere inside the table above. Then press F11.

The Chart will automatically prepared as soon as you press F11.

Hope the Tip is useful to you and the same is clear.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/alreadyresponded?c=0&w=1

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Please do share your valuable feedback. Keep Sharing.

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456