Instructions for the "re-creating" the sample EXCEL-workbook quadratic.xls.
The worksheet quadratic.xls demonstrates how to:
- Enter text comments
- Enter data in a tabular format
- Customize the alignment, cell boundaries, and number formats
- Create a table of function values for a given formula
- Use names to facilitate working with parameters
- Plot data and curves at the same time
- Customize the plot -- this takes a long time, and is "luxury"
The final worksheet allows you to interactively change the parameter values: The function values and the plot are updated automatically. The following instructions allow you to essentially re-create the worksheet step-by-step. It may be most helpful to have the existing worksheet quadratic.xls open, while working on a new worksheet following the instructions below. This way you may always go back and compare with the model.
- Launch EXCEL and open a new workbook.
Arrange the windows so that you can easily see both these instructions and the worksheet, or so that it is convenient to toggle back and fourth. Alternatively print out these instructions.
- Click on the cell B2, enter the title for your worksheet. Hit ENTER.
Write additional comments as needed -- you may want to leave some space for later changes.
- In the cells B12 to B14 enter the names for the parameters a, b, and c.
In the cells C12 to C14 enter the initial values of the parameters, here starts with a=1, b=0, and c=0.
Optional but strongly recommended: Select the cell C12 by clicking on it, and from the INSERT menu choose NAME, DEFINE. Repeat for the cells C13 and C14. Note that the name "c" may not be used -- e.g. cc is a good alternative -- simply type in the name cc into the dialogue box, and hit OK.
- If necessary, select TOOLBARS from the VIEW menu, and activate the FORMATTING toolbar.
Select the range B12 to C14 and define gridlines using the BORDERS button from the FORMATTING toolbar. Alternatively, you may use the FORMAT CELLS dialogue box, which may be opened from the FORMAT toolbar.
- Use the cells B17 to D17 to label the columns of the table of function values.
- Enter the smallest x-value into the cell B18.
- Select the cell B19. To enter a FORMULA, first type an =sign, then use the UP-arrow (or use the mouse) to select the cell B18, and continue by typing +0.5. In the end the formula bar should read: =B18+0.5. Hit ENTER.
- Select the range of cells B19 to B32 and hit CNTRL D (hold down the CNTRL key while striking D). Alternatively use the FILL command from the EDIT menu.
- To relax for a moment, manually enter a few data points in the C column. Refer to the model worksheet for a typical choice. Note: You may leave many cells blank!
- Select the cell D18. To enter the formula for the function again start by typing an =sign. Then type the formula in "standard" computer-math notation. Here we want to enter the formula ax^2+bx+c. First note that we need to explicitly write a * for multiplication and use the ^ for exponentiation. Instead of typing x we simply click on the cell B18 (or use the LEFT ARROW). The parameter values have been defined as names in step 3, and therefore we may simply type a, b, cc (note, since EXCEL did not allow the name c, we just renamed the parameter cc). In the end the cell should read: =a*B18^2+b*B18+cc.
If no NAMES a, b, and cc had been defined for the parameters, one needs to use ABSOLUTE ADDRESSES:
Instead of typing a, b, or cc select the corresponding cells C12, C13 and C14 (you may also type in these addresses, but clicking usually leads to fewer typing errors!). Then EDIT the formula in the formula bar by inserting $ signs to indicate absolute references: In the end the formula in the cell should read: =$C$12*B18^2+*$C$13*B18+$C$14
- Select the range of cells D18 to D32 and copy the formula down, using CNTRL D.
- Click e.g. on the cell D32 to check that the RELATIVE addresses have been copied correctly -- it should now read: =a*B32^2+b*B32+cc (or alternatively: =$C$12*B32^2+*$C$13*B32+$C$14 if no names were used).
- Make the table pretty by drawing thin grid lines and thick outlines, using the BORDERS button from the FORMATTING toolbar. Also, choose a consistent number format either using the appropriate buttons from the FORMATTING toolbar, or by using the FORMAT CELLS dialogue box from the FORMAT menu. Select the NUMBER tab in that dialogue box.
- Select the range of cells B18 to D32. Click on the CHART WIZARD button from the STANDARD TOOLBAR (alternatively select CHART from the INSERT MENU).
- Select LINE for the CHART TYPE and select the TOP-LEFT CHART SUB-TYPE.
(When you have some time you should by all means explore the many different chart types available!)
Hit the NEXT button.
- In step 2 of the chart-wizard dialogue-boxes select the SERIES tab. We want to use the data in the first column to label the first axis -- this takes some work: Click on series1 in the FIELD titles series. Select the formula =Sheet1!$B$18:$B$32 in the VALUES FIELD, copy it using CNTRL C. Select the FIELD labeled "Category (x) axis labels" and paste the formula using CNTRL V.
((There should be a better way for doing this. In EXCEL 95 there was a simple button to declare that the first data series be used as values along the first axis. Send me a note, if you know a better way!))
Finally click the remove button to get rid of the first data series (series1). Go to the NEXT step.
- Enter the title for the chart, the labels x and y for the first and second axes. Then take a brief look at further options behind the other tabs. Here you may only turn off the options of showing a legend.
Click the NEXT button, and in the final dialogue box make sure that "As object in" is selected, together with "Sheet 1" in the associated FIELD. Hit the FINISH button.
- Drag the chart to the desired place, and adjust its size by clicking and dragging on its corners.
Most of the following may be considered luxury for beginners -- yet in the end the quality of the visual presentation may make all the difference. Whenever you can afford the time, practice state-of-the-art formatting!
- Select the chart by clicking on it.
Practically all of its features may be fine-tuned. The modern way is to select each of its features using the mouse and double clicking in it. The following are just a few ideas and suggestions -- compare your chart with the one in the model worksheet -- try to do even better!
- Double-click on the labels along the first axis. In the dialogue box:
Under the SCALE tab make sure that none of the three bottom checkboxes are selected (e.g. to label points rather than intervals).
Also under the SCALE tab adjust the number of tickmarks and labels to be shown (fewer is nicer).
Straighten out the orientation of the labels under the ALIGNMENT tab.
Under the NUMBER tab adjust the number of decimal places that you want to be shown for the tickmarks.
- Double-click on the labels along the second axis and repeat the analogous steps.
Note, this is also the place where to cut off the vertical window size, e.g. to discard very large values that lie far outside the region of interest.
- Double-click on the data points or lines (graph) of either data series (this may take some trial and error). Initially you may only want to customize the PATTERN under the tab with the same name: In the dialogue box, select the desired color, style, and thickness for the lines, and for the data-points. To learn more, choose different symbols each time, and experiment with fore-and backgrounds. Eventually you want to be master at working with the most impressive presentation. Again, use the existing model worksheet for guidance.
- Repeat for the other data series -- sometimes one of the data series has a very faint color and difficult to select.
- Further options are to adjust the number, format, and placement of gridlines shown in either direction, the color used for the background etc. Usually one can simply get to the corresponding dialogue boxes by double clicking on the gridlines, the background etc. If this fails, simply use the OPTIONS from the CHART pull-down menu (make sure that the chart is selected!).
- To get to use the interactive features of the worksheet simply change the values of the parameters by editing the contents of the cells C12 to C14. After striking ENTER, the table and chart should both be updated automatically! Use your knowledge from algebra and precalculus to quickly adjust the parameters of he quadratic function so that its graph passes through the given data points.
- To extend your practical knowledge, separately select the chart, copy it using CNTRL C, then open a MS-WORD document, and paste the chart using CNTRL V. Repeat for the region of cells A15 to E33.
In the WORD document, you still can edit the entries of the table (but they are no longer live, i.e. the chart is no longer automatically updated), and you can still adjust the size and placement of the chart, but can't control individual features of the chart.
Note: It is actually possible to paste the chart and table as live objects that are automatically updated when the EXCEL workbook is edited -- this is usually done using OBJECT from the INSERT menu in WORD...