Excel Basics

1. Excel Basics Faghrie Mitchell BCB 703: Scientific Methodology

2. A spreadsheet is used to: display data in a table or graphically display and analyse data, and help you manage a project. The Aim of using Excel The Aim of using Excel

3. To give you a quick introduction to Excel, which will enable you to quickly master the tools and functions needed to create a spreadsheet. This is not a comprehensive Excel tutorial; only the necessary Excel tools and functions will be covered. The Aim of this Tutorial The Aim of this Tutorial

4. Before we start with the Excel it is important to note that we will be using Microsoft Excel 2002 for this tutorial. If you are not using this version of the software, it should not be problematic as I will only be covering the basics of the software. However, be aware that there might be small variations as you go along. Also note that you can patch or update your version of the Microsoft Office software by going to http://office.microsoft.com/en-au/officeupdate/default.aspx. The Aim of this Section Version of Excel

5. There are a number of ways to start Microsoft Excel: 1. From the desktop 2. Using the Start Button 3. Opening an existing spreadsheet 4. Going to the Program Files folder Remember: The method you use will depend on the particular computer you are using and whether the icons are displayed as shown. The icons might not always be displayed as shown here. In that case you should use an alternative method to start up the program. Start up Microsoft Excel Start up Microsoft Excel

6. Start up Excel from the desktop Start up Excel from the desktop • When you start up Microsoft Excel, a blank spreadsheet will be displayed. • The spreadsheet consists of a number of elements which allows you to create, edit, and analyse data. To do this you need to organize data into lists or tables, as shown above or in the next slide.

7. Elements within the Excel spreadsheet Elements within the Excel spreadsheet • When Excel start up, it displays a spreadsheet, more specifically it displays a worksheet • There are three worksheets displayed namely Sheet 1, 2 and 3 • Usually one sheet is sufficient, providing 256 columns and 65,536 rows of space within which to enter data, i.e. over 16 million cells per sheet

8. Elements within the Excel spreadsheet Elements within the Excel spreadsheet What are columns? Columns are denoted by a letter. In the image on the left the arrow points to column A; next to it you have columns B and C • What are columns? • Columns are denoted by a letter. In the image on the left the arrow points to column A; next to it you have columns B and C What are rows? Rows are denoted by a number. In the image on the left the arrow point to row 7 • What are rows? • Rows are denoted by a number. In the image on the left the arrow point to row 7 What are cells? Cells are denoted by a unique letter (from the column) and number (from the row). So, the arrow is pointing towards cell B29. • What are cells? • Cells are denoted by a unique letter (from the column) and number (from the row). So, the arrow is pointing towards cell B29.

9. Now that we know the most basic of elements of the spreadsheet, we can start entering data into the spreadsheet. However, before we enter any data click first save your file. Click on the menubar File|Save As … which will bring up the following dialog: Save the spreadsheet Save the spreadsheet • Enter a filename, for example, Sample.xls. Navigate (if necessary) to where you want to save the file and click the Save button.

10. After saving your spreadsheet should look as follows, with the filename displayed in the windows blue titlebar. The cursor is in cell A1. Save the spreadsheet Save the spreadsheet

11. There are several ways you can navigate within the worksheet. Navigating in the spreadsheet Navigating in the spreadsheet • When you press the tab key, you will see the cursor move one cell to the right from A1 to B1. • Press the tab key a second time and it will move from B1 to C1. • Now, press SHIFT-tab to move the cursor from C1 to B1. As you can see the SHIFT-tab moves the cursor one cell to the left. • Press the SHIFT-tab a second time to move the cursor from B1 to A1.

12. When you press the down arrow key (↓), you will see the cursor move one cell down from A1 to A2. Navigating in the spreadsheet Navigating in the spreadsheet • When you press the down arrow key (↓) a second time, the cursor will move another one cell down from A2 to A3. • Move the cursor back to A1 by pressing the up arrow key (↑) twice. • Note: You can also use the Enter key to enter data, and at the same time move the cursor one cell down.

13. Other useful keys for navigating in the worksheet include: Page DownMoves DOWN one screenful. The cursor effectively moves downwards to a cell in the same column, in this case A36. Page UpOpposite of Page Down; it moves UP one screenful. Navigating in the spreadsheet Navigating in the spreadsheet

14. Ctrl-arrow keys are useful if you have more than one table of data in the worksheet: Navigating in the spreadsheet Navigating in the spreadsheet Ctrl-→ Skips right, from one set of data to another set. Pressing Ctrl-→ takes the cursor to D1. Pressing it again takes the cursor to the next set of data in the worksheet. Ctrl-← Press Ctrl-← twice to return the cursor to A1.

