Learn Excel to work faster

Let’s Gain the Excel Knowledge to Create Useful Tools, to Solve Problems by using Excel  functions, pivot tables, conditional formatting, and charts with clear examples.

AppsPro Excel Utilities
ASAP Excel Utilities
Pk's Utility Toolkit

IMPORTANT SHORT CUTS IN EXCEL

Excel Shortcuts with Alt

Alt+Enter                                        Start a new line in the same cell.

Alt+=                                                AutoSum

ALT+;                                             Select the visible cells in the current selection.

Alt or F10                                        Activate the menu

Alt+Down arrow                            Display Auto Complete list

Alt+’                                                 Format Style dialog box

Alt+F1                                             Insert Chart

Alt+F2                                             Save As

Alt+F4                                             Exit

Alt+F8                                             Macro dialog box

Alt+F11                                           Visual Basic Editor

ALT+;                                             Select the visible cells in the current selection.

Excel Shortcuts with Ctrl +

Shortcut key                                   Action

 Ctrl+A                                              Select All

Ctrl+B                                             Bold

Ctrl+C                                             Copy

Ctrl+D                                             Fill Down

Ctrl+E                                              Flash Fill

Ctrl+F                                              Find

Ctrl+G                                             Goto

Ctrl+H                                             Replace

Ctrl+I                                               Italic

Ctrl+K                                             Insert Hyperlink

Ctrl+N                                             New Workbook

Ctrl+O                                             Open

Ctrl+P                                              Print

Ctrl+R                                             Fill Right

Ctrl+S                                              Save

Ctrl+U                                             Underline

Ctrl+V                                             Paste

Ctrl W                                             Close

Ctrl+X                                             Cut

Ctrl+Y                                             Repeat

Ctrl+Z                                              Undo

Ctrl+F3                                            Define name

Ctrl+F4                                            Close

Ctrl+F5                                            XL, Restore window size

Ctrl+F6                                            Next workbook window

Ctrl+F7                                            Move window

Ctrl+F8                                            Resize window

Ctrl+F9                                            Minimize workbook

Ctrl+F10                                          Maximize or restore window

Ctrl+F11                                          Inset 4.0 Macro sheet

Ctrl+F12                                          File Open

Ctrl++                                              Insert

Ctrl+-                                               Delete

Ctrl+1                                              Format cells dialog box

Ctrl+2                                              Bold

Ctrl+3                                              Italic

Ctrl+4                                              Underline

 

Ctrl+5                                              Strike through

Ctrl+6                                              Show/Hide objects

Ctrl+7                                              Show/Hide Standard toolbar

Ctrl+8                                              Toggle Outline symbols

Ctrl+9                                              Hide rows

Ctrl+0                                              Hide columns

CTRL+/                                           Select the array containing the active cell.

CTRL+\                                           In a selected row, select the cells that don’t match the formula or static value in the active cell.

CTRL+[                                          Select all cells directly referenced by formulas in the selection.

 

CTRL+]                                          Select cells that contain formulas that directly reference the active cell.

Ctrl+Enter                                      Fill the selected cell range with the current entry.

Ctrl+Spacebar                                Select the entire column

Excel Shortcuts with Shift key

Shift                                                 Hold down shift for additional functions in Excel’s menu

Shift+F1                                          What’s This?

Shift+F2                                          Edit cell comment

Shift+F3                                          Paste function into formula

Shift+F4                                          Find Next

Shift+F5                                          Find

Shift+F6                                          Previous Pane

Shift+F8                                          Add to selection

Shift+F9                                          Calculate active worksheet

Shift+F10                                        Display shortcut menu

Shift+F11                                        New worksheet

Shift+F12                                        Save

Shift+Spacebar                               Select the entire row

SHIFT+BACKSPACE                  With multiple cells selected, select only the active cell.

 

Excel Shortcut with Ctrl+Shift+Alt

 

Ctrl+Alt+F9                                    Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

Ctrl+Alt+Shift+F9                          Rechecks dependent formulas and then calculates all cells in all open workbooks,including cells not marked as needing to be calculated.

Shift+Ctrl+F6                                 Previous workbook window

Ctrl+Shift+F3                                 Create name by using names of row and column labels

Ctrl+Shift+F6                                 Previous Window

Alt+Shift+F1                                   New worksheet

Alt+Shift+F2                                   Save

