EXCEL PART II

This Presentation Contains NarrationTo hear the narration, turn on your speakers or plug in headphones.If you would like to view this slideshow WITHOUT narration please do the following:Click on the Slide Show tab in the ribbon.

In the Set Up section click the Set Up Slide Show button.

Under Show Options check the box next to Show without narration.

Click Ok and view the slideshow as normal.

Microsoft eXCEL 07

Fall 2009short course Section II

OFFERED BY

INSTRUCTIONAL COMPUTING UNIVERSITY OF MISSOURI ST.LOUIS

SETTING UP THE DATA BASESet up the data base with each field/category as a column and each record as a row. The name of your field is the top cell in the column. Widen the column if needed to show your full field. In most cases press to move to the next field and to move down to the next record. As an example, the image to the right is going to be our example.

FILTERINGClick on any categorized cell, e.g. Last NameClick on the Data tabClick on Filter

FILTERING EXAMPLESUse drop down menu to filter your data

Click on Text Filters -> Custom Filters

FILTERING EXAMPLESLets say we want to filter our data by last name and we need any entry starting with c or a letter before c in the alphabet.

FILTERING EXAMPLESHere is the result of the filtering.Notice the row numbers filtered are marked blue.Use drop down menu and check Select All to get the full list back.

CREATING A GRADEBOOKSet up your worksheet similar to the example above.Enter all the scores in the appropriate cell.Now you are ready to calculate the average grade for each student

FORMULA (1)Lets assume tests are 40%, projects 50%, and homework 10% of the final grade.Use formulas to do the calculation:Test grades Add the three grades (B2+C2+D2) Divide by the number of grades (average) Multiply by 0.4 (40 %) Project grades Add the two grades (E2+F2) Divide by the number of grades (average) Multiply by 0.5 (50 %) Homework grades Add the four grades (G2+H2) Divide by the number of grades (average) Multiply by 0.1 (10 %)

FORMULA (2)An example of a formula to get the average grade.Click on the bottom right corner of the cell and drag down to the last cell where the formula is needed

ASSIGNING A LETTER GRADENow we will ask Excel to look at the numerical average in column J and compare it to a list which defines the grading scale, for the purpose of assigning a letter grade to the average. We need to create a table for the grade scale. You can do the table in a different worksheet if you will.Here is an example:Notice the scores are in Sheet1

USE VLOOKUPNext we will write an equation which will look at a student's numerical average, look at a list of grades, and assign a letter grade to the student. This is done with a function named VLOOKUP. The equation must specify three elements: The location of the numerical grade to be compared (I2 in the example) The reference of the lookup table (Sheet2!$A$1:$A$5) The location of the letter grade in the lookup table (2) [because the letter grade is in column 2 in the lookup table]

GRADEBOOKHere is how our finished grade book looks like

AUTO FORMATTING To Format the Table:Highlight the data you want to add a style to (the table in this example)Select the Home tab from the topClick on the icon over Styles ->Format as Table

CONDITIONAL FORMATTINGSimilar to Formatting the cells, Conditional Formatting can be used to make the data easier to read.In the grade book example, in order for us to add the symbols next to the grades:

Click on Home from the topThen click on Conditional Formatting under StylesUse different schemes and rules depending on what you want, e.g. Color Scales and Icon Set

FORMATTING CELL STYLESUsing auto formatting makes databases look better and read easier. Here is an example of the grade book with auto formatting applied.

COMBINING DATALets say we have data in two columns:

To combine these two columns into one and have it in the form Lastname, Firstname.

Here is the function to do the conversion: =(B2&", "&A2)& is used to combine data and quotation marks are used to insert a comma and space.

TEXT TO COLUMNSWe can use Text to Columns option under Data menu to divide our text into columns. Lets say we have:Now we want to make our data to look like this:

TEXT TO COLUMNS (2)Click on Data from the top menuThen click on Text to Columns

TEXT TO COLUMNS (3)Depending on your choice click on Delimited or Fixed with.

In this example we use Delimited separation and our Delimiter is Comma.

TEXT TO COLUMNS (4)In the final step, we can see a preview of our data. Also there are other options to change the format and the destination of our data.

Questions?If any further questions arise, please contact a lab consultant in any of the following Instructional Computing Labs.

SSB 103 SSB 452Benton Hall 232 Math TLC (UC 050) Ward E. Barnes Library Thomas Jefferson Research Commons

Additional Informationhttp://www.cetc.umsl.eduThe Microcomputer Program at the University of Missouri-St. Louis offers day and evening computer courses as part of the Chancellor's Certificate series. http://www.umsl.edu/stg The Online Student Technology Guide has answers to many computing questions.http://help.umsl.eduOnline self-service solutions for UMSL Technology Issues