Spreadsheets

1. Spreadsheets

2. What are the parts • Rows are numbered vertically • Columns are lettered horizontally • Where rows and columns intersect is called a cell • A sheet is made up of a set of rows and columns • An excel work book contains several sheets

3. The Basic Spread Sheet • Rows are numbered vertically • Columns are lettered horizontally • Where rows and columns intersect is called a cell • A sheet is made up of a set of rows and columns • An excel work book contains several sheets

4. Using Formulas • Highlight the cell you want to write a formula for. • Click into the formula bar and type the operation you wish to perform • Remember you must always start a formula with an =

5. Formula Notations • * = multiplication • ^ = raise to the power of • / = divide by • +/- = addition/subtraction • ( ) = parenthesis very important for order of operation

6. Functions • Excel has tons of built in functions to perform everything from rounding to advanced statistical tests.

7. Graphing Data • Highlight the information you wish to graph • Then choose insert graph from the insert menu

8. Graphing Data • The graphing wizard will then start up. This will allow you to set the features of your graph. • Note: The most common mistake people make when trying to graph two sets of data against each other is that they chose line graph instead of xy (scatter) plot.

9. Graphing Data • And Voila! You get a graph!

10. Sorting Data • Highlight the data you want to sort. (note: if you want data in rows to stay together you must select all the data. e.g. in the picture below I want the x and y values to stay paired together so I highlight both rows. • Next choose sort under the data menu. • The sort window will appear and you can specify which columns you want to sort by and how.

11. Filtering Data • If you have a large set of data, sometimes you want to look at a certain chunk. In other words you may want to “filter” out all the other information. • Under the data menu choose autofilter

12. Filtering Data • Once you have turned on auto filter you should see little arrows appear across the columns • If you click on the black arrows a pop-up list with all the unique values from that column appear. • Select one

13. Filtering Data • Only the rows or records that have that value will remain the rest of the information will be filtered out.

14. Pivot Tables • Pivot tables group data into categories and can be used to perform calculations on the data. • I have a table in which I enter each of my students assignments. There are over 1300 records in it. I can use a pivot table to summarize the grade information for each student in the class. • Highlight the data you want to summarize and select, then choose pivot table from the data menu

15. Pivot Tables • The wizrard will guide you through the process of setting up the pivot table

16. Pivot Tables • A blank pivot table will appear and a blank floating tablet with all the field names on it. • Drag the

17. Pivot Tables • First set the rows. Do this by clicking and holding on a field name, then drag it to the row area. You can add more than one field to the row. This determines how you want the data grouped and separated vertically.

18. Pivot Tables • Now set the columns. This determines how you want the data grouped and separated horizontally. In this case I am using the category of the grade (Projects, Homework, etc. etc..)

19. Pivot Tables • The third piece is the value. This is the field on which you wish to perform calculations (count, average etc. etc.) • Once you have the dragged the field into the value section of the pivot table you can right click and select the field settings option to change the type of calculation performed on the field.

20. Pivot Tables • In this case I have added two more fields to the row area to further breakdown the data. The row area is the only part of the pivot table where you can use multiple fields.