Spread Sheet

MS Excel with Basic Definitions

Microsoft Excel is an electronic spreadsheet. You can use it to organize your data
into rows and columns. You can also use it to perform mathematical calculations
quickly. You can also create charts and perform many different types of
calculations using built-in formulas.

Definitions

1. Workbook vs. worksheet

When you open Excel, a new file is created called book 1. It is called a “book”
because it is a workbook that is initially made up of three worksheets.

Add sheets to your workbook:

o Insert > worksheet, or click on the start button appears at the bottom or press
(SHIFT+F11)

Delete worksheets
o By right-clicking on the tab of the worksheet, you wish to delete then selecting
“delete”

Rearrange them:
o By clicking on the worksheet tab and dragging it to the location you desire

Renaming:
o By double-clicking on the worksheet title.

2. Cell
Cells are the basic rectangular building blocks of a spreadsheet. The intersection
between row and columns is known as a cell. The active cell is shown in the name
box

3. Rows
Rows travel horizontally and are assigned letters.

4. Column
Columns travel vertically and are assigned letters
Maximum number of rows and columns
Rows 1048576
Columns 16384
You can also move to the first and last row and columns by pressing CTRL +
ARROW KEYS.

60
 Creating and saving Spreadsheets
Starting Excel
Click the start button- All programs- click Microsoft office Excel 2007

Creating a new Spreadsheet

Click start and click programs and click Microsoft office and click Microsoft excel 2007.
To create a new, blank workbook:
i) Left-click the Microsoft office button
ii) Select new. The new workbook dialog box opens and blank workbook is
highlighted by default
iii) Click the Create button or press CTRL + N

Opening an existing Spreadsheet

i) Click the Microsoft Office Button and click open or press CTRL+O

Saving a Spreadsheet
i) Click the Microsoft Office Button and click save or save as
ii) Press CTRL+S on the keyboard or click the file icon on the quick access
toolbar.

Close a Spreadsheet
i) Click the office button
ii) Click close or press alt+f4

Entering and Editing Text in Your Documents

Entering data

There are different ways to enter data in excel: in an active cell or formula bar.

 To enter data in an active cell:

o Click in the cell where you want the data
o Begin typing

Edit a cell

After you enter data into a cell, you can edit the data by pressing F12 while you are in the cell
you wish to edit or double click in the cell

61
Alignments:

- In excel 2007, you can change the horizontal and vertical alignment of cell data.
By default, the text is left-aligned and values or dates are right-aligned.
- Follow these steps to change the horizontal or vertical alignment of cell data:
a. Select the cells you want to align with.

b. On the Home tab, select a horizontal alignment:

 Align text left: horizontally aligns the data along the left edge of the cell.
 Center: centers the data horizontally in the middle of the cell. If you modify
the width of the columns; the data remains centered on the new column
width.
 Align text right: horizontally aligns the data along the right edge of the
cell.

c. Select vertical alignment:

o Top align: aligns the data vertically along the top edge of the cell
o Middle align: centers the data vertically in the cell
o Bottom align: this is the default option and aligns data along the bottom edge of
the cell.

Change the orientation of cells

 Select cells that you will change the text orientation in.
 Click the orientation button under the home tab
 In the dropdown list of the orientation button, select one of the orientation styles.

You can get more orientation styles in the format cells dialog box by clicking the format cells
alignment item on the list. Go to the orientation section under the alignment tab in the format
cells dialog box. Enter the orientation degree to change the orientation of the text in cells.

62
Wrap text: In excel, if you have a long entry in a cell the text automatically spans into multiple
columns.
 Select the cells you want to wrap
 Click Home on the ribbon
 Select wrap text.
Excel will wrap the text in the cell and also increase the height of the cell row.

Merge and center: It allows you to center titles evenly above your data by merging several
cells into one and then centering the title in this one cell.
 Click the Home tab
 Go to alignment group
 Then you will view the merge and center button there.

Saving a Spreadsheet
You can Save a Spreadsheet in two ways:

 Save: This option is used when you save a spreadsheet for the first time.

Steps

(1) Click the Microsoft Office Button and click save

(2) Press CTRL+S on the keyboard or click the file icon on the quick access toolbar.

 Save As: This option is used when you want to save an existing saved spreadsheet with a
different name and different location

Steps

(1) Click the Microsoft Office Button and click save As

(2) Click the file icon on
the quick access
toolbar.