Ctrl+Shift+A                                   Insert argument names into formula

Ctrl+Shift+~                                    General format

Ctrl+Shift+!                                    Comma format

Ctrl+Shift+@                                  Time format

Ctrl+Shift+#                                    Date format

Ctrl+Shift+$                                    Currency format

Ctrl+Shift+%                                  Percent format

Ctrl+Shift+^                                   Exponential format

Ctrl+Shift+&                                  Place outline border around selected cells

Ctrl+Shift+_                                    Remove outline border

Ctrl+Shift+*                                    Select the current region around the active cell. In a PivotTable report, select the entire PivotTable report.

Ctrl+Shift+(                                    Unhide rows

Ctrl+Shift+)                                    Unhide columns

Shift+Ctrl+Tab                               In toolbar: previous toolbar

Shift+Ctrl+F                                   Font Drop Down List

Shift+Ctrl+F+F                              Font tab of Format Cell Dialog box

Shift+Ctrl+P                                   Point size Drop Down List

CTRL+SHIFT+O                          Select all cells that contain comments.

 

CTRL+SHIFT+|                             In a selected column, select the cells that don’t match the formula or static value in the active cell.

CTRL+SHIFT+{                            Select all cells directly or indirectly referenced by formulas in the selection.

CTRL+SHIFT+}                            Select cells that contain formulas that directly or indirectly reference the active cell.

SHIFT+BACKSPACE                  With multiple cells selected, select only the active cell.

CTRL+SHIFT+SPACEBAR       Selects the entire worksheet. If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet. When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet

 

Excel Shortcuts with Function key
 

F1                                                     Help

F2                                                     Edit

F3                                                     Paste Name

F4                                                     Repeat last action

F4                                                     While typing a formula, switch between absolute/relative refs

F5                                                     Goto

F6                                                     Next Pane

F7                                                     Spell check

F8                                                     Extend mode

F9                                                     Recalculate all workbooks

F10                                                   Activate Menubar

F11                                                   New Chart

F12                                                   Save As

IMPORTANT SETTINGS IN EXCEL

Copy an Excel Sheet with Page Setup to a New Book in Excel

1. Open both the Excel workbook containing your desired print settings and the workbook to which you want to copy the settings. Bring up the workbook, and then the worksheet, where your desired settings are located.
2. Right-click the name of the worksheet on the tab located just below the spreadsheet. Click “Move or Copy” from the list of options that appear.
3. Click the drop-down menu under “To book:,” and then click the workbook to which you want to copy the settings. Click “(move to end)” from the list of worksheets, and place a check mark next to “Create a copy” at the bottom of the window. Click “OK” and the second workbook appears on the screen, with a new copy of your original worksheet inserted into the collection of worksheets.
4. Click on the tab for the new copy of the worksheet, located just beneath the spreadsheet. Then click the “Page Layout” tab at the top of the screen.
5. Hold “Ctrl” and click on the tab for every other sheet in the workbook that you want to have the new print settings.
6. Click the small arrow located in the bottom right corner of the Page Setup area of the ribbon to open the Page Setup window. Click “OK” to transfer the print settings to all the selected sheets.
7. Click on the tab for any worksheet in the workbook, other than the one you copied over. Then right-click on the tab for the worksheet you copied over and click “Delete.”
 

Copy Excel page setup settings from one sheet to another

Transferring the settings from one sheet to the another, as follows:

  1. Select the existing sheet, the source sheet, that contains the settings you want to transfer.
  2. Select the target sheet(s) — the sheets you want to update —  by pressing [Ctrl] and clicking each sheet tab.
  3. Select Page Setup from the File menu and click OK.
Excel Shortcuts with Shift key

Shift                                                 Hold down shift for additional functions in Excel’s menu

Shift+F1                                          What’s This?

Shift+F2                                          Edit cell comment

Shift+F3                                          Paste function into formula

Shift+F4                                          Find Next

Shift+F5                                          Find

Shift+F6                                          Previous Pane

Shift+F8                                          Add to selection

Shift+F9                                          Calculate active worksheet

Shift+F10                                        Display shortcut menu

Shift+F11                                        New worksheet

Shift+F12                                        Save

Shift+Spacebar                               Select the entire row

SHIFT+BACKSPACE                  With multiple cells selected, select only the active cell.

 

Excel Shortcut with Ctrl+Shift+Alt

 

Ctrl+Alt+F9                                    Calculate all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

Ctrl+Alt+Shift+F9                          Rechecks dependent formulas and then calculates all cells in all open workbooks,including cells not marked as needing to be calculated.

Shift+Ctrl+F6                                 Previous workbook window

Ctrl+Shift+F3                                 Create name by using names of row and column labels

Ctrl+Shift+F6                                 Previous Window

Alt+Shift+F1                                   New worksheet

Alt+Shift+F2                                   Save

Ctrl+Shift+A                                   Insert argument names into formula

Ctrl+Shift+~                                    General format

Ctrl+Shift+!                                    Comma format

Ctrl+Shift+@                                  Time format

Ctrl+Shift+#                                    Date format

Ctrl+Shift+$                                    Currency format

Ctrl+Shift+%                                  Percent format

Ctrl+Shift+^                                   Exponential format

Ctrl+Shift+&                                  Place outline border around selected cells

Ctrl+Shift+_                                    Remove outline border

Ctrl+Shift+*                                    Select the current region around the active cell. In a PivotTable report, select the entire PivotTable report.

Ctrl+Shift+(                                    Unhide rows

Ctrl+Shift+)                                    Unhide columns

Shift+Ctrl+Tab                               In toolbar: previous toolbar

Shift+Ctrl+F                                   Font Drop Down List

Shift+Ctrl+F+F                              Font tab of Format Cell Dialog box

Shift+Ctrl+P                                   Point size Drop Down List

CTRL+SHIFT+O                          Select all cells that contain comments.

 

CTRL+SHIFT+|                             In a selected column, select the cells that don’t match the formula or static value in the active cell.

CTRL+SHIFT+{                            Select all cells directly or indirectly referenced by formulas in the selection.

CTRL+SHIFT+}                            Select cells that contain formulas that directly or indirectly reference the active cell.

SHIFT+BACKSPACE                  With multiple cells selected, select only the active cell.

CTRL+SHIFT+SPACEBAR       Selects the entire worksheet. If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet. When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet

 

Excel Shortcuts with Function key
 

F1                                                     Help

F2                                                     Edit

F3                                                     Paste Name

F4                                                     Repeat last action

F4                                                     While typing a formula, switch between absolute/relative refs

F5                                                     Goto

F6                                                     Next Pane

F7                                                     Spell check

F8                                                     Extend mode

F9                                                     Recalculate all workbooks

F10                                                   Activate Menubar

F11                                                   New Chart

F12                                                   Save As

IMPORTANT FUNCTIONS & FORMULAS

 
Counting of members in a Family using COUNTIF?
Purpose
To count the members in a Family
Syntax

=COUNTIF($B$2:$B$4037,B2)

Here B column indicates family ID and 4037 last row number

How may members of a Family submitted Aadhar COUNTIFS?
Purpose
To count how may members in a Family & among them how many submitted Aadhar
Syntax

=COUNTIFS($B$2:$B$4038,B3,$D$2:$D$4038,”*”)

Here Column is Family ID and Column D Aadhar Number

Excel PROPER Function
 
Purpose
Capitalize the first letter in each word
Return value
Text in proper case.
Syntax
=PROPER (text)

ram is a good boy —> Ram Is A Good Boy

Subtraction cells only if both have a value in Excel else it will be Blank

subtraction two cells in excel but only if both cells have a value. More specifically in my case the formula in cell C2 is currently

=B2-A2 but I only want this to be calculated when B2 has a value or A2 has a value. I tried this formula

=IF(AND(B2<>"",A2<>""),B2-A2," ")

You can use the ISBLANK function:

=IF(OR(ISBLANK(A2),ISBLANK(B2)),"",B2-A2)

Use of Wildcards in Excel

Excel provides three “wildcards” for matching text in formulas:

Character Name Purpose
* Asterisk Match zero or more characters
? Question mark Match any one character
~ Tilde Match literal wildcard

Wildcards can be used alone or combined to get a variety of matching behaviors:

Usage Behavior Will match
? Any one character “A”, “5”, “c”, “z”, etc.
?? Any two characters “AA”, “AZ”,”7q” ,”zz”, etc.
??? Any three characters “Jet”, “AAA”, “ccc”, etc.
* Any characters “apple”, “APPLE”, “A100”, etc.
*th Ends in “th” “bath”, “fourth”, etc.
c* Starts with “c” “Cat”, “CAB”, “cindy”, “candy”, etc.
?* At least one character “a”, “b”, “ab”, “ABCD”, etc.
???-?? 5 characters with hypen “ABC-99″,”100-ZT”, etc.
*~? Ends in question mark “Hello?”, “Anybody home?”, etc.
*xyz* Contains “xyz” “code is XYZ”, “100-XYZ”, “XyZ90”, etc.

Here are a few examples of using wildcards for criteria in the COUNTIFS function.

=COUNTIFS(A1:A100,"*red*") // count cells that contain "red"
=COUNTIFS(A1:A100, "www*") // count cells starting with "www"
=COUNTIFS(A1:A100,"?????") // count cells with 5 characters
First Date and Last Date of a month

The Excel DATEVALUE function converts a date represented as text into a proper Excel date:

Purpose Syntax Return value
1st date of a month =DATEVALUE (“1″&month name cell&Year cell) Excel code of 1st date of the month of the year
Last date of the Month =EOMONTH(1st date of the month cell,0) Excel code of last date of the month
List of all dates of a Month =if(1st date cell < last date cell, 1st date cell+1,””) Next date of 1st date

 

 

Excel flip horizontal to vertical and vice versa
Excel TRANSPOSE Function
Purpose
Flip the orientation of a range of cells
Return value
An array in a new orientation.
Syntax
=TRANSPOSE (array)
Enter the TRANSPOSE function as an array formula that contains same number of cells as array, using Control + Shift + Enter.
Purpose Syntax Return value
     
     
     

 

 

Excel TRIM Function
Purpose 
Remove extra spaces from text
Return value 
Text with extra spaces removed.
Syntax 
=TRIM (text)

Clean up raw Stu-burn data in Excel

How to clean up raw data for processing in Excel. if you have multiple spaces in between numbers you can easily clean it through demonstrated formulas which are stated below.
Formula 1: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),””)))
Formula 2: =SUBSTITUTE(A2,” “,””)
To get Current Date and Present Time
Purpose 
To get current Date & Time
Return value 
 
Syntax 
=Today()
To get Present Date & Time
=now()
To get current time

=TEXT(NOW(), “hh:mm:ss AM/PM”)

 

IMPORTANT EXCEL VBA CODES

Excel VBA Code to convert Numbers to Words International format without Currency

There are no features or formulas can help you to solve this task directly in Excel, but you can create a User Defined Function to finish it. Please do with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications Window.

2. Click Insert > Module, and paste the following code in the Module Window.

3. Save and close this code and go back the worksheet, in a blank cell, enter this formula =NumberstoWords(A2)( A2 is the cell you want to convert the number to English word), see screenshot:

4. Then press Enter key, and select cell C2 then drag the fill handle to the range that you want to contain this formula. All the numeric values have been converted their corresponding English words.

 
Function NumberstoWords(ByVal pNumber)
'Updateby20140220
Dim Dollars
arr = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
pNumber = Trim(Str(pNumber))
xDecimal = InStr(pNumber, ".")
If xDecimal > 0 Then
pNumber = Trim(Left(pNumber, xDecimal - 1))
End If
xIndex = 1
Do While pNumber <> ""
xHundred = ""
xValue = Right(pNumber, 3)
If Val(xValue) <> 0 Then
xValue = Right("000" & xValue, 3)
If Mid(xValue, 1, 1) <> "0" Then
xHundred = GetDigit(Mid(xValue, 1, 1)) & " Hundred "
End If
If Mid(xValue, 2, 1) <> "0" Then
xHundred = xHundred & GetTens(Mid(xValue, 2))
Else
xHundred = xHundred & GetDigit(Mid(xValue, 3))
End If
End If
If xHundred <> "" Then
Dollars = xHundred & arr(xIndex) & Dollars
End If
If Len(pNumber) > 3 Then
pNumber = Left(pNumber, Len(pNumber) - 3)
Else
pNumber = ""
End If
xIndex = xIndex + 1
Loop
NumberstoWords = Dollars
End Function
Function GetTens(pTens)
Dim Result As String
Result = ""
If Val(Left(pTens, 1)) = 1 Then
Select Case Val(pTens)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(pTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(pTens, 1))
End If
GetTens = Result
End Function
Function GetDigit(pDigit)
Select Case Val(pDigit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
delete the blank columns from the data, follow below given steps and code
  • Open the VBE page to press the key Alt+F11
  • Insert the module to write the code
  • Write the below mentioned code:
 
Sub Delete_Columns()

Dim C As Integer

C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

Do Until C = 0

If WorksheetFunction.CountA(Columns(C)) = 0 Then

Columns(C).Delete

End If

C = C - 1

Loop

End Sub
delete the blank rows from the data, follow below given steps and code

  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.
      
    Test the code:
     

    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select DeleteBlankRows
    Click Run
     

 
 
Sub DeleteBlankRows() 
    Dim Rw As Long, RwCnt As Long, Rng As Range 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
On Error GoTo Exits: 
     
    If Selection.Rows.Count > 1 Then 
        Set Rng = Selection 
    Else 
        Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row())) 
    End If 
    RwCnt = 0 
    For Rw = Rng.Rows.Count To 1 Step -1 
        If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then 
            Rng.Rows(Rw).EntireRow.Delete 
            RwCnt = RwCnt + 1 
        End If 
    Next Rw 
     
Exits: 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
     
End Sub 
 
Sub DeleteBlankColumns() 
    Dim Col As Long, ColCnt As Long, Rng As Range 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
     
On Error GoTo Exits: 
     
    If Selection.Columns.Count > 1 Then 
        Set Rng = Selection 
    Else 
        Set Rng = Range(Columns(1), Columns(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column())) 
    End If 
    ColCnt = 0 
    For Col = Rng.Columns.Count To 1 Step -1 
        If Application.WorksheetFunction.CountA(Rng.Columns(Col).EntireColumn) = 0 Then 
            Rng.Columns(Col).EntireColumn.Delete 
            ColCnt = ColCnt + 1 
        End If 
    Next Col 
     
Exits: 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
     
End Sub

MY  DAILY USED EXCEL VBA CODES

Excel VBA Code to copy the entire data of a sheet and paste the same in a new sheet of RC wise FPL

There are no features or formulas can help you to solve this task directly in Excel, but you can create a User Defined Function to finish it. Please do with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications Window.

2. Click Insert > Module, and paste the following code in the Module Window.

 
Sub New_Sheet()
‘
‘ New_Sheet Macro
‘
‘ Keyboard Shortcut: Ctrl+Shift+Q
‘
Range(“A12:AD12”).Select

Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-16
End Sub

delete the blank columns from the data, follow below given steps and code
  • Open the VBE page to press the key Alt+F11
  • Insert the module to write the code
  • Write the below mentioned code:
 
Sub Delete_Columns()

Dim C As Integer

C = ActiveSheet.Cells.SpecialCells(xlLastCell).Column

Do Until C = 0

If WorksheetFunction.CountA(Columns(C)) = 0 Then

Columns(C).Delete

End If

C = C - 1

Loop

End Sub
Member Count Aadhar Count and Seeding Percentage

  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.
      
    Test the code:
     

    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select aadhar_Count
    Click Run
     

 
 
Sub Aadhar_Count()
'
' Aadhar_Count Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    Range("K2").Select
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Range("K2:M2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Members"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Aadhar"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Seeding %"
    Range("P10:P11").Select
    Range("P11").Activate
    Columns("K:M").Select
    Selection.ColumnWidth = 20.11
    Range("K2").Select
    ActiveWindow.SmallScroll Down:=-20
    ActiveCell.FormulaR1C1 = "=COUNTIF(R1C2:R20000C2,RC[-9])"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(R1C2:R20000C2,RC[-10],R1C10:R20000C10,""*"")"
    Range("L3").Select
    ActiveWindow.SmallScroll Down:=-16
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=ROUND((RC[-1]/RC[-2]%),2)"
    Range("M3").Select
    ActiveWindow.SmallScroll Down:=-12
    Range("M2").Select
    ActiveCell.FormulaR1C1 = "=ROUND((RC[-1]/RC[-2]%),2)"
    Range("M2").Select
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0.00"
    Range("K2").Select
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 2).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("L2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 3).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("M2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 4).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Columns("K:M").Select
    Selection.ColumnWidth = 7.11
    Range("O5").Select
    ActiveWindow.SmallScroll Down:=-16
    Cells.Replace What:="*/ ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Address"
    Range("N7").Select
    ActiveWindow.SmallScroll Down:=-20
    Columns("C:E").Select
    Selection.ColumnWidth = 12.89
    Selection.ColumnWidth = 15.11
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("D9").Select
    ActiveWindow.SmallScroll Down:=-28
    Columns("J:J").ColumnWidth = 11.78
    ActiveWindow.SmallScroll Down:=-136
    Range("K2:M2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N11").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "AAY ?"
    Range("N4").Select
    ActiveWindow.SmallScroll Down:=-52
