Introduction To Excel

1

Introduction to Excel

By

Mr. S. P. Dharigouda�Associate Professor�Department of Statistics,�Raje Ramrao Mahavidyalaya, Jath

Introduction To Excel

2

Excel 2007

• Excel is multipurpose software that uses the concept of workbook consisting of a number of worksheets.
• A worksheet is made up of 16384 columns and 1048576 number of rows.
• Columns are denoted by letters A, B,...,Z, AA, AB, …,AZ, BA, BB,… XFD etc.
• Rows are denoted by numbers 1,2,......etc.
• Intersection of a row and column is called a cell, referred by the corresponding row and column indices.
• Each column or row can be used to enter data that belong to one category.

Introduction To Excel

3

Introduction To Excel

4

Introduction To Excel

5

Introduction To Excel

6

Working with Excel

• One can move from cell to cell using navigation keys (←, ↑, →, ↓) or move by using the mouse and click the chosen cell to be made active.
• F5 Function Key is used for Go To dialog box.
• ‘Ctrl + →’ to go to last column of worksheet in current row/cell.
• ‘Ctrl + ↓’ to go to last row of worksheet in current column/cell.
• ‘Ctrl + Page Down’ to move down from one worksheet to another.
• ‘Ctrl + Page Up’ to move up from one worksheet to another.

Introduction To Excel

7

Working with Excel

• Numbers are values that can be used for calculation. They can consist of numerals 0 through 9 with decimal points. There are different formats for display of numbers like currency symbol, + or – sign or % sign etc.
• One can enter time in the formats of HH:MM, HH:MM:SS, HH:MM AM/PM or HH:MM:SS AM/PM. Specify AM or PM if you do not want Excel to use the 24 hour format.
• Excel considers any data that does not recognize as a number, date, time, or formula to be a text. In data analysis these are useful to give appropriate titles to various entities.

Introduction To Excel

8

• Formula is used for the purpose of calculations.
• For example, To get the total of the contents of A1 and B1 and to display the answer in C1 we will enter =A1+B1 in C1.
• Every formula should start with = or + sign. Or in other words anything starting with = or + sign will be treated as a formula by Excel.
• A formula would contain data and operators.
• Data consists of constants and references to cells.
• There are four types of operators; arithmetic, logical, text, and reference.

Working with Excel

Introduction To Excel

9

Working with Excel

Arithmetic Operators

Introduction To Excel

10

Working with Excel

Logical Operators

These operators compare one value or text with another. They return a result TRUE if the condition is true and a result FALSE if the condition is false. TRUE is represented as a mathematical 1 and FALSE as a mathematical 0. In other words, as a cell displays TRUE or FALSE, if we use the result of the cell in a formula, it will be treated as a 1 or 0.

Introduction To Excel

11

Introduction To Excel

12

Working with Excel

Introduction To Excel

13

Functions

• A function is Excel’s built –in formula. For example SUM is a function to obtain the sum of a set of observations. Instead of writing the formula =B2 +B3 +B4 +B5 +B6 +B7 +B8 +B9 +B10 +B11 one can use =SUM (B2: B11).

​

• To obtain the maximum in Excel, we use the MAX function on the appropriate range.

= MAX (B2:B11)

Working with Excel

Introduction To Excel

14

Functions

• To obtain the minimum in Excel, we use the MIN function on the appropriate range.

= MIN (B2:B11)

​

• To obtain the mean in Excel, we use the AVERAGE function on the appropriate range.

= AVERAGE (B2:B11)

​

• To obtain the median in Excel, we use the MEDIAN function on the appropriate range.

= MEDIAN (B2:B11)

​

​

Working with Excel

Introduction To Excel

15

Working with Excel

Introduction To Excel

16

Matrices

• Determinant: A matrix having its determinant value equal to zero is called as singular matrix otherwise non-singular matrix. To find determinant value function ‘MDETERM(Array)’ is used. Type in cell C8 as

= MDETERM(B3:D5)

Working with Excel

Introduction To Excel

17

Working with Excel

Introduction To Excel

18

• Transpose: To get a transpose of a matrix, ‘TRANSPOSE(Array) function is used. Here, while using the transpose function you have to select the required number of cells. After selecting cells type =TRANSPOSE(B3:D5)

Then by holding Ctrl + Shift press Enter key

Working with Excel

Introduction To Excel

19

Working with Excel

Introduction To Excel

20

• Multiplication: To get a product of two matrices of order ‘m x n’ and ‘n x p’ of order ‘m x p’ function ‘MMULT(Array1, Array2)’ is used. = MMULT(B3:D5,B7:C9)

Then by holding Ctrl + Shift press Enter key

Working with Excel

Introduction To Excel

21

Working with Excel

Introduction To Excel

22

• Inverse: To get an inverse of a square matrix function ‘MINVERSE(Array)’ is used. = =MINVERSE(B3:D5)

Then by holding Ctrl + Shift press Enter key

Working with Excel

Introduction To Excel

23

Cramer’s Rule 

a₁x₁ + b₁x₂ = d₁

a₂x₁ + b₂x₂ = d₂

Hence,

Introduction To Excel

24

Introduction To Excel

25

Thank You