NOTE: More recently I have recorded a video session where a student performed some of the functions similar to what is done in the exercises. Thanks Theresa. The link to these videos is within the text.

If you want to know more about playing such videos (Youtube Videos) go to this video I made on How to watch You Tube Videos:

https://www.youtube.com/watch?v=d-9lHGr48-Q

And How to watch a training video and do what is shows at the same time

http://youtu.be/J_0bSlIpids

A spreadsheet is the computerisation of the function of performing calculations and writing the results on a piece of paper and reusing those results in further calculations. This is achieved by presenting the user with an array of columns and rows. Any intersection of those columns and rows (cells) can perform a calculation. When you consider that a modern computer can perform approximately one million calculations per second this makes spreadsheets very efficient.

Prior to computer spreadsheets were paper based spreadsheets. Often these were called money column paper, for example 14 money column paper or ledger paper that could have looked like this:

Traditionally the first large column would be a description followed by a multiple columns for example one for each month and usually a total as the right most common. Often totals at the bottom of the sheet were also required. Regardless of the number of columns or whether a calculator was used or calculations were done manually, the user of the paper based spreadsheet would have to look at the figures perform the calculations and write the answers on the paper. The possibilities of making mistakes were endless, whether they be misreading the information or writing the wrong answers. Consequently either a cross calculation (adding across and down) or adding a second time would have to be performed to ensure accuracy.

Modern spreadsheets can do much more than just calculations, such as presenting the information in a graphical format (charts), database manipulation (storing and consolidating large amounts of information) and what if analysis e.g. Financial, or any other sorts of modeling. Some examples of modeling are, for a local council how much additional revenue would be generated if all properties had a different value, if interest rates increase by one per cent what affect is this going to have on the profitability of an organisation or if fuel prices reduced by 10 per cent how much can an airline reduce its ticket prices and still maintain the same level of profitability

This is an incomplete list:

1. Performance series of simple calculations
2. present information and a graphical format
3. record and summarise all your banking information
4. prepare a budget
5. record a list of all the dvds or CDs you have
6. determine the amount of material and total cost for a construction project
7. record and summarise all the bills for a period of time
8. help complete your tax return
9. record and summarise all your credit card information
10. check on the performance of your investments or superannuation
11. record summarise and show the performance of a physical exercise activity
12. record and summarise all the phone calls that you make
13. Summarise and perform additional calculations on any information you can obtain from the Internet
14. present any information in such a format as to help decision-making

Spreads are what made the first of the personal computers more usable by the ordinary people because prior to spreadsheets the users had to write or get written a computer program to solve their specific problems. To a large extent, with a relatively small amount of training, people with no programing experience were able to solve quite complex problems in a very short period of time and also repeat variations of these solutions very quickly.

This was in contrast to the prior to spreadsheet situation where it may have taken a computer programer a number of weeks or months to write a program to solve each specific problem and to a large extent if a completely different problem came up, the programer would have write a completely different computer program

A good description of the history of computer spreadsheets can be found here in Wikipedia.

Some other links also are:

http://j-walk.com/ss/history/ http://dssresources.com/history/sshistory.html http://library.thinkquest.org/J0110054/History.html

A blank Calc window looks like this. The text in red describes the name of each part of the window.

You can also find out what various parts of Calc window do or any other windows that appear by clicking on the Help menu and clicking on What's this. The mouse pointer will change to an arrow with a question mark beside it. Pointing the arrow at different items may then produce a hint box that describes the item. To exit the What's this mode double click the mouse or press the Esc key.

Rows are shown horizontally across the screen and are given numbers starting from 1 and go through to 65,536. Columns are shown vertically down the screen and a given letters starting from A and go through to AMJ, that is they work like the number plates on cars, A through to Z, then AA through to AZ and so on.

We're a colum and a row intersect this is called a cell. An individual cell is identified by its colum and row co-ordinates. B2 for example would be the cell at the intersection of column B and the row 2 and AMJ65536 would be the cell at the intersection of colum AMJ row 65,536, ie the last cell in that a spreadsheet. This reference to cells by their column and row is called the cell address

Update March 2012 OPen Office 3 Now has 1,048,676 Rows and 1,024 Columns A through to AMJ

The following exercises will help you learn how to move around in a empty Calc spreadsheet. They may operate differently if you have information in the spreadsheet so make sure you start with a blank spreadsheet.

1. Start Calc
2. what cell are you currently positioned on and why do you think you would be positioned on this cell?
3. click on cell B2, what happened? What is the cell indicator (above column A) showing and why?
4. what do you think will happen if you click on cell C3? Try it. What is the cell indicator showing now?
5. try clicking on other cells that you can see. In addition to the cell indicator what else indicates which colum and row you are currently positioned on?
6. use the vertical scroll bar to scroll down until you can see row 100. Note that the cell indicator is still showing cell C3. This is because scrolling the spreadsheet does not reposition the current active cell.
7. click on cell A100. What does the cell indicator show now?
8. use the horizontal scroll bar to scroll until you can see column AA. What is the cell indicator now showing? What must you'd do to make it show AA100? Do it, click on a AA100.
9. drag (click and drag the slider) the vertical scroll bar as far down as it will go. What happened when you let go the mouse? Try doing the same thing again. What happened? Try doing a similar function with the horizontal scroll bar. What happened? Note unlike other applications because spreadsheets are so large is not easy to scroll to the extremes. You will learn an easier way to do this later on.
10. Press the Home key. What happened? So what does the home key do? Press the End key. What happened? So what does the End Key do?
11. click on cell A1. Whilst holding down the CTRL key press the right arrow key. What happened? Whilst holding down the CTRL key pressed the down arrow key. what happened? What do you think will happen if whilst holding down the CTRL key you press the left arrow key? Try it. How would you get back to row 1? Try CTRL up arrow ! Note: the arrow key used with the CTRL key only moved to the extremes of the spreadsheet because it is currently empty, has no data in it, the CTRL Keys with the arrows will act differently when the spreadsheet contains information.
12. Click on the cell indicator a type in K300 and press enter. What happened? If you know the cell you want to go to this can be a quick way to get their.
13. click on the help menu, click on Open Office.ord Help. On the right pane click on “Shortcut Keys for Spreadsheets”. Read the section on “Navigating in Spreadsheets”.
14. switch back to the spreadsheet

You have now learn how to move around in a blank Calc sheet one cell at a time with the mouse and the keyboard, to quickly move the the extremities of the sheet and go to a specific cell.

To perform many of the functions within Calc you need to select parts of the spreadsheet. This section will give you exercises on doing this.

