CREATE FUNNEL CHART USING FORMULA

Following are the Steps:

1. Sort your data in Descending Order
2. Enter formula in C2  =REPT("|",B2/50) and drag it down
3.Choose the color that you want.

CHANGE TIME FORMAT FROM "HH:MM Hrs" TO "HH:MM:SS"

Formula in B2:
=TEXT(LEFT(A2,2)/24+MID(A2,4,2)/1440,"hh:mm:ss") and drag it down.

FIND AVERAGE OF VALUES EXCLUDING MAX AND MIN VALUE

Formula in A13:

=(SUM(A1:A12)-MIN(A1:A12)-MAX(A1:A12))/(COUNT(A1:A12)-2)

FIND OUT MAXIMUM OCCURED TEXT IN A RANGE

Array formula in B2:
=INDEX($A$2:$A$9,MATCH(MAX(COUNTIF($A$2:$A$9,$A$2:$A$9)),COUNTIF($A$2:$A$9,$A$2:$A$9),0)) with CSE

TO FIT THE SIZE OF USERFORM TO YOUR EXCEL WINDOW

Paste the below code in UserForm_Activate procedure and run. It will fit the size of userform to your excel window.


Private Sub UserForm_Activate()
ActiveWindow.WindowState = xlMinimized
With Application
    Me.Top = .Top
    Me.Left = .Left
    Me.Height = .Height
    Me.Width = .Width
End With
End Sub

DATA VALIDATION FOR RESTRICTING DUPLICATE VALUES

Select cell A7 and go to Data Tab, Click on Data Validation, select Custom in Allow field and enter formula in formula field as shown below (Click to enlarge)

Formula:  =ISNA(VLOOKUP(A7,A2:A6,1,FALSE))

ADD ITEMS TO ALL COMBOBOXES OF A USERFORM AT ONE TIME

This is required when you have a number of comboboxes on a userform and you need to add the same list of items to all comboboxes. Instead of adding items one by one to each combobox, you can just use the below code.

Paste this code on userform_intialize event and run.


Private Sub UserForm_Initialize()
Dim nme As Range
  Dim cntrl As Control
  Dim CB As ComboBox
  For Each cntrl In Me.Controls
    If TypeName(cntrl) = "ComboBox" Then
      If CB Is Nothing Then
        For Each nme In Sheet1.Range("MyName")
          cntrl.AddItem nme.Value
          Set CB = cntrl
        Next
      Else
        cntrl.List = CB.List
      End If
    End If
  Next
End Sub

Change highlighted part as per your requirement