15. Ctrl-arrow keys are useful if you have more than one table of data in the worksheet: Navigating in the spreadsheet Navigating in the spreadsheet Ctrl-↓ Moves the cursor to the last occupied cell of the current column, A8. Pressing it a second time, skips toward the next set of data at A72. Ctrl-↑ Press Ctrl-↑ twice to move from A72 to return the cursor to A1.

16. You should now know some of the elements of the spreadsheet, and have a basic knowledge of how to navigate within a worksheet. Adding data to the spreadsheet Adding data to the spreadsheet Let us now add some data to the blank Excel file called Sample.xls, which we have created earlier.

17. There are four types of data which are recognised by Excel, namely, Ordinary text Numbers Dates and times, and Boolean values Adding data to the spreadsheet Adding data to the spreadsheet The following slides will show you how to enter these data types into an Excel worksheet. Boolean values will however not be dealt with as it is an advanced technique, and is not essential for the requirements of this tutorial.

18. Adding data to the spreadsheet Adding data to the spreadsheet • Just to recap what has been said earlier: • A spreadsheet is used to: • display data in a table or graphically • *display and analyse data • help you manage a project *Note: I will omit the data analysis because those techniques will be dealt with under Biostatistics (BCB702).

19. Adding data to display data Adding data to display data • The following slides will show you how to enter the data types (ordinary text, numbers, and dates and times) mentioned in the previous slide to display the data in a table or graphically. • The sample data on the right was obtained from the Philosophy of Science Notes prepared by Prof Karen J Esler.

20. Adding data to display data Adding data to display data • The table below has been created in MS-Word. It is easy to copy and paste cells from MS-Word into Excel. However, for the purpose of showing you how to enter different data types, I will recreate this file from scratch in the following slides.

21. Ordinary text includes column headings, descriptions, and any content that Excel can't identify as one of the other data types. Text for the headings is easily inserted by typing the text into a cell and then using the cursors or Tab button to move to the next cell. Adding data: Ordinary text Adding data: Ordinary text

22. Ordinary text: Select the cells with the text. Centre the selected text. Bold the selected text. Next enter the legend of the table, starting at cell A10. Adding data: Ordinary text Adding data: Ordinary text

23. Numbers:Complete the text for the tables legend. Next, start entering the time values in column A. Select the legend text. Press the Cut button or Ctrl-X. Place the cursor in the cell (A13) where you want to insert the selected text. Press the Paste button or Ctrl-V. Adding data: Ordinary text Adding data: Numbers

24. Numbers:Complete the rest of column A. You will notice that when you enter 5:50, that Excel automatically recognizes it as a time value. Adding data: Ordinary text Adding data: Numbers

25. Numbers:Enter the remaining values for columns A to F. The values that was entered for columns A to F is automatically recognised by Excel as numbers. Adding data: Ordinary text Adding data: Numbers

26. Excel right-aligns numbers when they are entered Excel left-aligns text when it is entered Adding data: Ordinary text Different handling of Text and Numbers

27. The table on the right shows how Excel interprets text, numbers, and dates as you enter them into the cells In column A it left aligns the text Adding data: Ordinary text Different handling of Text and Numbers • In column B it right aligns the numbers • In column C it automatically right aligns the times • In column C it treats the dates as text

28. You can change the way Excel interprets the information you have entered. Right-click on the cell or cells you want to change and select Format Cells Adding data: Ordinary text Different handling of Text and Numbers

29. The Format Cells dialog has a number of tabs Text can be edited by using the Font tab of the Format Cells dialog Text can also be edited using the Align tab The Number tab allows you to set the format of a cell or group of cells Adding data: Ordinary text Different handling of Text and Numbers

30. We will omit editing column A with the text because it is very straightforward to do The numbers in column B will be edited using the Format Cells Number tab A number of categories are available with which text can be formatted Adding data: Ordinary text Different handling of Text and Numbers

31. General format cells have no specific number format This is the default setting when you enter numbers into a cell Adding data: Ordinary text Different handling of Text and Numbers

32. Number is used for general display of numbers. Allows you to set the decimal places, and allows you to specify whether a comma is used as a 1000 separator For example, set the number of decimal places in column B to two and remove the comma as a separator Adding data: Ordinary text Different handling of Text and Numbers

33. Currency formats are used for general monetary values For example change cell B5 to Rands Accounting category is similar to the Currency category Adding data: Ordinary text Different handling of Text and Numbers

