copy of excel training file
TRANSCRIPT
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 1/63
Use autofill to fill the following series
col1 col2 col 3 col 4 col 6 col 7
1 1-May-04 1-May-04 1-May-04 1-May-04 Sunday2 2-May-043 3-May-044 4-May-045 5-May-046 6-May-04
7 7-May-048 8-May-049 9-May-04
10 10-May-04
-
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 2/63
Fill Blanks Calculate Blanks
Year Product Name Sales Year Product Name Total
2007 Brand A 191 2001 Brand A 855
2008 2002 Brand A 582
2009 2003 Brand A 336
2007 Brand B 689 Total
2008 2004 Brand A 592
2007 Brand C 868 2005 Brand A 788
2008 Total
2009 2006 Brand A 661
2007 Brand D 183 2007 Brand A 6792008 2008 Brand A 612
2007 Brand E 862 Total
2008
2009 Grand Total
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 3/63
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 4/63
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 5/63
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 6/63
SrNo Name Physics Chemistry Biology Total Mark1 Sachin 39 92 712 Vinod 55 75 913 Sameer 20 56 63
4 Rahul 80 28 775 Imran 38 46 266 Vishal 30 52 807 Sushil 95 70 468 Viratji 98 100 719 Zenia 38 42 3810 Arvind 86 27 35
Grand Total
Number of Student Number of Subject
Maximum Marks
Minimum Marks
No.of Student >70
No.of Student Failed
2nd Maximum value
3rd Minimum value
Marksheet of ABC Juniour CollegeFinal Exam
Subjects ( Marks out of 100)
R E P O R T
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 7/63
Average
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 8/63
123456
7
89
1011121314151617181920
2122
232425262728293031
323334353637
383940
A B C D E F G H I J K
Empcode First Name Last Name Dept Region Branch Hiredate Salary Ta DA HRA1 Raja Raymondekar Sales north Ferozepur 1-Jan-77 218752 Suman Shinde Sales east Cuttack 1-Jan-77 175003 Kuldeep Sharma Admin south Hydrabad 1-Mar-99 70004 Beena Mavadia Mktg north Delhi 24-Nov-79 122505 Seema Ranganathan R&D north Kanpur 4-Sep-89 210006 Julie D'Souza R&D north Mathura 4-Sep-88 124257 Deepak Jain Personnel west Pune 17-Aug-90 138258 Neena Mukherjee R&D north Agra 4-Sep-89 124259 Pankaj Sutradhar Sales north Ambala 12-Dec-99 14875
10 Andre Fernendes Mktg east Darjeeling 20-Jul-77 1575011 Sujay Madhrani Finance west Pune 21-Dec-85 1487512 Shilpa Lele Admin north Jammu 1-Mar-83 21000
13 Meera Lalwani Finance east Calcutta 11-Dec-84 1925014 Sheetal Desai Director south cochin 12-Dec-84 49000
15 K. Sita Narayanan Personnel north Jammu 13-Dec-84 1487516 Priya Shirodkar Personnel north Jaipur 14-Dec-84 1487517 Aalok Trivedi Admin east Cuttack 1-Mar-83 1575018 Aakash Dixit Admin west Nasik 1-Mar-83 1575019 Parvati Khanna Mktg north Mathura 13-Aug-86 1050020 Farhan Sadiq Mktg north Jaipur 5-Jun-99 595021 Satinder Kaur Sasan Mktg east Patna 6-Jun-99 787522 Suchita Panchal Mktg west Nasik 7-Jun-99 787523 Shazia Sheikh Mktg west Pune 8-Jun-99 7875
24 Pooja Nimkar Sales west Nasik 21-May-80 1487525 Jasbinder Khurana R&D south Manglore 24-Apr-99 787526 Bharat Shetty Sales east Cuttack 1-Oct-82 1925027 Rishi Malik Sales south Banglore 2-Jul-92 1312528 Mala Bhaduri R&D west Mumbai 19-Mar-95 1050029 Hajra Hoonjan Admin north Jaipur 4-May-96 9625
30 Aalam Qureshi Personnel east Patna 19-Oct-88 1417531 Giriraj Gupta R&D north Agra 1-Oct-82 1575032 Ankur Joshi CCD south Manglore 1-Oct-82 15750
Referencing Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 9/63
414243
4445
4647
484950
5152
535455
5657
585960
6162
6364
656667
6869
7071
727374
7576
77
7879
80
A B C D E F G H I J K
33 Tapan Ghoshal CCD north Ambala 7-Jul-97 700034 Zarina Vora CCD north Lucknow 29-Sep-91 875035 Arun Joshi Mktg south Manglore 5-May-92 9275
36 Pooja Khetan Personnel west Surat 3-Sep-94 1172537 Shilpa Parikh Finance south Mysore 1-Oct-82 24500
38 Chitra Pednekar Finance north Aligarh 1-Oct-82 2450039 Sheetal Dodhia Finance north delhi 1-Oct-82 24500
40 Richa Raje Sales south Manglore 29-Sep-91 1050041 Kirtikar Sardesai Admin east Darjeeling 14-Jun-97 787542 Pooja Gokhale R&D east Calcutta 29-Sep-91 13300
43 Piyush Surti Personnel west Mumbai 19-Oct-88 1575044 Shaheen Khan Personnel east Cuttack 17-Nov-90 13825
45 Kinnari Mehta R&D north Ferozepur 7-Jul-97 1137546 Jeena Baig Sales north Lucknow 29-Sep-91 1575047 Vicky Joshi Admin north Kanpur 7-Feb-88 15750
48 Neha Joshi CCD west Baroda 19-Oct-88 1575049 Rakesh Kumar CCD south cochin 19-Oct-88 15750
50 Ruheal Baig Personnel west Baroda 19-Oct-88 1575051 Mario Fernandes Sales north Jammu 20-Oct-88 1575052 Heena Godbole CCD north Lucknow 21-Oct-88 19250
53 Mehul Sheth CCD west Nagpur 1-Dec-95 1575054 Vishal Virsinghani CCD east Gangtok 3-Mar-95 15750
55 Lalita Rao CCD east Guwahati 1-Nov-86 1575056 Kajal Joglekar Sales west Nagpur 1-Oct-92 14875
57 Katti Surti Sales south chennai 9-May-93 1487558 Maya Panchal Mktg north Agra 5-Nov-90 1750059 Disha Parmar Admin south Banglore 14-Jan-87 19250
60 Geeta Darekar Mktg south Trivanadrum 24-Nov-87 1925061 Anuradha Zha Admin north Agra 25-Nov-87 19250
62 Asha Trivedi Sales north Kanpur 26-Nov-87 1925063 Waheda Sheikh R&D north Jammu 24-Oct-88 17500
64 Veena Patil Personnel south Hydrabad 25-Oct-88 1750065 Timsi Desai CCD east Calcutta 26-Oct-88 1750066 Parul Shah Personnel north Agra 27-Oct-88 17500
67 Uday Naik Personnel north Lucknow 28-Oct-88 2012568 Mandakini Desai Sales north delhi 1-Dec-95 14000
69 Pravin Joshi Personnel north delhi 3-Mar-95 14000
70 Reeta Naik Mktg east Calcutta 4-Mar-95 1400071 Surendra Godse R&D west Panji 5-Mar-95 14000
72 Yamini Gupta R&D south Mysore 22-Oct-88 17500
Referencing Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 10/63
818283
8485
8687
888990
9192
939495
9697
9899
100
101102
103104
105106107
108
A B C D E F G H I J K
73 Laveena Shenoy CCD north Jaipur 23-Oct-88 1750074 Nita Pandhya Sales east Darjeeling 24-Oct-88 1750075 Suraj Saksena Admin east Calcutta 25-Oct-88 17500
76 Nayeem Khan CCD west Panji 26-Oct-88 1750077 Drishti Shah R&D north Delhi 27-Oct-88 17500
78 Deep Chhaya Sales west Mumbai 28-Oct-88 1750079 Sagar Bidkar Mktg north Delhi 29-Oct-88 17500
80 Dayanand Gandhi Mktg north Ferozepur 30-Oct-88 1750081 Tara Phule CCD south chennai 31-Oct-88 1750082 Kalpana Shirishkar R&D east Cuttack 1-Nov-88 17500
83 Kunal Shah CCD north Aligarh 2-Mar-99 700084 Pinky Robert R&D east Calcutta 3-Mar-99 7000
85 Ruby Joseph R&D north Agra 14-Jan-98 700086 Sonia Sasan CCD north Jammu 15-Jan-98 1750087 Jignesh Tripathi Mktg south Hydrabad 16-Jan-98 17500
88 Vinit Shrivastava Mktg west Pune 30-Dec-97 787589 Tejal Patel Sales north Aligarh 21-Feb-79 22750
90 Priyanka Mehta R&D north Jaipur 6-Jan-80 2275091 Niki Digaria Sales east Calcutta 13-Nov-79 2275092 Payal Singhani Mktg east Gangtok 5-Apr-89 17325
93 Harsha Trivedi Sales south Banglore 6-Apr-89 1732594 Radhika Kulkarni Mktg west Surat 7-Apr-89 17325
95 Kabir Vora Mktg south Banglore 8-Apr-89 1732596 Beena Sharma R&D west Nagpur 9-Apr-89 17325
97 Pushpa Raut R&D south Trivanadrum 10-Apr-89 1732598 Chetan Dalvi CCD north Delhi 11-Apr-89 1732599 Indu Shah Finance west Mumbai 30-Dec-97 10500
100 Rupesh Sawant Finance south Hydrabad 2-Feb-99 7875
Referencing Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 11/63
1234567
89
10111213141516171819202122232425262728293031
32333435363738
3940
M N O P Q R S T U V W
20% 30% 10% 20% 30% 12% 20% 30% 20% 20% 30%
DA1 HRA1 Ta2 da2 HRA2 Ta3 DA3 HRa3 Ta4 DA4 HRA4
Referencing Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 12/63
12345
6
7
89
1011121314
151617181920
2122
232425262728
293031323334
353637
383940
A B C D E F G H
Empcode First Name Last Name Dept Region Branch Hiredate Salary
1 Raja Raymondekar Sales north Ferozepur 1-Jan-77 21875
2 Suman Shinde Sales east Cuttack 1-Jan-77 175003 Kuldeep Sharma Admin south Hydrabad 1-Mar-99 70004 Beena Mavadia Mktg north Delhi 24-Nov-79 122505 Seema Ranganathan R&D north Kanpur 4-Sep-89 210006 Julie D'Souza R&D north Mathura 4-Sep-88 124257 Deepak Jain Personnel west Pune 17-Aug-90 138258 Neena Mukherjee R&D north Agra 4-Sep-89 12425
9 Pankaj Sutradhar Sales north Ambala 12-Dec-99 1487510 Andre Fernendes Mktg east Darjeeling 20-Jul-77 1575011 Sujay Madhrani Finance west Pune 21-Dec-85 1487512 Shilpa Lele Admin north Jammu 1-Mar-83 2100013 Meera Lalwani Finance east Calcutta 11-Dec-84 1925014 Sheetal Desai Director south cochin 12-Dec-84 49000
15 K. Sita Narayanan Personnel north Jammu 13-Dec-84 1487516 Priya Shirodkar Personnel north Jaipur 14-Dec-84 14875
17 Aalok Trivedi Admin east Cuttack 1-Mar-83 1575018 Aakash Dixit Admin west Nasik 1-Mar-83 1575019 Parvati Khanna Mktg north Mathura 13-Aug-86 1050020 Farhan Sadiq Mktg north Jaipur 5-Jun-99 595021 Satinder Kaur Sasan Mktg east Patna 6-Jun-99 787522 Suchita Panchal Mktg west Nasik 7-Jun-99 7875
23 Shazia Sheikh Mktg west Pune 8-Jun-99 787524 Pooja Nimkar Sales west Nasik 21-May-80 1487525 Jasbinder Khurana R&D south Manglore 24-Apr-99 787526 Bharat Shetty Sales east Cuttack 1-Oct-82 1925027 Rishi Malik Sales south Banglore 2-Jul-92 1312528 Mala Bhaduri R&D west Mumbai 19-Mar-95 10500
29 Hajra Hoonjan Admin north Jaipur 4-May-96 962530 Aalam Qureshi Personnel east Patna 19-Oct-88 1417531 Giriraj Gupta R&D north Agra 1-Oct-82 15750
32 Ankur Joshi CCD south Manglore 1-Oct-82 1575033 Tapan Ghoshal CCD north Ambala 7-Jul-97 700034 Zarina Vora CCD north Lucknow 29-Sep-91 8750
Logical functions Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 13/63
414243
4445
4647
484950
5152
535455
5657
585960
6162
6364
656667
6869
7071
727374
7576
77
7879
80
A B C D E F G H
35 Arun Joshi Mktg south Manglore 5-May-92 927536 Pooja Khetan Personnel west Surat 3-Sep-94 1172537 Shilpa Parikh Finance south Mysore 1-Oct-82 24500
38 Chitra Pednekar Finance north Aligarh 1-Oct-82 2450039 Sheetal Dodhia Finance north delhi 1-Oct-82 24500
40 Richa Raje Sales south Manglore 29-Sep-91 1050041 Kirtikar Sardesai Admin east Darjeeling 14-Jun-97 7875
42 Pooja Gokhale R&D east Calcutta 29-Sep-91 1330043 Piyush Surti Personnel west Mumbai 19-Oct-88 1575044 Shaheen Khan Personnel east Cuttack 17-Nov-90 13825
45 Kinnari Mehta R&D north Ferozepur 7-Jul-97 1137546 Jeena Baig Sales north Lucknow 29-Sep-91 15750
47 Vicky Joshi Admin north Kanpur 7-Feb-88 1575048 Neha Joshi CCD west Baroda 19-Oct-88 1575049 Rakesh Kumar CCD south cochin 19-Oct-88 15750
50 Ruheal Baig Personnel west Baroda 19-Oct-88 1575051 Mario Fernandes Sales north Jammu 20-Oct-88 15750
52 Heena Godbole CCD north Lucknow 21-Oct-88 1925053 Mehul Sheth CCD west Nagpur 1-Dec-95 1575054 Vishal Virsinghani CCD east Gangtok 3-Mar-95 15750
55 Lalita Rao CCD east Guwahati 1-Nov-86 1575056 Kajal Joglekar Sales west Nagpur 1-Oct-92 14875
57 Katti Surti Sales south chennai 9-May-93 1487558 Maya Panchal Mktg north Agra 5-Nov-90 17500
59 Disha Parmar Admin south Banglore 14-Jan-87 1925060 Geeta Darekar Mktg south Trivanadrum 24-Nov-87 1925061 Anuradha Zha Admin north Agra 25-Nov-87 19250
62 Asha Trivedi Sales north Kanpur 26-Nov-87 1925063 Waheda Sheikh R&D north Jammu 24-Oct-88 17500
64 Veena Patil Personnel south Hydrabad 25-Oct-88 1750065 Timsi Desai CCD east Calcutta 26-Oct-88 17500
66 Parul Shah Personnel north Agra 27-Oct-88 1750067 Uday Naik Personnel north Lucknow 28-Oct-88 2012568 Mandakini Desai Sales north delhi 1-Dec-95 14000
69 Pravin Joshi Personnel north delhi 3-Mar-95 1400070 Reeta Naik Mktg east Calcutta 4-Mar-95 14000
71 Surendra Godse R&D west Panji 5-Mar-95 14000
72 Yamini Gupta R&D south Mysore 22-Oct-88 1750073 Laveena Shenoy CCD north Jaipur 23-Oct-88 17500
74 Nita Pandhya Sales east Darjeeling 24-Oct-88 17500
Logical functions Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 14/63
818283
8485
8687
888990
9192
939495
9697
9899
100
101102
103104
105106
A B C D E F G H
75 Suraj Saksena Admin east Calcutta 25-Oct-88 1750076 Nayeem Khan CCD west Panji 26-Oct-88 1750077 Drishti Shah R&D north Delhi 27-Oct-88 17500
78 Deep Chhaya Sales west Mumbai 28-Oct-88 1750079 Sagar Bidkar Mktg north Delhi 29-Oct-88 17500
80 Dayanand Gandhi Mktg north Ferozepur 30-Oct-88 1750081 Tara Phule CCD south chennai 31-Oct-88 17500
82 Kalpana Shirishkar R&D east Cuttack 1-Nov-88 1750083 Kunal Shah CCD north Aligarh 2-Mar-99 700084 Pinky Robert R&D east Calcutta 3-Mar-99 7000
85 Ruby Joseph R&D north Agra 14-Jan-98 700086 Sonia Sasan CCD north Jammu 15-Jan-98 17500
87 Jignesh Tripathi Mktg south Hydrabad 16-Jan-98 1750088 Vinit Shrivastava Mktg west Pune 30-Dec-97 787589 Tejal Patel Sales north Aligarh 21-Feb-79 22750
90 Priyanka Mehta R&D north Jaipur 6-Jan-80 2275091 Niki Digaria Sales east Calcutta 13-Nov-79 22750
92 Payal Singhani Mktg east Gangtok 5-Apr-89 1732593 Harsha Trivedi Sales south Banglore 6-Apr-89 1732594 Radhika Kulkarni Mktg west Surat 7-Apr-89 17325
95 Kabir Vora Mktg south Banglore 8-Apr-89 1732596 Beena Sharma R&D west Nagpur 9-Apr-89 17325
97 Pushpa Raut R&D south Trivanadrum 10-Apr-89 1732598 Chetan Dalvi CCD north Delhi 11-Apr-89 17325
99 Indu Shah Finance west Mumbai 30-Dec-97 10500100 Rupesh Sawant Finance south Hydrabad 2-Feb-99 7875
Logical functions Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 15/63
1
23456
7
89
1011121314151617
1819202122
2324
25262728293031
323334353637
383940
A B C D E F G H
Empcode First Name Last Name Dept Region Branch Hiredate Salary
18 Aakash Dixit Admin west Nasik 1-Mar-83 1575030 Aalam Qureshi Personnel east Patna 19-Oct-88 14175
17 Aalok Trivedi Admin east Cuttack 1-Mar-83 1575010 Andre Fernendes Mktg east Darjeeling 20-Jul-77 1575032 Ankur Joshi CCD south Manglore 1-Oct-82 1575061 Anuradha Zha Admin north Agra 25-Nov-87 1925035 Arun Joshi Mktg south Manglore 5-May-92 927562 Asha Trivedi Sales north Kanpur 26-Nov-87 192504 Beena Mavadia Mktg north Delhi 24-Nov-79 12250
96 Beena Sharma R&D west Nagpur 9-Apr-89 17325
26 Bharat Shetty Sales east Cuttack 1-Oct-82 1925098 Chetan Dalvi CCD north Delhi 11-Apr-89 1732538 Chitra Pednekar Finance north Aligarh 1-Oct-82 2450080 Dayanand Gandhi Mktg north Ferozepur 30-Oct-88 1750078 Deep Chhaya Sales west Mumbai 28-Oct-88 17500
7 Deepak Jain Personnel west Pune 17-Aug-90 1382559 Disha Parmar Admin south Banglore 14-Jan-87 19250
77 Drishti Shah R&D north Delhi 27-Oct-88 1750020 Farhan Sadiq Mktg north Jaipur 5-Jun-99 595060 Geeta Darekar Mktg south Trivanadrum 24-Nov-87 1925031 Giriraj Gupta R&D north Agra 1-Oct-82 1575029 Hajra Hoonjan Admin north Jaipur 4-May-96 962593 Harsha Trivedi Sales south Banglore 6-Apr-89 1732552 Heena Godbole CCD north Lucknow 21-Oct-88 19250
99 Indu Shah Finance west Mumbai 30-Dec-97 1050025 Jasbinder Khurana R&D south Manglore 24-Apr-99 787546 Jeena Baig Sales north Lucknow 29-Sep-91 1575087 Jignesh Tripathi Mktg south Hydrabad 16-Jan-98 175006 Julie D'Souza R&D north Mathura 4-Sep-88 12425
15 K. Sita Narayanan Personnel north Jammu 13-Dec-84 14875
95 Kabir Vora Mktg south Banglore 8-Apr-89 1732556 Kajal Joglekar Sales west Nagpur 1-Oct-92 1487582 Kalpana Shirishkar R&D east Cuttack 1-Nov-88 17500
Lookup Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 16/63
414243
4445
4647
484950
5152
535455
5657
585960
6162
6364
656667
6869
7071
727374
7576
77
7879
80
A B C D E F G H
57 Katti Surti Sales south chennai 9-May-93 1487545 Kinnari Mehta R&D north Ferozepur 7-Jul-97 1137541 Kirtikar Sardesai Admin east Darjeeling 14-Jun-97 7875
3 Kuldeep Sharma Admin south Hydrabad 1-Mar-99 700083 Kunal Shah CCD north Aligarh 2-Mar-99 7000
55 Lalita Rao CCD east Guwahati 1-Nov-86 1575073 Laveena Shenoy CCD north Jaipur 23-Oct-88 17500
28 Mala Bhaduri R&D west Mumbai 19-Mar-95 1050068 Mandakini Desai Sales north delhi 1-Dec-95 1400051 Mario Fernandes Sales north Jammu 20-Oct-88 15750
58 Maya Panchal Mktg north Agra 5-Nov-90 1750013 Meera Lalwani Finance east Calcutta 11-Dec-84 19250
53 Mehul Sheth CCD west Nagpur 1-Dec-95 1575076 Nayeem Khan CCD west Panji 26-Oct-88 17500
8 Neena Mukherjee R&D north Agra 4-Sep-89 12425
48 Neha Joshi CCD west Baroda 19-Oct-88 1575091 Niki Digaria Sales east Calcutta 13-Nov-79 22750
74 Nita Pandhya Sales east Darjeeling 24-Oct-88 175009 Pankaj Sutradhar Sales north Ambala 12-Dec-99 14875
66 Parul Shah Personnel north Agra 27-Oct-88 17500
19 Parvati Khanna Mktg north Mathura 13-Aug-86 1050092 Payal Singhani Mktg east Gangtok 5-Apr-89 17325
84 Pinky Robert R&D east Calcutta 3-Mar-99 700043 Piyush Surti Personnel west Mumbai 19-Oct-88 15750
24 Pooja Nimkar Sales west Nasik 21-May-80 1487536 Pooja Khetan Personnel west Surat 3-Sep-94 1172542 Pooja Gokhale R&D east Calcutta 29-Sep-91 13300
69 Pravin Joshi Personnel north delhi 3-Mar-95 1400016 Priya Shirodkar Personnel north Jaipur 14-Dec-84 14875
90 Priyanka Mehta R&D north Jaipur 6-Jan-80 2275097 Pushpa Raut R&D south Trivanadrum 10-Apr-89 17325
94 Radhika Kulkarni Mktg west Surat 7-Apr-89 173251 Raja Raymondekar Sales north Ferozepur 1-Jan-77 21875
49 Rakesh Kumar CCD south cochin 19-Oct-88 15750
70 Reeta Naik Mktg east Calcutta 4-Mar-95 1400040 Richa Raje Sales south Manglore 29-Sep-91 10500
27 Rishi Malik Sales south Banglore 2-Jul-92 13125
85 Ruby Joseph R&D north Agra 14-Jan-98 700050 Ruheal Baig Personnel west Baroda 19-Oct-88 15750
100 Rupesh Sawant Finance south Hydrabad 2-Feb-99 7875
Lookup Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 17/63
818283
8485
8687
888990
9192
939495
9697
9899
100
101102
103104
105106107
A B C D E F G H
79 Sagar Bidkar Mktg north Delhi 29-Oct-88 1750021 Satinder Kaur Sasan Mktg east Patna 6-Jun-99 7875
5 Seema Ranganathan R&D north Kanpur 4-Sep-89 21000
44 Shaheen Khan Personnel east Cuttack 17-Nov-90 1382523 Shazia Sheikh Mktg west Pune 8-Jun-99 7875
14 Sheetal Desai Director south cochin 12-Dec-84 4900039 Sheetal Dodhia Finance north delhi 1-Oct-82 24500
12 Shilpa Lele Admin north Jammu 1-Mar-83 2100037 Shilpa Parikh Finance south Mysore 1-Oct-82 2450086 Sonia Sasan CCD north Jammu 15-Jan-98 17500
22 Suchita Panchal Mktg west Nasik 7-Jun-99 787511 Sujay Madhrani Finance west Pune 21-Dec-85 14875
2 Suman Shinde Sales east Cuttack 1-Jan-77 1750075 Suraj Saksena Admin east Calcutta 25-Oct-88 1750071 Surendra Godse R&D west Panji 5-Mar-95 14000
33 Tapan Ghoshal CCD north Ambala 7-Jul-97 700081 Tara Phule CCD south chennai 31-Oct-88 17500
89 Tejal Patel Sales north Aligarh 21-Feb-79 2275065 Timsi Desai CCD east Calcutta 26-Oct-88 1750067 Uday Naik Personnel north Lucknow 28-Oct-88 20125
64 Veena Patil Personnel south Hydrabad 25-Oct-88 1750047 Vicky Joshi Admin north Kanpur 7-Feb-88 15750
88 Vinit Shrivastava Mktg west Pune 30-Dec-97 787554 Vishal Virsinghani CCD east Gangtok 3-Mar-95 15750
63 Waheda Sheikh R&D north Jammu 24-Oct-88 1750072 Yamini Gupta R&D south Mysore 22-Oct-88 1750034 Zarina Vora CCD north Lucknow 29-Sep-91 8750
Lookup Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 18/63
0 Class 4 Emp ID Ince Kalyan Dombivalli Thane10000 Class 3 10 1000 Kalyan 41 72 3120000 Class 2 20 2000 Dombivalli 87 84 5830000 Class 1 30 3000 Thane 96 90 7940000 A Grade 40 4000 Ghatkopar 84 63 91
50 5000 Kurla 32 87 8460 6000 CST 90 83 5770 700080 800090 9000 Starting station Kurla
100 10000
Destination Station CST
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 19/63
Ghatkopar Kurla CST Panvel Nerul Vashi Kurla Wadala69 84 32 Panvel 36 81 35 61 7569 99 83 Nerul 39 33 58 42 3835 81 36 Vashi 79 91 30 47 5138 44 72 Kurla 61 54 92 59 66
79 95 35 Wadala 82 35 95 37 6049 65 47 CST 99 73 37 45 41
Kalyan Dombivalli Thane Ghatkopar Kurla
Kalyan 41 72 31 69 84Dombivalli 87 84 58 69 99
Thane 96 90 79 35 81Ghatkopar 84 63 91 38 44Kurla 32 87 84 79 95CST 90 83 57 49 65
Borivalli Andheri Bandra Dadar Mumbai CBorivalli 36 81 35 61 75
Andheri 39 33 58 42 38Bandra 79 91 30 47 51Dadar 61 54 92 59 66Mumbai C 82 35 95 37 60ChurchGat 99 73 37 45 41
Harbour
Central
Western
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 20/63
CST50384159
5543
CST
3283 Rail Route
3672 Starting Station3547 Destination Station
ChurchGate50
3841595543
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 21/63
Employee Code 56
Name
Region
Department
Salary
Employee Information System
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 22/63
1
234567
89
10
111213141516171819
202122
2324252627
28293031323334
353637
383940
A B C D E F G H
Empcode First Name Last Name Dept Region Branch Hiredate Salary20 Farhan Sadiq Mktg north Jaipur 5-Jun-99 59503 Kuldeep Sharma Admin south Hydrabad 1-Mar-99 7000
33 Tapan Ghoshal CCD north Ambala 7-Jul-97 700083 Kunal Shah CCD north Aligarh 2-Mar-99 700084 Pinky Robert R&D east Calcutta 3-Mar-99 700085 Ruby Joseph R&D north Agra 14-Jan-98 700021 Satinder Kaur Sasan Mktg east Patna 6-Jun-99 787522 Suchita Panchal Mktg west Nasik 7-Jun-99 787523 Shazia Sheikh Mktg west Pune 8-Jun-99 787525 Jasbinder Khurana R&D south Manglore 24-Apr-99 787541 Kirtikar Sardesai Admin east Darjeeling 14-Jun-97 7875
88 Vinit Shrivastava Mktg west Pune 30-Dec-97 7875100 Rupesh Sawant Finance south Hydrabad 2-Feb-99 7875
34 Zarina Vora CCD north Lucknow 29-Sep-91 8750
35 Arun Joshi Mktg south Manglore 5-May-92 927529 Hajra Hoonjan Admin north Jaipur 4-May-96 962519 Parvati Khanna Mktg north Mathura 13-Aug-86 1050028 Mala Bhaduri R&D west Mumbai 19-Mar-95 1050040 Richa Raje Sales south Manglore 29-Sep-91 10500
99 Indu Shah Finance west Mumbai 30-Dec-97 1050045 Kinnari Mehta R&D north Ferozepur 7-Jul-97 1137536 Pooja Khetan Personnel west Surat 3-Sep-94 117254 Beena Mavadia Mktg north Delhi 24-Nov-79 122506 Julie D'Souza R&D north Mathura 4-Sep-88 124258 Neena Mukherjee R&D north Agra 4-Sep-89 12425
27 Rishi Malik Sales south Banglore 2-Jul-92 13125
42 Pooja Gokhale R&D east Calcutta 29-Sep-91 133007 Deepak Jain Personnel west Pune 17-Aug-90 13825
44 Shaheen Khan Personnel east Cuttack 17-Nov-90 13825
68 Mandakini Desai Sales north delhi 1-Dec-95 1400069 Pravin Joshi Personnel north delhi 3-Mar-95 1400070 Reeta Naik Mktg east Calcutta 4-Mar-95 14000
Conditional formatting Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 23/63
414243
4445
4647
484950
5152
535455
5657
585960
6162
6364
656667
6869
7071
727374
7576
77
7879
80
A B C D E F G H
71 Surendra Godse R&D west Panji 5-Mar-95 1400030 Aalam Qureshi Personnel east Patna 19-Oct-88 14175
9 Pankaj Sutradhar Sales north Ambala 12-Dec-99 14875
11 Sujay Madhrani Finance west Pune 21-Dec-85 1487515 K. Sita Narayanan Personnel north Jammu 13-Dec-84 14875
16 Priya Shirodkar Personnel north Jaipur 14-Dec-84 1487524 Pooja Nimkar Sales west Nasik 21-May-80 14875
56 Kajal Joglekar Sales west Nagpur 1-Oct-92 1487557 Katti Surti Sales south chennai 9-May-93 1487510 Andre Fernendes Mktg east Darjeeling 20-Jul-77 15750
17 Aalok Trivedi Admin east Cuttack 1-Mar-83 1575018 Aakash Dixit Admin west Nasik 1-Mar-83 15750
31 Giriraj Gupta R&D north Agra 1-Oct-82 1575032 Ankur Joshi CCD south Manglore 1-Oct-82 1575043 Piyush Surti Personnel west Mumbai 19-Oct-88 15750
46 Jeena Baig Sales north Lucknow 29-Sep-91 1575047 Vicky Joshi Admin north Kanpur 7-Feb-88 15750
48 Neha Joshi CCD west Baroda 19-Oct-88 1575049 Rakesh Kumar CCD south cochin 19-Oct-88 1575050 Ruheal Baig Personnel west Baroda 19-Oct-88 15750
51 Mario Fernandes Sales north Jammu 20-Oct-88 1575053 Mehul Sheth CCD west Nagpur 1-Dec-95 15750
54 Vishal Virsinghani CCD east Gangtok 3-Mar-95 1575055 Lalita Rao CCD east Guwahati 1-Nov-86 15750
92 Payal Singhani Mktg east Gangtok 5-Apr-89 1732593 Harsha Trivedi Sales south Banglore 6-Apr-89 1732594 Radhika Kulkarni Mktg west Surat 7-Apr-89 17325
95 Kabir Vora Mktg south Banglore 8-Apr-89 1732596 Beena Sharma R&D west Nagpur 9-Apr-89 17325
97 Pushpa Raut R&D south Trivanadrum 10-Apr-89 1732598 Chetan Dalvi CCD north Delhi 11-Apr-89 17325
2 Suman Shinde Sales east Cuttack 1-Jan-77 1750058 Maya Panchal Mktg north Agra 5-Nov-90 1750063 Waheda Sheikh R&D north Jammu 24-Oct-88 17500
64 Veena Patil Personnel south Hydrabad 25-Oct-88 1750065 Timsi Desai CCD east Calcutta 26-Oct-88 17500
66 Parul Shah Personnel north Agra 27-Oct-88 17500
72 Yamini Gupta R&D south Mysore 22-Oct-88 1750073 Laveena Shenoy CCD north Jaipur 23-Oct-88 17500
74 Nita Pandhya Sales east Darjeeling 24-Oct-88 17500
Conditional formatting Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 24/63
818283
8485
8687
888990
9192
939495
9697
9899
100
101102
103104
105106107
108
A B C D E F G H
75 Suraj Saksena Admin east Calcutta 25-Oct-88 1750076 Nayeem Khan CCD west Panji 26-Oct-88 1750077 Drishti Shah R&D north Delhi 27-Oct-88 17500
78 Deep Chhaya Sales west Mumbai 28-Oct-88 1750079 Sagar Bidkar Mktg north Delhi 29-Oct-88 17500
80 Dayanand Gandhi Mktg north Ferozepur 30-Oct-88 1750081 Tara Phule CCD south chennai 31-Oct-88 17500
82 Kalpana Shirishkar R&D east Cuttack 1-Nov-88 1750086 Sonia Sasan CCD north Jammu 15-Jan-98 1750087 Jignesh Tripathi Mktg south Hydrabad 16-Jan-98 17500
13 Meera Lalwani Finance east Calcutta 11-Dec-84 1925026 Bharat Shetty Sales east Cuttack 1-Oct-82 19250
52 Heena Godbole CCD north Lucknow 21-Oct-88 1925059 Disha Parmar Admin south Banglore 14-Jan-87 1925060 Geeta Darekar Mktg south Trivanadrum 24-Nov-87 19250
61 Anuradha Zha Admin north Agra 25-Nov-87 1925062 Asha Trivedi Sales north Kanpur 26-Nov-87 19250
67 Uday Naik Personnel north Lucknow 28-Oct-88 201255 Seema Ranganathan R&D north Kanpur 4-Sep-89 21000
12 Shilpa Lele Admin north Jammu 1-Mar-83 21000
1 Raja Raymondekar Sales north Ferozepur 1-Jan-77 2187589 Tejal Patel Sales north Aligarh 21-Feb-79 22750
90 Priyanka Mehta R&D north Jaipur 6-Jan-80 2275091 Niki Digaria Sales east Calcutta 13-Nov-79 22750
37 Shilpa Parikh Finance south Mysore 1-Oct-82 2450038 Chitra Pednekar Finance north Aligarh 1-Oct-82 2450039 Sheetal Dodhia Finance north delhi 1-Oct-82 24500
14 Sheetal Desai Director south cochin 12-Dec-84 49000
Conditional formatting Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 25/63
1
234567
89
10
1112131415161718
19202122
232425
262728293031
323334353637
383940
A B C D E F G H
Empcode First Name Last Name Dept Region Branch Hiredate Salary61 Anuradha Zha Admin north Agra 25-Nov-87 1925062 Asha Trivedi Sales north Kanpur 26-Nov-87 19250
1 Raja Raymondekar Sales north Ferozepur 1-Jan-77 218752 Suman Shinde Sales east Cuttack 1-Jan-77 17500
10 Andre Fernendes Mktg east Darjeeling 20-Jul-77 1575089 Tejal Patel Sales north Aligarh 21-Feb-79 2275091 Niki Digaria Sales east Calcutta 13-Nov-79 227504 Beena Mavadia Mktg north Delhi 24-Nov-79 12250
90 Priyanka Mehta R&D north Jaipur 6-Jan-80 2275024 Pooja Nimkar Sales west Nasik 21-May-80 14875
38 Chitra Pednekar Finance north Aligarh 1-Oct-82 2450031 Giriraj Gupta R&D north Agra 1-Oct-82 1575039 Sheetal Dodhia Finance north delhi 1-Oct-82 2450026 Bharat Shetty Sales east Cuttack 1-Oct-82 19250
32 Ankur Joshi CCD south Manglore 1-Oct-82 1575037 Shilpa Parikh Finance south Mysore 1-Oct-82 2450012 Shilpa Lele Admin north Jammu 1-Mar-83 21000
17 Aalok Trivedi Admin east Cuttack 1-Mar-83 1575018 Aakash Dixit Admin west Nasik 1-Mar-83 1575013 Meera Lalwani Finance east Calcutta 11-Dec-84 1925014 Sheetal Desai Director south cochin 12-Dec-84 4900015 K. Sita Narayanan Personnel north Jammu 13-Dec-84 1487516 Priya Shirodkar Personnel north Jaipur 14-Dec-84 14875
11 Sujay Madhrani Finance west Pune 21-Dec-85 1487519 Parvati Khanna Mktg north Mathura 13-Aug-86 1050055 Lalita Rao CCD east Guwahati 1-Nov-86 1575059 Disha Parmar Admin south Banglore 14-Jan-87 1925060 Geeta Darekar Mktg south Trivanadrum 24-Nov-87 1925047 Vicky Joshi Admin north Kanpur 7-Feb-88 15750
6 Julie D'Souza R&D north Mathura 4-Sep-88 1242530 Aalam Qureshi Personnel east Patna 19-Oct-88 1417548 Neha Joshi CCD west Baroda 19-Oct-88 15750
Sort & filter Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 26/63
414243
4445
4647
484950
5152
535455
5657
585960
6162
6364
656667
6869
7071
727374
7576
77
7879
80
A B C D E F G H
43 Piyush Surti Personnel west Mumbai 19-Oct-88 1575050 Ruheal Baig Personnel west Baroda 19-Oct-88 1575049 Rakesh Kumar CCD south cochin 19-Oct-88 15750
51 Mario Fernandes Sales north Jammu 20-Oct-88 1575052 Heena Godbole CCD north Lucknow 21-Oct-88 19250
72 Yamini Gupta R&D south Mysore 22-Oct-88 1750073 Laveena Shenoy CCD north Jaipur 23-Oct-88 17500
63 Waheda Sheikh R&D north Jammu 24-Oct-88 1750074 Nita Pandhya Sales east Darjeeling 24-Oct-88 1750075 Suraj Saksena Admin east Calcutta 25-Oct-88 17500
64 Veena Patil Personnel south Hydrabad 25-Oct-88 1750065 Timsi Desai CCD east Calcutta 26-Oct-88 17500
76 Nayeem Khan CCD west Panji 26-Oct-88 1750077 Drishti Shah R&D north Delhi 27-Oct-88 1750066 Parul Shah Personnel north Agra 27-Oct-88 17500
67 Uday Naik Personnel north Lucknow 28-Oct-88 2012578 Deep Chhaya Sales west Mumbai 28-Oct-88 17500
79 Sagar Bidkar Mktg north Delhi 29-Oct-88 1750080 Dayanand Gandhi Mktg north Ferozepur 30-Oct-88 1750081 Tara Phule CCD south chennai 31-Oct-88 17500
82 Kalpana Shirishkar R&D east Cuttack 1-Nov-88 1750092 Payal Singhani Mktg east Gangtok 5-Apr-89 17325
93 Harsha Trivedi Sales south Banglore 6-Apr-89 1732594 Radhika Kulkarni Mktg west Surat 7-Apr-89 17325
95 Kabir Vora Mktg south Banglore 8-Apr-89 1732596 Beena Sharma R&D west Nagpur 9-Apr-89 1732597 Pushpa Raut R&D south Trivanadrum 10-Apr-89 17325
98 Chetan Dalvi CCD north Delhi 11-Apr-89 173258 Neena Mukherjee R&D north Agra 4-Sep-89 12425
5 Seema Ranganathan R&D north Kanpur 4-Sep-89 210007 Deepak Jain Personnel west Pune 17-Aug-90 13825
58 Maya Panchal Mktg north Agra 5-Nov-90 1750044 Shaheen Khan Personnel east Cuttack 17-Nov-90 1382546 Jeena Baig Sales north Lucknow 29-Sep-91 15750
34 Zarina Vora CCD north Lucknow 29-Sep-91 875042 Pooja Gokhale R&D east Calcutta 29-Sep-91 13300
40 Richa Raje Sales south Manglore 29-Sep-91 10500
35 Arun Joshi Mktg south Manglore 5-May-92 927527 Rishi Malik Sales south Banglore 2-Jul-92 13125
56 Kajal Joglekar Sales west Nagpur 1-Oct-92 14875
Sort & filter Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 27/63
818283
8485
8687
888990
9192
939495
9697
9899
100
101102
103104
105106107
108109
110111
112113114
A B C D E F G H
57 Katti Surti Sales south chennai 9-May-93 1487536 Pooja Khetan Personnel west Surat 3-Sep-94 1172569 Pravin Joshi Personnel north delhi 3-Mar-95 14000
54 Vishal Virsinghani CCD east Gangtok 3-Mar-95 1575070 Reeta Naik Mktg east Calcutta 4-Mar-95 14000
71 Surendra Godse R&D west Panji 5-Mar-95 1400028 Mala Bhaduri R&D west Mumbai 19-Mar-95 10500
68 Mandakini Desai Sales north delhi 1-Dec-95 1400053 Mehul Sheth CCD west Nagpur 1-Dec-95 1575029 Hajra Hoonjan Admin north Jaipur 4-May-96 9625
41 Kirtikar Sardesai Admin east Darjeeling 14-Jun-97 787545 Kinnari Mehta R&D north Ferozepur 7-Jul-97 11375
33 Tapan Ghoshal CCD north Ambala 7-Jul-97 700099 Indu Shah Finance west Mumbai 30-Dec-97 1050088 Vinit Shrivastava Mktg west Pune 30-Dec-97 7875
85 Ruby Joseph R&D north Agra 14-Jan-98 700086 Sonia Sasan CCD north Jammu 15-Jan-98 17500
87 Jignesh Tripathi Mktg south Hydrabad 16-Jan-98 17500100 Rupesh Sawant Finance south Hydrabad 2-Feb-99 7875
3 Kuldeep Sharma Admin south Hydrabad 1-Mar-99 7000
83 Kunal Shah CCD north Aligarh 2-Mar-99 700084 Pinky Robert R&D east Calcutta 3-Mar-99 7000
25 Jasbinder Khurana R&D south Manglore 24-Apr-99 787520 Farhan Sadiq Mktg north Jaipur 5-Jun-99 5950
21 Satinder Kaur Sasan Mktg east Patna 6-Jun-99 787522 Suchita Panchal Mktg west Nasik 7-Jun-99 787523 Shazia Sheikh Mktg west Pune 8-Jun-99 7875
9 Pankaj Sutradhar Sales north Ambala 12-Dec-99 14875
1553825
Sort & filter Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 28/63
1
23456
7
89
1011121314151617
1819202122
2324
25262728293031
323334353637
3839
40
A B C D E F G H
Empcode First Name Last Name Dept Region Branch Hiredate Salary
17 Aalok Trivedi Admin east Cuttack 1-Mar-83 1575041 Kirtikar Sardesai Admin east Darjeeling 14-Jun-97 7875
75 Suraj Saksena Admin east Calcutta 25-Oct-88 1750054 Vishal Virsinghani CCD east Gangtok 3-Mar-95 1575055 Lalita Rao CCD east Guwahati 1-Nov-86 1575065 Timsi Desai CCD east Calcutta 26-Oct-88 1750013 Meera Lalwani Finance east Calcutta 11-Dec-84 1925010 Andre Fernendes Mktg east Darjeeling 20-Jul-77 1575021 Satinder Kaur Sasan Mktg east Patna 6-Jun-99 787570 Reeta Naik Mktg east Calcutta 4-Mar-95 14000
92 Payal Singhani Mktg east Gangtok 5-Apr-89 1732530 Aalam Qureshi Personnel east Patna 19-Oct-88 1417544 Shaheen Khan Personnel east Cuttack 17-Nov-90 1382542 Pooja Gokhale R&D east Calcutta 29-Sep-91 1330082 Kalpana Shirishkar R&D east Cuttack 1-Nov-88 17500
84 Pinky Robert R&D east Calcutta 3-Mar-99 70002 Suman Shinde Sales east Cuttack 1-Jan-77 17500
26 Bharat Shetty Sales east Cuttack 1-Oct-82 1925074 Nita Pandhya Sales east Darjeeling 24-Oct-88 1750091 Niki Digaria Sales east Calcutta 13-Nov-79 2275012 Shilpa Lele Admin north Jammu 1-Mar-83 2100029 Hajra Hoonjan Admin north Jaipur 4-May-96 962547 Vicky Joshi Admin north Kanpur 7-Feb-88 1575061 Anuradha Zha Admin north Agra 25-Nov-87 19250
33 Tapan Ghoshal CCD north Ambala 7-Jul-97 700034 Zarina Vora CCD north Lucknow 29-Sep-91 875052 Heena Godbole CCD north Lucknow 21-Oct-88 1925073 Laveena Shenoy CCD north Jaipur 23-Oct-88 1750083 Kunal Shah CCD north Aligarh 2-Mar-99 700086 Sonia Sasan CCD north Jammu 15-Jan-98 17500
98 Chetan Dalvi CCD north Delhi 11-Apr-89 1732538 Chitra Pednekar Finance north Aligarh 1-Oct-82 24500
39 Sheetal Dodhia Finance north delhi 1-Oct-82 24500
Subtotal Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 29/63
414243
4445
4647
484950
5152
535455
5657
585960
6162
6364
656667
6869
7071
727374
7576
77
7879
80
A B C D E F G H
4 Beena Mavadia Mktg north Delhi 24-Nov-79 1225019 Parvati Khanna Mktg north Mathura 13-Aug-86 1050020 Farhan Sadiq Mktg north Jaipur 5-Jun-99 5950
58 Maya Panchal Mktg north Agra 5-Nov-90 1750079 Sagar Bidkar Mktg north Delhi 29-Oct-88 17500
80 Dayanand Gandhi Mktg north Ferozepur 30-Oct-88 1750015 K. Sita Narayanan Personnel north Jammu 13-Dec-84 14875
16 Priya Shirodkar Personnel north Jaipur 14-Dec-84 1487566 Parul Shah Personnel north Agra 27-Oct-88 1750067 Uday Naik Personnel north Lucknow 28-Oct-88 20125
69 Pravin Joshi Personnel north delhi 3-Mar-95 140005 Seema Ranganathan R&D north Kanpur 4-Sep-89 21000
6 Julie D'Souza R&D north Mathura 4-Sep-88 124258 Neena Mukherjee R&D north Agra 4-Sep-89 12425
31 Giriraj Gupta R&D north Agra 1-Oct-82 15750
45 Kinnari Mehta R&D north Ferozepur 7-Jul-97 1137563 Waheda Sheikh R&D north Jammu 24-Oct-88 17500
77 Drishti Shah R&D north Delhi 27-Oct-88 1750085 Ruby Joseph R&D north Agra 14-Jan-98 700090 Priyanka Mehta R&D north Jaipur 6-Jan-80 22750
1 Raja Raymondekar Sales north Ferozepur 1-Jan-77 218759 Pankaj Sutradhar Sales north Ambala 12-Dec-99 14875
46 Jeena Baig Sales north Lucknow 29-Sep-91 1575051 Mario Fernandes Sales north Jammu 20-Oct-88 15750
62 Asha Trivedi Sales north Kanpur 26-Nov-87 1925068 Mandakini Desai Sales north delhi 1-Dec-95 1400089 Tejal Patel Sales north Aligarh 21-Feb-79 22750
3 Kuldeep Sharma Admin south Hydrabad 1-Mar-99 700059 Disha Parmar Admin south Banglore 14-Jan-87 19250
32 Ankur Joshi CCD south Manglore 1-Oct-82 1575049 Rakesh Kumar CCD south cochin 19-Oct-88 15750
81 Tara Phule CCD south chennai 31-Oct-88 1750014 Sheetal Desai Director south cochin 12-Dec-84 4900037 Shilpa Parikh Finance south Mysore 1-Oct-82 24500
100 Rupesh Sawant Finance south Hydrabad 2-Feb-99 787535 Arun Joshi Mktg south Manglore 5-May-92 9275
60 Geeta Darekar Mktg south Trivanadrum 24-Nov-87 19250
87 Jignesh Tripathi Mktg south Hydrabad 16-Jan-98 1750095 Kabir Vora Mktg south Banglore 8-Apr-89 17325
64 Veena Patil Personnel south Hydrabad 25-Oct-88 17500
Subtotal Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 30/63
818283
8485
8687
888990
9192
939495
9697
9899
100
101102
103104
105106107
108109
A B C D E F G H
25 Jasbinder Khurana R&D south Manglore 24-Apr-99 787572 Yamini Gupta R&D south Mysore 22-Oct-88 1750097 Pushpa Raut R&D south Trivanadrum 10-Apr-89 17325
27 Rishi Malik Sales south Banglore 2-Jul-92 1312540 Richa Raje Sales south Manglore 29-Sep-91 10500
57 Katti Surti Sales south chennai 9-May-93 1487593 Harsha Trivedi Sales south Banglore 6-Apr-89 17325
18 Aakash Dixit Admin west Nasik 1-Mar-83 1575048 Neha Joshi CCD west Baroda 19-Oct-88 1575053 Mehul Sheth CCD west Nagpur 1-Dec-95 15750
76 Nayeem Khan CCD west Panji 26-Oct-88 1750011 Sujay Madhrani Finance west Pune 21-Dec-85 14875
99 Indu Shah Finance west Mumbai 30-Dec-97 1050022 Suchita Panchal Mktg west Nasik 7-Jun-99 787523 Shazia Sheikh Mktg west Pune 8-Jun-99 7875
88 Vinit Shrivastava Mktg west Pune 30-Dec-97 787594 Radhika Kulkarni Mktg west Surat 7-Apr-89 17325
7 Deepak Jain Personnel west Pune 17-Aug-90 1382536 Pooja Khetan Personnel west Surat 3-Sep-94 1172543 Piyush Surti Personnel west Mumbai 19-Oct-88 15750
50 Ruheal Baig Personnel west Baroda 19-Oct-88 1575028 Mala Bhaduri R&D west Mumbai 19-Mar-95 10500
71 Surendra Godse R&D west Panji 5-Mar-95 1400096 Beena Sharma R&D west Nagpur 9-Apr-89 17325
24 Pooja Nimkar Sales west Nasik 21-May-80 1487556 Kajal Joglekar Sales west Nagpur 1-Oct-92 1487578 Deep Chhaya Sales west Mumbai 28-Oct-88 17500
1553825
Subtotal Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 31/63
1
23456
7
89
1011121314151617
1819202122
2324
25262728293031
323334353637
383940
A B C D E F G H
Empcode First Name Last Name Dept Region Branch Hiredate Salary
1 Raja Raymondekar Sales north Ferozepur 1-Jan-77 218752 Suman Shinde Sales east Cuttack 1-Jan-77 17500
3 Kuldeep Sharma Admin south Hydrabad 1-Mar-99 70004 Beena Mavadia Mktg north Delhi 24-Nov-79 122505 Seema Ranganathan R&D north Kanpur 4-Sep-89 210006 Julie D'Souza R&D north Mathura 4-Sep-88 124257 Deepak Jain Personnel west Pune 17-Aug-90 138258 Neena Mukherjee R&D north Agra 4-Sep-89 124259 Pankaj Sutradhar Sales north Ambala 12-Dec-99 14875
10 Andre Fernendes Mktg east Darjeeling 20-Jul-77 15750
11 Sujay Madhrani Finance west Pune 21-Dec-85 1487512 Shilpa Lele Admin north Jammu 1-Mar-83 2100013 Meera Lalwani Finance east Calcutta 11-Dec-84 1925014 Sheetal Desai Director south cochin 12-Dec-84 4900015 K. Sita Narayanan Personnel north Jammu 13-Dec-84 14875
16 Priya Shirodkar Personnel north Jaipur 14-Dec-84 1487517 Aalok Trivedi Admin east Cuttack 1-Mar-83 15750
18 Aakash Dixit Admin west Nasik 1-Mar-83 1575019 Parvati Khanna Mktg north Mathura 13-Aug-86 1050020 Farhan Sadiq Mktg north Jaipur 5-Jun-99 595021 Satinder Kaur Sasan Mktg east Patna 6-Jun-99 787522 Suchita Panchal Mktg west Nasik 7-Jun-99 787523 Shazia Sheikh Mktg west Pune 8-Jun-99 787524 Pooja Nimkar Sales west Nasik 21-May-80 14875
25 Jasbinder Khurana R&D south Manglore 24-Apr-99 787526 Bharat Shetty Sales east Cuttack 1-Oct-82 1925027 Rishi Malik Sales south Banglore 2-Jul-92 1312528 Mala Bhaduri R&D west Mumbai 19-Mar-95 1050029 Hajra Hoonjan Admin north Jaipur 4-May-96 962530 Aalam Qureshi Personnel east Patna 19-Oct-88 14175
31 Giriraj Gupta R&D north Agra 1-Oct-82 1575032 Ankur Joshi CCD south Manglore 1-Oct-82 1575033 Tapan Ghoshal CCD north Ambala 7-Jul-97 7000
Pivot Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 32/63
414243
4445
4647
484950
5152
535455
5657
585960
6162
6364
656667
6869
7071
727374
7576
77
7879
80
A B C D E F G H
34 Zarina Vora CCD north Lucknow 29-Sep-91 875035 Arun Joshi Mktg south Manglore 5-May-92 927536 Pooja Khetan Personnel west Surat 3-Sep-94 11725
37 Shilpa Parikh Finance south Mysore 1-Oct-82 2450038 Chitra Pednekar Finance north Aligarh 1-Oct-82 24500
39 Sheetal Dodhia Finance north delhi 1-Oct-82 2450040 Richa Raje Sales south Manglore 29-Sep-91 10500
41 Kirtikar Sardesai Admin east Darjeeling 14-Jun-97 787542 Pooja Gokhale R&D east Calcutta 29-Sep-91 1330043 Piyush Surti Personnel west Mumbai 19-Oct-88 15750
44 Shaheen Khan Personnel east Cuttack 17-Nov-90 1382545 Kinnari Mehta R&D north Ferozepur 7-Jul-97 11375
46 Jeena Baig Sales north Lucknow 29-Sep-91 1575047 Vicky Joshi Admin north Kanpur 7-Feb-88 1575048 Neha Joshi CCD west Baroda 19-Oct-88 15750
49 Rakesh Kumar CCD south cochin 19-Oct-88 1575050 Ruheal Baig Personnel west Baroda 19-Oct-88 15750
51 Mario Fernandes Sales north Jammu 20-Oct-88 1575052 Heena Godbole CCD north Lucknow 21-Oct-88 1925053 Mehul Sheth CCD west Nagpur 1-Dec-95 15750
54 Vishal Virsinghani CCD east Gangtok 3-Mar-95 1575055 Lalita Rao CCD east Guwahati 1-Nov-86 15750
56 Kajal Joglekar Sales west Nagpur 1-Oct-92 1487557 Katti Surti Sales south chennai 9-May-93 14875
58 Maya Panchal Mktg north Agra 5-Nov-90 1750059 Disha Parmar Admin south Banglore 14-Jan-87 1925060 Geeta Darekar Mktg south Trivanadrum 24-Nov-87 19250
61 Anuradha Zha Admin north Agra 25-Nov-87 1925062 Asha Trivedi Sales north Kanpur 26-Nov-87 19250
63 Waheda Sheikh R&D north Jammu 24-Oct-88 1750064 Veena Patil Personnel south Hydrabad 25-Oct-88 17500
65 Timsi Desai CCD east Calcutta 26-Oct-88 1750066 Parul Shah Personnel north Agra 27-Oct-88 1750067 Uday Naik Personnel north Lucknow 28-Oct-88 20125
68 Mandakini Desai Sales north delhi 1-Dec-95 1400069 Pravin Joshi Personnel north delhi 3-Mar-95 14000
70 Reeta Naik Mktg east Calcutta 4-Mar-95 14000
71 Surendra Godse R&D west Panji 5-Mar-95 1400072 Yamini Gupta R&D south Mysore 22-Oct-88 17500
73 Laveena Shenoy CCD north Jaipur 23-Oct-88 17500
Pivot Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 33/63
818283
8485
8687
888990
9192
939495
9697
9899
100
101102
103104
105106107
A B C D E F G H
74 Nita Pandhya Sales east Darjeeling 24-Oct-88 1750075 Suraj Saksena Admin east Calcutta 25-Oct-88 1750076 Nayeem Khan CCD west Panji 26-Oct-88 17500
77 Drishti Shah R&D north Delhi 27-Oct-88 1750078 Deep Chhaya Sales west Mumbai 28-Oct-88 17500
79 Sagar Bidkar Mktg north Delhi 29-Oct-88 1750080 Dayanand Gandhi Mktg north Ferozepur 30-Oct-88 17500
81 Tara Phule CCD south chennai 31-Oct-88 1750082 Kalpana Shirishkar R&D east Cuttack 1-Nov-88 1750083 Kunal Shah CCD north Aligarh 2-Mar-99 7000
84 Pinky Robert R&D east Calcutta 3-Mar-99 700085 Ruby Joseph R&D north Agra 14-Jan-98 7000
86 Sonia Sasan CCD north Jammu 15-Jan-98 1750087 Jignesh Tripathi Mktg south Hydrabad 16-Jan-98 1750088 Vinit Shrivastava Mktg west Pune 30-Dec-97 7875
89 Tejal Patel Sales north Aligarh 21-Feb-79 2275090 Priyanka Mehta R&D north Jaipur 6-Jan-80 22750
91 Niki Digaria Sales east Calcutta 13-Nov-79 2275092 Payal Singhani Mktg east Gangtok 5-Apr-89 1732593 Harsha Trivedi Sales south Banglore 6-Apr-89 17325
94 Radhika Kulkarni Mktg west Surat 7-Apr-89 1732595 Kabir Vora Mktg south Banglore 8-Apr-89 17325
96 Beena Sharma R&D west Nagpur 9-Apr-89 1732597 Pushpa Raut R&D south Trivanadrum 10-Apr-89 17325
98 Chetan Dalvi CCD north Delhi 11-Apr-89 1732599 Indu Shah Finance west Mumbai 30-Dec-97 10500
100 Rupesh Sawant Finance south Hydrabad 2-Feb-99 7875
Pivot Pragati Software Pvt. Ltd. Confidential
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 34/63
Product Jun Jul AugTobacco 1900 1997 1873Fast Food 1867 2025 1788Cold Drinks 1831 1824 1733Biscuits 1654 1995 1797
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 35/63
Product Jan Feb MarchCold Drinks 1201 1020 1410Fast Food 1472 1365 931Biscuits 1090 1311 1117Tobacco 994 1169 1068
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 36/63
Product Jan Feb MarchBiscuits 618 742 862Cold Drinks 618 616 782Tobacco 746 781 868Fast Food 827 856 757
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 37/63
Product Jan Feb MarchFast Food 328 435 433Biscuits 306 480 479Tobacco 456 481 498Cold Drinks 302 425 426
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 38/63
Create a summary report of the total number of Sales in the year from North, East, West, and South.
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 39/63
Emp_Code Birthdate Salary UserID Region Area
1.Give validation to Emp_code so that the user can enter only between 1 to 102.Give validation to Birthdate so that the date after 1980 should not be accepted3.Give validation to Salary so that the salary is between 5000-150004.Give validation to User_Id so that the userid is minimum 5 character long5.Give validation to Region so that the user will select the region from list6.Depending upon the region selected the user will get the respective Area
VALIDATION SHEET
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 40/63
East West North SouthKurla Andheri Virar ChurchgateGhatkopar Santacruz Bhyander Nariman pointVashi Vileparle Mira Road ColabaThane Khar Vasai KalbadeviMulund Bandra Nallasopara Masjid Bunder
Chembur Gogeshwari Naigaon Dockyard
Region Details
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 41/63
FormatPositive number in Green colorNegative Number In Red colorNo color to 0Color the textGreen for Positive, Red for Negative, No color to 0, Hide textHide text from the cell
Color red if value is above 100 and green if value is less than 100Represent digits up to 3 decimal placesAdd the unit text with the number, e.g. 1000 kmConvert the date in to day nameconvert the date in to month namedisplay date as Saturday, 21st - August - 2010Display number as a fraction of 1000
Display 0 before numberDisplay the number in hundered separator
Display any series of single character after the value in the cell till the cell endsRepresent a number with a text
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 42/63
Value Code0.00[green];-#;#;@#;0.00[red];0;@#;#;0;@#;#;0;@[yello]0.00[green];0.00[red];0;;;;
[Green][>=100];[Red][<=100]???.???General" km"ddddmmmmdddd, dd"st" - mmmm - yyy??/1000
"0"General00","00","00","00","00","000
General*![<1000]"Do Not Accept";[>=1000]"Accept";General
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 43/63
Current Date 4-May-12 Last Modified DateCurrent Time 5/4/2012 1:32 Last Modified Time
FirstName Birthdate Date Month YearStan 15-Jul-73Melwyn 30-Oct-57Sachin 28-Aug-77Rajesh 1-Dec-63Tulsidas 31-May-58Rajesh 27-Aug-71Heena 9-Nov-71Yashraj 15-Jan-68
Rajeev 1-Jul-79Ram 1-Jun-56Piyush 5-May-63Dhiren 7-Sep-54Sudesh 27-May-80Jagjit 27-May-66Ruffina 20-Aug-60Viratji 9-May-81
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 44/63
23/Sep/10
4:55 PM
Age Week day Month datedif dif months Dif Year Months after year
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 45/63
Days after months date of retirement
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 46/63
SAchin Tendulkar 101SAchin
TEXT FUNCTIONS
Text Function Description Example
Upper Converts a text into uppercaseLower Converts a text into lowercaseProper Converts a text into First character as capitalLeft Extracts characters from leftRight Extracts characters from rightMid Extracts caracters from middleConcatenate Join text
Len Find the length of stringExact Checks for two text are same or notText Changes value to text format
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 47/63
Financial functions and what-if anlysisMonths PPMT
1
Loan Amount 100000Rate of Intrest 10.50%Payment /month 36PMT [EMI] 100000 150000 200000
U s i n g
D a t a T a
b l e 12
U s i n g
D a t a T a
b l e
24
U s i n g
D a t a T a
b l e
36
U s i n g
D a t a T a
b l e
48
U s i n g
D a t a T a
b l e
60 U s i n g
D a t a T a
b l e
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 48/63
IPMT PMT
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 49/63
Quantity Variable cost Fixed Cost Total cost Cost per unit Unit Sale priceProduct A 5000 26.00 6944.444444 136944.4444 27.39 31.00
Product B 1000 20.00 1388.888889 21388.88889 21.39 22.00
Product C 1200 15.00 1666.666667 19666.66667 16.39 17.00
Total Profit 7200 10000 178000
* Fixed Cost Apportioned on the basis of Quantity
Constraints
Product A - Quantity cannot be produced more than 5500Product B - Quantity cannot be produced more than 2500
Product C - Quantity cannot be produced more than 2500
Product A - Price cannot be more than 35 35
Product B - Price cannot be more than 23 23
Product C - Price cannot be more than 13 18
Product A - Variable Cost cannot be less than 25
Product B - Variable Cost cannot be less than 18
Product C -Variable Cost cannot be less than 12
Place your cursor at Cell I5
goto Tools, Solver and check the steps one by one
Click on solve and select the answer report option and then click OK
You will find one more sheet which gives the detail about the changes made
Changing Cells
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 50/63
Profit per unit Profit3.61 18055.56
0.61 611.1111
0.61 733.3333
19400
TargetCell
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 51/63
Invoice No Address
Date
M/s
Sr.No Product Qty Rate Amount
Less: Discount
Net Amount >>
TAX-INVOICE
Star Track Corporation LtdStation Road, Mumbai-05
Gross Amount >>
Add: VAT (14%)
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 52/63
12345
678910
111213141516
17
18
19
20
212223242526
A B C D E F G
2006 2007 2008 2009 2010 2011
Sales 50,000.00 54,500.00 59,405.00 64,751.45 70,579.08 76,931.20Other Income 25,000.00 28,000.00 31,360.00 35,123.20 39,337.98 44,058.54Gross Income 75,000.00 82,500.00 90,765.00 99,874.65 109,917.06 120,989.74
Expense
Tel. Exp 3,200.00 3,264.00 3,329.28 3,395.87 3,463.78 3,533.06Tra. Exp 3,500.00 3,570.00 3,641.40 3,714.23 3,788.51 3,864.28Mis. Exp 2,500.00 2,537.50 2,575.56 2,614.20 2,653.41 2,693.21Total Exp 9,200.00 9,371.50 9,546.24 9,724.29 9,905.70 10,090.55
Net Income 65,800.00 73,128.50 81,218.76 90,150.36 100,011.36 110,899.19
Current Best Worst
Sales 9% 13% 5%Other Incom 12% 18% 8%Tel. Exp 2% 3% 5%Tra. Exp 2% 4% 5%Mis. Exp 1.50% 1.50% 1.50%
Scenarios
Scenarios Pragati Software Pvt. Ltd. Page 52
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 53/63
12345
678910
111213141516
17
18
19
20
212223242526
H I
521,208.17
Scenarios Pragati Software Pvt. Ltd. Page 53
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 54/63
FirstName Birthdate Sex M_Status Qualification Profession EarningPHD
FirstName Birthdate Sex M_Status Qualification Profession EarningRuffina 20-Aug-60 Female Married Graduate Networker 150000
Boneca 22-Jun-79 Female Single PHD Professional 120000Venitha 26-Sep-58 Female Married Graduate Employee 90000Dinesh 21-Jan-83 Male Single Graduate Student 0Gururaj 1-Jan-67 Male Married M.Sc Professional 95000D 21-Aug-52 Male Married Graduate Employee 85000Sharadchandra 24-May-42 Male Married Graduate Consultant 105000
Ashok 14-Mar-90 Male Married PHD Businessman 200000Yogesh 17-May-84 Male Single Under-Graduate Student 0Nitin 8-Jan-64 Male Married Btech Musician 250000
1. How many member have the Graduate Qualification ?
Ans:
2. How many member are male and graduate ?
Ans:
3. Find the total earning of all Female member ?
Ans:
4.Find the maximum earning ?
Ans:
5. Find the minimum earning for graduate qualification?
Ans:
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 55/63
1
23
45
67
8
91011
1213
A B C D E F G H I J K
Product East West North South Total
Sales % Sales % Sales % Sales % Sales %
Thums Up 2.66 1.24 1.12 1.69 6.71Uncle Chipps 13.11 9.74 13.28 19.23 55.36Maggi 10.4 11.13 12.44 14.92 48.89Amul Choc. 171.68 115.89 187.29 135.05 609.91Total
Mixed cell Pragati Software Pvt. Ltd.
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 56/63
RAjesh Deshmukh 101
FUNCTIONS
Text Function Description Example
Upper Converts a text into uppercase RAJESHLower Converts a text into lowercase rajeshProper Converts a text into First character as capital RajeshLeft Extracts characters from left RAjRight Extracts characters from right eshMid Extracts caracters from middle jes
Concatenate Join text Hello RAjeshDeshmukhLen Find the length of string 6Exact Checks for two text are same or not TRUESearch Search for position of text 7Replace Changes the text India was my countryText Changes value to text format $101.00
Maths Functions Description Example
Abs Converts negative number to positive number 25Int Discards decimals 1236Mod Gives the remainder from division 3Sqrt Gives root value 9Power Gives the number raised to power of 8Round Rounds to nearest number 1236.57Trunc Truncates to lower number 1236.56
Statistical Functions Description Example
CountA Counts text and numbers 3Countblank Counts blank cells 1Small Finds the nth small value 20Large Finsd the nth large value 40
Date Functions Description ExampleDate Adds day, month, year 1/14/2004Today Current date 5/4/2012
Now Current date and time 5/4/2012 1:32Day Extracts day fromCurrent date 4Month Extracts month fromCurrent date 5Year Extracts year from Curent date 2012Time Adds Hr, min, second 5/4/2012 1:32Hour Extracts Hours from given time 1
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 57/63
Minute Extracts Minutes from given time 32Second Extracts Seconds from given time 3 #######
Information Functions Description Example
IsBlank Returns true if blank cell or false if non blank cell Blank cell
IsError Returns error value #NAIsnumber Returns true if data is number 450.75Istext Returns true if data is text apple
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 58/63
12 10Good 20Bad 30
4050
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 59/63
apple
medicine#ref1#NA
450.75
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 60/63
Select cells, columns, rows, or objects in worksheets and workbooks by using shortcut keys
Select the current region around the active cell (the current region is an areaenclosed by blank rows and blank columns) CTRL+SHIFT+* (ASTERISK)Extend the selection by one cell SHIFT+ arrow keyExtend the selection to the last nonblank cell in the same column or row as theactive cell CTRL+SHIFT+ arrow key
Extend the selection to the beginning of the row SHIFT+HOMEExtend the selection to the beginning of the worksheet CTRL+SHIFT+HOME
Extend the selection to the last cell used on the worksheet (lower-right corner) CTRL+SHIFT+ENDSelect the entire column CTRL+SPACEBARSelect the entire row SHIFT+SPACEBARSelect the entire worksheet CTRL+AIf multiple cells are selected, select only the active cell SHIFT+BACKSPACEExtend the selection down one screen SHIFT+PAGE DOWNExtend the selection up one screen SHIFT+PAGE UPWith an object selected, select all objects on a sheet CTRL+SHIFT+SPACEBARAlternate between hiding objects, displaying objects, and displaying
placeholders for objects CTRL+6
Show or hide the Standard toolbar CTRL+7In End mode, to PressTurn End mode on or off ENDExtend the selection to the last nonblank cell in the same column or row as theactive cell END, SHIFT+ arrow key
Extend the selection to the last cell used on the worksheet (lower-right corner) END, SHIFT+HOMEExtend the selection to the last cell in the current row; this keystroke isunavailable if you selected the Transition navigation keys check box on theTransition tab (Tools menu, Options command) END, SHIFT+ENTERWith SCROLL LOCK on, to PressTurn SCROLL LOCK on or off SCROLL LOCK
Scroll the screen up or down one row UP/DOWN ARROWScroll the screen left or right one column LEFT.RIGHT ARROWExtend the selection to the cell in the upper-left corner of the window SHIFT+HOMEExtend the selection to the cell in the lower-right corner of the window SHIFT+END
Select cells with special characteristics by using shortcut keys
SHORT CUT FOR EXCEL
Page 60 of 63
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 61/63
Select the current region around the active cell (the current region is an areaenclosed by blank rows and blank columns) CTRL+SHIFT+* (ASTERISK)Select the current array, which is the array that the active cell belongs to CTRL+/ Select all cells with comments CTRL+SHIFT+O (the letter O)
Select cells whose contents are different from the comparison cell in each row
(for each row, the comparison cell is in the same column as the active cell) CTRL+\ Select cells whose contents are different from the comparison cell in eachcolumn (for each column, the comparison cell is in the same row as the activecell) CTRL+SHIFT+|Select only cells that are directly referred to by formulas in the selection CTRL+[Select all cells that are directly or indirectly referred to by formulas in theselection CTRL+SHIFT+{Select only cells with formulas that refer directly to the active cell CTRL+]
Select all cells with formulas that refer directly or indirectly to the active cell CTRL+SHIFT+}Select only visible cells in the current selection ALT+SEMICOLON
Move and scroll on a worksheet or workbook by using shortcut keys
Move one cell in a given direction Arrow keyMove to the edge of the current data region CTRL+ arrow keyMove between unlocked cells on a protected worksheet TABMove to the beginning of the row HOMEMove to the beginning of the worksheet CTRL+HOMEMove to the last cell on the worksheet CTRL+ENDMove down one screen PAGE DOWNMove up one screen PAGE UPMove one screen to the right ALT+PAGE DOWNMove one screen to the left ALT+PAGE UP
Move to the next sheet in the workbook CTRL+PAGE DOWNMove to the previous sheet in the workbook CTRL+PAGE UPMove to the next workbook or window CTRL+F6 or CTRL+TABMove to the previous workbook or window CTRL+SHIFT+F6Move to the next pane F6Move to the previous pane SHIFT+F6Scroll to display the active cell CTRL+BACKSPACEIn End mode, to PressTurn End mode on or off ENDMove by one block of data within a row or column END, arrow keyMove to the last cell on the worksheet END, HOMEMove to the last cell to the right in the current row that is not blank END, ENTER
With SCROLL LOCK turned on, to PressTurn SCROLL LOCK on or off SCROLL LOCKMove to the cell in the upper-left corner of the window HOMEMove to the cell in the lower-right corner of the window ENDScroll one row up or down UP/DOWN ARROWScroll one column left or right LEFT/RIGHT ARROW
Format data by using shortcut keys
Page 61 of 63
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 62/63
Display the Style command (Format menu) ALT+' (APOSTROPHE)Display the Cells command (Format menu) CTRL+1Apply the General number format CTRL+SHIFT+~Apply the Currency format with two decimal places (negative numbers appearin parentheses) CTRL+SHIFT+$Apply the Percentage format with no decimal places CTRL+SHIFT+%
Apply the Exponential number format with two decimal places CTRL+SHIFT+^Apply the Date format with the day, month, and year CTRL+SHIFT+#
Apply the Time format with the hour and minute, and indicate A.M. or P.M. CTRL+SHIFT+@ Apply the Number format with two decimal places, 1000 separator, and – for
negative values CTRL+SHIFT+!Apply the outline border CTRL+SHIFT+&Remove all borders CTRL+SHIFT+_ Apply or remove bold formatting CTRL+BApply or remove italic formatting CTRL+IApply or remove an underline CTRL+UApply or remove strikethrough formatting CTRL+5
Hide rows CTRL+9Unhide rows CTRL+SHIFT+(Hide columns CTRL+0 (ZERO)Unhide columns CTRL+SHIFT+)
Print and preview a document by using shortcut keys
Display the Print command (File menu) CTRL+PMove around the page when zoomed in Arrow keysMove by one page when zoomed out PAGE UP or PAGE DOWNMove to the first page when zoomed out CTRL+UP ARROWMove to the last page when zoomed out CTRL+DOWN ARROW
Keys for menus
Show a shortcut menu SHIFT+F10Make the menu bar active F10 or ALTShow the program icon menu (on the program title bar) ALT+SPACEBARSelect the next or previous command on the menu or submenu DOWN/UP ARROWSelect the menu to the left or right, or, with a submenu visible, switch betweenthe main menu and the submenu LEFT/RIGHT ARROWSelect the first or last command on the menu or submenu HOME or ENDClose the visible menu and submenu at the same time ALT
Close the visible menu, or, with a submenu visible, close the submenu only ESC
Edit data by using shortcut keys
Edit the active cell and complete F2 + EnterCancel an entry in the cell or formula bar ESCEdit the active cell and then clear it, or delete the preceding character in theactive cell as you edit the cell contents BACKSPACE
Page 62 of 63
8/3/2019 Copy of Excel Training File
http://slidepdf.com/reader/full/copy-of-excel-training-file 63/63
Paste a name into a formula F3Enter a formula as an array formula CTRL+SHIFT+ENTER
Display the Formula Palette after you type a valid function name in a formula CTRL+AInsert the argument names and parentheses for a function, after you type avalid function name in a formula CTRL+SHIFT+A
Insert, delete, and copy a selection by using shortcut keys
Copy the selection CTRL+CPaste the selection CTRL+VCut the selection CTRL+XClear the contents of the selection DELETEInsert blank cells CTRL+SHIFT+PLUS SIGNDelete the selection CTRL+ –
Undo the last action CTRL+Z
Move within a selection by using shortcut keys
Move from top to bottom within the selection (down), or in the direction that isselected on the Edit tab (Tools menu, Options command) ENTERMove from bottom to top within the selection (up), or opposite to the directionthat is selected on the Edit tab (Tools menu, Options command) SHIFT+ENTERMove from left to right within the selection, or move down one cell if only onecolumn is selected TABMove from right to left within the selection, or move up one cell if only onecolumn is selected SHIFT+TABMove clockwise to the next corner of the selection CTRL+PERIODMove to the right between nonadjacent selections CTRL+ALT+RIGHT ARROWMove to the left between nonadjacent selections CTRL+ALT+LEFT ARROW