63
 Using editing functions
Number Group:

We can use this option to change actual formatting for our data like a fraction, date format,
percentage, and currency.

Roll no Name Date of Birth Price Tax Total (price+tax) Percentage

1 Anit 2/1/1998 $85.00 75.00 $160.00 1.6%

2 Amit 5/2/2008 $96.00 45.00 $141.00 14%

3 Sumit 10/4/2000 $36.00 25.00 $61.00 6%

Editing Group (Σ)

(a) Sum
1. Create a table

Roll no Name English Hindi Maths Sst Science Total

Anit 85 96 15 19 74
1

Riya 96 36 23 58 85
2

2. Place the cursor where you want the sum.

3. Select numbers to be added.

4. Click Auto sum option.

64
 (b) Average

1. Create a table.

Roll no Name English Hindi Maths Sst Science Total Average

1 Anit 85 96 15 19 74 289

2 Riya 96 36 23 58 85 298

2. Place the cursor where you want the average.

3. Click the average option from the auto sum option.
4. Select the numbers.

Roll no Name English Hindi Maths Sst Science Total Average

1 Anit 85 96 15 19 74 289 57.8

2 Riya 96 36 23 58 85 298 59.6

65
 (c) Min: - It gives minimum value from the selected cells.

1. Create a new column with the heading Minimum.

2. Place the cursor where you want the minimum value.

Roll no Name English Hindi Maths Sst Science Total Average Minimum

1 Anit 85 96 15 19 74 289 57.8

2 Riya 99 36 23 58 85 298 59.6

3. Click min option from the auto sum option.

4. Select numbers and press enter.

(d) Max: -It gives maximum value from the selected cells. Same steps as above

Roll no Name English Hindi Maths Sst Science Total Average Minimum Maximum

1 Anit 85 96 15 19 74 289 57.8 15

2 Riya 96 36 23 58 85 298 59.6 23

66
 Learning styles tab
Styles Group

 Conditional Formatting:

Using conditional formatting, you can highlight your data using a combination of color
scales, icon sets, and data bars.

Steps
1. Select the numbers.

2. Choose conditional formatting > Highlight cell rules.

3. Select any rule to highlight particular cells e.g select greater than.
4. Type any no. and choose any color.
5. Click the ok button.

67
 Format as a table: Apply quickly formatting to the table.
Steps

1. Select the cell.

2. Click the cell style option.

68
 Sorting and filtering
Sorting and filtering allow you to manipulate data in a worksheet based.

Basic Sorts
To execute a basic ascending or descending sort based on one column:

 Highlight the cells that will be sorted.

 Click the sort and filter button on the Home tab.
 Click the sort ascending (A-Z) button or sort descending (Z-A) buttons.

Filtering
Filtering allows you to display only data that meets certain criteria. To filter:

 Click the column or columns that contain the data you wish to filter.
 On the Home tab, click on Sort &Filter.
 Click the Filter button.
 Click the arrow at the bottom of the first cell.
 Click the Text Filter.
 Click the Words you wish to filter.
 To clear the filter click the Sort & Filter button.
 Click clear

69
 Using Formulas and Functions

Formulas Text

 Function

LOWER Converts text to lowercase

UPPER Converts text to upper case

Returns the number of characters in a text

LEN
string

LEFT Returns the leftmost characters from a text value

Returns a specific number of characters from a text

MID string starting
at the position you specify

Returns the rightmost characters from a

RIGHT
text value

CONCATENATE Joins several text items into one text item

EXACT Checks to see if two text values are identical

70
  Date and Time

Returns the serial number of the current date

NOW
and time

TODAY Returns the serial number of today’s date

HOUR Converts a serial number to an hour

MINUTE Converts a serial number to a minute

SECOND Converts a serial number to a second

DAY Converts a serial number to a day of the month

MONTH Converts a serial number to a month

YEAR Converts a serial number to a year

 Financial Formula

Returns the periodic payment for an annuity.

PMT It calculates the payment for a loan based on constant
payments and a constant interest rate.

Amount 45000

Rate 3%

Month 15

= PMT (*rate/12,*months, *-principal amount)

71
  Logical Functions

Returns TRUE if all of its arguments are

AND
TRUE

IF Specifies a logical test to perform

NOT Reverses the logic of its arguments

OR Returns TRUE if any argument is TRUE

72
 Using Charts and Graphics

Pivot Table

Create a Pivot Table to easily analyze data more easily.

 Create a Pivot Table

1. Select any cell in the worksheet that contains the data you want to create the report on.
2. Click the Insert tab click on the Pivot Table button.
3. The Pivot Table dialog box opens and the table or data range we already selected will
show in that field.
4. You can have it placed in a new worksheet or in an existing one where you will need to
select the location.
5. The Pivot Table Field List opens up and the Pivot Table tools become available.
6. Select the fields you want to include in the table by dragging them to the different boxes
below.

 Pivot Chart

A Pivot Chart is an interactive graphical representation of the data in a Pivot Table.

73
 Learning Defined names and Data Tools
Name Manager

 Select the cells.

 Click Name Manager.
 Click New Button and type the name in the textbox.
 Click ok and then click the close button.
Then you can apply any formula using the name you have given.

Data Tools
 Text to Column: -Using this command, you can separate the combined data into separate
columns.

Steps: - There are 2 ways: -

 Delimited Method
 Fixed Width Method

 Using Delimited Method

1. Select the range of data that you want to convert.

2. On the Data tab, in the Data Tools group, click Text to Columns.
3. In step 1 of the Convert Text to Columns Wizard, click Delimited and
then click next.
4. In step 2, select the Space check box, and then clear the other checkboxes under
Delimiters.
5. Click next.
6. Click finish.

 Using Fixed Width

1. Select the cell or range of cells.

2. On the Data tab, in the Data Tools group, click Text to Columns.
3. In step 1 of the converted text to columns wizard, click Fixed Width, and then click
next.
4. In the Data Preview box, drag the line to indicate where you want the content to be
divided.
5. Click next.
6. Click finish.

74
 Remove Duplicates: -

To remove duplicate values, use the Remove Duplicates command.

1. Select the range of cells, or make sure that the active cell is in a table.
2. On the Data tab, in the Data Tools group, click Remove Duplicates.
3. Do one or more of the following:
 Under Columns, select one or more columns.
 To quickly select the columns, click Select All.
 To quickly clear all columns, click Unselect All.
4. Click OK.

 Data Validation

Data Validation is an Excel feature that you can use to define restrictions on what data
can or should be entered in a cell.

Steps: -

 Select the cell you want to validate.

 On the Data tab, in the Data Tools group, click Data Validation
 The Data Validation dialog box will open.
 In the Data Validation dialog box, click the Settings tab.
 Click on the Allow box then select any item from the drop-down list e.g. select whole
numbers.
 The Data can then be set to be: Between Not Between Equal To e.g. Select between

 Once you have set the previous criteria, you have to set the Minimum and Maximum
amounts.
 Click the ok button. Input Message

 An input message is a message that appears when a cell is selected.

75
 Error Alert

The Error Alert can be set to stop, warn, or inform the user if they inserted an invalid
value.

 Consolidate: -
It consolidates data from multiple worksheets in the same workbook.
Steps: -

 Write data insheet1.

 Write data insheet2.
 Go to sheet3 where you want to consolidate.
 Select consolidate from the ‘data tools’ group.
 In the new consolidated window that pops up, you select the function according to
which you wish to consolidate the data, for example, ‘sum’.
 Now you select the cell or cell-range from the relevant file that you wish to
consolidate.
 Click on add.
 Next, you select the other ranges from the relevant files and also add them one-by-
one to the ‘all references’ area.
 Finally, click on OK.

 What-if analysis: -

To access these, select the Data tab, and locate the What-If Analysis command, if you
click this command, a menu with three options appears.
Scenario Manager is used when you want to change multiple values.
Steps: -

 Click the Data menu.

 On the Data menu, locate the Data tools panel.
 Click on the What-If Analysis item, and select the Scenario Manager.
 Scenario Manager Dialog box will appear.
 We want to create a new scenario, so click the add button.
 Add scenario dialog box will appear
 First, type a Name for your Scenario in the Scenario Name box.
 Enter the range of cells in the changing cells text box.
 Click OK and enter new values.
 Click the Show at the bottom.
 Click the Close button.

76
 Goal seek is useful if you know the needed result, but need to find the input value that will
give you the desired result.

Steps: -

 Select the cell containing the formula that will return the result you’re seeking.
 On the Data tab, choose What-If-Analysis Goal Seek in the Data Tools Group.
 Select the To Value Text box and enter the goal.
 Select the By Changing Cell text box and select the cell that you want to change.
 Click OK.

 Data Table
