Assignment 1

Introduction to Excel

Concatenate
nt 1

Around Excel Sheet Age Calculation

Upper,Proper,Lower,Trim ShortCuts in Excel

Intoduction to MS Excel

About Excel

Microsoft Excel is a Spreadsheet program. Spreadsheets allow you

to keep track of data, create charts based from data, and perform complex calculations. Just
like a book ledger, spreadsheets store information in columns and rows. You can have up to
256 columns and 65,536 rows per worksheet in excel 2003 and 1048576 rows and 16384 columns in excel 2007,2010,2013.

What is workbook?
A workbook is a spreadsheet file. By default, each workbook in Excel contains three pages or worksheets.

What is worksheet or sheets?

An Excel worksheet is a single spreadsheet that contains cells organized by rows and columns. A worksheet begins with row nu
A. Each cell can contain a number, text or formula. A cell can also reference another cell in the same worksheet, the same work
workbook.

What is Row?

Rows run horizontally in an Excel worksheet.

Each row is identified by a number in the row header.
There are 1048576 rows in each Excel worksheet.

What is Column?

Columns run vertically in a worksheet.

Each column is identified by a letter in the column header starting with Column A and running through to Column XFD.
There are 16384 columns in each excel worksheet.

What is Cell?
The intersection point between a row and a column is called a cell.

what is the heirarchy we follow in Excel?

 Home

n excel 2007,2010,2013.

orksheet begins with row number one and column

e worksheet, the same workbook or a different

ugh to Column XFD.

Around Excel Sheet

File Menu Quick Acces Toolbar

Adress Bar &

Name Box

Row Header

Insertion Point
Formula Bar
Active Cell

Rows

Sheet Tabs
Sheet Tabs
 Home

Title Bar
Menu Bar

Columns
mula Bar

Rows Cell

Horizontal Scroll Bar

Horizontal Scroll Bar
 Ribbon

Column Header

Vertical Scroll Bar

Status Bar
Status Bar
Zoom In /Out
Age Calculation : Datedif Function

Datedif Function
The DATEDIF function computes the difference between two dates in a variety of different intervals, such as the number of yea

Parameters:-
1 Date of Joining, Date of Birth or Start Date
2 End Date or Today's Date
3 Interval

2 =Datedif(Start Date, End Date, "Interval")

Interval Meaning Description

M Months Complete calendar months between the dates.

D Days Number of days between the dates.

Y Years Complete calendar years between the dates.

Complete calendar months between the dates as if they

YM Months Excluding Years were of the same year.

Complete calendar days between the dates as if they were

YD Days Excluding Years of the same year.

Complete calendar days between the dates as if they were

MD Days Excluding Years And Months of the same month and same year.

Examples:-
Start Date End Date
3/16/2001 9/5/2015

Years =DATEDIF(B26,C26,"Y") Output 14

Year Between Months =DATEDIF(B26,C26,"YM") 5

Months Between Days =DATEDIF(B26,C26,"MD") 20

Total Months =DATEDIF(B26,C26,"M") 173

Total Days =DATEDIF(B26,C26,"D") 5286

Year Between Days =DATEDIF(B26,C26,"YD") 173

Working:-

Start Date End Date Years Months Days Months

3/12/1987 9/6/2015 28 5 25 341

Rohit Gupta Rohit Gupta

28 Years 5 Months 25 Days 28 Years5Months25Days

Start Date End Date

3/12/1987 9/6/2015 28 years 5 months 25 days
4/11/1987 9/6/2015 28 years 4 months 26 days
5/11/1987 9/6/2015 28 years 3 months 26 days
6/10/1987 9/6/2015 28 years 2 months 27 days
7/10/1987 9/6/2015 28 years 1 months 27 days
8/9/1987 9/6/2015 28 years 0 months 28 days
9/8/1987 9/6/2015 27 years 11 months 29 days
10/8/1987 9/6/2015 27 years 10 months 29 days
11/7/1987 9/6/2015 27 years 9 months 30 days

Date of Joining Aging year month days

4/13/1990 7/4/2019 29 350 2 10674
4/18/1989 7/4/2019 30 362 2 11034
12/19/1990 7/4/2019 28 342 6 10424
8/20/1992 7/4/2019 26 322 10 9814
4/22/1994 7/4/2019 25 302 2 9204
12/23/1995 7/4/2019 23 282 6 8594
8/24/1997 7/4/2019 21 262 10 7984
4/26/1999 7/4/2019 20 242 2 7374
12/26/2000 7/4/2019 18 222 6 6764
8/28/2002 7/4/2019 16 202 10 6154

:eft
E:\Rohit Data\Classes Data\Excel Classes\Excel Assignments\New A
 Home
uch as the number of years, months, or days between the dates

14 5 20

14

20

173

5286

173
 Days Year Between dAys Y ""
10405 178 123
dsasd
1313

28
onths25Days

28 5 24

28 years 5 months 25 days

28 years 4 months 26 days
28 years 3 months 26 days
28 years 2 months 27 days
28 years 1 months 27 days
28 years 0 months 28 days
27 years 11 months 29 days
27 years 10 months 29 days
27 years 9 months 30 days

days
21 29 year 2months 21days
16
15
14
12
11
10
8
8
6
xcel Assignments\New Assignments\[Assignment-1 Working.xlsx]Age Calculation
Home
Concatenate

one of the text functions, to join two or more text strings into one string.

Syntax:  CONCATENATE(text1, [text2], ...)

Examples:

First Name Last Name Function Output

