Next

1 / 8

# Next - PowerPoint PPT Presentation

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Next' - dannon

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

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

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

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

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

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

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

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.

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

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