Working with Spreadsheets SST 2 Objectives 1 Perform

Working with Spreadsheets SST 2 Objectives 1. Perform data entry tasks 2. Use formulae and functions in worksheet calculations 3. Compare and make decisions to choose the most efficient method for calculations 4. State the advantage of the automatic recalculation feature 5. Format a worksheet SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 1 Introduction SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 2 Data Entry SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 2 Data Entry • Data you can enter into a cell • Text • Not used for calculations • Aligned left by default • Number • Used for calculations • Aligned right by default • Formula SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 2 Data Entry • Spreadsheet supports basic word-processing functions • To edit cell content, type over the portion of the content you want removed or changed SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • To enter a formula, begin with the equal sign (=) • Example, =B 3+C 3+D 3 • The main operators used for calculations • + Addition • - Subtraction • * Multiplication • / Division SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • X and are not found on the standard keyboard SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • To sum up Mary’s total marks in E 3, use the formula =B 3+C 3+D 3 to calculate the sum of 78, 70 and 65 SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • To sum up the other students’ total marks, copy the formula =B 3+C 3+D 3 to cells E 4 to E 10 • Cell addresses in the formula will be updated automatically SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae (a) = B 4 + C 4 + D 4 (b) = B 7 + C 7 + D 7 (c) = B 10 + C 10 + D 10 SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • Functions • Instead of typing your own formulae, you can use functions • Built-in formula that comes with a spreadsheet program SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • Functions (Cont’d) • The functions use range address instead of individual cell addresses • Much more efficient way to write a formula • The MIN and MAX function returns the minimum and maximum value in a range respectively • No need to do comparisons manually SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • = SUM(C 3: C 10) • = SUM(D 3: D 10) SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • = MAX(B 3: B 10); MIN(B 3: B 10) • = MAX(C 3: B 10); MIN(C 3: B 10) • = MAX(D 3: B 10); MIN(D 3: B 10) SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • Automatic recalculation • Updates computation returned by a formula or function automatically if referenced data is modified The automatic recalculation feature makes things very convenient. SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • Automatic recalculation (Cont’d) SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • Automatic recalculation (Cont’d) SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 3 Calculations and Formulae • Error messages when using formulae SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 4 Formatting Worksheets • Changing the appearance of a worksheet • Formatting text and changing alignment • Align the cell entries to the left, right or centre • Change the typeface, style, size and colour of the text • Formatting numbers • Change number formats • Number of decimal places, currency, date, etc. SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 4 Formatting Worksheets • Changing the appearance of a worksheet (Cont’d) • Adjusting the cell display • Adjust the column width and row height, merge cells, add fill effect etc. • To make sure that the cell entry can be seen clearly • When cell width is too narrow to fit a number, a series of # will be displayed SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 4 Formatting Worksheets • Adding borders • To make information easier to read, we can add table borders • Useful when presenting numbers • Note: Deleting a cell entry does not remove its formatting SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 4 Formatting Worksheets • Comparing a worksheet before and after formatting SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 4 Formatting Worksheets 1. 2. SST: Changes to C 1: bold, font colour, italicised Changes to A 3 to A 10: Font type Changes to B 2 to E 2: Bold Column E and F added Row 11 and 12 added Two decimal places SPREADSHEETS

Working with Spreadsheets SST 2 2. 5 Creating a Spreadsheet • To work out students’ test scores SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 6 Making a “What If” Decision • The recalculation feature is useful for planning and making decisions • Different input values can be used to compare the outcomes • Examples … SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 6 Making a “What If” Decision • Deciding on whether to buy a mobile phone that costs $600 or another that costs $400 SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 6 Making a “What If” Decision • Your school is organising a fun-fair to raise funds for charity • Your class’ target is $1, 000 • The class decides to buy these items to sell SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 6 Making a “What If” Decision • The intended sale prices SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 6 Making a “What If” Decision • How much profit can your class make? SST: SPREADSHEETS

Working with Spreadsheets SST 2 2. 6 Making a “What If” Decision SST: SPREADSHEETS

Working with Spreadsheets SST 2 Summary 1. We enter text, numeric values and formulae into cells. 2. Titles, column and row headings are referred to as labels. 3. Spreadsheet programs have built-in functions to help us in calculations. SST: SPREADSHEETS

Working with Spreadsheets SST 2 Summary 4. The automatic recalculation feature is useful when we are planning and making decisions. We can make changes of data easily. 5. To format a worksheet, we can change the format of text, change alignment, format numbers, adjust the cell display and add in fills and borders. SST: SPREADSHEETS