Excel is our friend. Really

1. Excel is our friend. Really

2. Excel is a spreadsheet program used a great deal in business and in science. It can do a lot of things but we will mainly be using it to set up tables and graph data from those tables.

3. Yes, I know that word and pages can also do tables but they can’t do the graphing and manipulation of the graphs that we need to do. They are not set up to do that. They are word processing programs. You can certainly do your tables and graphs in excel and cut and paste it into word or pages. That’s what most students do. Why, because excel is not set up to be a word processing program.

4. So, open up a new file in Excel. It’s called a workbook.

5. Make sure you have the HOME tab open and note where the alignment category is. You use merge cells and wrap text a lot so note where they are.

6. OK, I want to make a title for my table. I will make sure I use the proper rules for titles – it must include both variables and explain to someone who didn’t do the experiment, what my numbers mean. Click and hold onto the mouse (I use a mouse, you adapt to your style). Drag the curser to the size you want

7. Now, click on ‘wrap text’ and ‘merge’. I think you can see what the merge cells does for you if you look at the effect

8. Now we want to put some column headings for our data. Don’t forget that how to properly do column headings is written up in ‘lab write-up’ on my moodle in the labs tab. Examples are also given.

9. Let’s make one column heading – height (cm) of grade 9 boys. This is where you will see what ‘wrap text’ does for you. See how the words spread out over two columns. It doesn’t look good and it’s not correct. Now see what happens when I check off the wrap text Much better!

10. How come this column seems uneven? What went wrong? right wrong It’s because you have the wrong ‘alignment checked off. You need to use the middle one

11. Now, lets add some data – that usually means numbers. Hey, I wanted the second number to be 45.0. Where did my zero go? Excel has a mind of its own and you have to show it who’s boss. It will automatically take off trailing zeros. You must put them back if you need them. You need to go to ‘number’ on the tool bar.

12. One of them makes excel take a zero away, the other adds a zero. Click twice and two zeros appear etc. Now it’s right. Just remember, excel is our friend.

13. OK, lets do a sort of real example.

14. I double-clicked, merged and wrapped the first two rows of cells. Note, my number type is on “General”, always a safe one to use – it’s the default. My title has both important pieces of data in it – date and time. We will learn that these are the independent and dependent variables and must be in a title. And the title is written so that someone who has not read the intro information would still have some idea what the numbers mean. The ‘who cares’ is from me.

15. Now for column headings. I put days of the week first as it is the variable you knew first – remember, she was wondering what the texting time was ‘each day’. It is the independent variable and should go first. There is no unit for it. Then comes the thing you are trying to find out – time- and it is the dependent variable. All data MUST be in the same units and I choose minutes. Units must be included in the heading. I really made that title stretch out – oh well, we don’t care about pretty.

16. Now, back to numbers, zeros and Excel. Why does it matter how many zeros are there in a piece of data? Because of uncertainty. Make sure you check the equipment pptx about this. I had to use some piece of equipment to measure texting time which means that there is some amount of uncertainty in my data, based on the equipment used. For this experiment, the stop watch had an uncertainty of 0.1 minutes, meaning that only the minutes numbers were certain. I have to show that in my column heading. And I must make sure that all my numbers reflect that amount of uncertainty. Let’s go back and add the uncertainty of the equipment to the column heading.

17. You need to go to the media window and click on ‘symbols’

18. There’s the symbol we want – the + or – symbol. There is a short cut for this symbol – don’t ask me what it is. Now we go back to our column heading and we are going to add this symbol to it to show that our equipment was uncertain to the first decimal and therefore all data under it is also uncertain at that point

19. Double click on the column heading box or cell so that you can make changes. You will note that I moved the units, and combined it with the uncertainty. I just wanted it to look nicer. You could repeat the unit, it doesn’t matter. I also used the abbreviation for minutes so it would fit in better.

20. So, I changed all time data to minutes and put them in. Of course, I had to make sure that all my numbers had the same number of decimal places as each other and that it matched my uncertainty. My uncertainty is one decimal place so everyone has to have one. Excel tried to get rid of my zeros, of course, and I had to get them back.

21. One of the quirky things with excel has to do with the lines in and around your table. You can see the lines but if you print it out or copy and paste to word, they will NOT be there. You must tell excel that you want the lines. Excel calls them ‘borders’ You find borders under font

22. Now go over and highlight all the area that you want lines/borders around. (I do want borders around the title, the screen shot just makes it look like I didn’t highlight it) Click on borders and choose the ‘all borders’

23. And this is what you will get. Now all the lines/borders will be there when you copy and paste or print.

24. Another thing you should watch is in the number box of your tool bar. It’s best if you keep it as ‘general’. If, for eg. it switches to percentage, every number you enter will have a percentage sign after it and you can’t get rid of them. Remember, excel is our friend.

25. Always remember, if you used a calculation to get an answer, you must show the formula for the calculation. The formula can go under the table. It also requires one worked example. For eg. You used the displacement method to find the volume of an irregular solid. You had to subtract the final volume from the initial volume to get the volume of the object. Volume = final volume – initial volume 40 mL = 50 mL – 10 mL Average if also a calculate and requires a formula. Any time that calculator comes out!

26. Now, lets look at how we can use excel to graph our data. Below, I have set up a Good Table – proper title, column headings with units and uncertainty and my data compliments my uncertainty. Now I what to graph this data so I can see at a glance if the people with the bigger hand span are also the taller people.

27. Because I want to COMPARE the two sets of numbers, I will be using a bar graph. Excel calls graphs “charts” and bar graphs are called “columns” – don’t ask me why!

28. When you get there, you will find too many choices. DO NOT do the 3-D graphs and no cuties colours. This one only!!

29. Highlight the data you want to graph. NOTE – Excel will assume the the first column you highlight is the independent variable. I didn’t highlight subject number because it is not important to my question to know which subject belongs to which data.

30. So far so good but we’re not done yet

31. First off, get rid of the little legend (sometimes shows up as series) that Excel always puts there as a default. Click on it and delete Much better!

32. You need to add a proper title and label the axis. (Oh no). Click on the graph, this will cause a new option to appear in the tool bar called chart Layout. Then you can add a chart title.

33. Look at this pretty title. The title must include both column headings

34. Continue adding titles for each axis You don’t need uncertainty in graph axis

35. Now, lets put some error bars in. Note – see graphing assignment on Moodle. There is an error bar tab under chart format but I’m not sure what percentage is used for it so we will do it the more controlled way. Right click on one of the bars in your graph and select ‘format data series’.

36. Click on ‘error bars’, and for display, use both and cap as shown above

37. Now click on percentage and you will see that the default is 5%. Do the same procedure for the other bar. Hey, I wonder if you use the tool bar ‘error bar’ and select percentage, you will probably get 5% error bars too?? Maybe.

38. The final product – pretty!

39. Now lets manipulate some of the data so that we can see how to do a line graph. I’ve made a table that is looking at the growth of hands over 5 months and just used some of the data we already had.

40. Now we need to pick a style of graph/chart. We use scatterplots in Excel, NOT the line graphs. And pick the smooth marked scatter.

41. Using proper titles, axis etc, this is what you get. But we are not done as this is a ‘connect the dots’ type of graph and we want a line of best fit so we can determine if we have any outliers

42. Right click on one point of data and you will get this window. Remember, Excel calls line of best fit a trendline. So pick trendline

43. This window will open and you will choose linear which is the default setting.

44. And now your final product! Now it is obvious that the data point for three months does not follow thr trend. It is an outlier. The trendline also allows you to make predictions about months where data wasn’t collected like 6 months or 0.5 of a month.