Candy is Dandy
Created by Patricia Janann Nicholson
Lesson Plan
Grading Rubric

You have been hired to conduct some market research about M&M's. First, you had your team purchase 4 large bags and the results are given for the contents of those bags. You will summarize your findings on a worksheet created in Microsoft Excel and convert the numbers into charts.

Microsoft Excel 2007 Instructions:

Open Microsoft Excel:

Start > All Programs > Microsoft Office > Microsoft Excel 2007

Create a New Workbook

Excel should open as a workbook. If not click the Office button Office Button and select New New PowerPoint.

Select Blank Workbook:

Blank Workbook

Save Your Workbook

Click the Office button again Office Button and select Save As from the drop down menu and name your file Candy is Dandy. See the information below on how you want to save your document.

Save Options

Entering Data Into Your Cells

Enter the data in the table below and don't worry about resizing the cells. Once you have entered the data the next section will tell you how to format the cells as indicated in your table below:

Cell
What You Type in Cell
How to Format
A1 CANDY IS DANDY Merge and Center Title Across Rows A1 - F1, Bold, Font Color Red, Highlight Row
A3
COLORS Centered, Bold, Font Color Blue
B3 BAG 1 Centered, Bold, Font Color Brown
C3 BAG 2 Centered, Bold, Font Color Green
D3 BAG 3 Centered, Bold, Font Color Orange
E3 BAG 4 Centered, Bold, Font Color Red
F3 AVERAGE Centered, Bold, Font Color Pink
A4 Blue Bold, Font Color Blue
A5 Brown Bold, Font Color Brown
A6 Green Bold, Font Color Green
A7 Orange Bold, Font Color Orange
A8 Red Bold, Font Color Red
A9 Yellow Bold, Font Color Yellow
A10 TOTAL Center, Bold
A11 MINIMUM Center, Bold
A12 MAXIMUM Center, Bold
B4 6 No Formatting
B5 4 No Formatting
B6 3 No Formatting
B7 7 No Formatting
B8 5 No Formatting
B9 4 No Formatting
C4 9 No Formatting
C5 8 No Formatting
C6 2 No Formatting
C7 6 No Formatting
C8 5 No Formatting
C9 4 No Formatting
D4 6 No Formatting
D5 4 No Formatting
D6 8 No Formatting
D7 7 No Formatting
D8 5 No Formatting
D9 3 No Formatting
E4 8 No Formatting
E5 7 No Formatting
E6 5 No Formatting
E7 4 No Formatting
E8 3 No Formatting
E9 5 No Formatting

Formatting Your Cells:

Merge and Center Title:

Merge and Center Title Across Rows A1 - F1 by clicking in cell A1 and while holding down your left mouse drag and release your mouse in cell F1. Your Worksheet should now look like the graphic below:

Title Merged

Click the Merge and Center button on the Home tab:

Merge and Center

To Apply Bold Formatting:

Click in the cell that contains the data you want to appear bold and on with the Home tab still selected click on the Bold icon Bold.

To Change the Font Color:

Click in the cell that contains the data you want to change the font color of and on the Home tab and click on the font color icon arrow to drop down your font color options and select the color you need.

Font Color

To Highlight a Cell:

Click in the cell that contains the data you want to highlight and click on the Highlight options arrow to display your highlight on the Home tab and select a highlight color:

Highlight Data

To Center Align Data:

Click in the cell that contains the data you want to center align then click on the center align icon Center Align on the Home tab.

Inserting Formulas

You will now enter the total, minimum, maximum, and average functions into the cells listed in the table below. The data will appear in the following table.

You will place the following formulas in the cells as instructed below:

CELLS
FORMULAS
FORMAT
F4, F5, F6, F7, F8, F9 Average Format cells so that a whole number is returned (i.e. no decimal places) by using the Decrease Decimal button on the Formatting toolbar. Center Align Data
B10, C10, D10, E10 Total Center Align
B11, C11, D11, E11 Minimum Center Align
B12, C12, D12, E12 Maximum Center Align

Averaging Numbers:

Place your cursor in the cell you want your formula to appear.

Click the Insert Function button:

Insert Function

At the Insert Function Dialog box click All and select Average:

Insert Average Function

Click OK. Next you will need to tell Excel which cells you want to reference. An easy way to do this is just by selecting the cells you want it to look at. Click on the Number1 function select icon shown below to select the cells you want to reference.

Function Arguments

The Functions Argument box pops up. Now all you have to do is simply click and drag across the cells you want to average. Notice that the Function Arguments text box fills in the cells you have selected.

Select Cells

Click back on Function Argument select button:

Close

