INDEX
Sr No. Particulars
1 General Basic Formulas
2 Shortcut Keys
3 Convert Figures to Words
Sr No. Advanced Formulas
1 Hide the Sensitive Data
2 Sum Formulas
3 Round off Formulas
4 Cheque Printing from Excel
5 Information about your computer
6 EMI Calculation Formula & Loan Calculator
7 V-Lookup (Vertical Lookup)
8 H-Lookup (Horizontal Lookup)
9 Reverse V-Lookup (Vertical Lookup)
10 2 way Lookup using V-Lookup (Vertical Lookup)
11 Indirect Lookup
12 Lookup (Array)
13 Lookup (vector)
14 Pivot Table
15 Pivot Chart
16 Ceiling
17 Remove Unwanted words/characters
18 Insert PDF File in Excel
19 Join First Name & Last Name
20 Split Firstname & Surname
21 Convert Numbers to Roman Numbers
22 Get the Desired number of Characters from Left & Right
23 Hyperlinking in File
24 Count If Formula
25 Count Length of Numbers or Words
26 Count Cells that Start & Ends with Specific Text
27 Current Date & Time Formula
28 Separate Date & Time
29 Separate Day, Month & Year from Date
30 Time Calculation
31 Get Day Name by Date
32 Get Date in Desired Format
33 Calculate Month in Words & in Numbers from Date
34 Bank Reconciliation in Excel just by 2 Steps
35 Age Calculator
36 Rank Position
37 Convert Number to Dollar
38 File Name Formula
39 Forecast Formula
40 Repeated Formula
Sheet No
A
B
C
Sheet No
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
BACK
General Basic Formulas
Sr No Formula Name Query Answer
1 Multiplication 10 15 150
2 Addition 10 15 25
3 Subtraction 10 15 -5
4 Division 10 15 0.66666667
5 Percentage 10 15 67%
6 Average 10 15 12.5
7 Count 10 15 2
8 Sum Forumula 10 15 25
9 Days Calculator 8/1/2022 8/27/2022 26
10 Count Blank 15 1
11 Count Filled 10 20 2
12 Concatenate Ram Shyam RamShyam
Ram Shyam 0
13 Exact
Ram Ram 1
Ram Shyam 1
14 Match
Ram Ram 2
RAM Ram
15 Proper
shyam Shyam
16 Upper shyam SHYAM
17 Lower RAM ram
18 Right Raghuveer 4 veer
19 Left Raghuveer 5 Raghu
20 Middle Raghuveer 5 Raghu
21 Small 10 15 10
22 Large 10 15 15
23 Minimum 10 15 10
24 Maximum 10 15 15
25 Product 10 15 150
10 15
26 Sum Product 325
10 15
27 Trim Ram Shyam Ram Shyam
28 Find Raghuveer e 7
29 Replace Raghuveer a Raghuvar
30 Search Raghuveer v 6
BACK
Shortcut Keys
Sr no Function Short Cut Key
1 Copy Formula CTRL + C
2 Paste Formula CTRL + V
3 Cut Formula CTRL + X
4 Undo Formula CTRL + Z
5 Copy Editing CTRL + insert
6 Paste Editing SHIFT + Insert
7 Save Selection CTRL + s
8 Select entire region Selection CTRL + A
9 Print Navigation CTRL + p
10 Bold a cell’s content Formatting CTRL + B
11 Sum range Formulas ALT + =
12 Insert hyperlink Formatting CTRL + K
13 Select the whole column Selection CTRL + SPACE
14 Select the whole row Selection SHIFT + SPACE
15 Select table Selection SHIFT + CTRL + SPACE bar
16 Previous sheet Navigation CTRL + Page Up
17 Next sheet Navigation CTRL + Page Down
18 Go to last non-blank cell Navigation CTRL + end
19 Go to previous sheet Navigation CTRL + PgUp
20 Go to next sheet Navigation CTRL + PgDn
21 Repeat whatever you did last Formulas F4
22 Calculate formulas Formulas F9
23 Hide row Formatting CTRL + 9
24 Hide column Formatting CTRL + 0
25 Unhide row Formatting CTRL + SHIFT + 9
26 Unhide column Formatting CTRL + SHIFT + 0
27 Sets/removes strikeout in current cell Formatting CTRL + 5
28 Single border around selected cells Formatting CTRL + SHIFT + 7
29 Sort Formatting ALT + DS
30 Save as Excel Options F12
31 Opens print preview Excel Options CTRL + F2
32 Activate next window Excel Options ALT + TAB
33 Close an excel workbook Excel Options crtl + F4
34 Split screens Excel Options ALT + W + S
35 Show visual basic editor Everything Else ALT + F11
36 Macro dialog Everything Else ALT + F8
37 Apply/remove filter Everything Else ALT + DFF
38 Insert pivot table Everything Else ALT + NVT
39 Turn filter on or off Everything Else CTRL + SHIFT + L
40 Edit a cell, place cursor at the end Editing F2
41 Fills down value from cell above Editing CTRL + D
42 Add a comment or Edit comment Editing SHIFT + F2
43 Insert new sheet Editing SHIFT + F11
44 Insert row Editing CTRL + +
45 Make chart/pivot chart Editing F11
46 Edit a cell in Apple Macs Editing CTRL + U
47 Delete box (cell, row, column) Editing ALT + ED
48 Insert box (cell, row, column) Editing ALT + IE
49 Enter current date Auto Complete CTRL + ;
50 Enter current time Auto Complete CTRL + :
Convert Amount in Fig. to Amount in words
Enter the Amount in Fig.
151,215,101.00
Amount in words
Rupees Fifteen Crores Twelve Lacs Fifteen Thousands One Hundred and One only
Rajiv Chawla
rajiv.chawla@icai.org
91-9216555125
BACK
Hide the Sensitive Data
Actual Data Query Answer
Raghuveer veer (hidden require) Raghu****
Raghuveer Raghu (hidden require) *****veer
BACK
Sum Formulas
Data -1 Data -2 Answer
100 150 250
100 150 250 Press Alt =
Sum (Running Total) Formula
Data -1 Data -2 Answer
100 150 250
100 150 500
100 150 750
100 150 1000
Sumif Formula
Data -1 Data -2 Answer
Ram 100
525
Ram 175
Shyam 150
Shyam 120 270
Ram 250
BACK
Round off Formulas
Description Data -1 Answer
Round the Value to Zero Decimal Place 23.458 23
Round the Value to One Decimal Place 23.458 23.5
Round the Value to Multiple of 10 23.458 20
Round the Value to the Next Integer Number 23.458 24
Round up the Value to 1 Decimal Place 23.458 23.5
Round up the Value to Next Multiple of 10 23.458 30
Round the value to the Previous Integer Number 23.458 23
Round down the Value to 1 Decimal Place 23.458 23.4
Round down the Value to Previous Multiple of 10 23.458 20
8/14/2022
Yourself Neft A/c
Twelve Hundered Only
1200
BACK
Information about your computer
Query Answer
Current directory #N/A
Number of active worksheets 1
Cell currently in the top left of the window #N/A
Operating system Linux 5.10
Recalculation mode Automatic
Excel version 47eb0cf7efbacdee9b19ae25d6752381ede23126
Name of system. (PC or Mac) LINUX
BACK
EMI Calculation Formula & Loan Calculator
Loan 100000
Rate 9% Per Annum
Tenure 12 Months
EMI Formula 8745.15 Answer
Months Opening Bal. Interest EMI Closing Bal.
1 100,000.00 750.00 8,745.15 92,004.85
2 92,004.85 690.04 8,745.15 83,949.74
3 83,949.74 629.62 8,745.15 75,834.22
4 75,834.22 568.76 8,745.15 67,657.83
5 67,657.83 507.43 8,745.15 59,420.11
6 59,420.11 445.65 8,745.15 51,120.61
7 51,120.61 383.40 8,745.15 42,758.87
8 42,758.87 320.69 8,745.15 34,334.42
9 34,334.42 257.51 8,745.15 25,846.78
10 25,846.78 193.85 8,745.15 17,295.48
11 17,295.48 129.72 8,745.15 8,680.05
12 8,680.05 65.10 8,745.15 -
BACK
V-Lookup (Vertical Lookup)
Sr No Name Amount Query
1 Ram 1500 3
2 Shyam 2000
3 Raghu 2500 Answer
4 Raghuveer 3000 Raghu
5 Veer 3500 2500
BACK
H-Lookup (Horizontal Lookup)
1 2 3 4 5
Ram Shyam Veer Raghuveer Raghu
Amount 1500 2000 2500 3000 3500
Query Answer Answer
3 Veer 2500
BACK
Reverse V-Lookup (Vertical Lookup)
Name Amount Sr No
Ram 1500 12
Shyam 2000 15
Raghuveer 2500 18
Raghu 3000 20
Veer 3500 25
Query Answer Answer
20 Raghu 3000
BACK
2 way Lookup using V-Lookup (Vertical Lookup)
Name Jan Feb Mar Apr
Ram 1000 200 2200 3100
Shyam 1200 400 2500 3500
Raghu 1500 700 2800 3700
Veer 2000 900 3000 4000
Name Shyam
Query
Month Apr
Answer Amount 3500
BACK
Indirect Lookup
Jan Feb Mar Apr
Ram 1000 200 2200 3100
Shyam 1200 400 2500 3500
Raghu 1500 700 2800 3700
Veer 2000 900 3000 4000
Query Name Veer
Answer 2000 900 3000 4000
BACK
Lookup (Array)
Name Jan Feb Mar
Ram 1000 1200 1500
Shyam 1100 1400 1800
Raghu 700 900 1500
Veer 1500 1800 2000
Query Shyam
Answer Mar Value 1800
BACK
Lookup (vector)
Name Jan Feb Mar
Ram 1000 1200 1500
Shyam 1100 1400 1800
Raghu 700 900 1500
Veer 1500 1800 2000
Query Shyam
Answer Feb Value 1400
BACK
Pivot Table
Name Surname Amount
Ram Shyam 1000
Shyam Ram 1100
Raghu Veer 700
Veer Raghu 1500
Answer
Step -- 1 -- Press Alt + N, V, T
Step -- 2 -- Create Pivot Table Box will Open like this
Step-- 3 -- Select Above Range from B4 to D8
Step-- 4 -- Select Exiting Worksheet as Cell B16 then OK
Step--5 -- Pivot Table Field List will Open then Select Name, Sur & Amt.
Sum of Amount
Name Surname Total
Raghu Veer 700
Raghu Total 700
Ram Shyam 1000
Ram Total 1000 PIVOT TABLE
Shyam Ram 1100
Shyam Total 1100
Veer Raghu 1500
Veer Total 1500
Grand Total 4300
BACK
Pivot Chart
Name Surname Amount
Ram Shyam 1000
Shyam Ram 1100
Raghu Veer 700
Veer Raghu 1500
Answer
Step -- 1 -- Press Alt + N, V, C
Step -- 2 -- Create Pivot Table Box will Open like this
Step-- 3 -- Select Above Range from B4 to D8
Step-- 4 -- Select Exiting Worksheet as Cell B16 then OK
Step--5 -- Pivot Table Field List will Open then Select Name, Sur & Amt.
Sum of Amount
Name Surname Total
Raghu Veer 700
Raghu Total 700
Ram Shyam 1000
Ram Total 1000
Shyam Ram 1100
Shyam Total 1100
Veer Raghu 1500
Veer Total 1500 PIVOT CHART
Grand Total 4300
BACK
Ceiling
Data Adjustment Answer
1000.52 0.05 1000.55
1000.58 0.05 1000.6
BACK
Remove Unwanted words/characters
Data-1 Query Answer
1234-5678-1234 - 123456781234
5678*1234*5678 * 567812345678
1234/5678/1234 / 123456781234
1234+5678+1234 + 123456781234
BACK
Insert PDF File in Excel
Step - 1 Press ALT + N, ZT, J
Step - 2 Object Box will Open like this
Step - 3 Select Adobe Acrobat Document
Step - 4 Select Display as icon & OK
Step - 5 Browse & Select PDF File
BACK
Join First Name & Last Name
First Name Last Name Answer
Ram Shyam Ram Shyam
Shyam Ram Shyam Ram
Raghu Veer Raghu Veer
Veer Raghu Veer Raghu
BACK
Split Firstname & Surname
Fullname Answer-1 Answer-2
Ram Shyam #VALUE! #VALUE!
Shyam Ram #VALUE! #VALUE!
Raghu Veer #VALUE! #VALUE!
Veer Raghu #VALUE! #VALUE!
BACK
Convert Numbers to Roman Numbers
Data-1 Answer
7 VII
9 IX
12 XII
20 XX
Get the Desired number of Characters from Left side of Text
Data Query Answer
Ram Shyam 3 Ram
Shyam Ram 5 Shyam
Raghuveer 5 Raghu
Veerraghu 4 Veer
Get the Desired number of Characters from Right side of Text
Data Query Answer
Ram Shyam 5 Shyam
Shyam Ram 3 Ram
Raghuveer 4 veer
Veerraghu 5 raghu
BACK
of Text
e of Text
BACK
Hyperlinking in File
Linking in Existing File
Step -- 1 --- Press Ctrl + K
Step -- 2 --- Select Option Place in this Document
Step -- 3---Select File you want to Hyperlink then OK
BACK
Count If Formula
Answer
Names Day-1 Day-2 Day-3 Day-4 Present Absent
Ram P P A P 3 1
Shyam P A A P 2 2
Raghu A P P P 3 1
Veer P A A A 1 3
BACK
Count Length of Numbers or Words
Names Answer
Ram 3
Shyam 5
Raghu 5
Veer 4
BACK
Count Cells that Start with Specific Text
Data-1 Start With Answer
A-Ram A 6
B-Ram B 3
A-Shyam C 1
B-Raghu
A-Raghu
C-Veer
B-Veer
Count Cells that Ends with Specific Text
Data-1 Start With Answer
Ram-A A 4
Ram-A B 2
Shyam-A C 1
Raghu-B
Raghu-A
Veer-C
Veer-B
BACK
Current Date & Time Formula
Answer
12/21/2024 14:30
Back
Separate Date & Time
Answer
Date & Time Date Time
8/24/2022 12:10 24/08/2022 12:10:00
8/25/2022 23:10 25/08/2022 23:10:00
8/26/2022 21:20 26/08/2022 21:20:00
8/27/2022 19:40 27/08/2022 19:40:00
BACK
Separate Day, Month & Year from Date
Answer
Date Day Month Year
8/24/2022 24 8 2022
8/25/2022 25 8 2022
8/26/2022 26 8 2022
8/27/2022 27 8 2022
BACK
Time Calculation
Answer
Old Time Addition in Hours New Time
1:20 5:00 6:20
4:40 3:00 7:40
8:30 4:00 12:30
15:20 5:00 20:20
BACK
Get Day Name by Date
Answer
Date Day
8/12/2022 Friday
8/15/2022 Monday
8/18/2022 Thursday
8/24/2022 Wednesday
BACK
Get Date in Desired Format
Answer
Date Formats
Date "DD MMM YYYY" "DD-MM-YYYY" "DD MMM YY"
8/12/2022 12 Aug 2022 12-08-2022 12 Aug 22
8/15/2022 15 Aug 2022 15-08-2022 15 Aug 22
8/18/2022 18 Aug 2022 18-08-2022 18 Aug 22
8/24/2022 24 Aug 2022 24-08-2022 24 Aug 22
Calculate Month in Words & in Numbers from Date
Answer
Date Month in Numbers Month in Words
4/12/2022 4 April
6/15/2022 6 June
7/18/2022 7 July
8/24/2022 8 August
BACK
Bank Reconciliation in Excel
Bank Book of XXXX Bank for the Month of January, 2020
Date Particulars Cheque No Deposit Withdrawl Closing Balance
Opening Balance as per our Records 512100
31/12/2019 Cheuqe Deposit xxxxxx 6500 518600
31/12/2019 Cheuqe Deposit xxxxxx 302400 821000
31/12/2019 Cheuqe Withdrawl xxxxxx 0 815000 6000
02/01/2020 Cheuqe Deposit xxxxxx 1020600 1026600
07/01/2020 Cheuqe Deposit xxxxxx 104700 921900
07/01/2020 Cheuqe Deposit xxxxxx 55200 977100
08/01/2020 Cash Withdrawl xxxxxx 25200 951900
09/01/2020 Cash Deposit xxxxxx 95100 1047000
09/01/2020 Cash Deposit xxxxxx 7735 1054735
15/01/2020 Cheuqe Deposit xxxxxx 302400 1357135
15/01/2020 Cheuqe Withdrawl xxxxxx 0 800000 557135
20/01/2020 Cheuqe Deposit xxxxxx 1020500 1577635
20/01/2020 Cheuqe Withdrawl xxxxxx 102000 1475635
25/01/2020 Cheuqe Deposit xxxxxx 51000 1526635
29/01/2020 Cheuqe Withdrawl xxxxxx 24000 1502635
29/01/2020 Cheuqe Deposit xxxxxx 95000 1597635
31/01/2020 Cheuqe Deposit xxxxxx 75000 1522635
01/02/2020 Cheuqe Deposit xxxxxx 13000 1535635
Closing Balance as per our Records 1535635
BACK
Bank Clearing Uncleared Uncleared
Date Deposits Withdrawls Summary
Closing Balance as per our Record 1535635
6500 0 Add :- Uncleared Deposits 114500
03/01/2020 Add :- Uncleared Withdrawls 99000
03/01/2020 Closing Balance as per Statement 1520135
05/01/2020
10/01/2020
10/01/2020 2 Steps for Bank Reconciliation in Excel
08/01/2020 1) Prepare your Bank Book in Excel Format, As Prepared here.
09/01/2020 2) Insert Bank Clearing Date by referring from Bank Statement
09/01/2020
18/01/2020
18/01/2020
23/01/2020
23/01/2020
28/01/2020
0 24000
95000 0
0 75000
13000 0
mat, As Prepared here.
g from Bank Statement in I Column
BACK
Age Calculator
Birth Date 9/29/1991
Age 33.23
Years Lived 33.00
Months Lived 2.00
Days Lived 22.00
BACK
Rank Position High to Low
Data Answer
25 3
18 4
40 2
50 1
4 5
Rank Position Low to High
Data Answer
25 3
18 2
40 4
50 5
4 1
BACK
Convert Number to Dollar
Data Answer
1000 $1,000.00
1500 $1,500.00
2500 $2,500.00
3000 $3,000.00
BACK
File Name Formula
Answer =CELL("filename")
BACK
Forecast Formula
Month Sales
1 1000
2 1500
3 2000
4 1200
5 1800
6 2500
Month to Predict 11
Answer Forecast Sales 3295
BACK
Repeated Formula
Answer
Data-1 Number of Repeats Repeated Data
Ram 3 RamRamRam
Shyam 2 ShyamShyam
* 5 *****
15 4 15151515