34. Date category allows you to choose which date and time format you want to use. For example, change the general format of cell C2 and C5 to date formats Likewise, the time format of C3 and C4 can be changed Adding data: Ordinary text Different handling of Text and Numbers

35. The data on the right will be used to draw a descriptive chart Adding data: Numbers Creating Charts: Descriptive Data

36. There are two ways to insert a chart in to your spreadsheet. 1. Click the Chart Wizard icon on the Standard toolbar OR 2. Click on Insert|Chart on the menu bar Both will display the Chart Wizard dialog Adding data: Numbers Creating Charts: Descriptive Data

37. The Chart Wizard dialog (Step 1 of 4) has a number of chart types and sub-types, each with a specific purpose Pick the Line chart, and the subtype line with markers displayed at each data value, as shown on the right Click the Next button Adding data: Numbers Creating Charts: Descriptive Data

38. The Chart Wizard dialog (Step 2 of 4) will be displayed (as well as a preview of your chart) And you will notice that Excel has selected your data in the spreadsheet Select only the data that is needed for the chart, as shown on the right Adding data: Numbers Creating Charts: Descriptive Data

39. The Chart Wizard dialog (Step 2 of 4) will change to show the new selection Click the Next button The Chart Wizard (Step 3 of 4) will be displayed, which allows you to add titles, and to modify various parts of your chart For now leave this blank and click the Next button Adding data: Numbers Creating Charts: Descriptive Data

40. The Chart Wizard dialog (Step 4 of 4) will be displayed, which allows you to insert the chart into its own worksheet, or within the present worksheet (Sheet 1) Pick the latter option, as shown on the right Click the Finish button to insert the chart in Sheet 1 Adding data: Numbers Creating Charts: Descriptive Data

41. The chart is finished, and it is a good preliminary chart However, elements of the chart need to be changed, for example, the labels like the timescale, and the colours used on the chart Note: This chart is not be acceptable for publication, because of lack of replication (only one measurement, only one site) Important: The following slides will include replication, however, do not assume that the replication shown here is sufficient, as it is only being done to show how to display data Point: Each experiment needs to be assessed on its own, to determine if there is sufficient replication for statistical purposes Adding data: Numbers Creating Charts: Descriptive Data

42. Change the timeline data from “actual time” to “time elapsed” Select the time data in your spreadsheet and change the data’s format to Number or General The data in column A will change, and it will also show on the chart Type in the “time elapsed” for each observation Adding data: Numbers Creating Charts: Descriptive Data

43. Labels can be easily added to the chart by right-clicking the chart, and selecting Chart Options Colours can be changed by selecting each element individually on the chart For simplicity, the colours and labels will remain unchanged, so that we can move on to data analysis Adding data: Numbers Creating Charts: Descriptive Data

44. Remember that in order for your data to be accepted for publication, it is necessary to replicate the experiment in the field In other words, more similar experiments need to be performed near the nest of the Pugnacious ant, and to observe the behaviour of the ants in relation to the seeds Adding data: Numbers Creating Charts: Data analysis

45. A total of five sites have been selected for replication For simplicity, the individual species will be looked at separately, and only species A, C and E will be considered here This gives the following data sets :A1 to A5, C1 to C5 and E1 to E5 Adding data: Numbers Creating Charts: Data analysis

46. The data for sites A1 to A5 in Sheet 1 The data for sites C1 to C5 in Sheet 2 The data for sites E1 to E5 Sheet 3 Adding data: Numbers Creating Charts: Data analysis

47. You are going to create new data in column G In cell G1, type MEAN Select cell G2. You are going to insert a formula into cell G2. Type =AVERAGE(B2:F2) into cell G2 to get the average or mean for cells B2 to F2 Copy this formula further down column G Adding data: Numbers Creating Charts: Data analysis

48. Do the same for species C (Sheet 2) And also for species A (Sheet 1) Adding data: Numbers Creating Charts: Data analysis

49. Next in cell H1, type STD DEV for standard deviation (bold and centered) Standard deviation will be used as error bars Select cell H2 Click the SUM function on the Standard toolbar and select More Functions… Or Click the menubar Insert|Function Adding data: Numbers Creating Charts: Data analysis

50. Insert|Function will display the “Insert Function” dialog Standard deviation (STDEV) is selected, so click OK The “Function Arguments” dialog will be displayed, automatically selecting the series A2:G2. Change this selection to B2:F2 by clicking the selector (see cursor) Adding data: Numbers Creating Charts: Data analysis