Materials Credited to DMACC
Agriculture Technology Class
Spreadsheet Unit – Lesson #7
Part 1
Allow me to embellish this lesson with a story:
After struggling with a fairly complex worksheet for an hour or two, everything suddenly falls into place. Immediately after you type the very last formula on the very last row, you spot what looks like a pattern in the calculated values.
You study your work carefully and check and double-check your formulas. Then, you play with the numbers a little bit. Your suspicions are confirmed: the worksheet you developed reveals a trend that could have serious financial consequences for your company.
You hurriedly click Excel's 'Print' button and rush over to the printer. The document takes less than a minute to print, but it seems like an hour. Too excited to wait for the printer to finish ejecting the sheet, you rip the document from the print rollers and burst into your manager's office.
You gently fling the document toward your employer. It sails to a stop in the surprised administrator's outstretched arms. "Look at this," you exclaim, reaching across the broad desk and jabbing your fingers at the neat columns and rows of numbers.
Your boss slides on a pair of glasses, lifts the document slightly, and stares at the data for a few seconds. You seat yourself in an overstuffed chair. As you watch the administrator's eyes dart back and forth across your carefully constructed table, you find yourself leaning farther and farther forward in anticipation.
Still clutching your document in one hand, your manager slowly removes the eyeglasses, folds them neatly and tucks them into a padded carrying case. The manager's expression is blank.
The manager looks at you, then at your handiwork, and then back at you again.
"What is this?" the manager finally asks, handing the document back to you. "Why is this so all-fired important?"
You grab the worksheet from your manager's outstretched hand. Words tumble from your mouth, but your boss doesn't understand. You point at the numbers again and again, but your superior's eyes just glaze over. Eventually, you give up.
Frustrated, you pick yourself up from the chair. You mumble something about an appointment and slink from the office.
"Numbers are like people; torture them enough and they'll tell you anything."
-Anonymous
A worksheet can have many layers of meaning. When you create a worksheet, you'll have very little difficulty interpreting the meaning hidden behind your data.
To you, the words and numbers in your worksheet tell a story. That story will usually lead you to what you perceive as an inescapable conclusion.
To others, the numbers in your worksheet will seem, well...just like numbers. Patterns that seem obvious to you will often elude others, especially if they don't share the same intimate familiarity with the data that you do.
"A picture is worth a thousand words."
-Ancient proverb
Humans are visual animals. Often, the only way we can understand a concept is to visualize it. Imagine trying to describe the color blue to someone who has never seen it. Wouldn't it be easier to just show the person something colored blue? Or just how easy would it be to explain the difference between a cat and a dog to a small child without showing the child pictures of both animals?
"Words strain, crack, and sometime break, under the burden."
-T.S. Eliot
If you really want people to understand the true meaning of your worksheet, you're going to have to help them visualize the concept embedded in your numbers. The best way to graphically show relationships between a group of numbers is with a chart.
Excel makes charting your data a breeze. Usually, all you have to do is select the information you would like to chart. Then, you just call up the Chart Wizard and let Excel do the rest!
Let's give it a shot!
Part 2 – In reading assignment
Please start Excel. Once Excel appears, click the 'File' menu, choose 'Open,' and then double-click the 'tires.xls' file we created in lesson 2.
Our first chart
When you create a chart, you will need to select one set of numbers and one set of labels to identify the numbers.
Both the numbers and the labels need to be present in your worksheet. They do not need to be in adjacent columns or rows.
You should select the same number of labels as you have numbers. For example, if you wanted to graph three numbers, you would need to select those three numbers. You would also need to select three labels that correspond to the numbers you have selected.
Let's say you're eager to discontinue the CR-133 line of tires. You believe they are selling poorly and would like to create a graph to illustrate that poor performance. This graph should compare sales of the CR-133 tires to sales of the other tires.
Before we start, please make sure that rows 2-5 in column B contains the following numbers (please feel free to change your numbers so that they match the numbers typed below):
B2: 240
B3: 712
B4: 615
B5: 520
We will need to select these four numbers to construct our graph. As I've mentioned before, we will also need to select four labels to go with the numbers. The labels on rows 2-5 of column A will serve that purpose nicely.
In order to select the labels in column A and the numbers in column B, you'll need to place your mouse in cell A2, right in the middle of the label 'CR-133.' Make sure your mouse looks like a white cross so you do not copy or move this label.
When your mouse changes into a white cross, hold down the mouse button and drag your mouse over to column B and then down to row 5. All of the values from A2 through B5 should be selected.
Notice how we are NOT selecting the total on row 6. It is usually not a good idea to include totals in your graph. The total is ordinarily so much greater than the other numbers that it will dwarf the other values in your graph.
Once you have the values in column A and B selected, click the 'Insert' menu at the top of your screen and choose 'Chart.'
From this point forward, the procedure will vary depending on the version of Excel you're using.
Part 3
Instructions for Users of Excel 97 and 2000
Note: If you use an older version of Excel, please skip to the next part.
After you choose 'Chart' from the 'Insert' menu, the Chart Wizard will start.
Click the tab that says 'Standard Types' in the top left corner of the Chart Wizard to see a list of common chart types.
Let's start with a pie chart. Click the word 'Pie' from the list of chart types on the left.
Excel will show three different sub-types of the pie chart. Let's try creating a 3-D pie chart by clicking the second chart on the first row of sub-types.
Now, look for a long, gray button that says 'Press and hold to view sample.' The button is located near the lower right corner of the Chart Wizard. Position your mouse over this button and then hold down the mouse button to see a preview of your chart.
In the preview, you will find a legend located immediately to the right of your pie. This legend was created entirely from the labels you selected in column A of the worksheet. According to the legend, CR-133 tire sales are represented by the smallest slice of the chart, proving our point that the CR-133 represents the smallest percentage of total tire sales.
However, the angle of the chart does not afford us the clearest view of the particular slice in question. Release the mouse button to lose the preview. Now, let's try going with a flat (2-D) chart sub-type instead. Click the first chart sub-type on the first row of sub-types. Now, use the 'Press and hold to view sample' button to see a preview. This chart is a bit easier to read than the 3-D chart, and the CR-133 slice is now in plain view.
The CR-133 slice is clearly smaller than the others. But it doesn't look that much smaller. Perhaps the pie chart isn't the best one to use to try and make our point. Let's try another.
Click on the word 'Bar' in the chart type list. Let's select the first sub-type on the first row of sub-types. You may notice that this is a flat (2-D) sub-type. Although the 3-D charts are fairly attractive, they are also usually quite difficult to read. If you're going for clarity, I'd recommend going with a flat sub-type.
Now, use the 'Press and hold to view sample' button to get a preview. This time, you'll see the names of the tires (the labels from column A) running down the left side of your chart. In the preview, you'll see that the CR-133 looks quite a bit shorter than the others.
That one just might prove our point. But let's try one more, just to be safe.
Click on the word 'Column' on the list of chart types. Then, select the first sub-type on the first row. Once again, use the 'Press and hold to view sample' button to get a preview. You'll find the labels from column A underneath your chart this time. That CR-133 column sure looks puny compared to the others, doesn't it? The difference is much more dramatic on this chart than it was on the others. This is the chart I would go with.
Let's create this chart. To do so, release the 'Press and hold to view preview' button and click the 'Next' button at the very bottom of the Chart Wizard instead.
Step 2
Now, click the 'Data Range' tab at the top of the Chart Wizard. Although the numbers we are graphing are arranged in a column, try clicking the round option button in front of the word 'Rows.' Doing so will often improve the appearance of your graph. But our labels are in one column and our numbers are in another, so we should click the round option button in front of the word 'Columns.'
Click the 'Next' button at the bottom of the Chart Wizard to proceed.
Step 3
Click the 'Titles' tab at the top of the Chart Wizard. Then, click inside the 'Chart Title' text box and type the following:
Sales by Type
Do NOT press the ENTER key when you finish. If you were to accidentally do so, you will be thrown out of the Chart Wizard and you'll have to start all over again!
Instead, click in the 'Category (X) axis' text box and type:
Type of Tire
Then, click in the 'Value (Y) Axis' text box and type:
Quantity Sold
Perfect! Now, click the 'Axes' tab at the top of the dialog box. If you were graphing dates or times along the horizontal axis at the bottom of your graph, you'd want to click the 'Time-scale' option button on this screen. We're not, so leave the 'Automatic' option button selected.
Let's see what the graph looks like without numbers running along the vertical axis. Click the 'Value (Y) axis' checkbox to remove the checkmark. I kind of like the numbers, so click the checkbox again to restore them.
Next, click the 'Gridlines' tab at the top of the Chart Wizard.
Now, click the 'Minor gridline' checkbox in the 'Value (Y) axis' section to add a checkmark. Dozens of tiny lines will be added to your graph. Yikes--that's a bit much. I liked it better without all those gridlines. Click the checkbox again to turn off the minor gridlines.
Click the 'Legend' tab at the top of the Chart Wizard.
Deselect the 'Show Legend' checkbox to get rid of the box that says 'Series 1' on the right side of your chart.
Click the 'Data Labels' tab and then click the 'Show value' option button. Notice how Excel places a value above each column. This looks a bit tacky, so click the 'None' option button instead.
Finally, click the 'Data Table' tab and then click the 'Show Data Table' checkbox. Excel will construct a table of values just below your graph. I don't think we need this, so click the checkbox again to remove the checkmark.
Now, click the 'Next' button at the bottom of the Chart Wizard.
Step Four
Select the option button in front of the words 'As New Sheet.' This will ensure that Excel creates your chart on a separate worksheet. Placing your chart on the same sheet as your data usually results in a chart that is both hard to read and difficult to print.
If you'd like, you can change the name of this sheet from 'Chart1' to anything you'd like. To do so, click in the text box that says 'Chart1' and use your backspace or delete keys to eliminate all traces of the old name. In its place, type a new name. Let's use 'QtyChart' as the name.
Now, click the 'Finish' button, and your chart will appear.
Part 4
Note: If you're running Excel 97 or 2000, please skip this part.
In older versions of Excel (such as version 5), another menu will fly out after you click the 'Insert' menu and choose 'Chart.' If this happens, choose 'As New Sheet' to ensure that your chart does not wind up on the same sheet as your data. Placing your chart on the same sheet as your data will make it difficult to read and print.
The Chart Wizard should now start.
Step 1
The Chart Wizard will ask you to verify the addresses of the cells to be graphed. If you selected the cells I specified, you shouldn't have to change anything here. Just click the 'Next' button.
Step 2
Now, the Chart Wizard will ask you to select a chart type.