This returns you back to the original Function Argument dialog box. Notice that the cells you selected now appear in your text box.

Function Argument

Now you will continue putting your formulas in the correct cells. The functions are listed as follows:

Total - Sum
Minimum - MIN
Maximum - MAX

The last thing is to center align the data in the cells.

Decreasing Decimal Points to None:

For this activity you will return to any cells that contain decimals. You will need to let Excel know that you only want whole numbers to be present. Notice the number below and how many decimal numbers are displayed after the decimal point:

Decimal Points

With the home tab selected in the Numbers section you will see a Decrease Decimal icon shown below. You will need to click the decrease decimal button the number of times that there are numbers after the decimal point. In the above example it would be two because there are two numbers after the decimal point..

Decrease Decimal

Make sure that all your formulas display with no decimal point.

Center align the data in the cell.

Creating Graphs:

Microsoft Office Excel 2007 no longer provides the chart wizard. Instead, you can create a basic chart by clicking the chart type that you want on the Ribbon.

In order to compare information graphically, you must choose the data to reflect in our graphs. The first data you will want to display graphically are the color name of the M & M's that were found in bag 1n (A4 - A9). You also want to display the actual number that was found in the bag (B4 - B9). You will then need to select both data to show it in your graphs.

To select the color name data, place you cursor in cell A4, press the left mouse button, and drag to select all the cells to A9. The next data will be the actual number found in column B. Hold down the Ctrl key before clicking in Cell B4, press the left mouse button, and drag to select all the cells to B9. You should see that both columns are highlighted blue.

Cell Selected

On the Insert tab, in the Charts group, click the a chart subtype that you want to use. For this activity you will use the column chart.

Column Chart

With the chart still selected click the Title Layout which is Layout 1.

Title

This adds a title placeholder on you chart that you can simply click in and type in your title, "Colored M & Ms Found in Bag 1."

Title in Chart

Click on Series 1 on the right side of your chart and delete it.

Insert Chart in Separate Chart Sheet:

By default, the chart is placed on the worksheet as an embedded chart. If you want to place the chart in a separate chart sheet you can change its location by doing the following:

Click the embedded chart to select it. This displays the Chart Tools, adding the Design, Layout, and Format tabs.

On the Design tab, in the Location group, click Move Chart.

Chart Tools

Under Choose where you want the chart to be placed select News sheet then type in a name for your chart, "Bag 1."

Move Chart

You will now be on a new worksheet titled Bag 1. Notice the worksheet names at the bottom left hand side of your document window.

Sheet Names

Click back on Sheet1 to return to your worksheet that contains your data. Continue creating graphs to represent the data below:

  • The number of colors found in Bag 1-Created Above
  • The number of colors found in Bag 2
  • The number of colors found in Bag 3
  • The number of colors found in Bag 4
  • The average compared for each colored M & M
  • The totals compared for all bags
  • The minimum compared for all bags
  • The maximum compared for all bag

Horizontal Rule

Microsoft Excel 2003 Instructions:

Open Excel by following the directions listed below:

  • Click on Start, select Programs, and the click on Microsoft Excel.

Save Excel Worksheet:

  • Click on File on the Menu Toolbar, then select Save As to open the Save As dialog box.
  • Navigate to your folder in the Save In text box by clicking the down pointing arrow.
  • Name the Excel Worksheet by clicking in the File Name text box, and then typing the files name "Candy is Dandy."
  • Click OK to close the Save As dialog box.

Setting up the Worksheet and Inputting Data

Open a worksheet in Microsoft Excel. In cell A1, type the title Candy Is Dandy! Finish putting in the rest of the data in the table below into Excel. Then go back and merge your title to appear centered across your rows.

Moving Around in the Worksheet:

There are several methods that can be used to move through the worksheet:

  • The Down Arrow Key-Moves you down one cell at a time in the worksheet.
  • The Up Arrow Key-Moves you up one cell at a time in the worksheet.
  • The Right and Left Arrow Keys-Moves you one cell to the right or left in the worksheet.
  • Page Up and Page Down-Moves you up and down a page.
  • End Key-Moves you to the last cell in the worksheet.
  • Home Key-Moves you to the first cell in the worksheet.

Entering Data:

Data is entered into single cells. The cell that has been selected to enter data is called the active cell and is identified by a heavy border. Only one cell can be active at a time. To change the active cell, move the mouse pointer into a cell and just click. To enter data, simply click in the cell and then type in the information you want to appear in the cell. In this case, you would enter a letter in each cell.

Editing Data

Once you have entered data into a cell, that data can be edited by first clicking on the cell to make the cell active and then clicking on the formula bar above the worksheet. The formula bar is shown below:

This allows you to insert or delete characters in that cell. An alternate method of editing the contents of a cell is to click on the cell to make it the active cell and then press the [F2] function key at the top of the keyboard. You can delete the contents of a cell by clicking on the cell and pressing [Delete] on the keyboard.

Formatting Worksheet:

The formatting toolbar, shown below, allows you to format the contents of a highlighted cell in a number of ways. If this toolbar is not already visible on the screen, select View|Toolbars|Formatting from the menu bar.


Merging the Title:

  • Place your cursor in cell A1 and type the title in the cell. Do not resize the cell!!!
  • Click again in cell A1.
  • While holding the left mouse button down, drag across the cells to you reach the last column that contains your data.
  • Click the merge and center cells button on the formatting toolbar.
  • Your title should now appear centered at the top of your worksheet.
Align Cells with Center Alignment:
  • Select the cells that you want to align to the center.
  • Click the center alignment button on the Formatting Toolbar.
  • The letters should now be centered in each cell.

Formatting Cells Bold:

  • Place your cursor in the cell you wish to format bold.
  • Click the bold button on the Formatting Toolbar.
  • The letters should now appear bold in the cell.

Changing Font Color:

  • Select the cells that you want to change the font color of.
  • Click the font color button on the Formatting Toolbar.
  • Click on the down arrow to get a list of colors. Choose the color you want.
  • The letters should now be the color you selected.

Highlight Row:

  • Select the row that you want to highlight.
  • Click the fill button on the Formatting Toolbar.
  • Click on the down arrow to get a list of colors. Choose the color you want.
  • The row should now be the color you selected.

 

Cell
What You Type in Cell
How to Format
A1 CANDY IS DANDY Merge and Center Title Across Rows, Bold, Font Color Red, Highlight Row
A3
COLORS Centered, Bold, Font Color Blue
B3 BAG 1 Centered, Bold, Font Color Brown
C3 BAG 2 Centered, Bold, Font Color Green
D3 BAG 3 Centered, Bold, Font Color Orange
E3 BAG 4 Centered, Bold, Font Color Red
F3 AVERAGE Centered, Bold, Font Color Pink
A4 Blue Bold, Font Color Blue
A5 Brown Bold, Font Color Brown
A6 Green Bold, Font Color Green
A7 Orange Bold, Font Color Orange
A8 Red Bold, Font Color Red
A9 Yellow Bold, Font Color Yellow
A10 TOTAL Center, Bold
A11 MINIMUM Center, Bold
A12 MAXIMUM Center, Bold
B4 6 No Formatting
B5 4 No Formatting
B6 3 No Formatting
B7 7 No Formatting
B8 5 No Formatting
B9 4 No Formatting
C4 9 No Formatting
C5 8 No Formatting
C6 2 No Formatting
C7 6 No Formatting
C8 5 No Formatting
C9 4 No Formatting
D4 6 No Formatting
D5 4 No Formatting
D6 8 No Formatting
D7 7 No Formatting
D8 5 No Formatting
D9 3 No Formatting
E4 8 No Formatting
E5 7 No Formatting
E6 5 No Formatting
E7 4 No Formatting
E8 3 No Formatting
E9 5 No Formatting

Inserting Formulas

You will now enter the total, minimum, maximum, and average functions into the cells listed in the table below. The data will appear in the following table.

You will place the following formulas in the cells as instructed below:

CELLS
FORMULAS
FORMAT
F4, F5, F6, F7, F8, F9 Average Format cells so that a whole number is returned (i.e. no decimal places) by using the Decrease Decimal button on the Formatting toolbar.
B10, C10, D10, E10 Total No Formatting
B11, C11, D11, E11 Minimum No Formatting
B12, C12, D12, E12 Maximum No Formatting

You will use the function wizard option to enter the data. Click in the cell you want to enter the formula, and follow the steps below for each formula:

Total/Sum Function:

  • To insert the total function, click in the cell you want the total to appear. The first Total should appear in cell B10.
  • Click the function wizard button . This will open the Paste Function dialog box.
  • Under the Function Category list, select All.
  • Under the Function Name list, select SUM from the choices.
  • Click OK to close the Paste Function dialog box.
    The SUM dialog opens. Click in the Number 1 text box and type in the first cell you want the add.
    In the first case you want the program to start looking at the data in column B. The first set of data you want to compare starts in row B4 and the last data to compare is in row B9. In the Number 1 text box, type B4:B9 to indicate the data span you want to use.
  • Click OK to close the SUM function dialog box. The sum of cells B4 to B9 should now appear in cell B10.
  • Continue entering formulas for SUM in the appropriate cells listed in the table above (C10, D10, E10).

