Here I am going to teach you the way how you can use the selenium webdrivers in excel vba in order to scrape the websites information into excel spreadsheet as per user requirement.
Creation of named ranges are explained in deep as we have already seen in the vlookup tutorial.
Previously when we were using excel 2003 this named range was used frequently to automate the excel activities using Excel macros. In stead of ranges, we were using the named ranges as those fixed with particular range of cells, rows or columns. In order to create named range, we just need to select the required range of cells that you want to name, then go to cell address bar and give the desired name, once you give press enter tab. That’s it next if you select that range, you can see the given name for that range in the same cell address bar. You can check the below screen shot for your reference.
This is the simple method which we can use. we have another method for creating the named range. That option is available in the Formulas Menu -> Name Manager. You can click the name manger and select the desired sheet and range of cells in the respective text boxes which will be available in the Name Manger option, then you can give the desired name. You can check the below screen shots for your understanding purpose.
Once you get the above name manager option, you can click the new option or edit option(if you want to change any given name) to name the range of cells, rows or columns as per your requirement. Once you click the new or edit option in the name manger, you get the below option box
Hope you understand basic excel option named range. If you have suggestion, please comment in the below comment box to encourage us.
There is an option called Goal Seek to find the value of principle amount or number of years or rate of interest when tow values available among there 3 then we can find the 3rd value using this option. Usually this option very useful in Banking sector. Also when you situation to calculate the PNR/100.
This is one of excellent option in the excel to easy calculation process for interest related activities.
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.
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 Insert Menu:
Once you select either one of the methods in the above, you will get the below screen to link with other sources.
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.
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.
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.
Here you are going to learn about the advantages and disadvantages of VBA ( Visual Basic Applications )
- Removing the non value added things like copy paste work, repetitive works.
- Reducing the TAT ( Turn around time) for preparation of the report, template or dashboard
- 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.
- 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.
- Providing the FTE (Full time employee) reduction benefit for the organization in terms of saving money to organizations.
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.
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.