Manisha Agarwal =CONCATENATE(A10," ",B10) Manisha Agarwal

Years Months Days

23 6 12

Aging =A13&" "&A12&" "&B13&" "&B12&" "&C13&" "&C12

Output 23 Years 6 Months 12 Days

Note:- You can used "&" to join two words

Working:-

Start Date End Date Years Months Days Months

3/12/1987 9/6/2015 28 5 25 341

28 Years 5 Months 25 Days

Employee ID Date of Joining Aging

30015 12-Mar-87 32 Years 3 Months 22 Days
30006 6-Jan-88 31 Years 5 Months 28 Days
30008 1-Nov-88 30 Years 8 Months 3 Days
30004 28-Aug-89 29 Years 10 Months 6 Days
30015 24-Jun-90 29 Years 0 Months 10 Days
30019 20-Apr-91 28 Years 2 Months 14 Days
30013 14-Feb-92 27 Years 4 Months 20 Days
30002 10-Dec-92 26 Years 6 Months 24 Days
30016 6-Oct-93 25 Years 8 Months 28 Days
30007 2-Aug-94 24 Years 11 Months 2 Days
30014 29-May-95 24 Years 1 Months 5 Days
 Home

Manisha Agarwal

Manisha Agarwal

Days Year Between dAys

10405 178
11802

7/4/2019
UPPER, Proper, lower & Trim

The LOWER function will convert all letters in a text string to lowercase

Syntax:  Lower(Text)

The UPPER function will convert all letters in a text string to uppercase.

Syntax:  Upper(Text)

The PROPER function will convert a text string to proper case. That is, the first letter in each word in uppercase, and all other le

Syntax:  Proper(Text)

The TRIM function returns a string with extra spaces, starting spaces and ending spaces removed. The CLEAN function removes

Syntax:  Trim(Text)

Examples:

Hi how r U doing? Hi how r U doing? Hi how r U doing?

=LOWER(A19) =UPPER(C19) =PROPER(E19)

Output Output Output

hi how r u doing? HI HOW R U DOING? Hi How R U Doing?

HI HOW R U DOING? hi how r u doing? Hi How R U Doing?

Working:-

Hi how r U doing? Hi how r U doing? Hi how r U doing?

HI HOW R U DOING? hi how r u doing? Hi How R U Doing?

 Home

uppercase, and all other letters in lowercase.

e CLEAN function removes nonprintable characters from a string.

Hi how r U doing?

=Trim(G19)

Output

Hi how r U doing?

Hi how r U doing?

Hi how r U doing? Abhay

Hi how r U doing? Abhay

Home
ShortCuts in Excel

1 Ctrl+PgDn

2 Ctrl+PgUp

2 trl+Shift+&

3 Ctrl+Shift_

3 Ctrl+Shift+%

4 Ctrl+1

4 Ctrl+2

5 Ctrl+3

5 Ctrl+4

6 Ctrl+5

6 Ctrl+9

7 Ctrl+0

7 Ctrl+A
 8 Ctrl+C

8 Ctrl+D

9 Ctrl+F

9 Ctrl+G

10 Ctrl+H

10 Ctrl+I

11 Ctrl+K

11 Ctrl+L

12 Ctrl+N

12 Ctrl+O

13 Ctrl+P

13 Ctrl+R

14 Ctrl+S

14 Ctrl+T
 15 Ctrl+U

15 Ctrl+V

16 Ctrl+W

16 Ctrl+X

17 Ctrl+Y

17 Ctrl+Z

Note: Use Hot Keys Shortcuts by pressing ALT Button

Switches between worksheet tabs, from left-to-right.

Switches between worksheet tabs, from right-to-left.

Applies the outline border to the selected cells.

Removes the outline border from the selected cells.

Applies the Percentage format with no decimal places.

Displays the Format Cells dialog box.

Applies or removes bold formatting.

Applies or removes italic formatting.

Applies or removes underlining.

Applies or removes strikethrough.

Hides the selected rows.

Hides the selected columns.

Selects the entire worksheet.

Copies the selected cells.

Uses the Fill Down command to copy the contents and

format of the topmost cell of a selected range into the cells
below.
Displays the Find and Replace dialog box, with the Find tab
selected.

Displays the Go To dialog box.

Displays the Find and Replace dialog box, with

the Replace tab selected.

Applies or removes italic formatting.

Displays the Insert Hyperlink dialog box for new hyperlinks

or the Edit Hyperlink dialog box for selected existing
hyperlinks.
Displays the Create Table dialog box.

Creates a new, blank workbook.

Displays the Open dialog box to open or find a file.

Displays the Print tab in Microsoft Office Backstage view.

Uses the Fill Right command to copy the contents and

format of the leftmost cell of a selected range into the cells to
the right.
Saves the active file with its current file name, location, and
file format.

Displays the Create Table dialog box.

Applies or removes underlining.

Inserts the contents of the Clipboard at the insertion point

and replaces any selection. Available only after you have cut
or copied an object, text, or cell contents.
Closes the selected workbook window.

Cuts the selected cells.

Repeats the last command or action, if possible.

Uses the Undo command to reverse the last command or to

delete the last entry that you typed.
 Home

errer
Date Number Currency Time Percentage
6-Sep-15 40,791.00 ₹ 40,791.00 0:00 4079100%

6-Sep-15 40,791.00 ₹ 40,791.00 0:00 4079100%

6-Sep-15 40,791.00 ₹ 40,791.00 0:00 4079100%

Home