1. If you have not already done so start Calc
2. Click on cell B2. Drag to cell D2. What happened? The cells B2, C2 and D2 are all selected
3. Click on cell B2. What happened? The previous selected cells and now no longer selected
4. Drag over cells D2 to D20. What happened? These cells are selected. Note that the row number are also highlighted to indicate the selected cells as well as the column letter D. Click on cell D2. What happened? Again the previous selected cells were unselected
5. Drag from cell B2 to K30. You can do this by dragging the mouse diagonally rather then to the right and then down. What happened? These cells were selected. How would you now un-select them? Click on any one cell.
6. Click on cell B2. Whilst holding down the Shift Key press the right allow key twice and then the down arrow key twice. What happened? The cells B2 to D4 where selected. You can also select part of a spreadsheet with the keyboard. Sometimes it can be easier to select with the keyboard rather than the mouse, epically when when selecting beyond what can currently be seen on the screen. Press the down arrow key by itself. What happened? the revious selected cells were un-selected. You can un-select cells via the keyboard.
7. Click on the letter A as the heading of column A. What happened? Even though you can't see it you have selected the entire column A from row 1 to row 65,536. This is a much quicker way than dragging with the mouse. Un-select this column. Drag over columns letters A through to I. What happened? You have selected all of the rows for columns A through to I or cells A1 through to I65536. Notice this is shown in the cell indicator, above column A. Un-select theses columns
8. Drag over the row numbers 2 through to 20. What happened? You can select entire rows similar to selecting entire columns. Un-select these rows.
9. Click on the rectangular area that is to the left of the A letter for column A and above the 1 for column 1. What happened? All the cells in the sheet are selected. Note what the cell indicator above column A is showing.

You have learn how to select a range of cells by dragging over them or holding down the shift key and using the arrow keys, by dragging over column letters or row numbers and selecting all cells by clicking on the rectangle at the top left of the column row letter/numbers.

A video of a student performing some of these functions: http://youtu.be/G_VDh9_YvDU

1. if you have not done so already start Calc
2. Click on cell A1 and type “This is what I entered in A1” and the press the Enter Key. What happened? Notice that you can enter information that is wider than the width of the cell. You will learn later how to change the width of columns
3. click on Cell A2. Type “123” without the quotes, Press Enter, What happened? Notice that because you entered a number that it is right hand aligned (placed to the right of the cell)
4. click on cell A3 and type the following phone number 0738005222 and press enter. What happened? Notice Calc removed the zero. That is because it thought what you entered was a number and numbers don't have leading zeros.
5. click on cell A4 and type '0738005222 including the apostrophe “'”. What happened. This time the zero was included but also notice that the number is against the left of the cell. This is because, by starting what you entered with the apostrophe you have told Calc that what you entered is text and not a number.
6. click on cell A5 and enter the phone number like this 02 3800 5222 and press enter. What happened? This time, even though you did not start it with a apostrophe the leading zero was included and the entered information was to the left of the cell. This is because part of what you entered was a space. A space is not a number and so Calc assumed what you entered was text.
7. click on cell A6 and type -15 and press enter. What happened. The “-” Calc assumes is a minus sign. The only keys you can enter for which Calc will reconize is a number are 0 through to 9 - and ,
8. click on cell A7 and enter the following number, 123456789012345 and press enter. What happened? The ### means that the cell is not wide enough to display its numeric contents. You will learn how to widen columns later on.
9. click on cell A8 and enter 12345678901234567890 and press enter. What happened? The 1.23E+019 is Calcs way and the scientific way of displaying very big numbers. You will,learn how to display it as a real number later on.

You have now learnt to enter text and numeric information into cells, how Calc distinguishes between text and numeric information and how to force Calc to accept a number as text.

Part of a video of a student deleting information: https://youtu.be/JDPeowP6QLM?t=2m22s

These exercise assume you using Calc and have completed the above exercises. If you have not you will need to complete the above exercises.

1. Click on cell A8, press the Backspace key. What happened? The contents of cell A8 was deleted.
2. click on cell A7, press the Del key, what happened? A delete contents window has appeared. Notice it has a Help button. Open office is good at doing this. Click on the help button and read the page of help. At this stage you have not learnt about formatting cells, formulas, Dates and times, Notes and objects, so don't be to concerned about then at the moment. Change back to the Calc window. Press enter. Notice the contents of cell A7 have been deleted.
3. Drag the mouse over cells A6 through to A3. What happened? Press the Backspace key. What happened? You can delete the contents of multiple cells by selecting them before pressing the Backspace or Delete keys

NOTE: It is possible to do what looks like deleting the contents of a cell by typing a space over the original contents of a cell. However this does not make the cell blank, it stores a space, which you can not see, in the cell. Although this initially appears ok, it can later cause problems when moving around in the spreadsheet and when performing certain functions. Also because spaces in blank cells are invisible they are very difficult to find.

You now know how to delete information in cells via the Backspace and Delete Keys and that you not put a space in a cell to delete it contents.

1. Click on cell A2, and type 456 and press enter. What happened? The 456 went over the top of 123
2. Click on cell A2 again. Notice the 456 appears in the formula bar. Click between the 4 and the 5 in the formula bar. What happened? Enter 2 and press enter. What happened?
3. Click on cell A1 and press the F2 function key. What happened? Press the left arrow key sufficient times so that you are to the left of the “i” in “is”. Without the quotes enter the following “has been edited ” and press enter. What happened? The F2 Function key can be used to edit the contents of any cell the same as clicking on the formula bar.
4. Use what you have learnt to put the word “and” between the words “edited” and “is” in cell A1
5. Click on cell A1 and press the F2 function key. Press the left arrow key sufficient time to place the insertion to the right of the “d” in “edited”. Press the Backspace key 6 times to remove the word “edited”, enter, without the quotes, “changed”. What happened? Whilst holding down the Alt key press the Backspace key. What happened? The text changed back to what it was before you edited it. Alt Backspace is the Undo function. Now Press Enter.
6. In Cell A10 enter, without the quotes, “one”, in cell A11 “two” and in cell A12 “Three”. Click on a blank cell.
7. From the Edit Menu Click on Undo:Input. What happened? The last input you did was removed. What do you think will happen if you do Edit, Undo:Inut again? Try it. Do undo input again. Everything in cells A10 through to A12 has been undone. Part of a Video of a student doing this a slightly different way: https://youtu.be/G_VDh9_YvDU?t=8m59s See Below
8. From the Edit Menu, click on Redo: Input. What happened? The last thing you undid has been redone, ie the “one” has reappeared in A10. Do Edit, Redo input 2 more times. The cells A10 to A12 should now have ther contents again.
9. Whilst carefully watching the cells A10 to A12, slowly, whilst holding down the CTRL key press the Z key 3 times. What happened? CTRL + Z is the same as undo.
10. Whilst carefully watching the cells A10 to A12, slowly, whilst holding down the CTRL key press the Y key 3 times. What happened? CTRL + Y is the same as redo. Part of a video showing this: https://youtu.be/G_VDh9_YvDU?t=8m59s
11. In preparation for the next exercises delete the contents of all the cells

You have learnt how to change the contents of a cell by typing new information over existing information, changing information by clicking in the formula bar, pressing the F2 (edit) function key and undoing the last changes you made with Alt Backspace, or Edit Undo and redoing functions and they the keyboard way of undoing is CTRL + Z and for redoing is CTRL + Y.

1. If you have not already done so start Calc. If any of the cells contain information delete them so you have a completely empty spreadsheet.
2. Click on cell B3 and enter =1+1 and press Enter. What happened? By starting what you entered with a = you have told Calc that you are entering a formula rather then other information and Calc displays the answer to the formula. Click on cell B3 again and look at what is shown in the Formula bar. Notice It shows what you entered, the =1+1. With Formulas, which all start with =, Calc stores the formula in the cell but displays the result of the formula in the spreadsheet. To see the formula rather then its result you click on the cell and read the formula in the formula bar.
3. Press the down arrow to move to cell B4 and enter =4-2 and press the enter. What happened? the - means minus.
4. In cell B5 enter =3*3 and press Enter. Yes * means multiply. Why not x? I don't know.
5. In cell B6 enter =15/5 and press Enter. / mean divide. Again I dont know why this character was chosen for divide
6. In Cell B7 enter =3+4+5+6+7 and press enter. You guessed it, you can enter multiple calculations in one cell. How many? I don't know. I don't no much do I?
7. In cell B8 enter =2+3*2 and press Enter. Why did you get 8? 2 + 3 is 5 and 5 multiplied by 2 is 10 so why did Calc display 8. If you remember the maths you did at school you may know why. If you don't it's because of the rules of mathematics say that in formulas multiplications and divisions are performed first before additions and subtractions.
8. In cell B8 enter =(2+3)*2 and press enter. Now the answer is 10. This is because another rule of mathematics is that anything inside brackets is calculated first, even before multiplication and division. Therefore the 2+3 resulting in 5 is calculated first and then the 5 is multiplied by 2 to give 10.
9. Try going to other cells and performing you own calculations
10. In preparation for the next exercises delete the contents of all the cells

You have learnt that you enter formulas in Calc by starting them with a =, that + means add, - subtract, * multiply and / divide. You also have learnt that Calc follows the rules of mathematics which states that in formulas you calculate from left to right except that anything in brackets is done first followed by multiplication and division then addition and subtraction.

A video of a student performing some of these functions: https://youtu.be/G_VDh9_YvDU?t=4m43s

In the previous section you performed the calculations by entering a formula in a cell by enter the figures and the operation (ie +,-,* or /). If you did all of a spreadsheet this way their would not be much benefit compared to using a calculator. The greatest benefit in using a spreadsheet is being able to use the contents of cells in calculations including the contents of cells that are the results of calculations.

1. If you have not already done so start Calc. If any of the cells contain information delete them so you have a completely empty spreadsheet.
2. In cell B2 enter 2 and in cell B3 enter 3 press enter and the click on cell B4.
3. In cell B4 enter =B2+B3 and press enter. What happened? Instead of having the figures included in the cell with the formula the formula contains the cells that contain the figures, in this case B2 and B3. Calc then retrieved the figures from cells B2 and B3 performed the calculation and displayed the answer.
4. Click in cell B5 and enter 2 and press enter. Click on Cell B6, type = and then click on cell B5 type * and then click on cell B4 and press Enter. What happened? Did you notice what was happening in the formula bar as you were going through these steps? if not do this point again. Rather than typing the cell address (its coordinates eg B4) you can click on the cell and Calc will put the cell address in for you. You finished the entering of the formula this way by pressing Enter or you could have clicked on the green tick that was to the left of the formula bar while you were entering the formula.
5. Click on cell B7 and enter 5 and press enter. Click on cell B8, Type = press the up arrow two time so that you are pointing at cell B6, type / press the down arrow once so that you are pointing at cell B7, press Enter. What happened? Notice that in addition to clicking on cells to include them in formulas you can move to them by using the keyboard arrow keys. NOTE: this only works if you start the formula by typing an = from the keyboard. It will not work if you start a formula by clicking on the = icon to the left of the formula bar.
6. Do NOT delete the contents on any of the cells, you will need then in the next section.

You have learnt how to use the contents of cells in formulas, to type cell addresses directly into a formula, to have cell addresses enter by clicking on it or by moving to it with the keyboard arrow keys.

Part of a video where a student shows doing something like this: https://youtu.be/G_VDh9_YvDU?t=8m1s

These exercise assume you using Calc and have completed the exercises in the above section. If you have not you will need to complete the above section exercises.

1. In the above section you may have observed that you were using the results on one formula in another formula. To confirm this click on each of the cells with information in then and read and note what the formula bar is showing. You can see that cell B4 is based on cells B2 and B3 and cell B6 is dependent on cells B4 and B5 and cell B8 is dependent on cell B6 and B7. So what do you think will happen if you change cell B2 from 2 to 3?
2. Click on cell B2 and type 3 and press Enter. Which cells changed and why? All the cells that had formulas in them that were dependent on or indirectly dependent on cell B2 changed. Indirectly dependent means that the cell is dependent on another cell that is in turn dependent on another cell and possibly so on.
3. Which cells do you think will change if you change cell B5 from 2 to 3? Why? Change cell B5 from 2 to 3. What happened? is it what you thought would happen? Only Cell B6 and B8 changed because these were the only ones dependent on B5. Because of the formulas you enter, Calc knows which cells a dependent on other cells and automatically calculates only those dependent cells. NOTE: Although it can be advantageous to do so dependencies of formulas do not have to go down rows or across columns. They can jump all over the place. It is possible to switch off automatic recalculation. See Auto Recalculation
4. In preparation for the next exercises delete the contents of all the cells

You have learnt how cells can be dependent on other cells and that if you change a precedent cell (base cell for calculations) Calc will recalculate all the appropriate dependent cells. Also that you can switch off automatic recalculation.

A video of a student performing something like this: http://youtu.be/JDPeowP6QLMU

