Candy is Dandy
Created by Patricia Janann Nicholson
Created by Patricia Janann Nicholson
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.
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 buttonand select New
.
Select Blank Workbook:
Save Your Workbook
Click the Office button again
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:Click the Merge and Center button on the Home tab:
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.
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.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:To Center Align Data:Click in the cell that contains the data you want to center align then click on the center align iconon 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:At the Insert Function Dialog box click All and select Average:
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.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.Click back on Function Argument select button:This returns you back to the original Function Argument dialog box. Notice that the cells you selected now appear in your text box.
Now you will continue putting your formulas in the correct cells. The functions are listed as follows:Total - Sum
Minimum - MIN
Maximum - MAXThe 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: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..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.
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.With the chart still selected click the Title Layout which is Layout 1.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."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.Under Choose where you want the chart to be placed select News sheet then type in a name for your chart, "Bag 1."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.Click back on Sheet1 to return to your worksheet that contains your data. Continue creating graphs to represent the data below:
