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.

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)

Pin It on Pinterest

Share This