1. Right Click on this http://www.greig.net.au/geoff/Fruit.ods and then click on save link as, to download the weather spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the fruit spreadsheet and open it
3. Click on cell E3 to enter the formula for the value of Apples. Click on the = button to the left of the Formula bar, click on D3 type * click C3 click on the tick to the left of the formula bar. You may be tempted to do the same for each of the other items but remember computer programs are ment to make like easier.
4. Click on cell E3, Right click and click on copy, drag over E4 through to E9, right click and click on Paste. What happened? Click back on E3 and note the formula in the formula bar. With the down arrow key move down to the other item formulas and note what they are. Notice that the row part of the cell address has been adjusted so that it reflects the appropriate row. This is because spreadsheet use what is called relative cell addressing. That is even though in E3 it sais multiple D3 by C3 to you internally to Calc it's saying multiply the cell to the left of me with the cell that is two to the left of me. When you copied E3 to the other rows it still sais multiply the cell to the left of me with the cell that is two to the left of me, so ends up with the correct result. Their are times when you do not want this to happen so you can change a formula to make it absolute rather then relative. You will learn how to do this later on.
5. To get the total for all the items you could enter the formula =E3+E4+E5+E6+E7+E8+E9 in E12, but that would be long winded and very difficult if you had 1,000's of items. Click on E12 and click on the sigma icon (one in blue) to the left of the formula bar. Notice what calc has put in E12 and the formula bar and how it has shown which cells are included in the formula. If these were not the cells you wanted added you can drag over the cells want added. Click the tick. Note the formula. =SUM(E3:E11) Sum means sum up (total) the cells in the bracket. Notice also the E3:E11 part. This is the way you define what is called a range of cells. A range of cells can be any rectangular group of cells and is specified by any two corners of the rectangle.
6. Click on E12, Whilst holding down the Ctrl Key press C (The keyboard short cut for Copy), Press the left arrow key twice to get to C12, Whilst holding down the Ctrl Key press V (The keyboard short cut for Paste). What happened? You can copy any type of formula and this is the preferred way of getting formulas into a spreadsheets because it reduced the chances of making typing errors in cell addresses

In this section you have learnt how to copy cells, what relative cell addressing is, how to build fomulas in the formula bar, the SUM() function, what a range of cells are, using keyboard shortcuts for copying and pasting and the prefered way of getting formulas into a spreadsheet.

1. Start Calc if you have not already done so and make sure you have a empty spreadsheet.
2. Enter a 1 in both B2 and B3. In B4 enter =B3+B2+B4 and press enter. What happened?
3. Click on B4 again and read what it said in the bottom right had box on the status line. Error Circular reference. A circular reference means that either directly or indirectly you have referred to the formula itself in the formula. It this case we have referenced B4 in the formula that is in B4, which is a mistake. Usually circular references are mistakes and should be corrected. In some special circumstances they can be deliberately entered, but that is beyond the scope of this course.
4. Chance the formula in B4 to be =B3+B2. What happened? The error has been replaced by the correct answer.

You have learnt that by directly or indirectly refering to cell of the formula in a formula you what is called a circular reference which needs to be corrected

A Video of a student doing some of this http://youtu.be/XhKro7a2nOk

1. Right Click on this http://www.greig.net.au/geoff/DVDs.ods and then clic on save link as, to download the DVDs spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the DVDs spreadsheet and open it
3. Click on the C column, that is the C letter on column C. What happened? Right click and click on Insert columns. What happened? You have inserted a new column
4. Enter the text, without the quotes, “Last Watched” in C1. Note that the end of this text is cut off by the cell to the right of it. You will learn how to fix this later on.
5. Drag over the column letter C, D and E. What happened? Right click and click on Insert columns. What happened? You have inserted a three new columns. To insert multiuple columns you select (highlight) the number of columns you wish to insert.
6. From the Edit Menu click on Undo Insert. What happened? The new columns you just insert disappeared because you undid the insert columns function. You can use undo on the edit menu to undo the last functions you have done.
7. Click on row 8, that is the 8 that represents column 8. What Happened? What do you think will happen if you right click and click on Insert rows? Try it. Did what you thought would happen? Noticed that the row was inserted below row 8. Undo the row insertion.
8. Based on what you have learn above how do you think you would inset 3 rows below row 10? Try doing it. If it did not work out undo what you did and follow the steps in the next point below
9. Drag over row number 11, 12 and 13. Right click and click on insert rows. Three rows below row 10 have been inserted. Notice that to insert rows below row 10 you had to start selecting from row 11. Undo this latest inserted rows.
10. At the bottom left of the Calc window is are three sheet tabs, DVD's, Photo Disks and Sheet3, Click on sheet3. What happened? You can have many sheets in one spreadsheet file. You will learn more about this later on.
11. Note the formulas in F2, F3, F4, F6, B6, C6 and D6 by pointing at these cells and looking at the formula bar. They calculate the appropriate totals.
12. Click on row 5, right click and click on Insert rows. What happened. Notice the totals remained the same. Look at the formulas that are now in B7 through to F7. How have they changed? They have changed to include then new inserted row so that if you were to put values in row 6 they would be included in the totals.
13. Use what you have learn to insert another column before column F and see how this effects the formulas for totalling the columns.

You have learn that you can insert single and multiple columns and rows between existing rows and columns by selecting the column letters or row numbers and generally this will not effect the formulas you had in place

A Video of a student doing some of this http://youtu.be/XhKro7a2nOk

1. If you have not followed on from the previous section Right Click on this http://www.greig.net.au/geoff/DVDs.ods and then clic on save link as, to download the DVDs spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the DVDs spreadsheet and open it
3. Again if you have not followed on from the previous section do steps 3 and 4 from it so that you have the “Last watched” column.
4. Click on the C for column C, right click and click on Delete Columns. What happened? This is how you delete a column
5. Drag over columns B, C and D, right click on Delete Columns. What happened? This is how you can delete multiple columns. Note that you have deleted the columns. It would have been also possible to delete the information in the columns by pressing the Backspace or Delete keys after selecting the columns, however in that case the columns would have still existed and the information within then would been removed. You need to understand the difference between removing columns and removing the information within the columns
6. Whilst holding down the Ctrl Key press the Z key. What happened. Ctrl + Z is the keyboard equivalent to Undo
7. How do you thing you would delete rows 5 through to 50? Try it. If it worked skip the next step. Undo the deleting of the rows (Ctrl + Z)
8. Drag over rows from 5 to 50, right click and click on Delete Rows. What happened? Note that as with deleting columns the row numbers stay intact as you can now see because column A “Number” now jumps from number 2 to 49. Undo the deleting of the rows (Ctrl + Z)
9. Click on Sheet3 at the bottom of the window. Note the formulas for the Totals.
10. Use what you have learnt to delete the rows 2 though to 4. What happend to the totals and why?
11. Use what you have learnt to undo the deletion of those rows
12. Use what you have learnt to delete columns B through to D What effect does this have on the formulas and the totals?
13. Undo the deletion of those columns.

You have learnt how to delete single and multiple columns and rows by selecting them, right clicking and clicking on Delete Columns or Rows and that this may not effect the result of formulas in some cases.

