 67 %
33 %

Published on September 10, 2007

Author: jhando

Source: slideshare.net

Introduction to Spreadsheets Fundamental Skills 1

Spreadsheets • A spreadsheet is an application that is used for performing calculations. • Spreadsheets consist of columns and rows. A B C D E F 1 2 3 4 5 6 7 8 2

Spreadsheets • The intersection of each row and column is called a CELL A B C D E F 1 2 3 4 5 6 7 8 • Each cell is identified by a row and column reference such as A4, D6 etc. 3

Cells • Cells can contain either text, numbers or formulas. • To enter data in the cell it must first be selected (by clicking on the cell) A B C D E F 1 2 3 4 5 6 7 8 4

• Cells can be formatted to display numbers as either date, currency, percent, decimal and other numerical formats. A B C D E F 1 ABC Ca r pet s 2 3 Profit 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% 7 8 • Most common text formatting options are also available 5

Formulas • Formulas are used to make a spreadsheet more versatile. • When writing formulas in a cell an equal sign “=“ is first entered. Relational Operations Equal = Mathematical Operations Not equal to <> Multiplication* Less than < Greater than > Division / Less than or equal to <= Addition + Greater than or equal to >= Subtraction - 6

Formulas • Formulas should be written in terms of cell references e.g. = C4 + C5 + C6 A B C D E F 1 ABC Ca r pet s 2 3 Profit 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% =C4+C5+C6 7 8 7

Formulas • Once the formula is entered into the cell the results will be shown. A B C D E F 1 ABC Ca r pet s 2 3 Profit 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% 7 \$ 226.60 8 8

Functions • There are many formulas or FUNCTIONS that can be used in a spreadsheet. Functions make calculations easier. • These functions cover a variety of categories such as mathematics, financial or logical. 9

Functions • Common functions include SUM, AVERAGE, MAX and MIN. • Functions consist of a name, a set of brackets and arguments or parameters. • Arguments are the values on which the functions operate. 10

SUM • The SUM function is one of the most commonly used. It adds up all the numbers in a range of cells. =sum(A1,A2,A3,A4) adds cells A1,A2,A3 and A4 =sum(A1,B6,C67,F2) =sum(A1:B5) adds the cells indicated. adds all the numbers from A1 to B5 11

A B C D E F 1 ABC Ca r pet s 2 3 Profit 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% 7 =sum(C4:C6) 8 A B C D E F 1 ABC Ca r pet s 2 3 Profit 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% 7 \$ 226.60 8 12

AVERAGE • The AVERAGE function operates in a similar way as SUM. =AVERAGE(A1,A2,A3,A4) averages cells A1,A2,A3 and A4 =AVERAGE(A1,B6,C67,F2) averages the cells indicated. =AVERAGE(A1:B5) averages all the numbers from A1 to B5 13

A B C D E F 1 ABC Ca r pet s 2 3 Profit 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% 7 =average(D4:D6) 8 A B C D E F 1 ABC Ca r pet s 2 3 Profit 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% 7 6.0% 8 14

MAX and MIN • MAX returns the largest value from a =MAX(B2:B9) range of cells. • MIN returns the smallest value from a range of =MIN(B2:B9) cells. 15

A B C D E F 1 ABC Ca r pet s 2 3 Profit Highest Profit =MAX(D4:D6) 4 March \$ 78.50 6.2% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% Lowest Profit 7 =MIN(D4:D6) 8 A B C D E F 1 ABC Ca r pet s 2 3 Profit Highest Profit 4 March \$ 78.50 6.2% 7.3% 5 April \$ 102.34 7.3% 6 May \$ 45.76 4.5% Lowest Profit 7 4.5% 8 16

Repetitive Formulas • Often the same formula will need to be applied to different ranges of cells. A B C D E F 1 ABC Ca r pet s 2 3 X Y Z Profit 4 March \$ 78.50 \$ 98.56 \$ 88.89 6.2% 5 April \$ 102.34 \$ 77.89 \$ 145.62 7.3% 6 May \$ 45.76 \$ 124.90 \$ 97.45 4.5% 7 Total 8 17

Repetitive Formulas • Instead of entering the same formula in each cell and adjusting the cell reference, it is possible to ‘fill’ the formula across and have the cell referencing adjusted automatically. • First select the cell with the formula and then either select FILL from the edit menu or use the ‘FILL’ handle. 18

Repetitive Formulas A B C D E F 1 ABC Ca r pet s 2 3 X Y Z Profit 4 March \$ 78.50 \$ 98.56 \$ 88.89 6.2% 5 April \$ 102.34 \$ 77.89 \$ 145.62 7.3% 6 May \$ 45.76 \$ 124.90 \$ 97.45 4.5% 7 Total =sum(C4:C6) =sum(D4:D6) =sum(E4:E6) 8 FILL handle Note: Fill Down may also be used to copy formulas down a column/s 19

Absolute References • When ABSOLUTE references are used the contents of the formula are not changed when filling down or across. – i.e. the cell references remain the same. • Absolute references are indicated by placing a ‘\$’ before the column and/or row reference. – e.g. \$A\$1 20

Absolute References A B C D E F 1 ABC Ca r pet s 2 3 No. metres Cost Rate/m \$42.50 4 Jones 12 =C4*\$F\$3 5 Day 23 =C5*\$F\$3 6 Mckay 5.6 =C6*\$F\$3 7 Hill 32 =C7*\$F\$3 8 21

 User name: Comment:

## Related pages

### Spreadsheet1 - Excel Data Analytics - Home

Our domain name pays tribute to the pioneer spreadsheet software of the 1980s, which had 1 sheet only.

Create amazing Kim spreadsheets Create spreadsheets With Google Sheets, you can create, edit, and collaborate wherever you are.

SpreadsheetML 2003. SpreadsheetML 2003-Dokumente werden in einer (1) unkomprimierten XML-Datei gespeichert. Diese Dateien bestehen im Wesentlichen aus vier ...

### Spreadsheet - Wikipedia, the free encyclopedia

VisiCalc was the first electronic spreadsheet on a microcomputer,  and it helped turn the Apple II computer into a popular and widely used system.

### Kingsoft Spreadsheets Free 2013 (9.1.0.4550)

Kingsoft Spreadsheets Free 2013 is totally free spreadsheet software alternative to Microsoft Excel that offers a wide range of easy-to-use features which ...

PC-Spreadsheet 1.05 - Stand: 24.01.2012: ... Windows 2000 bis Windows 8.1. Testen. Sie können das Programm hier zum Testen downloaden (30 Tage-Test), ...

### List of spreadsheet software - Wikipedia, the free ...

VisiCalc The first widely used normal spreadsheet with A1 notation etc. Lotus 1-2-3 Took the market from Visicalc in the early 1990s.