Download - Spreadhsheets 1
Introduction to SpreadsheetsIntroduction to Spreadsheets
Fundamental Skills 1
2
SpreadsheetsSpreadsheetsSpreadsheetsSpreadsheets
• A spreadsheet is an application that is used for performing calculations.
• Spreadsheets consist of columns and rows.
A B C D E F12345678
3
A B C D E F12345678
SpreadsheetsSpreadsheetsSpreadsheetsSpreadsheets
• The intersection of each row and column is called a CELL
• Each cell is identified by a row and column reference such as A4, D6 etc.
4
CellsCellsCellsCells
• 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 F12345678
5
• Cells can be formatted to display numbers as either date, currency, percent, decimal and other numerical formats.
A B C D E F1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%78
ABC Car pets
• Most common text formatting options are also available
6
FormulasFormulasFormulasFormulas• Formulas are used to make a spreadsheet more versatile.• When writing formulas in a cell an equal sign “=“ is first entered.
Mathematical OperationsMultiplication *Division /Addition +Subtraction -
Mathematical OperationsMultiplication *Division /Addition +Subtraction -
Relational OperationsEqual =Not equal to <>Less than <Greater than >Less than or equal to <=Greater than or equal to >=
Relational OperationsEqual =Not equal to <>Less than <Greater than >Less than or equal to <=Greater than or equal to >=
7
FormulasFormulasFormulasFormulas
• Formulas should be written in terms of cell references
e.g. = C4 + C5 + C6A B C D E F
1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%78
ABC Car pets
=C4+C5+C6
8
FormulasFormulasFormulasFormulas
• Once the formula is entered into the cell the results will be shown.
A B C D E F1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%7 226.60$ 8
ABC Car pets
9
FunctionsFunctionsFunctionsFunctions
• 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.
10
FunctionsFunctionsFunctionsFunctions
• 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.
11
SUMSUMSUMSUM
• 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,A2,A3,A4)adds cells A1,A2,A3 and A4
=sum(A1,B6,C67,F2)adds the cells indicated.
=sum(A1,B6,C67,F2)adds the cells indicated.=sum(A1:B5)
adds all the numbers from A1 to B5
=sum(A1:B5)adds all the numbers from A1 to B5
12
A B C D E F1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%78
ABC Car pets
=sum(C4:C6)
A B C D E F1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%7 226.60$ 8
ABC Car pets
13
AVERAGEAVERAGEAVERAGEAVERAGE
• The AVERAGE function operates in a similar way as SUM.
=AVERAGE(A1,A2,A3,A4)averages cells A1,A2,A3 and A4
=AVERAGE(A1,A2,A3,A4)averages cells A1,A2,A3 and A4
=AVERAGE(A1,B6,C67,F2)averages the cells indicated.
=AVERAGE(A1,B6,C67,F2)averages the cells indicated.
=AVERAGE(A1:B5)averages all the numbers from A1 to B5
=AVERAGE(A1:B5)averages all the numbers from A1 to B5
14
A B C D E F1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%78
ABC Car pets
=average(D4:D6)
A B C D E F1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%7 6.0%8
ABC Car pets
15
MAX and MINMAX and MINMAX and MINMAX and MIN
• MAX returns the largest value from a range of cells.
=MAX(B2:B9)=MAX(B2:B9)
• MIN returns the smallest value from a range of cells.
=MIN(B2:B9)=MIN(B2:B9)
16
A B C D E F1
2
3 Profit4 March 78.50$ 6.2%5 April 102.34$ 7.3%6 May 45.76$ 4.5%78
ABC Car pets
=MAX(D4:D6)
=MIN(D4:D6)
Highest Profit
Lowest Profit
A B C D E F1
2
3 Profit4 March 78.50$ 6.2% 7.3%5 April 102.34$ 7.3%6 May 45.76$ 4.5%7 4.5%8
ABC Car petsHighest Profit
Lowest Profit
17
A B C D E F1
2
3 X Y Z Profit4 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 Total8
ABC Car pets
Repetitive FormulasRepetitive FormulasRepetitive FormulasRepetitive Formulas
• Often the same formula will need to be applied to different ranges of cells.
18
Repetitive FormulasRepetitive FormulasRepetitive FormulasRepetitive 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.
19
Repetitive FormulasRepetitive FormulasRepetitive FormulasRepetitive FormulasA B C D E F
1
2
3 X Y Z Profit4 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 Total8
ABC Car pets
=sum(C4:C6)
FILL handle
=sum(D4:D6) =sum(E4:E6)
Note: Fill Down may also be used to copy formulas down a column/s
20
Absolute ReferencesAbsolute ReferencesAbsolute ReferencesAbsolute 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
21
Absolute ReferencesAbsolute ReferencesAbsolute ReferencesAbsolute ReferencesA B C D E F
1
2
3 No. metres Cost Rate/m $42.504 Jones 125 Day 236 Mckay 5.67 Hill 328
ABC Car pets
=C4*$F$3=C5*$F$3
=C6*$F$3=C7*$F$3