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.

 

 

 

 

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.

 

 

 

How to write Excel VBA Macros

2

Excel VBA’s role is very important in the MS Excel application. Microsoft Excel  is main application in the MS Office. Because Office related tasks like MIS activates are getting done in the MS Excel. In order to automate this Excel tasks, VBA Macros are best choice. And we can control any application from the Excel VBA programming. Hence VBA programming has significant role in the MS Excel automations.  Usually when user is preparing some report and he is taking around one hour to do that which is not a judgmental task. Since it is copy paste work, you can automate with VBA Macro programming and save that one hour time. User has to spend time only on reviewing the report. This is what the best advantage of the Excel VBA Macros.

VBA Macros programming can be written with in the excel environment.  In order to see the VBA program environment, you have two ways. One is Alt + F11 short cut key from Excel workbook. Or you can click on view menu and then see the macro option then view option as shown in the below screen shot.

VBA Macro Environment
VBA Macro environment

Then either you can create new by giving the name or you can select the existing one, if you already written. Once you are in the VBA environment, you can see the coding, if you have already written. Else you can start writing the code in the white screen. Here there is an option called record macro. This is very useful option for the beginners. Sometimes when you are not familiar with VBA programming, this option can be used to see the exact code for particular desired thing that you want to do in the front end. This  is main feature of the excel macro programming.

Record Macro option:

This option works like a tape recorder. Just click the record macro option and start doing some activity in the Excel, you can see the pre written code in the VBA module of that excel workbook. Even intermediate users can use this option as a help to know, when one particular piece of code.  You can find the record macro option in the above screen shot.

Writing VBA Code:

When you press Alt +F11 option, you will be in the VBA Macro environment of that particular Excel workbook. Then you can insert module by clicking in the Insert menu. Once you done it, you need to start subroutine.

Example:

Adding module in the VBA Environment
Adding module in the VBA Environment

Within the below codes, we have to write the macro coding part.

Sub Testing

End sub

I am going to write small VBA Macro for you to understand yourself.

Adding two numbers program

Macro Sample program

Sub Testing

Dim I, j, k as integer

I=5

J=10

K= I + j

Range(“A1”).value = k

End sub

Similar way you need to write the vba code for any other tasks in the Excel. VBA programming is ocean to automate the things which would be available in excel. Almost every application we can control with VBA programming. All you need to do is adding the DLL of other application. Even in this programming language we can not automate the judgmental cases. This is only the drawback in this programming language. If we are effective programmer, we can just save the lot of FTEs ( Full Time Employees ) work. It would be converted into money. Sometimes If you are in six sigma projects, then we are eligible to take the green belt projects.

Hope you understand about the Excel VBA Macro the way how you can write the coding part within the Excel.

 

 

Excel VBA Freelancer

0

Now a days in the USA and Uk countries prefer to outsource the work for reducing the cost to their companies. Here Freelancer concept came into the picture. And it became wonderful opportunity to the people who wants to work from the home. Also freelancer can work at any time based on his interest where employees can only work in one particular point of time. In order to work as freelancer, we have many websites which would work on commission basis. Here i am going to provide some name from those. Fiverr.com, Upwork.com, Freelancer.com etc,,

Since my website is belongs to excel based, here i am going to brief you about the Excel VBA Freelancer tasks. First thing all excel related works can be done by this freelancer. Also before you give any work to Excel freelancer, you must need to discuss with him about your activity that you want to automate or formulate. Then excel vba freelancer checks the feasibility study on that activity and confirm you the timeline. And let you know in case if there any challenges to do that. Then as a buyer, you can negotiate that particular piece of part to do in a alternate manner. This is how it goes.

I am also an Excel VBA Freelancer and you can find my profile in the below mentioned link  https://www.fiverr.com/sivaramakris170 . Being as a excel vba freelancer, I got some relief from the daily basis job activities. In day to day employee life I did not have the freedom wherein I am getting lot free time and able to earn better that what I was earning as an employee. Hence, if any one has the outstanding skills in any aspect, you can take my help, in order to become as a freelancer. That would become an awesome step in your life.

As a excel vba freelancer, i am getting works like formulating the workbooks, automating the Excel reports using VBA Macros, in order to save the processing time of the user. Using the Excel VBA, we can do almost many things like communicating with other applications, fetching the data from websites to excel. If you are a Excel VBA freelancer, you must need to clarify the user, as what you are going to automate or formulate and it would be helpful to use that particular tool. Also need to support to the beginner users as they will have lot of confusions until they get habituated with your tool. Once you do 2 or 3 tools for the users and you get positive feedback from them. Then you will become very busy with offers. Feedback system or reviews are very important for the freelancer. Hence as a freelancer, you should be more specific on what you are going to do to the user and how it is going to helpful in future.

Hope you people understand that what exactly is freelancing job and how it is going to be worked out. You can comment in the below in case, if you have any questions. Also i will give you the referral link to enroll in the Fiverr website. If you are interested, you can signup and check you luck. http://www.fiverr.com/s2/2e6e2eefb0

 

 

 

 

 

Sumif Formula

0

Sumif formula has significant role in MS Excel. It is very useful to create the dashboard. Also it can be used with combination of formulas based on the requirement. When you need to sum the values based on the one particular criteria (value), you can use this Sumif formula. This can be used as an array formula, if necessary.

Syntax: =SUMIF(Range,Criteria,Sumrange)