How To Use Data Validation in Excel

0

Here you are going to learn the way you can do the data validation in Excel. Data validation option can be used to validate the data in excel. Data in the sense there might be numbers or text based on the requirement. This is the one best features in the excel spreadsheet to restrict end users from committing errors by selecting incorrect data. Also this feature can be used to create many options to end user while updating statements or forms. Data validation has many options and varieties. I am going to give you some of examples to explain you in depth for all the options available in this feature.

Data Validation Option in Excel spreadsheet
Data Validation Option in Excel spreadsheet

How To Create Hyperlink in Excel

0

Creating hyperlink is an easy method where you can just go into particular cell and right clink then select the option called hyperlink option or you can just go to insert menu then select the Hyperlink option which is available in the menu bar. Once you select hyperlink option within these 2 options, you have to select location either within the document or outside the document.

Hyperlink using mouse right click:

Hyperlink using mouse right click in Excel
Hyperlink using mouse right click in Excel

 

Hyperlink using Insert Menu:

Hyperlink using Insert Menu
Hyperlink using Insert Menu

Once you select either one of the methods in the above, you will get the below screen to link with other sources.

Hyperlink screen to link with other source
Hyperlink screen to link with other source

In the above screen we need to choose correct option based on our requirement. If you need to link with web page or Place in the current Document options most of the times in the Excel.

How To Learn Basic Excel to Advanced Excel

1

Here i am going to explain you the way how you can learn all the excel formulas easily within less time from basic excel to advanced excel.

Learning excel is very easy. Once you learn, you must start using formulas in the live scenarios. That’s where your real learning part would be started. Mainly you have to focus on important formulas and options that are available in the Microsoft Excel.

As soon as you learn the important formulas and options then you can start developing the dashboards, reports and templates for daily, weekly, monthly, quarterly and yearly based on the team’s requirement.  This is how Excel learning procedure can be started from basic level to advanced level.

 

 

How To Connect With Database In Excel VBA

0

Here you are going to learn the way how you can connect with any database like MS Access application or Sql. When you speak about the connecting with database, you have to create connection string and adding suitable provider to open the connection of database. Once you know these two things, you can easily write the program to connect database. Then we have to write the query to execute respective task. Example “Select * From Emp”. Also we need to define Resultset and Connection objects at the initial stage. At the end of the program need to close the connection. This is the procedure we need to follow.

 

 

VBA Advantages and Disadvantages

0

Here you are going to learn about the advantages and disadvantages of VBA ( Visual Basic Applications )

VBA Advantages:

  1. Removing the non value added things like copy paste work, repetitive works.
  2. Reducing the TAT ( Turn around time) for preparation of the report, template or dashboard
  3. Reducing the formulas burden from the excel reports like when you give manual formulas in the excel, usually it hangs for a while to calculate formulas ( when you have more formulas or data in the sheet ). You can keep the formula in the coding part to overcome this issue.
  4. Can be removed the manual intervention to prepare the reports and improve the accuracy of the report. Because when you are preparing manually, accuracy would depending your mental condition whereas excel macro does not have that issue. It prepares based on the provided inputs.
  5. Providing the FTE (Full time employee) reduction benefit for the organization in terms of saving money to organizations.

 

7 QC Tools

0

Here we are going to learn about 7 Qc tools and their uses. & Qc tools are part of the Six sigma.

7QC Tools are very useful in reducing the issues, wastage, improving the quality etc.. In six sigma. Those are Pareto analysis, Histogram, Fish bone analysis ( Cause and affect diagram or 80 – 20 rule ), Bell Curve, Control charts, Checklists, Scatter plot. Six sigma concepts were first invented by Motorola company. Usually these concepts using in the industries  to reduce the wastage, while producing the products or service. Now after Motorola brought these tools to service industry in 1986. And first implemented and got the benefit by General Electric Company in 1995. As soon as General Electric saved much money by implementing this Six Sigma concepts, then many company started implementing the Six sigma concepts.

How To Create Pivot Table

0

Let’s discuss about the Pivot table and the way how we can create the pivot table in excel.

Pivot table creation process is very easy. We have to just select data which you need to pivot table then go to insert menu and select the pivot table option. Once you select then you get the empty table in the worksheet. Then you have to choose the rows, columns, data etc.. to show in the table.

 

How To Automate Powerpoint activity Using Excel VBA

0

Here you are going to learn the way how you can automate power Point activity using excel vba.

How To Do Web scraping Using Excel VBA

0

Here in this article you are going to learn the way how you can scrap the data from the browser and update in the excel spreadsheet.

In order to communicate with internet explorer,  you need to add the internet explorer type of object to Vba module. To add there are two types of procedures early binding and late binding. Once you define the object then you need call the specific url to start scrapping the data from the browser. Then we have to try to catch the getElementsbytagname, getElementsbyid, getElementsbyclass or any other element to pull the data. If you are able to catch the data, then you can paste the same data into excel sheet.

Outlook Automation Using Excel VBA

0

Excel VBA can control almost all applications. All we need to do is specific DLL file to be linked in the reference of specific VBA Module. When you want to automate Microsoft outlook related activities such as sending bulk emails to various people, getting alerts when specific email related mails reached to inbox, setting up bulk meetings at the same time, searching in specific folder of outlook for emails related content to fetch into excel spreadsheet and getting email body or subject lines into excel for multiple emails. Anything that need not judge in outlook can be automated using Excel VBA. Because Outlook also Microsoft Application. Hence it is very easier to control by Excel VBA.

VBA module related reference to select dll
VBA module related reference to select dll
DLL file adding
DLL file adding

As soon as you add outlook related DLL (Dynamic linked library) files in VBA module reference, you can start writing the code to automate the outlook related activity.  Here I will show you one small example to automate the outlook related activity.

There are two types of declarations you can use to communicate with Outlook Application from Excel VBA. First one is early binding and second one is late binding.

Early binding option to connect with Outlook from Excel VBA

In this option you will get the help while writing the code from VBA Module. It is bit easier to use this option because you can see the choices to call the various outlook related objects

 

Example code

 

Late binding option to communicate with Outlook from Excel VBA

This is very easy to use and difficult to get the help with in the VBA module environment. Here you cannot get any help to call the outlook objects. When you are expert in the programming, you will know all the objects, that time you can use this Late binding concept to work with outlook application automation.

 

Example Code

 

Open outlook application to use the above code from the Excel VBA Module.