Excel Tips - Tip 35 (Using Macros)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

Macros provide an ideal way to save time on predictable, repetitive tasks as well as standardize document formats – many times without having to write a single line of code.

What Does It Do ?

Macros are incredibly powerful and can do pretty much anything your imagination can conjure. As a (very) short list of functions you can do with a macro:

·         Apply style and formatting.

·         Manipulate data and text.

·         Communicate with data sources (database, text files, etc.).

·         Create entirely new documents.

·         Any combination, in any order, of any of the above.

How to use it ?

We have a sample of data as shown in figure below :

Now we need to create a macros for the repetitive command that we need to use.

To create a macro, go to View > Macros > Record Macro. As shown in the below image. From there we need to select Record Macros.

As we click on record macros a new dialog box will appear as shown in images.

As soon as you click OK, our Macros command start working.

Now we have to give command that we need to record it.

Examples of Command that we uses :

·         =SUM(B2:F2)

·         =AVERAGE(B2:F2)

·         =MAX(B2:F2)

·         =MIN(B2:F2)

You can also use other command like formatting ( Bold, Italics etc)

Once the above command used in the one cell, now use Drag and Drop option to copy all the formulas to all the desired cell.

Once we complete all the commands then we have to go to Macros again and click on Stop Recording.

Once the macros stop, then Congratulations – you have just created an Excel macro.

Now Select the all by pressing Ctrl+A and then delete the all text. Now save the file as macros enable as shown in image.

Now close the file. Now open the new file that we have just saved in our PC.

Enable the macros as shown in figures

Click on options and select Enable as shown in images

Now Copy paste the data as shown in figures 1 which is our base DATA file.

Now Run macros as shown in figures

The all command will automatically filled as per our command used while Recording Macros.

That’s how we can use Macros.

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/viewform?c=0&w&usp

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 34 (Using Lookup Formulae)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to find the pick up a particular item from a group of range , then we uses “ LOOKUP” Command.

What Does It Do ?

·         This function looks for a particular  information from a list, and then picks an item from a second range of cells.

·         The What To Look For should be a single item.

·         The Range To Look in can be either horizontal or vertical.

·         The Range To Pick From must have the same number of cells in it as the Range To Look in.

How to use it ?

''=LOOKUP(What To Look For ,Range To Look In ,Range To Pick From)

For example, please check the images.

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/viewform?c=0&w&usp

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 33 (Using Mround Formulae)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to round the number upward or downward to the nearest multiple, then we can used the same by using “MROUND” Command.

This function can be used to calculate the MCA Fees for incorporating a Company as per revised fees structure .

What Does It Do ?

·         This function rounds a number up or down to the nearest multiple specified by the user.

How to use it ?

'=MROUND(NumberToRound,MultipleToUse)

For example, please check the images.

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/viewform?c=0&w&usp

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 32 (Using net working days Formulae)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to calculate the number of working days between two dates. Then we can do the same by using command “NETWORKDAYS”

What Does It Do ?

·         This function will calculate the number of working days between two dates.

·         It will exclude weekends and any holidays.

·         Holidays : This is a list of dates which will be excluded from the calculation, such as Festivals and bank holidays

How to use it ?

'=NETWORKDAYS(StartDate,EndDate,Holidays)

For example, please check the images.

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/viewform?c=0&w&usp

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 31 (Using NOT Formulae)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to test one or more command to get the outcome, we use the NOT Command for the same. Also we can say this command as A REVERSE LOGIC

What Does It Do ?

·         This function performs a test to see if the test fails

·         If the test fails, the result is TRUE.

·         If the test is met, then the result is FALSE.

How to use it ?

'=NOT(TestToPerform)

For example, please check the images.

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/viewform?c=0&w&usp

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 30 (Using OR Formulae)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to test one or more command to get the outcome, we use the OR Command for the same.

What Does It Do ?

·         This function tests two or more conditions to see if any of them are true.

·         It can be used to test that at least one of a series of numbers meets certain conditions.

·         Normally the OR() function would be used in conjunction with a function such as =IF().

How to use it ?

'IF(OR(Test1,Test2),TRUE,FALSE)

For example, please check the images.

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/viewform?c=0&w&usp

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 29 (Using Quotient Formulae)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to find the exact Quotient of the given number, then we can use QUOTIENT Command. It is mostly used where we have to find the number of packets to be packed in the box.

What Does It Do ?

·         This function calculates the number of times a number can be divided by another number.

·         It ignores any remainder, only showing the whole number.

How to use it ?

'=QUOTIENT(NumberToBeDivided,Divisor)

For example, please check the images.

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/viewform?c=0&w&usp

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