A Guide to Using Excel for Handling Data

Contents

• Saving, opening and closing files
• Working with multiple sheets
• Sorting data
• Filtering data
• Using Excel for Inferential Statistics

• Saving, opening and closing files

  Data entered into an Excel workbook is kept in units known as files. These files can be opened, saved, closed, and then opened again, by following the right procedures.

  For this section, it will be useful to open a new Excel file.

  Select cell A1 and enter your name.

  Now save this information, by selecting File and choosing one of the three 'save' options that are listed - save, save as, and save workspace. The first two of these options are the most important to us at this stage:

  Save will save the workbook with whatever name is currently displayed (either in the file name bar, or at the top left hand side of the sheet. So, saving by this process will save this worksheet with the file name 'Book 1'.

  Save as allows you to choose a file name that is appropriate for you. This could follow some system of filing that you use, or just an easy-to-remember name. Once you have given the workbook a name, you can use the save option to save the workbook by the name you have already given it. File names must contain no more than eight characters and have no blank spaces.

  So, use the save as option, calling the file 'myfile1.xls'.

  The file is now saved with the name myfile1. We now want to close the file and re-open it to make sure that the saved information has really been kept.

  Select File from the menu bar and click on Close. The file will now be closed.

  We could do a number of things now: from selecting File from the menu bar and clicking on Exit, we could leave the program; to begin to create a new workbook we would select File from the menu bar and click on New; or we could open an existing workbook, which is what we want to do now:

  Select File from the menu bar and click on Open. All the Excel files that are available for use will appear. Click on the one that you want to open (myfile1) and then click on OK.

  Myfile1 should appear on the screen exactly as you last saw it.

  It is possible to work with two separate workbooks: with myfile1 still open and on-screen, select File from the menu bar and then New. A new workbook, which by default will be named as Book2, will appear on the screen. It is simply a matter of using the Window option on the menu bar to move from one of these workbooks to the other.

  Now use Window to make Book2 active and enter your age in cell A2. Now go through the Save As process again, making sure that you change the default name of thre workbook to 'myfile2'.

  Then close both files by selecting File from the menu bar and Close from the present screen and then File and Close again from the screen that appears next.

  Both workbooks should now be closed and a blank screen should appear.

  Now select File and Open. Click on the myfile1 name that appears in the left-handside list of files available for opening, and then on the myfile2 name. Both files will now be open once again.

• [Top]

• Working with multiple sheets:

  The sheets of an Excel workbook are automatically named Sheet 1, Sheet 2, Sheet 3, and so on. Often, though, you will want to change these to names that are relevant to you task and needs.

  To see how this is done, open a new Excel workbook. With Sheet 1 open, double click with the left-hand mouse button on the Sheet1 tab at the bottom left of the screen. This causes the tab to be highlighted in black. Press the delete key and enter 1999data, which should now appear on the sheet tab.

  Now double click on the Sheet2 tab and rename it 2000data.

  The data in the various sheets of the workbook can be linked in a number of ways. Let's try to do this now:

  Select the 1999data sheet by clicking once, then in A1 enter the label 'Quarterly profits'. Next in cells A2:A5 enter the following data:

  15000 21000 12500 8250

  Now select the A1 cell and copy the label on this sheet to the 2000data sheet by choosing Copy from the Edit function on the menu bar, (cell A1 then flashes). Click on the 2000data sheet tab and click in cell A1 of this sheet. Now select Edit again and Paste. Notice how the label appears in cell A1 of the 2000data sheet.

  Making sure that you still have 2000data sheet selected, enter the following data in the A2:A5 cells:

  13000 26000 15900 12350

  What we have done is to enter the firm's quarterly profits for 1999 in the range A2:A5 of the 1999data sheet, and the firm's 2000 profits in the corresponding area of the 2000data sheet.

  Now we are going to name a new sheet 1999-2000 data and get Excel to calculate the combined two year profits.

  Firstly, re-name sheet3 as 1999-2000data in the same way as we re-named sheets1 and 2. Then in cell A1 enter the label 'Combined profits 1999-2000'.

  Now activate cell A2 of the 1999-2000data sheet and enter the following formula:

  =1999data!A2+2000data!A2

  Notice that we use an exclamation mark to separate the sheet reference from the cell reference in that sheet.

  A result of 28000 should be returned.

  What we have done is to instruct Excel to calculate the combined profit for the first quarter of 1999 and 2000. These values were located in cell A2 of the 1999data sheet and cell A2 of the 2000data sheet.

  This process can be mirrored throughout the four quarters of each year by activating the 'handle' of cell A2 on the 1999-2000data sheet (at the bottom right-hand corner of the cell):

  Activate cell A2 of 1999-2000data sheet, activate the handle and drag down to cell A5. When you release the mouse button, the formulae should have been entered into the cells and the combined quarterly profit values should now appear.

  This process may not have seemed too laborious, but we were dealing with data for two years; imagine if we had data over a twenty year period to handle! The formula entered into cell A2 and subsequently copied through the sheet would quickly become too complicated.

  As you've probably guessed, we can use a short-cut to overcome this problem. Try entering the formula:

  =SUM(1999data:2000data!A2:A2)

  we get Excel to add the A2 cells of all the sheets within the range specified either side of the first colon sign in the formula.

  Check for yourself that this works by replacing the formula in cell A2 of 1999-2000data with this new formula. Then copy the formula down into cells A3:A5 to get the 2nd, 3rd and 4th quarter combined totals.

• [Top]

• Sorting data:

  Excel has a very powerful data sorting feature. Use the accompanying Excel 'Eupower' workbook to follow an illustration of this degree of sophistication. Get eupower.xls (15K).

  On the EUpower workbook, we have the EU Council of Ministers' voting rights and European Parliament representation of each of the 15 member states, agreed at the December 2000 Inter-Governmental Conference in Nice.

  The column headers at the top of the sheet identify what we call fields and the individual EU member states' names are known as records.

  As you can tell from an inspection of the data, there are several states that share either the same number of voting rights or MEPs. It is also clear that the data have not been sorted into any kind of order.

  What we are going to do is to sort the data into descending order of voting rights and then to use the total of MEPs for each member state as an additional sorting criterion.

  The first thing to do is to select the entire area, from A1 to C16 as the range to be sorted. Note for future reference that the whole area of a worksheet must represent the sorting range. If your worksheet contained, say, 10 fields and 30 records, then even if you intend to sort those records only on the basis of one of the fields, you must include all 10 fields and all 30 records in the range selected for the sort.

  So, with the A1:C16 range selected, choose Data and then Sort from the menu bar.

  A dialogue screen should appear. Click on the Sort By tab and select 'Votes' as the primary sort from the list of available fields that appears. The Descending button alongside should also be clicked.

  The secondary sort, to differentiate between tied states, was activated by clicking on the first Then By tab and selecting MEPs from the list. The Descending button was selected once again.

  Note that if there was a third criterion on which the sort could proceed, there is the capacity to do this within the Sort function.

• [Top]

• Filtering data:

  This is the process of selecting those record from a list that meet certain criteria, decided upon by the user.

  To illustrate this function we will use the Eupower workbook from the previous task.

  The first thing to do is to identify to Excel all of the field names and the full list of records. This is done by clicking and dragging with the mouse A1:C16.

  Next choose Data, then Filter and then AutoFilter from the menu bar. When this is done you should see that four arrow tabs have been inserted into the cells containing the field names (A1:C1).

  To interrogate this data list you need to click on the arrow tab in the field that relates to the question you want to ask. For example, if you want to know which EU member states have voting rights over and including the level of 10, you should use the process that follows:

  Click on the arrow tab in the B1 cell, because this is the top of the votes field, and from the prompt that follows, select (Custom...). A dialogue box should appear, within which you will be offered the chance to select from a list of arithmetic operators.

  From this list select 'greater than or equal to' and on the adjacent box enter the criterion, which in this case is 10 (votes).

  Clicking on OK should then start the filtering process, which should result in the following outcome:

  EU state	Votes	MEPs
  Germany	29	99
  France	29	74
  UK	29	74
  Italy	29	74
  Spain	27	52
  Netherlands	13	25
  Greece	12	20
  Belgium	12	20
  Portugal	12	20
  Sweden	10	18
  Austria	10	17

  Notice that the arrow tab in cell B1 has changed colour, from black to blue. This shows you that this is a filtered list.

  You can change the list back to its unfiltered state by clicking on the blue arrow tab and selecting (All) from the prompt that follows. This restores the list to its full status and the arrow tab reverts to its original black colour.

  You may have seen that the Custom AutoFilter dialogue box also contains a second set of tabs at the bottom of the box. These allow you to ask more complex questions. For example, you may want to know the EU member states whose voting rights are equal to or in excess of 10 votes, but do not exceed 27 votes.

  To do this, click on the arrow tab on the Votes field, select (Custom...), greater than 10. Then, making sure that the 'And' is checked in the middle of the screen, select less than and enter 28 in the bottom two tabs. Click OK and the records that comply with both of these criteria should be selected from the list, as is illustrated below:

  EU state	Votes	MEPs
  Spain	27	52
  Netherlands	13	25
  Greece	12	20
  Belgium	12	20
  Portugal	12	20
  Sweden	10	18
  Austria	10	17

  The next stage of this Excel guide requires that all of the original records are restored to their original state. To do this simply click on the arrow tab in B1 and select All from the list that appears.

  The first filtering examples only took place on the first available field 'Votes', but it is simple to broaden the interrogation and ask questions that relate to more than one field.

  For example, imagine that we wanted to know the names of those EU member states that have voting rights equal to and in excess of 10 votes and a total number of MEPs equal to or in excess of 15.

  To do this we follow the same procedure as before in the voters column, to select all records with a number of votes greater than or equal to 10. Once the list has been narrowed down to only include these records, click on the arrow tab in the MEPs filed and use the same procedure as above to select only those states whose total MEPs equal or exceed 15.

  You should find that the following list results:

  EU state	Votes	MEPs
  Germany	29	99
  France	29	74
  UK	29	74
  Italy	29	74
  Spain	27	52
  Netherlands	13	25
  Greece	12	20
  Belgium	12	20
  Portugal	12	20

  Two additional points are needed to be made here:

  Firstly, selecting Print from a filtered list will produce a hard copy of the filtered list rather than the original full list. This is usually the best way of gaining a copy of the records that satisfy the criteria you have set.

  Secondly, you will often want to add the values in a filtered list. For example in the Eupower workbook used above, you might want to know the combined voting strength of those EU member states whose voting rights are at least equal in total to 10 , but not in excess of 28. But be careful, for a SUM function will not work on a filtered list, only on a list that has been left unfiltered.

  For filtered lists, Excel has a special totalling function known as SUBTOTAL.

• [Top]

• Using Excel for Inferential Statistics

  Much of the content of TimeWeb is about describing data sets. What we are interested in in this section is the process of inference from data sets.

  With inferential statistics we are dealing with a set of sample values, drawn from a larger data set known as the population. There's no guarantee that sample data are truly representative of the population from which it comes.

  If we can be confident about the sample data being representative, then we know that the sample data are going to be useful. We can then use what we know about the sample data to make judgements about the statistical nature of the population data.

  These judgements will always have to be subject to reservations. These are measured in terms of probabilities: for example, we may have a sample data set whose mean value appears to approximate to the population mean, bearing in mind that there is a ?% chance of this not being so.

  [Top]

The next part in this series.