1. Right Click on this http://www.greig.net.au/geoff/DVDs.ods and then click on save link as, to download the DVDs spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the DVDs spreadsheet and open it
3. Notice on row 20 that the name of the Movie spills out of the cell and into the cell to the right of it because column C is not wide enough. Point exactly at the line that divides the B and C column letter headings. What happened? If you are pointing exactly at the line the cursor should change from a arrow to a small vertical line with and two arrows one pointing left and the other right. With the pointer this shape drag (hold the left mouse button down and move the mouse) this boarder of the column to the right so their is sufficient space for all the information in the column. Let the mouse go. What happened? You can use the same technique to change the width of any column including reducing widths
4. Drag over column A through to D, whilst pointing at the column letter headings right click and click on Optimal Column Width, click on Ok. What happened? Columns A and C became narrower and B and D wider. If you did not notice this happen undo it (Ctrl + Z) and do this step again. The Optimal Column Width function adjusts all the selected columns to the width of the widest item in them plus the amount set in the Add part of the Optimal Column Width window. Again if you did not see this perform an Undo and repeat this step.
5. Based on what you have learn, how do you think you would change the height of one row and optimise the height of multiple rows? Try it. If were able to do it skip the next step.
6. Point exactly at the line that divides the 1 and 2 row number headings. What happened? If you are pointing exactly at the line the cursor should change from a arrow to a small horizontal line with two arrows one pointing up and the other down. With the pointer this shape drag (hold the left mouse button down and move the mouse) this boarder of the row down so the row is deeper. Let the mouse go. What happened? You can use the same technique to change the height of any row including reducing heights.
7. A very similar method for optimising row heights is used as for columns. However row heights do not normally have to be adjusted unless the font size is different in different rows. Usually if your change makes the text larger Calc automatically increases the row height. However it does not automatically reduce the row height if you make the text smaller.

You have learnt that you can change single column widths and row heights by dragging the heading boarders or optimise the size by multiple columns or row by right clicking on their heading and clicking on the appropriate Optimal function.

1. Right Click on this http://www.greig.net.au/geoff/BrisWeatherfromBOM.ods and then click on save link as, to download the weather spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the BrisWeatherfromBOM spreadsheet and open it
3. This is the weather observations from the Bureau of Meteorology Web site for January to November 2008. After you have more experience with spread sheets you can try Getting information from Websites
4. Click on the B heading of column B. The entire column is selected. Right click and click copy. Scroll the sheet across until you can see column W. Click on the W heading for column W, right click and click on Paste. What happened? You have copied the entire date column to W. Undo this copy (Ctr +Z) This is the right clicking method of copying a column, you could have also used the edit menu or the toolbar method. Next we will use the click and drag method
5. Click on the B heading of column B. The entire column is selected. Whilst pointing anywhere other than the B heading of column B drag to column W and then release the mouse button. What happened? The Date column was copied to W. Did you notice how the pointer changed shape as you were dragging it and how the column you were dragging over were indicated? If you did not then undo the copy (Ctrl + Z) and repeat this step. Undo the copy (Ctl + Z)
6. How do you think you would copy columns B, C and D to W in the one go? If you think you can have a go at it. If you did it successfully skip the next step.
7. Drag over the heading of columns B, C, and D. All 3 should be selected. Whilst pointing anywhere other than the selected heading, drag to column W and then release the mouse button. What happened? The 3 columns were copied to W, X and Y. Did you notice how the pointer changed shape as you were dragging it and how the columns you were dragging were indicated? If you did not then undo the copy (Ctrl + Z) and repeat this step. Undo the copy (Ctl + Z)
8. Lets say we want to move the rainfall and evaporation columns, E and F, so they are the right of the date, column B. Based on what you have learnt so far you could insert 2 columns to the right of column B, copy the the rainfall and evaporation to them and then delete where you had copied from. That would be a bit messy and time consuming. Fortunately their is an easier way.
9. Drag over the heading of columns E and F. Both columns should be selected. Whilst pointing anywhere other than the selected heading, hold down the Alt key and drag to column C and then release the mouse button before releasing the Alt key. What happened? Did you notice how the pointer changed shape as you were dragging it and how the columns you were dragging over were indicated and how this differed from copying? If you did not then undo the copy (Ctrl + Z) and repeat this step. Undo the move (Ctl + Z).
10. How do you think you would go about copying and moving single and multiple rows? Basically you do the same as for columns only you do in on rows. To test your skills at this perform the following: Copy the January dates to the below the last rows on information, undo that. Move the February dated to the just under row 9

You have learnt how to copy single and multiple rows and columns by selecting them via their column letters or row numbers right clicking and clicking on copy then position one where you want the copy and clicking on paste or dragging to where you want the copy. You have also learn you can move rows or columns by selecting them, and whilst holding down the Alt key drag then to where you want them inserted.

So that the Weather spread sheet is constant with these notes we will download and open it again.

1. Right Click on this http://www.greig.net.au/geoff/BrisWeatherfromBOM.ods and then click on save link as, to download the Weather spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the BrisWeatherfromBOM spreadsheet and open it
3. This is the weather observations from the Bureau of Meteorology Web site for January to November 2008. After you have more experience with spread sheets you can try Getting information from Websites
4. Click on B9. Whilst hold down the CTRL key press the down arrow. What happened? Unlike when you had a empty sheet this time you are positioned on the last row in data. However although this is what happened that is not what CTRL + and arrow exactly does as you will see in the next few points
5. Whilst hold down the CTRL key press the up arrow, What Happened? You are at the top of the information. But not quite, B1 is the top.
6. Press the right arrow so you are in C9. Whilst hold down the CTRL key press the down arrow. What happened? Notice you are not at the bottom of the information as happened in column B but are at C313. Why? Because C314 is empty. The CTRL + and arrow key means move in tat direction until you find a empty cell then move back one cell.
7. Whilst hold down the CTRL key press the down arrow. What happened? This time because the cell below you was empty it moved to the end of the empty cells
8. Try moving around other places on this spreadsheet using the CTRL + the arrow keys. This method of moving around a large spreadsheet is much quicker than scrolling using the mouse and very useful when combined with other functions like copying and moving information and formulas
9. Whilst hold down the CTRL key press the home key. What happened? It took you to A1, Which is what it will always do.
10. Whilst hold down the CTRL key press the End Key What happened? It too you to the bottom right of the information.

You have learnt that you can quickly move around a large spreadsheet by using the CTLT key in combination with the arrows, Home and End keys

So that the Weather spread sheet is constant with these notes we will download and open it again.