End Sub

Member Count Aadhar Count and Seeding Percentage at member ID
  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.

    Test the code:

    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select aadhar_Count
    Click Run 

 

Sub Count_Member_Aadhar()
‘
‘ Member_Aadhar_Count Macro
‘When new column Beneficiary ID found
‘
‘
Application.ScreenUpdating = False
Range(“L2:N2”).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range(“C1”).FormulaR1C1 = “Ben ID”
Range(“I1”).FormulaR1C1 = “AAY ?”
Range(“J1”).FormulaR1C1 = “Address”
Range(“L1”).FormulaR1C1 = “Members”
Range(“M1”).FormulaR1C1 = “Aadhar”
Range(“N1”).FormulaR1C1 = “Seeding %”
Range(“L2”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(R1C2:R20000C2,RC[-10])”
Range(“M2”).Select
ActiveCell.FormulaR1C1 = _
“=COUNTIFS(R1C2:R20000C2,RC[-11],R1C11:R20000C11,””*””)”
Range(“N2”).Select
ActiveCell.FormulaR1C1 = “=ROUND((RC[-1]/RC[-2]%),2)”
Range(“N2”).NumberFormat = “0.00”
Range(“L2”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“H2”).End(xlDown).Select
ActiveCell.Offset(0, 4).Range(“A1”).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range(“M2”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“H2”).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Range(“A1”).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range(“N2”).Select
Application.CutCopyMode = False
Selection.Copy
Range(“H2”).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 6).Range(“A1”).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Columns(“D:F”).Select
Selection.ColumnWidth = 15.11
Columns(“L:N”).Select
Selection.ColumnWidth = 7.11
Columns(“K:K”).ColumnWidth = 11.78
Cells.Replace What:=”*/ “, Replacement:=””, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Columns(“A:A”).Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range(“L2:N2”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range(“N4”).Select
Application.ScreenUpdating = True

End Sub

Page Setup after member and Aadhar Count
  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.

    Test the code:
    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select aadhar_Count
    Click Run

 

Sub Page_Setup()
‘
‘ Page_Setup Macro
‘
‘ Keyboard Shortcut: Ctrl+Shift+O
‘
Application.ScreenUpdating = False
Dim row As Integer, N As Integer
N = Cells(Rows.Count, “K”).End(xlUp).row
For row = N To 2 Step -1
If Not IsEmpty(Range(“K” & row)) Then Range(“A” & row).EntireRow.Delete
Next row
Range(“A2”).Select
ActiveCell.FormulaR1C1 = “1”
Range(“A3”).Select
ActiveCell.FormulaR1C1 = “2”
Range(“A2:A3”).Select
Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
Columns(“N:N”).Select
Selection.EntireColumn.Hidden = True
Range(“A1”).Select
ActiveCell.FormulaR1C1 = “S N”
Range(“B1”).Select
ActiveCell.FormulaR1C1 = “F N”
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “Spouse”
Columns(“G:G”).Select
Selection.Delete Shift:=xlToLeft
Columns(“H:H”).Select
Selection.Delete Shift:=xlToLeft
Columns(“I:I”).Select
Selection.Cut
Columns(“M:M”).Select
Selection.Insert Shift:=xlToRight
Range(“I1”).Select
ActiveCell.FormulaR1C1 = “M”
Range(“J1”).Select
ActiveCell.FormulaR1C1 = “A”
Range(“K1”).Select
ActiveCell.FormulaR1C1 = “%”
Range(“L1”).Select
ActiveCell.FormulaR1C1 = “Remarks”

With Selection
Rows(“2:2”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RowHeight = 24
End With
Columns(“A:A”).ColumnWidth = 3.67
Columns(“B:B”).ColumnWidth = 3.67
Columns(“C:C”).ColumnWidth = 6.89
Columns(“D:D”).ColumnWidth = 15.89
Columns(“E:E”).ColumnWidth = 9.44
Columns(“F:F”).ColumnWidth = 14.89
Columns(“G:G”).ColumnWidth = 5.89
Columns(“H:H”).ColumnWidth = 10.89
Columns(“I:I”).ColumnWidth = 2.67
Columns(“J:J”).ColumnWidth = 2.67
Columns(“K:K”).ColumnWidth = 0#
Columns(“L:L”).ColumnWidth = 14
Rows(“1:1”).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range(“A1:L1”).Select
With Selection.Font
.Name = “Arial”
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Merge
Rows(“1:1”).RowHeight = 21.8
Application.CutCopyMode = False
With ActiveSheet.PageSetup
.PrintTitleRows = “$1:$2”
.PrintTitleColumns = “”
End With
ActiveSheet.PageSetup.PrintArea = “”
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = “”
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “Page &P of &N”
.RightFooter = “”
.LeftMargin = Application.InchesToPoints(0.078)
.RightMargin = Application.InchesToPoints(0.078)
.TopMargin = Application.InchesToPoints(0.078)
.BottomMargin = Application.InchesToPoints(0.47)
.HeaderMargin = Application.InchesToPoints(0.27)
.FooterMargin = Application.InchesToPoints(0.078)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
End With
Application.PrintCommunication = True
Range(“L3”).Select
Application.ScreenUpdating = True
End Sub

VBA code to Delete Matched rows Blank rows and Page setup

  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.
      
    Test the code:
     

    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select Delete_Matched
    Click Run
     

Sub Delete_Matched()
‘
‘ Delete Macro
With ActiveSheet
.AutoFilterMode = False
With Range(“K1”, Range(“K” & Rows.Count).End(xlUp))
.AutoFilter 1, “Matched”
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
Columns(“K:K”).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

Range(“L2:M2”).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range(“K2”).Select
Cells.Replace What:=”*/ “, Replacement:=””, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:= _
“Unmatched(100-Due to Personal Identity information did not match)”, _
Replacement:=”100-Name”, LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Cells.Replace What:=”Unmatched(998-Invalid Aadhar Number)”, Replacement:= _
“998-Invalid”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=”Unmatched(998-Invalid Aadhaar Number)”, Replacement:= _
“998-Invalid”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Cells.Replace What:=”Unmatched(997-Aadhaar Suspended)”, Replacement:= _
“997-Suspend”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Range(“K1”).Select
ActiveCell.FormulaR1C1 = “Status”
Range(“I1”).Select
ActiveCell.FormulaR1C1 = “Address”
Columns(“M:M”).Select
Selection.Delete Shift:=xlToLeft
Range(“L1”).Select
ActiveCell.FormulaR1C1 = “Remarks”
Columns(“A:A”).Select
Range(“A2”).Select
ActiveCell.FormulaR1C1 = “1”
Range(“A3”).Select
ActiveCell.FormulaR1C1 = “2”
Range(“A2:A3”).Select

Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown))
Range(“B3”).Select

