# 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+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+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+F9                                          Calculate active worksheet

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

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+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+F9                                          Calculate active worksheet

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

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:

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(Selection, Selection.End(xlDown)).Select
Selection.Copy
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:

Press Alt + F8 to open the macro dialog box.
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:=-52End 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:

Press Alt + F8 to open the macro dialog box.
Click Run

‘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(“L1”).FormulaR1C1 = “Members”
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
.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:
Press Alt + F8 to open the macro dialog box.
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
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.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
.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)
.FooterMargin = Application.InchesToPoints(0.078)
.PrintGridlines = False
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
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:

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

“997-Suspend”, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Range(“K1”).Select
ActiveCell.FormulaR1C1 = “Status”
Range(“I1”).Select
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
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
.IndentLevel = 0
.ShrinkToFit = False
.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
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.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
.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)
.FooterMargin = Application.InchesToPoints(0.236220472440945)
.PrintGridlines = False
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.EvenPage.LeftFooter.Text = “”
.EvenPage.CenterFooter.Text = “”
.EvenPage.RightFooter.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:

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:

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:

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

Day(s)

:

Hour(s)

:

Minute(s)

:

Second(s)