1. Right Click on this http://www.greig.net.au/geoff/BrisWeatherfromBOM.ods and then click on save link as, to download the weather spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the BrisWeatherfromBOM spreadsheet and open it
3. This is the weather observations from the Bureau of Meteorology Web site for January to November 2008. After you have more experience with spread sheets you can try Getting information from Websites
4. Click on A1. Click on the font selector, it should be the second item from the left on the toolbar. Click on any font. What happened? That cell changed to that font. Click on the font size selector on the toolbar, it should be the next one to the right, and click on 14. What happened?
5. In turn click on the B, I and U icons on the tool bar. What happened? B is for bold, I for Italic and U for underlined. Again in turn click on the B, I and U icons. What happened this time? Clicking them a second time switched them off.
6. Click on Column C to select the entire column. Whilst still pointing at the selected column right click and click on Format Cells. Note how the number looks for 23/01/08 in C32. Make sure you are on the Numbers Tab and change the Decimal Places to one and click on OK. What happened? Particularly to C32. If you did not see what happened undo what you did and repeat the step again. The 21 in C32 changed to 21.0 because you set the Numbers formatting to one decimal place.
7. Select all of column V by clicking on the V heading. Whilst still pointing at the selected column right click and click on Format Cells. Note how the number looks. Make sure you are on the Numbers Tab and click on the Thousands Separator check box and also change the Decimal Places to one and click on OK. What happened? Try some of the other functions on the Numbers tab.
8. Select C9 through to V9. Whilst pointing at the selected cells right click and click on Format Cells. Click on the Alignment tab and click on the Wrap text automatically check box and click on ok. What happened? As these cells are still selected you can do more formatting on them. Whist pointing at the selected cells right click and click on Format Cells. Click on the Boarders tab and click on the second item for the left under the Line Arrangement Default. Also change the Line colour and style to what you like. As these cells are sill selected have a go at setting a background colour.
9. Select column B, right click on it and click on Format cells. Click on the Numbers tab. Under Category click on Date. Click on the second format, ie Friday 31 December 1999. Note What the Format code changes to. Click on Ok. What happened? Column B has ### in it. This means the information is to wide to be displayed. Widen the column and see what happens. Dates and times are stored in Calc as numbers. Dates as whole numbers with each day being one greater than the day before and Times as a fraction of a number, .5 being 12 mid day, .25 6 AM .75 6 PM and so forth. Because Dates and times are internally stored as these number and they can be displayed in many formats and more importantly can have calculations performed on them. This is not covered here but may be later on.
10. Their are quite a number of other function that can be performed to format cells. Try experimenting with the ones that have not been covered in this section

You have learnt that cells can be formatted in many ways via the toolbars or by selecting format cells from the right click (contect) menu.

So that the Weather spread sheet is constant with these notes we will download and open it again.

1. Right Click on this http://www.greig.net.au/geoff/BrisWeatherfromBOM.ods and then click on save link as, to download the weather spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the BrisWeatherfromBOM spreadsheet and open it.
3. Notice Cell C9 contains the heading “Minimum temperature (°C)” and D9 “Maximum temperature (°C)”. It would be better if their was a heading above these two cells that said “Temperature” but how would you make it be over both the cells. Click on C8. Type in Temperature and press enter. Select cells C8 and D8 by dragging over them. From the Format Menu click on Merge Cells. What happened. The two cells are now one. You can use the format cells function to centre this text so that it is over the middle of the two other headings. To un-merge cells you select then and from the Format menu select merge cells again. ie you are switching off the tick on Merge Cells.

You have learnt how to combine a number of cells into one cell and reversing the process by selecting the cells and clicking in Merge Cells from the Format Menu.

This exercise requires that we start with a blank sheet. We are going to to create a table that shows the times table from 2 through to 12, that you may remember for you old school days, that you where meant to memorise.

The concepts making a formula absolute can be difficult to comprehend and so difficult to explain so read this section very carefully and slowly to make sure you understand what is involved. If you do not understand it then repeat the section a number of times.

1. In cell B3 enter 1. Click on cell B3. Notice the boarder that shows the selected cell, B3, has a small solid box on its bottom right corner. Point the mouse on that box. What happened? The mouse pointer should have turned from the normal arrow to a + symbol. Whilst showing that the symbol slowly drag down to cell B14. What was happening as you dragged the mouse down? It was showing you how many rows you had gone down. Now release the mouse button. What happened? The number 2 through to 12 have been put in the selected cells. This is the automatic increment function of Calc
2. See if you can use this same process to have the numbers 2 through to 12 in cells C3 to M3. It is the same as above except you are drag over cells C3 to M3 after pointing at the sold box a the bottom right of B3
3. The Idea now is to have the result of the multiplication of the numbers in Row 3 and column B shown. In C4 enter the formula to multiply C3 by B4 ie =C3*B4 and press enter.
4. In a previous section you learnt how to copy formulas, which is a faster way than entering them for every cell required. Copy cell C4 to C4 through to M14. That is, click on C4, Whilst holding down the Ctrl Key press the C key (Copy). Drag over cells C4 though to M14, Whilst holding down the Ctrl key press the V key (Paste). What happened? You will get a warning message that you are pasting over cells that already contain data, click Yes. What happened? All the cells have information in them but they are the wrong result or have #NUM! in them. Why?
5. Click on D4 and look at the formula in the formula bar. It shows as =D3*C4. But this is not what we wanted to happen we wanted D3 time B4. Click on M14 and look at its formula, =M13*L14. As M13 and L14 where both blank Calc put the #NUM! which means is was not able to calculate an answer. We wanted M14 to be =M3*B14. Why did Calc not copy the formulas as we wanted them?
6. Remember when we learnt to copy formulas in a previous section I said that internally Calc stores its formula relatively. That internally Calc performs calculations bases on the position of cells relative to other cells. Click on C4 and read its formula. =C3*B4. Internally Calc is saying multiply the number in the cell above me with the number of the cell that is to the left of me. Therefore when we copied cell C4 to all the other cells it continued to say multiply the number in the cell above me with the number of the cell that is to the left of me. To prove this look at the formula for M14, =M13*L14 is still saying multiply the number in the cell above me (M13) with the number of the cell that is to the left of me (L14). This is called relative cell addressing.
7. In the current situation we do not want to use relative cell addressing as it will not give us the formulas we want. Calc allows the row or column part of a formula addressing, or both, to be absolute be proceeding the column letter and and or the row number of the formula with a $ sign.
8. Whilst holding down the Crtl Key press the Z key. What happened? The copy we previously did was undone.
9. Click on C4 and examine the formula to determine which part of it should be absolute and which part relative.
10. Remember, =C3*B4 is saying multiply the number in the cell above me with the number of the cell that is to the left of me.
11. Lets take the above part first (C3).
12. When we copy the cells we don't want the row above we always want row 3. The 3 part of the cell address needs to be absolute but the C column part must remain relative because that will change when the cells are copied.
13. Now lets look at the left of me part (B4).
14. When we copy the cells we don't want the column to the left we always want column B. The B part of the cell address needs to be absolute but the 4 part must remain relative because that will change when the cells are copied.
15. Therefore if the $ sign is used to make the the appropiate part of the cell address absolute then the formula must be =C$3*$B4. To do this click on the formula bar and with arrow keys move to where the $ sigh needs to be inserted and enter them. Press enter to accept the change to the formula
16. Click on C4, Whilst holding down the Ctrl Key press the C key (Copy). Drag over cells C4 though to M14, Whilst holding down the Ctrl key press the V key (Paste). What happened? You will get a warning message that you are pasting over cells that already contain data, click Yes. What happened? You now have all the correct answers.
17. Click on cell M14 and examine the formula. Notice that the absolute part of the formula has remained intact.

You have learnt how to automatically increment values and no when and how to make formulas absolute