ActiveWindow.SmallScroll Down:=-4
Columns(“F:F”).Select
Selection.Delete Shift:=xlToLeft
Columns(“G:G”).Select
Selection.Delete Shift:=xlToLeft
Range(“J5”).Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = “”
.PrintTitleColumns = “”
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = “”
Application.PrintCommunication = False
Application.PrintCommunication = True

Range(“H1”).Select
ActiveCell.FormulaR1C1 = “Aadhaar”
Range(“J3”).Select

ActiveWindow.SmallScroll Down:=-4
Rows(“1:1”).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range(“A1:J1”).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Rows(“1:1”).RowHeight = 21
With Selection.Font
.Name = “Arial”
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range(“A1:J1”).Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = “$1:$2”
.PrintTitleColumns = “”
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = “”
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = “”
.CenterHeader = “”
.RightHeader = “”
.LeftFooter = “”
.CenterFooter = “Page &P of &N”
.RightFooter = “”
.LeftMargin = Application.InchesToPoints(0.078740157480315)
.RightMargin = Application.InchesToPoints(0.078740157480315)
.TopMargin = Application.InchesToPoints(0.078740157480315)
.BottomMargin = Application.InchesToPoints(0.511811023622047)
.HeaderMargin = Application.InchesToPoints(0.275590551181102)
.FooterMargin = Application.InchesToPoints(0.236220472440945)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = “”
.EvenPage.CenterHeader.Text = “”
.EvenPage.RightHeader.Text = “”
.EvenPage.LeftFooter.Text = “”
.EvenPage.CenterFooter.Text = “”
.EvenPage.RightFooter.Text = “”
.FirstPage.LeftHeader.Text = “”
.FirstPage.CenterHeader.Text = “”
.FirstPage.RightHeader.Text = “”
.FirstPage.LeftFooter.Text = “”
.FirstPage.CenterFooter.Text = “”
.FirstPage.RightFooter.Text = “”
End With
Application.PrintCommunication = True
Range(“B2”).Select
ActiveCell.FormulaR1C1 = “F N”
Range(“A2”).Select
ActiveCell.FormulaR1C1 = “S N”
Range(“C4”).Select
Columns(“A:A”).ColumnWidth = 4.33
Columns(“B:B”).ColumnWidth = 4.44
Columns(“C:C”).ColumnWidth = 16.67
Columns(“D:D”).ColumnWidth = 10.11
Columns(“E:E”).ColumnWidth = 11.78
Columns(“F:F”).ColumnWidth = 6.56
Columns(“G:G”).ColumnWidth = 11.44
Columns(“H:H”).ColumnWidth = 10.44
Columns(“J:J”).ColumnWidth = 8.67