A Data Table is a way to see different results by Altering an Input Cell in your formula.
E.g. if you want to print tables from 1 to 10 then follow these steps: -

 Type 2 times one (1) in the same row.

 Multiply these 2 nos.
 Type counting from 2 to 10 horizontally and vertically.
 Select the area in which you want the
 On the Data tab, choose the What-If-Analysis data table in the Data Tools Group.
 Select 1 in row input cell.
 Again select 1 in the Column input cell.
 Click OK.

77
 Page Setup and Printing

Steps to be followed for Page Setup

 Press CTRL and then click each

worksheet tab in the workbook
that you want to affect.

 On the file menu, click Page setup.

In Excel 2007, click the dialog box
launcher in the page setup group in
the Page Layout tab

 Make the changes that you want in

the Page Setup dialog box, and then
click OK

Steps to be followed for Printing whole or a part of the worksheet

 Click the worksheet, and then select

the range of data that you want to
print.

 Click File, and then click print

 Under settings, click the arrow

next to Print Active Sheets and
select the appropriate option

 Click Print.

78
 Formulas of MS Excel

 Basic Formulas of MS Excel

A B C D

1 1 2 3 6

FORMULA RESULT

=SUM(A1: C1) 6

=AVERAGE(A1: C1) 2

=COUNT(A1: C1) 3

=MAX(A1:C1) 3

=MIN(A1:C1) 1

=PRODUCT(A1,B1) 2

=A1*B1 2

=A1/B1 0.5

=B1-A1 1

=A1+B1+C1 6

=D1*100/10 60

=D1*2/100 0.12

 IF CONDITION

A B C
1PERCENTAGE RESULT GRADE

274.3 PASS B

383.6 PASS B

494.3 PASS A

569.7 FAIL B

654.3 FAIL C

Formula:
1) For Result Column =IF(A2>70,” PASS”,” FAIL”)
2) For Grade Column =IF(A2>90,”A”,IF(A2>60,”B”,”C”))

79
  SUMIF AND COUNTIF

A B C
1 PERCENTAGE RESULT GRADE
2 74.3 PASS B
3 83.6 PASS B
4 94.3 PASS A
5 69.7 FAIL B
6 54.3 FAIL C

FORMULA RESULT

SUMIF
=SUMIF(C2:C6,”B”,A2:A6) 376.2
FORMULA
COUNTIF
=COUNTIF(B2:B6,”PASS”) 3
FORMULA

 PMT FORMULA

Returns the periodic payment for an annuity.

PMT It calculates the payment for a loan based on constant
payments and a constant interest rate.

Amount 45000
Rate 3%
Month 15
= PMT (*rate/12,*months, *-principal amount)

80
  MORE SHORT FORMULAS
Demo of Excel Data which are used to show the results of the formulas explained.

A B C D E F G H
1 HARTRON 8-15-2020 Hartron car 35 20 HAR TRON

Formulas Results

=left(A1,2) HA

=mid(A1,2,3) Art

=right(A1,3) RON

=sqrt(36) 6

=Abs(1.89) 1.89

=Abs(-1.89) 1.89

=Trunc(1.895) 1

=Trunc(-1.895) -1

=Now() Today’s data and time

=Today() Today’s date only

=Date(year,month,day) Date in computer format

=month(B1) 8

=Year(B1) 2020

=Day(B1) 15

=Upper(C1) HARTRON

=Lower(A1) hartron

=Proper(A1) Hartorn

=Trim(D1) Car

=round(12.2548,2) 12.25

81
=round(12.2568,2) 12.26

=roundup(12.254,-1) 20

=rounddown(12.254,-1) 10

=product(2,3) 6

=power(2,2) 4

=Counta(D1:F1) 3
 All logics correct: TRUE
=And(logic1, logic2)
 At least one logic is false: FALSE
 All logics correct: TRUE
=Or(logic1, logic2)  At least one logic is correct: TRUE
 All logics incorrect: FALSE
 Correct logic: FALSE
=Not(logic)
 Incorrect logic: TRUE
Calculate working days between given
=Networkdays(start date, end date)
dates
=Concatenate(G1,H1) HARTRON

=Concatenate(G1,“ “,H1) HAR TRON

=LEN(A1) 7

82