1. Right Click on this http://www.greig.net.au/geoff/BrisWeatherfromBOM.ods and then click on save link as, to download the weather spreadsheet. Note where you save the down loaded file to
2. If you have not already done so start Calc. From the File menu click on Open, navigate to where you saved the BrisWeatherfromBOM spreadsheet and open it
3. This is the weather observations from the Bureau of Meteorology Web site for January to November 2008. After you have more experience with spread sheets you can try Getting information from Websites
4. Remember in a previous section you used SUM to total up numbers. SUM is just one of the 100's of what is called functions in Calc
5. Click on C346 and enter the formula =SUM(C10:C345) and press enter. What happened? It totalled up column C. Click on C347 and enter the formula =COUNT(C10:C345) and press enter. What do you think the answer of 334 represents considering the formula starts with Count? The number of cells? Actually it counts how many number are between C10 and C345. You can prove this by deleting what is in C344 by clicking on it and pressing the backspace key. Try it. What happened to the answer in C346. Whilst holding down the Ctrl key press the Z key to undo the delete. What happened?
6. As you now have the total and how many numbers are in column C you can work out the average by entering =C346/C347 in C348. Do that.
7. Even though you now have the average of column C (Minimum temperature (°C)) that was the hard way to get it. Calc has a function (fancy name for how to do something) for calculating averages and many other thing. It has hundreds of functions. To see a list of them all click on a blank cell, C349 and then click on the Fx to the left of the formula bar. The function wizard window should open. Make sure the Category is set to All. Drag the function scroll bar down to see them all. As you can see their are 100's of functions. They are listed in alphabetical order. Scroll until you can see the AVERAGE function and then click on it. Notice in the middle to the right in the Wizard window is sais “Returns the Average of a sample”. This a short description of what the function does. Above this it sais “AVERAGE( number1:number2;…). This is what is called the syntax of the function. That is the way the function must be written to make it a valid formula.
8. Click on the Next button at the bottom of the Function Wizard window. What happened? The wizard is now asking you to enter a number (number 1 (required)).
9. Notice in the middle right of the wizard window the number 1 in bold with numbers 2,3,and 4 below it. Click on the icon with the upward pointing arrow to the right of number 1. What happened? The main function wizard window has been replaced with a smaller one. The purpose of this window is for you to select the numbers you want to average. Drag over C344 to C10 and then press enter. What happened? You are returned to the original wizard window and the range C10:C344 is shown against number 1. Notice in the formula section at the bottom right of the wizard window is the formula =AVERAGE(C10:C344) and also at the top right of the window is the result of the formula. The Number 2,3,4 etc. allow, via is little scroll bar to have up to 30 arguments (in this case ranges of cells) to be in the one average formula. Click on the OK button at the bottom of the Function Wizard window. What happened. The formula has been put in C349 and the answer shown.
10. Each function can have from one to many arguments depending of the complexity of the function. Click on C349 and click on the function wizard icon (Fx) to the left of the formula bar. What happened? Because you already had a formula in C349 it now shows the Structure of the formula. Note how it is showing this. Click Ok
11. The best way to lean what functions that Calc has and how you can use then is to go through the list of then in the function wizard and if any are of interest to you try using them.

You have learnt how to use the function wizard to enter a function it a cell and how to find other functions in Calc

You may have noticed at the bottom left of the Calc window 3 tabs, Sheet1, Sheet2 and Sheet3. These are each separate sheets that have the same number of columns and rows that you have been using so far. Everything you have done so far can be done it any of these sheets. In fact you can have more than 3 sheets and can give each one a name other the 1 through to 3. But most importantly you can perform calculations across sheets. That is numbers and results in one sheet and be used in other sheets.

To demonstrate this we are going to build a simple personal budget. We will put the assumptions about our budget in one sheet such as the % change we expect from one month to the next and the main calculations of the budget in another sheet. The final budget result will then be returned to the assumptions sheet so that you can make changes to the assumptions and immediately see the results on the one sheet. Lets Start.

