|
||
| HOME : EXCEL GUIDE : PART 1 |
| Digging |
| Crunching |
| Buffing |
| Reference - Using data - Excel Guide - Glossary - Statisticians |
A Guide to Using Excel for Handling Data
Introduction
This section introduces you to Excel, a powerful spreadsheet program, and its use in handling data. The aim of this section is to provide a basic familiarity with Excel, particularly its usefulness in helping us to analyse time series data, statistics and produce charts.
The assumption in this section is that users have no prior skills in Excel, so if you have a good grounding in the package you may wish to use this section selectively.
The section focuses on only a part of Excel's functions, but there should be sufficient here to allow you to continue independently, to find out perhaps more about the various shortcuts that have not been covered here.
Contents
- Getting started
- The grid and its cells
- Selecting a cell
- Selecting a range of cells
- Entering data in a selected cell
- Data entry on the worksheet
- Mathematical functions in Excel
- How to put a formula into a cell
- Useful short-cuts
- When things go wrong
- Updating totals
- Copying a formula
- Short-cut for copying formulae
- Formatting options
First Steps
- Getting started
Select Excel from your list of available programs in Windows. After Excel has opened, you will notice that its screen is a grid, made up of many equal-sized rectangles. At the top of the screen should be the usual Windows menu options. Beneath this there will be a toolbar with a certain number of icons (the exact number of icons depends on your pc set-up). At the bottom of the screen are a row of shapes labelled Sheet 1, Sheet 2 and so on.
This whole page is called a worksheet. At the top-left corner of the grid there is a label marked Book 1. This is the name of the worksheet that you start working with. This name only changes when you change it to a name that you select.
[Top]
- The grid and its cells
The worksheet grid is built of columns which are given an alphabetical letter on the top; and rows running horizontally across the sheet, each of which is given a number. The rectangles created by the combined columns and rows are known as cells.
[Top]
- Selecting a cell
When Excel is open, the empty worksheet is highlighted at cell A1 (it has a black outline). This outline shows that cell A1 is selected and can receive some data. You can select any cell you want by moving the mouse and clicking the left-hand mouse button.
Do this and notice how cell A1 no longer has a black outline and is therefore no longer selected. The cell that you move to and click on, in the way described above, is now the selected cell, in place of the previous one. Practice this manoeuvre until you are familiar with selecting cells.
You can also move from one cell to another by using the arrows on your keyboard. This is convenient when you want to move only small distances from the selected cell; using the mouse is appropriate in a number of other procedures.
[Top]
- Selecting a range of cells
You will find that often you will want to select more than one cell at a time. This is carried out by using a click-and-drag mouse movement.
Let's say you wish to select cells A1, A2, A3 and A4. Move the mouse pointer to cell A1 and push and hold down the left-hand mouse button. Drag the mouse, whilst holding the left-hand button down, from cell A1 straight down to cell A4. Let go of the mouse button when you have selected the four cells.
When you try this, you'll notice that the first cell is white and the other selected cells are black; also, there's a highlighted outline around the whole area selected. If this process goes wrong, simply move the mouse pointer away from the range you want to highlight, select a cell outside the range, then start again to select the desired range of cells.
You can use the same procedure to select cells horizontally as well as vertically.
A combination of vertical and horizontal selections is also possible using this process. Clicking and dragging A1 to A4 can be continued into a horizontal movement with the mouse until the whole range selected includes B1, B2, B3 and B4 as well. This selection of blocks of cells in separate columns and rows, as well as in adjacent columns and rows, is very commonly used; so make sure you are comfortable carrying out the procedure.
Finally, you can select entire rows or columns by moving the mouse arrow to the letter or number that identifies that row or column. Clicking the left-hand mouse button on that letter or number turns the entire row or column black. This is a useful process if you wish to add or delete a column, or to change the format of all the data in the column or row, for instance by emboldening it.
[Top]
- Entering data in a selected cell
Now that you're feeling comfortable with selecting cells, you're ready for the next step, which is to start putting information into the worksheet. In general there are three types of information that we may want to enter into our selected cells: letters, numbers and formulae.
If you select cell A1 and use the keyboard to type the words 'Wage rates', you will notice that the words 'Wage rates' appear in cell A1 and also, on a line underneath the toolbar. This area is called the formula bar and as you type your information into the selected cell, you can follow what you are typing by reading this bar. Any mistakes in typing can be corrected easily with the arrow keys and the delete button.
When you have typed the title 'Wage rates', hit the Enter key. The selected cell now moves to cell A2 and the text you typed is now entered in cell A1. If you have to change this title, you select the cell, using the mouse or the arrow keys, and then press the backspace button. This removes the contents of cell A1 and you are then free to enter whatever title you want.
In fact, you may notice that it is not strictly necessary even to press the backspace button, to carry out this procedure. If you select a cell with some information in it, the material will disappear as soon as you start to enter the new information.
Numbers are entered into cells in exactly the same way: select the cell, enter the number(s), hit Enter.
[Top]
- Data entry on the worksheet
Now you should be looking at a worksheet in Excel that is blank apart from the title 'Wage rates' in cell A1.
Next let's enter some more information into this worksheet: in column A, starting in cell A2, enter the following ten numbers in order, ending at cell A11.
3.87, 3.90, 3.94, 3.99, 4.06, 4.12, 4.20, 4.20, 4.22, 4.45.
These numbers represent the hourly wage rates (in £ sterling) of the ten lowest paid jobs in the UK in 1997 (before the introduction of the National Minimum Wage). Let's say we want to analyse these to compare them with low pay in other EU countries.
There are a number of ways in which we could describe this data, but the most common of these is to calculate the average of all the wage rates. In this case we want to find the mean average of the data. This is something Excel can do quickly for us, but we must learn some of the symbols for mathematical operations in Excel first.
Make sure you save this worksheet if you want to leave this Guide temporarily. This will save you having to input this data again later.
[Top]
- Mathematical functions in Excel
You can find the following mathematical symbols on your keyboard: + - / *. To instruct Excel to perform a mathematical operation, you simply key-in the number, the symbol for the operation, and the second number. Make sure you don't leave any spaces between any parts of the instructions.
So, =9*4 tells Excel to multiply 9 by 4.
=83.2/17.1 tells Excel to divide 83.2 by 17.1
=A4*.95 tells Excel to multiply the value in A4 by .95
Excel performs calculations in the usual mathematical way. In other words it doesn't necessarily complete all operations from left to right. First, Excel completes all operations in brackets, then it multiplies and divides, and finally it adds and subtracts.
Be sure to take care about the sequence in which Excel carries out the operations. A good way to understand this point is to carry out the following exercise:
Go to your worksheet and select cell C1 and type in the following formula:
=2+4*5 Then hit Enter. The result in cell C1 is 22.
Now select cell C2 and type in =(2+4)*5 and hit Enter. Result? 30.
Delete the numbers in cells C1 and C2 before continuing.
Remember the importance of putting the equals sign in before the formula that you want Excel to perform. The equals sign tells Excel to carry out the calculation rather than just put in the numbers that you type into a cell.
[Top]
- How to put a formula into a cell
If you learn the ways of using formulae in Excel, you will be able to use the package to carry out quickly all manner of complicated mathematical functions.
Go back to your worksheet with the list of ten hourly wage rates in cells A2 to A11. If you wanted to know the average level of wages in the group, you'd need to add up the list of numbers, and divide the result by the number of items on the list. Excel will do this for you.
Select cell A13. This is the cell that we want to record the total in. Type in the formula that follows. Remember to leave no gaps in this or any formula:
=Sum(A2+A3+A4+A5+A6+A7+A8+A9+A10+A11)
Then hit Enter. The total (40.95) will appear in cell A13.
The formula told Excel to add up a range of cells and has shown each cell in the series to be summed. If you get an error message or the formula seems to have been ignored, don't panic! Review carefully the formula you have entered into cell A13. If you have made only the slightest error, Excel won't be able to perform the operation.
If the numbers in any of the cells indicated in the formula, change, the total in cell A13 will change too. Try changing the value in cell A7 to 4.17, (by left-hand mouse clicking in cell A7), entering the new value and hitting Enter and notice what happens to the total in A13. After doing this, change A7 value back to 4.12.
Now, let's get to calculating the average of the wage rates. We do this by dividing the total we have produced with the formula in cell A13, by the number of entries in the list. So select cell A14 and enter the following formula:
=A13/10
This tells Excel to take the total in cell A13, divide it by 10 and put the result in cell A14. Hit Enter and see the average appear in A14.
As before, if any of the values change in the original list, then the values in cells A13 and A14 will alter accordingly. Try to change the value in cell A7 to 4.17 again. Change A7 value back to 4.20 afterwards.
Because formulae in Excel can be quite long, we could have chosen to combine the two operations in cells A13 and A14. Try entering the following formula into cell A15:
=Sum(A2+A3+A4+A5+A6+A7+A8+A9+A10+A11)/10
Notice that if you have entered the formula correctly, you should see the same result in cell A15 as you have in A13.
[Top]
- Useful short-cuts
Even with the combined formula that you have just used in cell A15, the process may seem a little laborious. Fortunately, we can use a number of short-cuts to streamline this effort.
In the previous section we created a formula for the total of the ten cells by entering the value in each of the cells. The way to achieve the same results in a fraction of the time is to use the short-cut
=Sum(A2:A11)
This formula tells Excel to add up all the values in the cells from A2 to A11.
In the same way, Excel will carry out certain calculations by a word command alone. In this case, we can ask Excel to give us the average of the values in the given range of cells. Simply select the cell where you want the average to appear and type in the following formula:
=Average(A2:A11)
There is a large number of operations for which you can use Excel in this way. For example, if you want to know the square root of a value in a particular cell, simply select the adjacent cell and enter the following formula:
=sqrt(cell number)
[Top]
- When things go wrong
If you make a mistake in entering any formula, you will get an error message which is usually preceded by the symbol #.
This will happen also if you ask Excel to perform an operation on a cell which is empty and if you ask it to carry out the operation on the cell in which you are entering a formula.
The immediate response to any error message should be to check carefully the formula entered or the cell in which it is entered.
If you get an error message that you don't understand, use the Excel Help menu and the Index option.
[Top]
- Updating totals
If we return to the list of wage rates in column A and imagine that we wanted to calculate the impact of a pay increase. If, for example, we wanted to view the effect of a 3% claim on the wage rate in cell A2, we multiply the value in that cell by 103/100 (or by 1.03).
Excel can perform this for us. On the worksheet you used in earlier tasks, select cell B1 and enter the heading '3% increase'. Then select cell B2. This is where we want the wage rate in A2 to be compared to the rate after the pay increase. To get Excel to do this, enter in cell B2 the following formula:
=A2*1.03
Remember not to leave any spaces in the formula. This formula tells Excel to multiply the value in A2 by 1.03 and then enter the result in cell B2.
[Top]
-
Copying a formula
Let's say we want to convert all the remaining wage rates in column A to reflect the wage increase outlined above. We could choose to repeat the process that we used in cell B2, through cells B3, B4, B5 to B11.
There is a much faster way to get the same result. We simply copy the formula in cell B2 and apply it to all the cells we want updating. To do this, carry out the following steps:
- Select cell B2. It already has the formula in it, of course, and we want to copy it and apply it to all the cells from B3 to B11.
- Move the mouse pointer to the Edit drop-down menu at the top of the screen and left click on the Copy option. Cell B2 should now have a rotating black and white highlight around it.
- Now put the mouse pointer in cell B2, press down the left-hand mouse button and hold it down.
- Next, drag the mouse pointer through cells B3, B4, B5, B6, B7, B8, B9, B10 and B11. The selected cells should turn black, except for cell B2, which should remain as before.
- Release the left-hand mouse button when you have selected the desired range of cells.
- If you make a mistake, click on a cell at random to clear the mistake, and try again.
- When you are happy with your selection of cells, hit Enter. The selected cells in the B column should now contain the updated values.
- Click outside the range of highlighted cells and the black background to the cells should disappear. The formula will now have been copied into the previously empty cells.
[Top]
- Short-cut for copying formulae
Once you have entered a formula into a cell, you can easily copy that formula by using this short-cut.
Firstly, select the cell which contains the formula. Notice that when you select the cell, there is a border around the cell, with a small rectangle in the bottom right-hand corner. If you position the mouse pointer directly over that small rectangle, the pointer changes into a cross-hair. Press down and hold the left-hand mouse button, dragging it through the cells into which you want to copy the formula. When you reach the last of your selected cells, release the left mouse button and select a cell outside the range.
[Top]
- Formatting options
Excel offers you the opportunity to change the layout of your worksheet in a number of ways; here are two of these options:
You can change the width of the columns manually, by positioning the mouse pointer directly over the line between the two letters that identify a column, (for instance between B and C column). Notice that the cursor becomes a double arrow. If you hold down the left-hand mouse button and move to the left or right, you will see that the width of the column changes. This is a useful option if the cell contains more information than it can display in its original format.
You can also add or delete rows or columns easily in Excel. To do this, you need to select the particular row or column, go to the Edit menu and click on the Delete option. Not only does that area in the worksheet disappear, but so too does all the data contained within it. This means that all formulae entered in the row or column will also be lost.
If you make a mistake and didn't mean to perform a row or column deletion, remember that you can restore the lost information by clicking on the Undo button on the Toolbar. If you're not sure which button this is, run your mouse pointer along the Toolbar slowly, stopping as you reach each button; a description of the button function will appear underneath the mouse pointer.
To insert a row or column, you need to select the whole row or column, click on the Insert label at the top of the page and select Row or Column from the drop-down menu that appears. The new row will appear immediately above the row you selected; the new column will appear immediately to the right of the column you selected.
Notice that, unlike with row or column deletion, inserting a new row or column does not affect any formulae that are already entered in the worksheet. This is because Excel automatically changes the entries already made, to accommodate the new column.
Practice these deletion and insertion steps until you are comfortable with the procedures.
[Top]