SPREADSHEETING WITH EXCEL
II. GOALSEEKING, SOLVING, AND GRAPHING
R.M. Felder
Department of Chemical Engineering
North Carolina State University
November, 1994
(Revised by E. M. Wilcox, August, 1998)
Statement of the problem.
Given below are five equations relating seven variables: x, T,
p_{a}*, p_{b}*, p_{a}, p_{b}, and
y.
We wish to create an EXCEL spreadsheet that will take as input several
values of x, and for each one, find T for which
(p_{a} + p_{b} = 760). (This is a sixth equation).
The procedure will be as follows:
 For a given x, assume a value of T.
 Calculate log_{10}p_{a}* and log_{10}p_{b}* from Eqs. (1)
and (2). Then determine p_{a}* and p_{b}* by raising 10 to each of the two calculated values.
 Calculate p_{a} and p_{b} from Eqs. (3) and (4).
 Calculate (p_{a} + p_{b}) and compare the result with 760. If the two figures do not agree, try a new value of T and repeat from Step 2.
Iterate to convergence.
 When convergence has been achieved, calculate y from Eq. (5).
 Repeat Steps 15 for a set of values of x between 0 and 1. Then
plot T vs. x and T vs. y on a single graph.
The calculation will involve generation of the spreadsheet shown in Table 1.
For example, when x = 0.900, a value of T = 82.2 substituted into Eqs. (1) and (2) yields p_{a}* = 809.6 and p_{a}* = 313.7,
respectively. These values substituted into Eqs. (3) and (4) in turn lead
to values of p_{a} and p_{b} that add up to the target value of 760.0. The corresponding value of y from Eq. (5) is then 0.959.
After this spreadsheet is generated, it will be sorted: Rows 1222
will be rearranged in order of increasing values of x in Column A. EXCEL
will then create plots of T vs. x and T vs. y that will appear as shown in Figure 1.
In the tutorial exercise that follows, we will give instructions for
running the modern version of EXCEL (which runs on Windows NT and Windows 95)
and the old version (which runs on SPARCstations under a Windows 3.1 emulator
like SoftWindows or WABI). We strongly suggest that you use a machine that
runs the new version, howeverthe old one is very slow and generally
inferior.
EXERCISE
 Bring up a EXCEL window
 Windows 95 or Windows NT: Open EXCEL from the Start menu (button on
lower left of screen, select "Programs" and find EXCEL) or the Office toolbar.
 Windows 3.1: Hold middle mouse button down to bring up the Application
Menu, select MSWindows Emulators  WABI. When WABI has
opened (it may take a VERY long time), go to the Program Manager window, double click on the MS Office icon,
then, double click on EXCEL.) After another considerable interval, an EXCEL
window will appear.
 Name a new spreadsheet file. Choose the File menu in
the EXCEL window and select Save As. When the "Save As" dialogue window
comes up, find the directory in which you want the file to be stored in the
"Directories" window (change the drive in the "Drives" window first if
necessary), then double click in the "File Name" window and type
mod2.xls[CR]. If another window comes
up asking for summary information, type anything you want and click on "OK" or
"Return."
The spreadsheet is now stored under the name "mod2.xls", and that name should
appear in the title bar at the top of the EXCEL window.
 Enter labels and data in Rows 110. Note that
[rightarrow] and [downarrow] refer to the arrow keys below the
"Insert"..."Page Down" keys.
 In Cell [A1], enter your name, your class and section (e.g., CHE 205,
Section 1), and "EXCEL Tutorial II." Make the typing boldface. (Use the "B"
button on the formatting toolbar.)
 Starting in Cell [A3], enter P(mm Hg) = [rightarrow] 760 [CR]
You should see P(mm Hg) = in Cell [A3] and 760 in Cell [B3].
If Column A is too narrow for the contents of [A3] (including the equal sign) to
show, click on the column label (A) to highlight the entire column; point to the
location right between Columns A and B (the pointer symbol should change to a
vertical bar); hold down the left mouse button, and
drag the pointer to the right to make Column A wider.
 Starting in Cell [A5], enter the following
Antoine equation coefficients [downarrow] Compound [rightarrow]
A [rightarrow] B [rightarrow] C [CR]
 Rightjustify the column headings. Point at Cell A6,
hold down the left mouse button, drag the pointer to Cell D6, and
release. The range [A6:D6] should be highlighted.
Click the "right justify" icon on the EXCEL tool bar.
Row 6 should now contain Compound A B C, with each label at the right
of its cell.
 Enter the items shown in Columns AD of Rows 7 and 8 of the table