1. If you have not already done so start Calc and make sure you have a blank spreadsheet
2. At the bottom left of the Calc window click on Sheet2. What happened? Because you have and empty spreadsheet nothing much appears to have changed other then at the extreme bottom left it sais Sheet 2/3 indicating you are in the second sheet of a 3 sheet file. Click on Sheet3 then Sheet1
3. Right click on Sheet1. What happened? Notice the options on this menu. Click on insert sheet. In the name box enter “Assumptions” and click on OK. What happened? You not have a new sheet called assumptions. We could have renamed one other the existing sheet to Assumptions but I want to show you how to create a new sheet.
4. Notice you may now not be able to see Sheet3. Click on the single right pointing triangle to the left of the Assumptions tab. What happened? You can now see Sheet3 but not the Assumptions tab. Click on the left pointing triangle. Now you may not be able to see Sheet3 again. Drag the small rectangle to the edge of sheet 3 to right, about half way across. What happened. Now you should be able to see all the sheets
5. Right click on Sheet1 and click on Rename Sheet. Type in Budget and click OK. What happened? you have renamed sheet to Budget
6. Click on the Assumptions tab. In A1 type “Assumptions”.In A3 type “Item/Month”. In B3 type “Start Value”. In C3 type “January”
7. Point at the little black box at the bottom right of C3 and drag it to the right. Notice what the little yellow hints are saying. Continue dragging to the right until the hint sais December. Release the mouse button. What happened. You now have from February to December inserted without typing them. The Auto repeat function also work on dates as well as numbers. Actually dates are number which you may learn later on.
8. In A5 type “Income % change”. In A7 type “Expense % Change”. Increase the width of column A so that what you entered fits
9. In B5 enter 2000. In C5 and H5 enter .02. In D7 and I7 enter .01. Drag over C5 to N7, right click on Format Cells, Click on Percent and click Ok. What happened? The .02 show as 2.00% and the .01 as 1.00%. By formatting these as a percent Calc simply displays the value multiplied by 100 which is what a percentage is.
10. In A20 type “Results” in B20 type “Year Total”
11. In A22 type “Income” Notice what happened as soon as you typed the “I”? The text “Income % change” appeared. this is the auto complete function of Calc. It has completed what you stated typed based on the information that is above it in this column. This function can save you a lot of typing when you are typing the same or similar items in the one column. As we just want the word Income after typing the “e” press the delete key.
12. In A23 type “Expense”. In A25 type “Difference”. This is the all we need to do with the assumptions tab for now
13. Click on the Budget tab. In A1 type “Budget Calculations”. In A3 type “Item Month” In B3 Type “January”. Use what you have learnt above to automatically fill in February to December. In N3 type “Total”
14. In A6 type “Income” In A10 type “Expenses”
15. Rather than typing a list of expenses click on this link expenses list drag over the list right click and click on Copy. Switch back to Calc click on A12 right click and click on Paste. See you can copy information into Calc from almost anywhere.
16. In A50 type “Total” The layout of the Budget Sheet is now complete, now for the formulas to do the calculations. But before we start on that it would be a good idea to save the work we have done so far. From the file menu click Save, for the file name enter “My Budget”. Notice the Save as Type is set to ODF Spreadsheet. ODF is Open Document format with is a standard for all spreadsheets that all spreadsheet should adhere to. Unfortunatly the most popular spreadsheet. Microsoft Excell does not do this. So if you want people that use the proprietary Microsoft format to be able to read your spreadsheet you may also want to save it in one of the Microsoft formats. Click OK.
17. Click on B6. Type = to start a formula, Click on the Assumptions tab, Click on B5. Notice what it is showing in the formula bar, =Assumptions.B5. This is Calcs way of showing a cell in another tab, that is the name of the tab followed by a full stop and the cell address
18. Type +, Click on B5 again, Type *, click on C5, Press Enter, What happened. You are returned to the Budget sheet and the answer, 2040 is displayed. Click on B6 and look at the formula. =Assumptions.B5+Assumptions.B5*Assumptions.C5. That is from the assumption tab take the Start value and to it add the start value multiplied by the % change for January. If we were to only multiply the % change by the start value we would only have got the $40 increase so we also had to add the start value. Remember from a previous section that Calc does the multiplication before the addition which is exactly what we want.
19. See if you can work out the formula for Febl ruary considering it will be based on the January figure and the % change for February. Try entering it. If it does not work do the next point. Otherwise skip to the point after.
20. Click C6 and type =, click B6, Type +, Click B6, type *, click on the Assumptions tab, click D5, Press enter. The answer should be 2040 because the % change in February is empty which is the same as zero, so their is no change from January.
21. Click on C6, right click and click on copy, drag over D6 to M6, right click and click on paste. The results should be 2040 up to may because we did not have any % changes in those months and 2080.80 from June to December because of the 2% increase in June Only.
22. As we don't have starting figures for each expense type we for the purpose of this exercise we will may all the expense items $50 for January. Click on B12 end enter 50. Click on B12, right click and click on copy, drag over B13 to B41, right click and click on Paste. You should now have $50 for ever expense in January.
23. Click on C12. What do you think the formula will be for it considering it should be the based on January with the Expense % change for February? Try entering it. If it does not work do the next point. Otherwise skip to the point after.
24. Type =, Click on B12, Type +, Click on B12, Type *, click on the Assumptions tab, click on D7, Press Enter. The result should be $50.5 because of the 1% change.
25. We could now copy the formula for this expense to the remaining months and then repeat the above processes for the remaining expenses, however computers are meant to make life easier so why now copy this on cell to the remaining months for all the remaining expenses. We could do this but their is a potential problem. Do you know what it is? Remember the section on absolute verses relative cell addressing. look at the formula in C12, =B12+B12*Assumptions.D7. Internally Calc is saying take the cell to the left of me add it to the cell to the left of me times what on the Assumptions tab one to the right and five up. That is the relativity still applies to other sheets. Carefully look at this formula and decide what part of it must be made absolute to allow it to be copied to all the remaining months and expenses. The answer is in the next point
26. On the budget sheet the expenses go down from row 12 but on the Assumptions sheet the Expense % change is always on Row 7 so to make the formula appropriately absolute a $ needs to be placed in front of the 7. On the Budget tab click on C12 and on the formula bar click between the D and the 7 and type in the $ and press enter
27. Click on C12, right click and click on copy. Drag over C12 to M41 right click and click Paste. What happened? All these cell now have a result
28. To total the expenses, Click on B50, Click on the Sum icon to the left of the = on the left of the formula bar. Press enter. Click on A52 and enter “Difference”. Click on B52, type =B6-B50, press enter. Drag over B50 to B52 and right click and click on copy. Drag over C50 to M50, right click and click on paste. What happened. You now have the total expenses and difference for all the months
29. See if you can put put the formula to total the months across ways. If you succeed skip the next step
30. Click on N6, Click on the Sum icon to the left of the = on the left of the formula bar. Press enter. Click on N6, right click and click on Copy, drag over N12 to N41, right click and click on Paste. Drag over N50 to N52, right click and click on Paste. Notice you can paste multiple times. click on N51, press Backspace. Sometimes it is easier and quicker to paste over a range and delete what you don't want.
31. We now have the Budget sheet complete, but need to transfer the final result to the results section of the Assumptions sheet. Click on the Assumptions sheet, Click on B22, type = ,click on the Budget tab, click on N6, press Enter. What happened? What was in N6 on the Budget tab is now show. The simple way of getting a result from one sheet to another is to use a formula simply with = and the appropriate cell in it. See if you can enter the formulas for Expense and Difference. If you are successful skip the next point .
32. Click B23, type = ,click on the Budget tab, click on N50, press Enter. Click B25, click on the Budget tab, click on N52, press Enter.
33. The Assumptions tab is now complete, but lets see what it can do for us. Click on B5, the start value for income and change it to 3000 and press enter. What happened? The results change, Change it back to 2000. Try entering whatever % change for income and expenses for the months that don't have them.
34. Save the spreadsheet
35. This spreadsheet has some limitations if you want to use it in a real situation that you might like to see if you can overcome. These are:
36. You may have more than one source of income
37. Unlike the income each expense item does not have a start value
38. Some Expenses, eg Car Registration or Rates only happen once or a few times a year
39. The difference (income less expense) would be added to a Bank balance which could be invested and show as income in the next month
40. Taxation has not been taken into account
41. Anything else you think of

You have learnt how to create new tabs, rename tabs and perform calculations across tabs

You have now completed an introduction to using Calc. It is very important that you now practise what you have learnt by putting it to some practical use. You do not have to make a sophisticated spreadsheet. You can do something as simple as using it to add up a row of figures. Perhaps what you bought from the supermarket. You then may want to extend that to keep a record of what you are spending your money on by adding a column for the classification of items. A spreadsheet that has a list of values with a column beside it with a running total can be very handy to see and instantly you can see the total so far.

Although it may be easier to do some calculations in you head or us a calculator, as a way of learning, force yourself to use Calc. You will learn faster that way. Additionally you will find that a simple list of numbers, once stored in a spreadsheet, often gets used for other purposes. As you have seen in the exercises we have done, the results of some simple calculations can be used in other calculations and can extend to other sheets, and also, what you have not yet learnt, can be used in and with other files, that may not even be Calc files.

You have barely scratched the surface in terms of the functionality of Calc. It can do many more things. Once you are comfortable using the functionality we have covered then start looking at the other things you can do in Calc by going through the Menus, toolbars item and reading the help screens.

If you find something you want to use and can not work out how to use it yourself then email me at geoffreykgreig@gmail.com or write something on the Feedback page and I will get back to you.

Lastly please go to the Registration Page page and complete it, now that you have finished the course

Summarising information, Pivot Tables