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 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 columns 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

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)

A B C D E
Sr No Family ID Full Name Gen Formula
1 0001 PURNACHANDRA SAHU M =COUNTIF($B$2:$B$4037,B2)
2 0001 SANJAYA KUMARSAHU M =COUNTIF($B$2:$B$4037,B3)
3 0001 SUDHASHNA SAHU F =COUNTIF($B$2:$B$4037,B4)
4 0002 MANORAMA PADHY F =COUNTIF($B$2:$B$4037,B5)
5 0002 BIPRACHARANA PADHY M =COUNTIF($B$2:$B$4037,B6)
6 0002 SWARNA LATA PADHY F =COUNTIF($B$2:$B$4037,B7)
7 0002 RITIKA PADHY F =COUNTIF($B$2:$B$4037,B8)
How may members of a Family submitted Aadhar COUNTIFS?
Purpose
To How may members of a Family submitted Aadhar
Syntax

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

A B C D E
1 Family ID Full Name  Aadhaar Number Formula
2 0001 PURNACHANDRA SAHU XXXXXXXX9532 =COUNTIFS($B$2:$B$8,B3,$D$2:$D$8,”*”)
3 0001 SANJAYA KUMARSAHU XXXXXXXX8225 =COUNTIFS($B$2:$B$8,B3,$D$2:$D$8,”*”)
4 0001 SUDHASHNA SAHU XXXXXXXX0401 =COUNTIFS($B$2:$B$8,B3,$D$2:$D$8,”*”)
5 0002 MANORAMA PADHY XXXXXXXX7033 =COUNTIFS($B$2:$B$8,B3,$D$2:$D$8,”*”)
6 0002 BIPRACHARANA PADHY XXXXXXXX3254 =COUNTIFS($B$2:$B$8,B3,$D$2:$D$8,”*”)
7 0002 SWARNA LATA PADHY XXXXXXXX5335 =COUNTIFS($B$2:$B$8,B3,$D$2:$D$8,”*”)
8 0002 RITIKA PADHY XXXXXXXX2032 =COUNTIFS($B$2:$B$8,B3,$D$2:$D$8,”*”)
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 Stuburn 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,” “,””)

The Countdown Begins!

Day(s)

:

Hour(s)

:

Minute(s)

:

Second(s)

Get Notified

Pin It on Pinterest

Share This