Next

1. Project (Group 1): Create a Set of Lists of These Six Fields (and all possible values of these fields) • Create lists all possible courses (code and title), teachers, student cohorts, days, times and rooms. • Enter and format these lists (vertically, as shown) on an Excel worksheet. • Learn how cell entries can be forced to choose from a drop-down list from one of the lists you’ve created. Dictionary Data Validation Make a presentation on Data Validation (especially lists). See XL Bible 2010, ch. 25. Take Notes Back Next

2. Project (Group 2): Create a Table Using the Six Fields (And Drop-Down Lists to Select Field Values) • Create and format a Table with six (plus one) fields. • Set up the table so that data validation > drop-down lists limit the selections. Dictionary Make a presentation on how to create and format Tables, and especially their advantages over standard ranges, with emphasis on sorting and filtering. See XL Bible, ch.5. Tables Take Notes Back Next

3. Project (Group 3): Create Concatenated Fields (combining different text fields) • Excel has hundreds of functions, divided into “families”. • One such family comprises functions that manipulate text. One such function is CONCATENATE(). • Create concatenated fields in the schedule table. Specifically, concatenate the appropriate fields to create content for the text boxes in the teacher, student and room schedules. (See next page.) Dictionary Teachers Students Rooms Text Functions Make a presentation on Text Functions, especially CONCATENATE(). See XL Bible 2010, ch. 11. Take Notes Back Next

4. Project (Group 4): Create a Pivot Table (using appropriate fields) Using a table consisting of the following fields: Day, Time, Course ID, Instructor, Room, Students, …. Dictionary • Create a properly formatted Pivot Table with • Days of the Week as the (horizontal) column labels. • Time of Day as the (vertical) row labels. • Course Code (and number of courses) as the main values entries. • Alternately, Instructor, Room, and Students as the report filter. Pivot Tables Make a presentation on Pivot Tables. See XL Bible 2010, ch. 34 and 35. In particular, demonstrate how fields can be organized and filtered. Take Notes Back Next

5. Project (Group 5): Create a Derivative Pivot Table That Looks Up Appropriate Text Data We need to create a second table with is derivative from the pivot table containing course codes as values. This table should: Dictionary • Read course code values from the pivot table. • Use a lookup or index function to get the appropriate text information from the table. • Insert that information in the derivative table (see next page). • Appear appropriate for distribution and printing. LookUp Functions Make a presentation on LookUp Functions, specifically IF(), VLOOKUP(), HLOOKUP() and MATCH() & INDEX(). See XL Bible 2010, ch. 11 and 14. Take Notes Back Next

6. Project (Group 6): Apply Conditional Formatting To Cell Ranges We wish to use conditional formatting in cells or cell ranges to highlight: Dictionary • When data needs to be entered or updated. • When cell values are inappropriate or unacceptable. • Important or critical values or results. Conditional formatting can be applied to font and fill colors, but Excel 2010 also allows for conditional formatting with • Data bars • Color Scales • Icons Conditional Formatting Make a presentation on Conditional Formatting, particularly using RULES for data bars, color scales and icons. See XL Bible 2010, ch. 20. Take Notes Back Next

7. Project (Group 7): Inserting, Editing and Reviewing Comments Comments should be inserted whenever there is a need to assist the workbook user in entering cell data or in understanding the function of particular cells. Dictionary • Demonstrate how particular cells can be enhanced with comments. • Show how all comments in a workbook can be comprehensively edited and/or viewed. Comments Make a presentation on how comments can be used and managed. Insert a phot into a comment. See XL Bible 2010, ch.4. Take Notes Back Next

8. Project (Group 8): Record Simple Macros That Navigate or Perform Other Simple Tasks We will use macros in this project to perform repetitive tasks and to navigate the workbook. Dictionary • Use (only) the macro recorder, do NOT enter or edit VBA. • Insert an appropriate shape, clipart, photo, or other object, and attach the macro to (a click on) that object. • Ensure that the macro performs the desired task, and that the curser returns the Excel user to an appropriate place. Record Macros Make a presentation on recording, assigning and using macros, for both repetitive tasks and for navigating. See XL Bible 2010, ch. 39. assign macro, start macro by clicking Take Notes Back Next