For example, give them a list of movies with identification numbers that you assign and corresponding prices, quantities, etc. On another sheet list all the customers who rented movies from the store and relevant data about the corresponding sales for, say, a one-week period. Have the students write excel formulas to determine various statistical information that a fictitious manager might request.

– Which movie had the most rentals this week?

– How many sales were there each day?

– How many movies are in stock that were not rented?

This scenario allows them to practice using formulas like COUNTIF, SUMIF, VLOOKUP, etc., and you can expand the scenario to provide an opportunity to apply other formulas to suit your needs. The key is to pick a situation with which nearly everyone can identify — like a movie rental store. It also reassures them that formulas are not just book knowledge, it may come in handy in the “real world.”

We did something similar in college but with Quattro Pro. One group created a workbook for a bank. My group created a dichotomous key to use for biology to determine different types of insects based on their characteristics.

It helps them learn the applications, makes it fun, and helps to somewhat put it into a real world perspective.

Basically you could have your own little version of the show Apprentice in a way. The team with the best presentation wins.