How To Learn Basic Excel to Advanced Excel

2

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

3

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.

 

 

 

 

Excel based works for freelancers

0

There are many excel works in the market for freelancers. Web scraping, Excel templates, Dashboards, Analytics, Data Analysis and VBA Macros. Here we are going to know on each and every work that freelancers are getting in the market. If any wants to become freelancer for Excel related works, this will be helped them a lot. Excel job would be very enjoyable to yourself you know excel very well. It has many features like conditional formatting, formulas, Array formulas and user defined formulas. Also it has many events like workbook, sheet, cell activate etc.

Web scraping:

This would be happened when user requires some data from web browser to excel. Most of the times web scraping related projects would be outsourced to freelancers. This work can be done by various programs like python etc. Even using Excel VBA programming we can scrap the data from web browser to Excel spreadsheet. In order to do this activity, manly we can use the” get element by id” related codes in the VBA programming to scrap the browser data.  VBA Programming also known as Excel Macro programming. Here in this web scraping model, due diligence is very important than programming skill.

Excel templates:

These are very useful to get important numbers from raw data. Here most of the times Sumif and Countif, Vlookup, Hlookup, Index, Match, if, nested if, and, or, not, indirect, and Text formulas can be used to fetch the important numbers from the raw data and project in the Excel template based on the given requirement. Also you can align the column headers to update the data on frequency basis. Excel templates very similar to Dashboards. The only difference between dashboard and template is data size would be lesser in the excel template comparing to dashboard. Excel templates can be used for simple tasks means one to three sheets of the data related numbers projection. As a freelancer, I am getting many excel template requirements to do in the market.

Analytics in Excel:

In the MS Excel, we have lot of analytical facilities. Now we have many software programs for analytics like SAS, R, MATLAB and so on. But previously we used to do in the MS Excel. Single Linear regression, multi linear regression, predictive analytics and statistical analytics like finding standard deviation, coefficient. Small companies are doing analytics in excel even now since they cannot afford the price of the other software program’s license cost.  Analytics base works can be under the data mining concept related projects for freelancers in the market.

Data analysis in the MS Excel:

90% of data analysis would be completed with graphs and charts in the ms excel. Remaining would be arranging the data. Hence we need to arrange the data in a specific format and then draw the graphs or charts to interpret the correct scenario of the data. We get many data analysis projects in the freelancer market.  These concepts are also under the data mining department.

Hope you understand all about excel based freelancer works, you can comment in the below, if you have any doubts about freelancer jobs.

 

 

 

 

 

How to create Excel Dashboard?

0

Excel dashboard designing is very easy, if you have good grip on data handling and business drivers of the particular activity. Once you prepare the dashboard, it can be used for a long time until that particular process get huge changes in the dimension of the data. It is going to be dashboard template for your valid information. Hence you can use the same template every time by clearing the old data and feeding new data into the dashboard template.  Excel dashboard is creation is one of major work in the MIS department

What is Excel dashboard?

Dashboard is a excel template that will have all the KPI (Key performance indicators) and CPI (Critical performance indicators) metrics along the visual graphs for all the important data points. Also raw data would be included in the dashboards by cleaning the data. Usually raw data sheets can be hided when you are circulating to the management.

 

Important points while building the excel dashboard

  • Template should have the home page or summary sheet which should be connected with all the display sheets as show in the below screen shot
Dashboard Home page
Dashboard Home page
  • Main sheet which is going to be circulated for management as screen shot will have all the important number which are related to KPI & CPI metrics. The same screen shot can be sent as an email copy and provide the link below for the dashboard shared location by hyperlink
  • Graphs and tables can be showcased in the rest all of the sheets for the same business drivers.
  • Main sheet related each and every KPI or CPI metric can be enlarged in the separate sheets by creating the visualized tables. If management wants look into particular business driver, they can go and check in line.
  • Based on requirement, we can write the macros also to automate the dashboard. This can be very helpful to clean the data.
  • There are many varieties of dashboards, like Accounts payable dashboard, Procure to Pay dashboards, Accounts receivable dashboard, finance related dashboard, banking related dashboard, marketing related dashboard and sales dashboard,
  • Usually dashboards have the huge data with lot of formulas. In such cases, you need to turn off the formula calculation to update the raw data in the template sheets. Else dashboard gets hang each and every time when you update the data. It keeps on calculate the formulas.
  • If it is daily dashboard, you can clear the data on daily basis or weekly basis in the sense weekly you can clear the data, similar way for month or quarter as well. In order to clear the data, it is better to write small VBA macro code as described in the above point. You can save some time to clear all the data from various sheets.
  • It would be awesome experience in creating the excel dashboard. It takes some time to create fully. Because once you start, you will keep on getting ideas to key the information in understanding manner for the management.
  • Once you create the template, it is very easy update the data into the dashboard based on your frequency either daily or weekly.

This is what all about the dashboard creation, hope you understand. And this was written based on my work experience.