Rows(“3:3”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RowHeight = 15
Rows(“3:8677”).EntireRow.AutoFit
Range(“J4”).Select
End Sub

VBA code to Convert all sheets to one PDF

  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.
      
    Test the code:
     

    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select Delete_Matched
    Click Run
     

Sub Convert_to_one_PDF()
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=”C:\Users\Desktop\Allsheets.pdf”, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
VBA code to Remove Special Character

  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.
      
    Test the code:
     

    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select Delete_Matched
    Click Run
     

Function RemoveSPChar(allc as string)
dim i as integer
for i=1 to len(allc)
CurChar = Asc(Mid(allc,i,1))
Numeric =Mid(allc,1,1)
If CurChar>=65 And CurChar<=90 or CurChar >=97 And CurChar <=122 Or Numeric >=0 And Numeric <=9 Then
NewName = NewName + Mid(allc,i,1)
End If
Next
RemoveSpChar=NewName
End Function
VBA code to Split Names using formula

  • How to use:
    Copy above code.
    In Excel press Alt + F11 to enter the VBE.
    Press Ctrl + R to show the Project Explorer.
    Right-click desired file on left (in bold).
    Choose Insert -> Module.
    Paste code into the right pane.
    Press Alt + Q to close the VBE.
    Save workbook before any other changes.
      
    Test the code:
     

    Enter some data in random locations on your spreadsheet
    Press Alt + F8 to open the macro dialog box.
    Select Delete_Matched
    Click Run
     

‘Function for FIRST NAME

Function firstname(allc As String)
Dim i As Integer
For i = 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
GoTo ENDLOOP
Else
NEWTEXT = NEWTEXT & Mid(allc, i, 1)
End If
Next i
ENDLOOP:
firstname = NEWTEXT

End Function

‘Function for MIDDLE NAME

Function midname(allc As String)
Dim i As Integer
For i = 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then

SP_POS = Application.WorksheetFunction.Find(” “, allc, 1)

GoTo ENDLOOP
Else
End If
Next i
ENDLOOP:
For i = SP_POS + 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
GoTo ENDLOOP1
Else
NEWTEXT = NEWTEXT & Mid(allc, i, 1)
End If
Next i

ENDLOOP1:
midname = NEWTEXT
End Function

‘Funciton for LAST NAME

Function lastname(allc As String)
Dim i As Integer
For i = 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
SP_POS = Application.WorksheetFunction.Find(” “, allc, 1)
GoTo ENDLOOP
Else
End If
Next i
ENDLOOP:
For i = SP_POS + 1 To Len(allc)
c_char = Mid(allc, i, 1)
If Asc(c_char) = 32 Then
sp_pos2 = Application.WorksheetFunction.Find(” “, allc, SP_POS + 1)
GoTo ENDLOOP1
Else
End If
Next i
ENDLOOP1:
For i = sp_pos2 + 1 To Len(allc)
NEWTEXT = NEWTEXT & Mid(allc, i, 1)
Next i
lastname = NEWTEXT
End Function

The Countdown Begins!

Day(s)

:

Hour(s)

:

Minute(s)

:

Second(s)

Get Notified

Pin It on Pinterest

Share This