Making Changes
Now is the time to make some changes to this spreadsheet. However, we do not want to lose the original, so first we will make a copy. Here is the spreadsheet as it is at the moment.
- Click in cell A1
- Select the whole spreadsheet by clicking and dragging, or by using the SHIFT and arrow keys
- Go up to the EDIT menu
- Choose COPY
- Click in cell A20. We are telling the computer that we want the copy of the spreadsheet to start from this point when we paste it.
- Go to EDIT
- Choose PASTE
Now you should be able to see an identical copy of the original spreadsheet.
Important: In the subsections that follow (4.2 to 4.6) you are going to make changes to this copied spreadsheet but NOT to the original spreadsheet above it.
Inserting Rows And Columns
Make these changes to the spreadsheet copy:
Insert a new row called Car between the rows containing Clothes and Others. To create this new row:
- Click in any cell in row 32 (for example, in A32, which contains the word “Others”)
- Go up to the Insert menu
- Click on Rows
- You now have an empty row in which you can type:
Car 59.56 4.67 296.45 112.49
Now insert another row called Presents between Car and Others.
- Again click in the row containing the word Others. Go to INSERT then ROW and type in:
Presents 45.78 10.78 27.99 278.89
To improve the layout of the spreadsheet, let us create a blank column between the figures and the Yearly Total, i.e. between columns E and F:
- Click anywhere in column F
- Go to the INSERT menu
- Choose COLUMNS
NoteF: After you typed in each of the new figures for Car and Presents, did you notice that the totals at the bottom of columns B to G changed? When we insert a new row or column, the formulae take the changes into account.
Deleting Rows and Columns
The expenditure figures in row 27 suggest that you gave up smoking after March 2003, so you have decided to delete this entry completely from your spreadsheet. This means that you can delete the whole row.
You would follow a similar procedure to delete a whole column. To do this you would click anywhere in the column, select EDIT, followed by DELETE then click on Entire Column and OK
Copying and Pasting Cells, Formulae, Rows and Columns
In Excel, as in Word and other Microsoft applications, we can copy the contents of any part, or even the whole, of a spreadsheet. When we do so in Excel, it copies not only text and numbers but also any formulae that are in the area that we are copying from. As an example, let us copy the first line of the lower spreadsheet (row 24 beginning “Fares and Travel”).
- Select cells A24 to G24
- EDIT
- COPY
- Click in cell A38
- EDIT
- PASTE
So, you can see that you can copy and paste not only text and numbers, but also the formulae and the computer will calculate the answer for the new location.
- Finally, as we do not need the extra row, click on the Undo button
to remove the data in row 38.
Formatting Number Cells to Show Decimal Places or Currency
To make our finished spreadsheet look more professional we shall make a couple of changes to the formatting of the numbers.
You may have noticed that some of the numbers in this spreadsheet look a bit odd. Look at these two rows:
Most of the numbers show two figures after the decimal point e.g. 24.32, 104.39 but others don't do so. This is because these other numbers would have 0 (zero) at the end after the decimal but do not show zeros. So, 226.00 appears as 226 and 19.50 appears as 19.5. We can change this so that all numbers show two decimal places, including zeros.
- Highlight all cells from B24 to G35
- Go to the FORMAT menu and choose CELLS
Next, change the Quarterly Totals and Yearly Totals to currency values:
- Do the same for cells G24 to G35 in the Yearly Totals column
Save your work again.
There are many different options in the NUMBER screen. You will be asked to FORMAT cells quite frequently so you will become familiar with these as your knowledge of Excel increases.
Your lower spreadsheet should look like this:
More About Number Format and Integers
In an earlier section you learnt how to change the format of numbers so that all numbers showed 2 decimal places. There is a shortcut way to do this, using buttons on the Formatting Toolbar. Try this:
- Select cell B24 containing the number 127.63.
- Click on the Increase Decimal button on the toolbar. Note how the number now has a 0 (zero) at the end: 127.630. This does not change the value of the number because the 0 comes at the end of the number after the decimal point.
- Click the Increase Decimal button again and you will see that another 0 has been added: 127.6300. The number therefore has 4 decimal places.
- This time, click on the button to the right of the Increase Decimal button. This is the Decrease Decimal button, which has the opposite effect. If you click it twice, your number will return to its original form: 127.63.
- Click the Decrease Decimal button twice more and you will see that the number has no decimal places and that it has been rounded up to the next whole number: 128
- We describe a whole number like this as being in Integer Format. You will meet this term in future Excel exercises, so you need to know what it is and how you can produce it (as you have just done!).
NEXT: PRINTING YOUR SPREADHSEET