shown at the beginning of this tutorial:
Row 7: Benzene [rightarrow] 6.90565 [rightarrow] 1211.033 [rightarrow]
220.790
Row 8: Toluene [rightarrow] 6.95334 [rightarrow] 1343.943 [rightarrow]
219.377
 Rightjustify the compound names by selecting the range [A7:A8] (point
to A7, Press and hold, drag to A8, and release), and then clicking the
"right justify" icon on the EXCEL tool bar.
Save the spreadsheet with the Ctrls command. Hold down
the "Ctrl" key and type s. (Alternatively, click the "floppy disk"
icon on the EXCEL tool bar.)
 Enter the labels in Row 10 (x through y) from Table 1.
Beginning in Cell [A10], type
x [rightarrow] T [rightarrow] pa* [rightarrow] pb* [rightarrow] pa+pb
[rightarrow] y [CR]
Then rightjustify each entry by selecting [A10:F10] and clicking
the "right justify" icon.
 Enter the values of x shown in Column A of the table.
Beginning in Cell [A12] (two cells below the x, type
0 [downarrow] 1 [downarrow] .1 [downarrow] .2 [downarrow] .3
[downarrow] .4 [downarrow] .5 [downarrow] .6 [downarrow] .7 [downarrow] .8
[downarrow] .9 [CR]
(Don't use a formula to enter these values, and don't bother typing the
trailing zeros, like the trailing 00 in 0.100.)
Save the spreadsheet.
 Enter a guess of 100 for the value of T when x = 0.0. In Cell
[B12], enter 100 [rightarrow].
 Enter the formula for p_{a}* in Cell
[C12]. In Cell [C12], type (beginning with an equal sign)
= 10 ^ ($B$7  $C$7 / (B12 + $D$7))
[CR]
Verify that this formula corresponds to Eq. (1) for T=100. If you
entered the formula correctly, the value 1350.491 should appear in Cell [C12].
We anticipate copying this formula into the rest of Column C.
When we do so, as we move down the column we will want the cell address [B12]
in the formula to change to [B13], [B14], etc., which it will since we used
the relative cell address. However, we do not want cell address [B7] in the
formula (which contains the Antoine coefficient A for benzene) to change to
[B8], [B9], etc., and so we use the absolute address [$B$7].
 Copy the formula from [C12] to [D12], and change the cell addresses
as necessary to calculate p_{b}*
 Click on Cell [C12]. The cursor box will enclose the cell, and a small dot
will be seen at the lower right corner. Point to the dot (the pointer should
change to a thin cross), press and hold the left mouse button,
drag the pointer to the right to Cell [D12]), and release. This procedure copies
the contents of Cell [C12] to Cell [D12].
A very large number should appear in [D12]. (The formula is not yet
correct.)
 Edit the formula in the formula bar [= 10^($B$7...], replacing the three
7's with 8's and the C12 with B12. (Aim the pointer just next to the
character to be deleted on the edit line, press the left mouse button and
drag the pointer over the character in question, release the button, and
type the replacement character.) When all the desired changes have been
made, hit [CR].
The formula should now read = 10^($B$8$C$8/(B12+$D$8)),
and the
value in the cell should be 556.3212. Verify that this is the correct formula
(Eq. 2) for calculating p_{b}* at 100°C.
 Calculate (pa + pb) [which from Eqs. (3) and (4) equals
xp_{a}* + (1x)p_{b}*]. In Cell [E12], type
= A12*C12 + (1A12)*D12 [CR]
The value in Cell [E12] should be 556.3212. When the desired value of T has
been found, this figure will be 760.0.
 Calculate y [which from Eqs. (3) and (5) equals
xp_{a}*/(p_{a}+p_{b})]. In Cell [F12], type
The value in [F12] should be zero.
Save the spreadsheet.
Goalseeking
We are now ready to begin the trialanderror search for the correct
value of T corresponding to x = 0 in Row 12.
 Vary the value of T in Cell [B12] (the variable
cell) until the value of (p_{a} + p_{b}) in Cell
[E12] (the target cell) equals the specified target value
(760).
 Click on Cell [E12].
 Choose the Tools menu and select Goal Seek. A dialog
window will come up containing three data entry boxes, with the address $E$12
entered in the first one ("Set cell"). If necessary, move the dialogue window
(press and hold on the title bar, drag the window to the desired location,
release) so that you can see Row 12 of the spreadsheet.
 Click on the To Value box and type 760.
 Click on the By Changing Cell box and type B12 [CR] .
After a little activity, a message box will open labeled Goal Seek
Status, informing you that the search was successful and the final value in
Cell [E12] is 760.0000018. (You won't always get this close to the target
value.) Move the message box if necessary to see Row 12 of the worksheet.
You should see a value 110.6253 in Cell [B12], which is the desired
value of T.
 Click on OK in the Goal Seek Status window. The results
of the search are retained in Cells [B12] and [E12], and you can also see the
correct value of y (0) in Cell [F12]. (If you had clicked on
"Cancel," the old values would have been returned.)
 Save the spreadsheet.
 Copy the formulas in Row 12 into Rows 1322 and repeat the goalseek calculations.. We will calculate T and y for the values of x in Cells [A13] through [A22], taking as initial guesses for each search the value in Column B (110.6253).
 Point to Cell [B12], hold the left mouse button down, drag the
pointer right to Cell [F12], and release. The selected range should be
highlighted, and a small dot should appear at the lower right corner of the
highlight box.
 Point to the dot (the pointer should turn into a thin cross), hold the
left mouse button down, drag down to Cell [F22], and release. The worksheet
should be filled over the target range.
 Save the spreadsheet.
 Do the calculation for Row 13.
 Click on Cell [E13]. Choose the Tools menu and select Goal Seek.
 Move the dialog window if necessary so you can see Rows 1222 of the worksheet.
 Click on the To Value box and type 760.
 Click on the By Changing Cell box and type B13 [CR].
You will see a blur in Row 13 as the search proceeds. Click OK on the message box once it appears. For x=1, EXCEL calculates T=80.09999 and y=1.
 Do another goalseek, selecting E14 as the set cell, 760 as the
target value and B14 as the changing cell. Continue this procedure,
increasing the row numbers of the variable and target cells by one each time
until you complete the calculation for Row 22.
 Save the spreadsheet.
Reformatting the Spreadsheet
You may have noticed that the appearance of the worksheet is
somewhat sloppy, with variable numbers of decimal points in the entries
(unless you specify otherwise, EXCEL does not print trailing zeros after
decimal points). Before doing any more calculations, let us reformat the
worksheet to make it neater.
 Show the values of x and y with three decimal places,
and format Cells [B12:E22] so that all values have one decimal place.
 Select the range [A12:A22] by holding down the
left mouse button on [A12], dragging down to [A22], and releasing.
 Click on Format from the Menu bar. Select Cells.
This will activate the Format Cells window.
 Click on the Number tab that appears at the top of the window if
it is not already highlighted in boldface. This window allows you to change the
way numbers appear in the selected cells. (If you click on a column heading you
can format the entire column this way.) In the Category window, click
on Number.
None of the format codes at the window on the right matches what we want (three
decimal places), so we create our own code.
 Doubleclick on the Code entry box at the bottom of the dialogue
window. When the box is highlighted, type 0.000 [CR].
All the values in the selected range should now have the desired appearance.
 Select the range [F12:F22]. Follow the above procedure to format
the range so that the values are all displayed with three decimal places.
 Select the range [B12:E22]. Format it to display the values with
one decimal place (Code = 0.0).
 Select the range [D7:D8], and format it so that the values are
displayed with three places to the right of the decimal point.
Your worksheet should now look exactly like the one shown at the beginning of
this tutorial.
 Save the spreadsheet.
Sorting
We now want to generate plots of T vs. x and T vs.
y (as in the graph at the beginning of this tutorial). Before doing
this, it will be necessary to reorder the rows of the worksheet so that the
values of x in Column A are listed in ascending order, which for
x would be 0, 0.1, 0.2,..., 1.0. In spreadsheet jargon, this means we
must sort the worksheet in ascending order based on Column A.
 Sort the portion of the worksheet from [A12] to [F22] in
ascending order based on Column A.
 Select the rectangular range [A12:F22] by pointing at [A12], holding
down the mouse button and dragging down and right to [F22], and releasing.
 Choose the Data menu and select Sort to bring up the
"Sort" dialog window. Move it so that you can see the selected range on the
worksheet.
 Point and click the left mouse button in the Sort By box, and
select the column in which you had your x values (Column A) if it is not already
selected. This means that the rows in the selected range will be reordered so
that the values in Column A will be listed in ascending order. (You could also
arrange them in descending order by checking the appropriate box in the dialogue
window.)
At this point, you could enter another column in the Then By box to
be used as the basis for ordering the rows if two elements in Column A have the
same value. (This will not occur in this example, so we won't bother to enter
a secondary key.)
 Click OK. The specified range of the worksheet should now be
sorted. (Look at Column A.)
 Save.
Note: If you sort a worksheet based on a column containing
text entries, the sorting is in alphabetical order of those entries.
Plotting: Windows 95 & Windows NT
We are now ready to generate the graph. You will first generate a line
plot of T vs. x, then a second line plot of T vs.
y.
Click on Windows 3.1 if you are working on a
SPARCStation or another computer running an old version of EXCEL.
 Create the initial line graph.
 Start the plot generation procedure. Click the "graph" icon on
the EXCEL toolbar. (It looks like a bar graph with colored vertical bars toward
the right of the toolbar. Alternatively, you could choose the Insert
menu and select Chart.)
A dialogue window labeled "Chart Wizard" will appear, which will take you
step by step through much of the graph generation process.
 Select a chart type and subtype. Click on XY Scatter
under "Chart type:" and then on the plot with smooth curves and no data points
under "Chart subtype." Click on Next> .
 Define the range containing the data to be plotted and title the legend. In the Step 2 dialogue box, click on the "Data Range" tab at the top if it is not already selected.
In the "Data range" entry box, type A12:B22 , and click to indicate that your data series are in Columns . A plot of T vs. x will appear in the dialogue window. Don't click on "Next>."
Still in the Step 2 box, click on the "Series" tab. In the "Name" entry box, type
 Liquid: T vs. x.
Click on Next>.
 Define the chart title and label the axes.
Click on the Titles tab of the Step 3 window if it is not already selected. Make the following entries in the appropriate boxes (click in each new box rather than hitting [CR]).
 Title the chart TXY DIAGRAM FOR BENZENE/TOLUENE AT 760 MM HG
 Title the Category(X) axis Benzene fraction
 Title the Value(Y) axis Temperature (Deg. C). Don't click on "Next>."
 Specify whether you want gridlines displayed. Click on the Gridlines tab of the Step 3 window. Insert and remove check marks in each of the boxes to see the effect on the graph, but finish by eliminating all check marks (so that the graph will not display gridlines). Don't click on "Next>."
 Specify whether you want a legend displayed. Click on the Legend tab of the Step 3 window. The graph displayed should have a "T vs. x" legend displayed on the right. If you did not want to display a legend (which you generally won't when your graph contains only one curve) you would delete the check mark next to "Show legend." (Don't do it now.) Click on Next>.
 Specify whether you want the chart displayed on your worksheet or in a separate sheet. Make sure "As object in: Sheet 1" is selected in the Step 4 window. Click on Finish.
A small ugly graph will appear somewhere on your worksheet.
 Save.
In what follows, "graph window" refers to the large white box containing
the graph, title, axis labels, and legend, and "plot area" refers to the
interior shaded box that contains the curve. We will now modify the graph to
get the desired appearance (shown in the beginning of this tutorial).
 Move and resize the graph.
 Move the graph. Point to a blank space inside the chart
window but outside the plot area, reasonably far away from any text or object, and wait for a second or two. When a label appears that says "Chart area," Press and hold, drag the window to a suitable location on the worksheet (to the right of the data or
below it), and release.
 Resize the graph. You should see black dots at the corners and at
the midpoints of each border of the graph window. If you don't, click on the
window (outside the plot area) to make them appear. Point to the dot at the
bottom right corner, finding a location where the pointer turns into a
twoheaded arrow. Press and hold, and drag down and to the right to enlarge the
window enough for everything to be clearly displayed. (Don't worry about
getting it perfectyou can always change it again later.)
To resize the graph only vertically you would use the bottom middle dot,
and to resize it only horizontally you would use the right middle dot.
 Save.
 Rescale and format the axes. Put the pointer directly on the
horizontal (x) axis and wait a moment. When a small box labeled "Value (X) axis
comes up, doubleclick. A "Format Axis" window will appear. In the next series of commands, just go from one tab to another without
clicking "OK."
 Under "Patterns," select major tick marks inside, no minor tick marks.
 Under "Scale," make the axis run from 0 to 1, with major tick marks at
intervals of 0.1.
 Under "Font," select "Font style:" = Arial, Regular.
 Under "Number," select Category = Number, Decimal places = <3>.
 Under "Alignment," move the pointer on the semicircle to the top (12
o'clock) position.
 Click on OK.
Repeat this procedure for the vertical axis, first doubleclicking on the axis to bring up the "Format Axis" window.
 Under "Patterns" put major tick marks inside.
 Under "Scale" have the axis run from 80 to 112,
with the x axis crossing at 80.
 Under "Font" select Arial, regular.
 Under "Number" select Number, 0 decimal places.
 Under "Alignment," leave the pointer at the horizontal (3 o'clock) position.
 Click on OK.
 Remove the legend border and change the legend background color to white. Doubleclick on the legend box. A "Format Legend" window will come up. Select the "Patterns" tab if it is not already selected.
 Under the "Border" options, click None.
 Under the "Area" options, click on white (lower right box).
 Click on OK.
 Save.
 Add the second plot (T vs. y) to the graph.
 Select the graph window (click outside the plot area) and then click on the chart wizard icon again. Click Next> to get to Step 2, and click on the "Series" tab.
 Click on the "Add" button near the bottom left of the window.
 Type Vapor: T vs. y in the "Name:" box.
 Type =Sheet1!F12:F22 in the "X Values:" box.
 Type =Sheet1!B12:B22 in the "Y Values:" box.
 Click on Finish.
 Save.
The second graph should now be on the chart.
 Reformat the second curve. Point to the top curve (T vs. y), finding a location where a small "Series: T vs. y" box appears next to the pointer. Then doubleclick to get the "Format Data Series" window. In the "Patterns" window, "Line" options, use the pulldown menus to select a dashed line for "Style" and black (the square at the upper left of the pulldown menu options) for "Color."
 Move the legend inside the plot area. Press and hold on the legend, drag it to the upper right of the graph, and release.
 Change the background of the graph from gray to white. It could be hard to see black lines on a gray background.
 Doubleclick on the grey area of the graph to bring up the "Format Plot Area" window.
 Under the "Area" section, select white. Click on OK.
 Save.
The graph is now complete.
 Preview, print, and exit.
 Deselect the graph (click anywhere outside the graph window) and select "Print Preview" under the "File" menu.
 If the worksheet including the graph does not fit on a single page, close the preview window, make the graph smaller, and repeat. When everything is on one page, close the preview window and print the worksheet (select "Print" under the file menu).
 Exit from EXCEL. Choose "Exit" under the "File" menu, or click on the X button at the upper right corner of the EXCEL window.
Click on Physical basis of the tutorial to see a description of the vaporliquid equilibrium problem that this spreadsheet was designed to solve. Otherwise, you may exit from the tutorial.
Plotting: Windows 3.1
 Create the initial line graph.
 Specify the range containing the data that you want to plot.
Select the range A12:B22 on the worksheet by clicking and holding on
[A12], dragging the pointer to [B22], and releasing.
 Start the plot generation procedure. Scroll the worksheet down
so that Row 19 appears at the top. (This is to show the intended location of the graph, which will be below Row 22.) Click the "graph" icon on the EXCEL toolbar. (It looks like a bar graph with vertical bars toward the right of the top toolbar.)
 Define the graph location and size. When you click on the
graph icon the pointer will become a thin cross. Move it to a point in Column A
somewhere below Row 22, Press and hold, drag the pointer down and right to
somewhere in Column F to define an approximate square, and release. (Don't
worry about getting it perfectonce you have created the graph you may always
change its size and position.)
A dialogue window labeled "Chart Wizard" will appear, which will take you
step by step through much of the graph generation process. It will first ask
if the range you selected is the range you actually want (A12:B22). Click
Next> .
 Select a chart type. In the Step 2 window, click on XY
Scatter chart type, and then click on Next> . In the Step 3 window,
click on the plot with smooth lines and no data points
(Subtype 6), and then click on Next>
 Define the location of the horizontal axis data. In the Step 4
Window, a sample chart will come up showing what the plot will look like, and
several selection boxes will be displayed on the right.
 Under "Data series in:" click on Columns
 Enter that you want to use the first 1 column for X
data
 Enter that you want to use the first 0 row for legend
text
 Click on Next>
 Define the chart title and label the axes.
In the Step 5 window,
 Click on Yes under "Add a legend." (We will later
change it from "Series 1.")
 Title the chart
 TXY DIAGRAM FOR BENZENE/TOLUENE AT 760 MM HG
 Title the Category(X) axis Benzene fraction
 Title the Value(Y) axis Temperature (Deg. C)
 Click on Finish.
 Save.
The graph will appear in the specified location on the worksheet. If
necessary, scroll down so that you can see the entire graph window.
In what follows, "graph window" refers to the large white box containing
the graph, title, axis labels, and legend, and "plot area" refers to the
interior shaded box that contains the curve. We will now modify the graph to
get the desired appearance (shown in the beginning of this tutorial).
 Modify the appearance of the graph.
 Specify whether you want gridlines displayed. We don't in this
example, but let's see how you would get them if you wanted them. First,
doubleclick on the graph window to activate (select) the graph. You should see
two sets of 8 dots, at or near the corners of the graph window and at or near
the midpoints of the window borders. If you don't see them, keep clicking until
you do.
Under the Insert menu, select
Gridlines. If you clicked in the appropriate boxes on the dialogue window that comes up, you could get vertical or horizontal gridlines at major and minor tickmarks on the graph. (Don't do it now.) Click Cancel.
 Rescale and format the axes. Put the pointer directly on the
horizontal (x) axis and doubleclick. A "Format Axis" window will appear.
In the next series of commands, just go from one tab to another without clicking
"OK."
 Under "Patterns," select major tick marks inside, no minor tick marks.
 Under "Scale," make the axis run from 0 to 1, with major tick marks at
intervals of 0.1.
 Under "Font," select "Font style:" = Regular.
 Under "Number," select Category = Number, Decimal places = <3>.
 Under "Alignment," select the middle vertical option (text going from bottom
to top)
 Click on OK.
Repeat this procedure for the vertical axis. Doubleclick on the axis to bring up the "Format Axis" window.
 Under "Patterns" put major tick marks inside.
 Under "Scale" have the axis run from 80 to 112,
with the x axis crossing at 80.
 Under "Font" select Arial, regular.
 Under "Number" select Number, 0 decimal places.
 Under "Alignment," designate the horizontal text position.
 Click on OK.
 Save.
 Rename the legend. Activate the chart (click on the graph
window to bring up the sizing dots), point directly at the curve, and
doubleclick. You should see a series of squares along the length of the
curve. If you don't see them, singleclick on the curve to bring them up.
Still pointing directly at the curve, doubleclick. A "Format Data Series" window should come up. Under the "Name and Values" tab in the "Name:" entry box, change "Series 1" to Liquid: T vs. x . Click on OK.
 Remove the legend border and change the legend background color to white. Doubleclick on the legend box. A "Format Legend" window will come up. Select the "Patterns" tab if it is not already selected.
 Under the "Border" options, click None.
 Under the "Area" options, click on white (lower right box).
 Click on OK.
 Save.
 Add the second plot (T vs. y) to the graph. The easiest way to add a second plot to a graph is to put the new data point coordinates in adjacent columns (horizontal axis coordinates on the left), copy the 2column range, and paste it into the graph.
 Copy the T column and paste it to the right of the y column. Here is one way to do it.
 Select the T column including the heading (Press and hold on Cell [B10], drag down to Cell [B22], and release).
 Move the pointer to the right edge of the highlighted area. The pointer should become an arrow.
 Press and hold the right mouse button, drag the area to Column G (Row 10 through Row 22), and release.
 Save.
 Paste the data points on the new plot (horizontal axis values in Cells F12:F22, vertical axis values in Cells G12:G22) into the graph.
 Highlight the rectangular range [F12:G22].
 Move the pointer to the right edge of the higlighted area. The pointer should become an arrow.
 Press and hold the right mouse button, drag the area into the graph, and release.
A "Paste Special" window will appear. Place check marks as follows:
 Add cells as New Series
 Y values in Columns
 Categories (X values) in first column
 Click on OK. The second plot will now appear in the graph.
 Save.
 Change the line style and rename the legend of the second plot.
Currently the two plots are shown as two solid lines of different colors. We want to change the "Vapor" line to be a dashed black line. (Different colors cannot be distinguished on a blackandwhite printout.) We also want to change the legend to "Vapor: T vs. y."
 Activate the graph by doubleclicking on it.
 Click on the top (vapor) curve on the graph to bring up the boxes along the length of the curve.
 Still pointing directly at the curve, doubleclick to bring up the "Format Data Series" window.
 Under the "Name and Values" tab in the "Name:" entry box, change "Series 2" to Vapor: T vs. y .
 Under the "Patterns" tab, "Line" options, click on the arrow to the right of "Style:" and select a dashed line.
 Still under the "Patterns" tab, "Line" options, click on the arrow to the right of "Color:" and select black.
 Click on OK.
 Save.
The graph should now look like the one at the beginning of this tutorial.
 Move the legend inside the plot area. Press and hold on the legend, drag it to the upper right of the plot area, and release.
 Change the background of the graph from gray to white. It could be hard to see black lines on a gray background.
 Doubleclick on the gray area of the graph to bring up the "Format Plot Area" window.
 Under the "Area" section, click on the white box. Click on OK.
 Resize the graph. The axis labels are rather crowded.
Adjust the window size until you are satisfied with the appearance of the graph.
 Save.
 Print the worksheet. Deselect the graph (click anywhere on the worksheet outside the graph window). Choose the File menu and select Print.
The worksheet and the graph should come out on your default printer. If you wish to print only the graph, click on the graph and then print. EXCEL will automatically scale up your graph to fit on one full page.
 Exit from EXCEL. Select Exit under the File menu.
A description follows of the vaporliquid equilibrium problem that this spreadsheet is designed to solve. If you don't wish to read it now, you may exit from the tutorial.
APPENDIX: PHYSICAL BASIS OF THE TUTORIAL PROBLEM
The vapor pressure of a substance, p* , can be expressed
as a function of temperature, T, by the Antoine equation:
The Antoine constants (A, B, and C) for benzene and toluene are given
below for T in degrees Celsius (°C) and p* in millimeters
of mercury (mm Hg):
Compound  A
 B
 C 
   
Benzene  6.90565  1211.033
 220.790 
Toluene  6.95334  1343.943
 219.377

For example, at a temperature of 100.0°C, the vapor
pressure of benzene can be calculated as follows:
and so
p*(100^{o}C) = 10^{3.1305} = 1350 mm Hg
Another way of stating
this result is that at a pressure of 1350.5 mm Hg, the boiling point
of pure liquid benzene is 100°C.
Suppose we have a liquid mixture of benzene and toluene, with
x being the fraction of benzene (technically x is the mole
fraction of benzene in the liquid mixture, but if this means nothing to
you it doesn't matter). That is, if x=0.30, the mixture contains 30%
benzene and 70% toluene. Further suppose that the mixture is heated in an
open flask on a day when the barometric pressure is P_{atm} = 760 mm Hg.
(Under normal atmospheric conditions, P_{atm} varies in the range 750770
mm Hg.) As the liquid temperature increases, the vapor pressure of benzene
(p_{a}*) and that of toluene (p_{b}*) both increase, following the Antoine equation. According to Raoult's Law, the liquid in the flask will boil when the temperature reaches a point such that
where the partial pressures of benzene and toluene in the vapor bubbles
are calculated as
and (p_{a}*) and (p_{b}*) are given by the Antoine equation. Moreover, the fraction of benzene in the vapor bubbles that form when the liquid boils is
The problem we wish to solve is, given a value of P_{atm}
(=760 mm Hg), calculate the boiling point temperature, T, and the fraction of benzene in the vapor bubbles, y, for a series of benzene fractions in the liquid, x. The procedure is the trialand error calculation outlined at the beginning of the tutorial.
 For a given x, assume a value of T.
 Calculate log_{10}p_{a}* and log_{10}p_{b}* from the Antoine equation. Then determine p_{a}* and p_{b}* by raising 10 to each of the two calculated values.
 Calculate p_{a} and p_{b} from Eqs. (8) and (9).
 Calculate (p_{a} + p_{b}) and compare the result with 760. If the two figures do not agree, try a new value of T and repeat from Step 2. Iterate to convergence.
 When convergence has been achieved, calculate y from Eq. (10).
If we perform this calculation for a number of values of x between zero and one, we can generate the TXY diagram created in this tutorial. Once we have it, vaporliquid equilibrium calculations become easy.
For example, if we heat a 50% liquid mixture of benzene and toluene
(x=0.50) at a pressure of 1 atm (760 mm Hg), the mixture will boil
at 92°C (obtained by going straight up from x=0.5 to the
liquid curve and reading the temperature), and the vapor bubbles will
contain about 71% benzene and 29% toluene (obtained by going across
at 82°C to the vapor curve and reading the fraction on the
horizontal axis).