Minimum Function:

  • Click in the cell where you want your minimum formula to appear. The first minimum function should appear in cell B11.
  • Click the function wizard button . This will open the Paste Function dialog box.
  • Under the Function Category list, select All.
  • Under the Function Name list, select MIN from the choices.
  • Click OK to close the Paste Function dialog box.
  • The MIN dialog opens. Click in the Number 1 text box and type in the first cell you want the results to be compare with.
    In the first case we want the program to start looking at the data in column B. The first set of data you want to compare starts in row B4 and the last data to compare is in row B9. In the Number 1 text box, type B4:B9 to indicate the data span you want to use.
  • Click OK to close the MIN function dialog box. The lowest number should now appear in cell B11.
  • Continue entering the MIN function in the cells listed in the table above (C11, D11, E11).

Maximum Function:

  • Click in the cell you want your maximum function to appear in. The first maximum function should appear in the cell B12.
  • Click the function wizard button . This will open the Paste Function dialog box.
  • Under the Function Category list, select All.
  • Under the Function Name list, select MAX from the choices.
  • Click OK to close the Paste Function dialog box.
  • The MAX dialog opens. Click in the Number 1 text box and type in the first cell you want the results to be compare with.
    In this case we want the program to start looking at the data in column B. The first set of data you want to compare starts in row B4 and the last data to compare is in row B9. In the Number 1 text box, type B4:B9 to indicate the data span you want to use.
  • Click OK to close the MAX function dialog box. The highest number should now appear in cell B12.
  • Continue entering the MAX function in the cells listed in the table above (C12, D12, E12).

Average Function:

  • Click in the cell you want your average function to appear in. The first average should appear in the cell F4.
  • Click the function wizard button . This will open the Paste Function dialog box.
  • Under the Function Category list, select All.
  • Under the Function Name list, select AVERAGE from the choices.
  • Click OK to close the Paste Function dialog box.
  • The AVERAGE dialog opens. Click in the Number 1 text box and type in the first cell you want the results to be compare with. In this case we want the program to start looking at the data in row 4. The first set of data you want to compare starts in cell B4 and the last data to average is in cell E4. In the Number 1 text box, type B4:E4 to indicate the data span you want to use.
    Click OK to close the AVERAGE function dialog box. The average should now appear in cell F4.
  • Continue entering the AVERAGE function in the cells listed in the table above (F5, F6, F7, F8, F9).

Creating Graphs

You will now use the Chart Wizard in Microsoft Excel to produce different charts and graphs that will display your data in a more visual way. In order to compare information graphically, you must choose the data to reflect in our graphs.

  • The first data you will want to display graphically are the color name of the M & M's that were found in bag 1. You also want to display the actual number that was found in the bag. You will then need to select both data to show it in your graphs.
  • To select the color name data, place you cursor in cell A4, press the left mouse button, and drag to select all the cells to A9.
  • The next data will be the actual number found in column B. Hold down the Ctrl key before clicking in Cell B4, press the left mouse button, and drag to select all the cells to B9. You should see that both columns are highlighted blue.
  • Click the Chart Wizard button on the standard toolbar to view the first Chart Wizard dialog box.
  • Chart Type - Choose the Chart type and the Chart subtype if necessary. Hold down the View Sample button to see if the results reflect the information in a meaningful way. You should see the color name and the number of M & M's in some type of graphical form.
  • Click Next.

 

  • Chart Source Data - Select the data range (if different from the area highlighted in step 1). You chart should look similar to the one below:
  • Click Next.

  • Chart Options - Enter the name of the chart (M & M Colors Found in Bag 1) and titles for the X- and Y-axes. X axes would be M & M Colors and the Y axes would be Number Found. Other options for the axes, grid lines, legend, data labels, and data table can be changed by clicking on the tabs.
  • Before clicking next, you will want to get rid of the word series 1 shown at the right of the chart. To do this, click the Legend tab at the top of the chart options dialog box, and click in the Show Legend checkbox to removed the check. The Series 1 box should now be removed.
  • Press Next to move to the next set of options.


  • Chart Location - Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu. Choose As object in.

Choose the chart types that best represent the data such as bar chart or column chart. Make sure you give your charts a title and label the x and y axis. Follow the directions above to include charts for the following:

  • The number of colors found in Bag 1-Created Above
  • The number of colors found in Bag 2
  • The number of colors found in Bag 3
  • The number of colors found in Bag 4
  • The average compared for each colored M & M
  • The totals compared for all bags
  • The minimum compared for all bags
  • The maximum compared for all bags

Nicholson's Lesson Plan Blues