Microsoft Excel Training Updates

If you are New to VBA Macro for Excel Automation, please follow step by step you can automation your Task.

Step First - Enable the Developer Tab (This Tab is only for Macro Development)

Goto -> Options ->Customize Rippon -> Enable "DEVELOPER" 

once you're done, you can find additional Tab name called Developer.

Step Two - Record Macro and Shortcut key Creation

In the Developer tab, you have an option called Record Macro. once you clicked that -> you will see another window to give Shortcut Key. (Conditions to follow while giving shortcut key - Shortcut key should not be a Keyboard Short cut keys because using keyboard shortcuts system won't work as expected).

Step Three - Record your Activities

Once we are done with the Shortcut Key Creation System, start to record your actions (Your actions should be only keyboard actions mouse actions will not record) 

Important Note: Only Excel Window Keyboard actions record, Keyboard actions can perform whatever we want

Step Four - Stop Recording

We need to stop the recording once we are done with our keyboard actions, in the Developer tab you have an option to STOP (Same button where we select the Record Macro)

Step Five - Run Macro (What we Recorded)

Once we are done with Everything, this is the Output or Final step Press Shortcut key we created while recording.

There is no direct option to add bullets in EXCEL. So we can create the bullets by the following steps:

1. Select the range where you want to apply the bullets.
2. go to formate cell ==> Custom Formate.
3. delete the General word and do following.
4. add five blank space.
5. add your symbol with Alt+7 then add one space.
6. type @ and press ok.

In the following, You would find lesser-known, unexplored yet Powerful Features of Filter.

Please refer to sample dataset below:

To do Data Analysis:
Step 1) Apply Filter on the dataset.
Step 2) In Sales Amount column, click on Filter drop-down.
Step 3) Take Your cursor to Number Filters. A-List of Options will open up.
As highlighted in the screenshot below:
i) Please click on Top 10 - to see list of Top 10 Performers OR Bottom 10 Performers
ii) Please click on Above Average - to see list of Above Average Performers
iii) Please click on Below Average - to see list of Above Average Performers

Likewise, in the Date column, if You click on Filter drop-down, and take the cursor to Date Filters, a List of Options will open up.
As highlighted in the screenshot below, the Options will allow You to do Data Analysis across different periods, in a Click.

Isn't this Cool. Data Analysis in Few Clicks!

Hi Friends, I am trying to bulid a macro for Web page automation where I am not getting how to write a code for that. In HTML source page there is no any id,class to get element, the tag is 

<a href="javascript:;" onclick="enableMandate('MjQ9Kio9ZW5hYmxl')">Enable</a> 

where if I click enable button the pop up message will come are you sure you need to enable I need to click yes. So, I have to do it for 1000 items, hence I am trying to build a VBA code.

Can any any one help me, it is emergency? read less

MS Excel is an extremely versatile and flexible tool. You will be able to perform simple tasks like data entry to running simple analysis to sophisticated data modelling. In short, it can do nothing or a lot, depending on the problem you are trying to solve.

Some of the critical things you should master in Excel are:

Data Scrubbing: Getting your dataset “ready” for analysis. This involves (but isn’t limited to) looking for missing info, identifying outliers (these can hurt a lot when computing averages), creating derived fields using TEXT(), MONTH(), YEAR() etc.

Data Analysis: Running moderately complex analysis (using pivot tables, VLOOKUP(), MATCH() & INDEX, SUMPRODUCT(), SUMIF(), COUNTIF() etc. Asking questions of the data and looking for answers.

Presentation: Once the data has been crunched and insights found, presenting the data in a clear, crisp and concise manner is critical. This is where you can demonstrate your prowess as an Excel power user.

VBA: Once you have a good handle on the above, you can look at developing macros/procedures in VBA to extend Excel functionality.

I have tried to capture the thought process behind using Excel and not necessarily be (overly) prescriptive. If you can nail the thought process, you will discover the nuances yourself. Being an Excel savant myself, I can tell you that there is a lot of fun you can have in Excel, problems abound all around you that you can use Excel to analyze. 

All the Best!

Following are some of the tips that you can use in MS Word.

A. The Microsoft office was be started in Safe mode. One of the most common uses is to disable a Word add-in that is behaving erratically. You can use Safe mode to suppress any customizations made to the program. One would use to take screenshots of the default word instead of the customized screen.

B. You can disable the start screen because it can be an annoyance for regular word users. You can disable it by Ribbon>File>Options>General.

C. You can use The Touch or Mouse mode for more Precise Taps, Large fingers and a touch-enabled screen, that is a recipe for a few disastrous taps. Word '13 offers a Touch or Mouse Toggle that makes working on touch-enabled screens far easier by enlarging the buttons and increasing the distance between them. You will be able to do that by clicking on the drop-down arrow on the right side of Quick Access Toolbar and select Touch or Mouse Mode from the list.

D. To remove double spaces; here is a quick and easy tip.

Select all text in the document. Open Find and Replace from the Navigation pane (View > Show > Navigation Pane) or press CTRL+H. You need to Place the cursor on the Find what field, then press the Spacebar twice to insert two spaces. Place your cursor in the Replace with a field, then press the Spacebar once.

Hope this helps!

You can use these tips while writing VBA Codes.

1. Use the application screen updating at the starting of the coded so that the user can see the final results directly instead of looking at the whole process of doing the activities which do not look good.

2. Using the Application.Display Alerts to stop showing any alerts in between running the macro. 

3. Using Application.Calculation to speed up your macro and calculate the values in the macro when needed.

4. Using Application.Status Bar which helps the user to know precisely at what process the macro is doing and how much time would it takes to complete the task.

5. Using Objects (Like-Worksheets, Range. With etc.) as much as you can, which helps you to speed up and improve the performance of the macro.

6. If you have a big macro code, then it is suggested to build in parts and always comment which helps to read the code and very quickly whenever changes required.

7. Declaring variable names which helps the coder to understand which variable is used for which purpose.

VBA in Excel can be used in several MS Excel-based Automation use cases. For example, if you want to capture sales orders from users Computer using Excel, then you can use Sales Order capture tools - Excel-based VBA application. 

Hope this helps.