Undergraduate and graduate students in technical programs will encounter Excel at some point during their studies and in the workplace beyond.
For example, I require students in my undergraduate analytical chemistry and instrumental analysis lecture and laboratory courses to use Excel extensively. I have also introduced first-year students to Excel in their second-term general chemistry courses, and my undergraduate research students must use the software for the various types of data analysis they perform.
There are probably numerous reasons not to use Excel, but I have to say it serves the purposes of many of us analytical scientists well and is in my humble opinion, an excellent no pun intended tool for performing many types of analytical data treatment. So, I ask: why not Excel? By clicking on any of the above social media links, you are agreeing to our Privacy Notice. After taking undergraduate analytical chemistry in the mids, Mark Stauffer was convinced that analytical chemistry was the type of work he wanted to do as a chemist. Stauffer wanted to pursue an academic career in analytical chemistry, at a primarily undergraduate institution.
In column B we will now calculate a sine wave. Excel instructions do not distinguish between lower case and capitals, but the formula bar always displays them as capitals, which are more clearly legible. By now your spreadsheet should look like that depicted in Fig. There is a more convenient way to generate the second column.
This will copy the instruction down as far as the column to its immediate right contains data! This is a very useful method, especially for long columns. When there are no data to its immediate right, the column to its immediate left will do.
When both are absent, the trick will not work. Finally we will make a graph of this sine wave. We will here describe the procedure for each of these two versions. Bring the mouse pointer to cell A3, click on it, drag the pointer while keeping the mouse button depressed to cell B11, then let go of the mouse button. This will activate and highlight the rectangular area from cell A3 through B11 in spreadsheet parlance: A3:B11 containing the data to be graphed.
In Excel 97 or a more recent version, go with the mouse pointer to the menu bar, click on Insert, and in the resulting drop-down submenu click on Chart. The line plot can give you very misleading graphs because it pre- sumes that the x-values are always equidistant. For now, pick the points connected by smooth lines — you can always change it later. This is a general property of working withWindows Excel: you need not agonize over a choice, because there are almost always opportunities to change it later. So the best strategy is: when in doubt, pick something, move on, and worry about the details later.
Step 2 of the Chart Wizard shows the Data range selected. Also, under the Series tab, it shows which column will be used for X-values, and which for Y- values. The default i. But it is handy to know that you can here, in step 2 of the ChartWizard, change the assignments for X andY. Step 3 lets you enter a Chart title and axes labels.
Click on the Chart title window, and enter Sine wave. Then click in the Value X Axis window, and enter angle. Finally, click in the Value Y Axis window, and enter sine. A picture will show you what your graph is going to look like. There are other things you can specify at this point, such as the axes, grid- lines, legends, and data labels, but we will forgo them here in order to keep things simple for now, and to illustrate later how to modify the end product.
Select the latter, and Finish.
This will place the graph on the spreadsheet. Now click on the graph, preferably inside its outer frame near its left edge, where the computer cannot misinterpret your command.
This will adorn the graph with eight black handles, which allow you to change its size and location. First, locate the mouse pointer on the graph, depress the mouse button, and while keeping it down move the graph to any place you like on the spreadsheet, preferably somewhere where it does not block data from view. To release, simply release the mouse button.
Repeat with the other sides. If you want to remove the gray background which seldom prints well just click somewhere in the plot area where the label shows Plot Area , right- click, highlight Format Plot Area, and under Area either select None or, in the choice of colors, click on white. Exit with OK. If you want to get rid of the horizontal grid lines, point to them the label will identifyValue Y Axis Major Gridlines , right-click, and select Clear.
To change the range of the x-scale, point to the axis the label will show Value X Axis , right-click, select Format Axis, and under the Scale tab pick the scale properties you want. Ditto for the numbers on the vertical axis. To change the type of graph itself, point at the curve, right-click, and select Format Data Series. And so it goes: you can point at virtually every detail of the graph, and modify it to your taste. Figure 1. Go with the mouse pointer to the menu bar, click on Insert, and in the resulting drop-down submenu click on Chart.
A second box will appear, which lets you select a graph either On the spreadsheet, or As a separate sheet. Select the former by clicking on it.
You will now see a succession of ChartWizard boxes that let you specify how the graph should look. Either method will produce a dialog box labeled ChartWizard. The second ChartWizard box lets you specify the type of graph you want. Click on the XY Scatter plot; your choice will be highlighted. Do not select the Line plot, because it will automatically assume that all X-values are equidistant. This is convenient when you want to plot, e. Sine wave Excel has many options, and often several ways to achieve each of them. Here we describe only a few simple ways to get you started, without confusing you with many possible alternatives.
The fourth box shows you a sample chart.
The top right-hand corner will let you specify whether you want to plot rows or columns; we will usually plot columns, and that will most probably already have been selected. Step 5 allows you to add a legend, and to label the axes. If the question Add a Legend? Point to the rectangular window under the heading ChartTitle, click on it, then type a title of your choice, say, Sine wave, and deposit that title. Similarly, enter a legend for the X-axis in the text box next to Category [X]: , and a legend for theY-axis in the box next toValue [Y]:.
That is all for now: click on the Finish button in the lower right-hand corner of the ChartWizard. You should see the graph, properly scaled, with tick marks and associated numbers, and it should look more or less like Fig. If you had made the graph As a separate sheet, click the mouse on the tab labeled Sheet1 at the bottom of the spreadsheet; to go back again to the graph, click on the tab labeled Chart1, etc.
The numbers for the horizontal scale in Fig. It is nice that Excel selects and labels the scales for you, automatically, but you may want to have the numbers outside rather than inside the graph area. In that case, point with your mouse to a number with the horizontal axis, and click on it. This will result in two black blocks, one on each end of the axis, showing that you have activated the axis. Right-click to produce a small pop- up menu, and click on Format Axis, then select the tab Patterns, click on Tick mark lables Low, and end with OK.
There are two ways to do so. The obvious one is to calculate more points per cycle, so that the points get closer together, the linear seg- ments are shorter, and therefore more closely approach a smooth curve. The easier one OK as long as you do not use the curve for precise interpola- tion is to let the computer draw a smooth curve through the points, which 12 How to use Excel You can do this as follows: double-click on the graph, click on a connecting line segment, right-click on it to get its properties, then click on Format Data Series.
That does it. Finally, we change the font of the legends and labels. Now click on the axis numbers, then in the Formatting toolbar select Times New Roman and, in the adjacent Font Size window, click on 12 points. Do this for both axes. Then click on the axis labels and the graph title and adjust them likewise. Now move the pointer to cell B4 again it should show a cross and click on it. Clearly, as you copied the instruction from cell B3 down, the address of the cell to 1. This is called relative addressing, and is a main feature of all spreadsheets.
In other words, the instruction refers to a cell in a given position relative to that of the cell from which it is called. In copying a formula in a spreadsheet from one cell to another, relative addressing is the norm, i. In fact, most chess moves are relative to the starting position of the moving piece. Sometimes we need to refer to a particular cell, for instance when such a cell contains a constant. In that case we must specify that we want absolute addressing; we do this by preceding both components of the cell address its column letter and its row number by that symbol of stability, the dollar sign.
We can also protect the column but not the row, by placing a dollar sign in front of the column letter, or vice versa; we will occasionally encounter such mixed address modes in subsequent chapters. Now go back to column A, and examine its cell contents. If we anticipate that we might subsequently want to modify the contents of column A, here are two alternative ways to do so. First, deposit the number 1 in cell F1. Now copy this instruction down to cell A11; again, there are several ways to do this. They all start with cell A4 as the active cell; if cell A4 is not the active cell, make it so by clicking on it.
You can also click on the copy icon in the icon bar, indicated by two 14 How to use Excel In Excel 95 and subsequent versions, you can point to the icon if you are not sure of its meaning, and wait one or two seconds: an explanatory note will appear to tell you its function. The above methods illustrate the use of relative and absolute addressing. Now let us look at the result. Go to cell F1 and deposit the value 2; immedi- ately, column A will show the sequence 0, 2, 4, 6, etc.
Play with it, and satisfy yourself that the constant value stored in cell F1 indeed determines the increment. The constant in F1 can be a fraction, a negative number, what- ever. Again the data in column A adjust immediately, as do the values in column B that depend on it. First we lengthen the columns in the spreadsheet to contain more data. Using any of the methods described in section 1.
This will copy both columns. The spreadsheet should now contain several complete cycles of the sine wave. Check that this is, indeed, the case. We will now modify this. With the mouse, point to the line in the graph, and press the Enter key.
Quite a mouthful, but let that be so. Instead of modifying Chart1 we can also make a new graph. Because our earlier graph was embedded in the spreadsheet, now make a separate graph. Embedding a graph has the advantage that you can see it while you are working on the spreadsheet, and the disadvantage that it tends to clutter up your workspace, and that in order to keep them visible on the screen embedded graphs are usually quite small.
Likewise, their size can be changed readily. In Excel 97 etc. Activate the chart, then select Chart Location and use the dialog box. Note that the Chart menu appears only after you have activated a chart, otherwise the same location hosts the Data menu label. If you use a more recent version of Excel, which treats embedded and separate charts the same way, you may want to speed-read or skip this part.
Highlight activate block A3:B Click on Insert Chart, then select On this sheet. Bring the pointer to the left top corner of cell D1, and click. Click on Next. In step 2, select the XY Scatter plot, then click on Next. In step 3, select 2, then Next.
In step 4 use Data Series in 16 How to use Excel If you are adventuresome, make alternative choices and see what they do. There is no penalty for experimenting; to the contrary, this is how you will quickly become familiar with the spreadsheet. To abolish the graph, bring the mouse pointer anywhere inside the graph area, click on it, then use the Delete key to abolish it.
To modify it, highlight the curve and make your changes in the formula bar. You are in charge here, the spreadsheet is your willing servant. Again, the graph you just made may need some adjusting. First let us do its positioning. These handles are there for you to grab if you want to move or resize the graph. In order to move it again, click again on the graph, grab it, and this time move it right smack on top of the data in block A3:B These columns will emerge unscathed, since you did not erase them, but only placed an image over them.
It is like the sun, which is not obliterated by a cloud moving in front of it, but is merely blocked from our view. Now resize the graph. Activate the graph again, and go to the middle bottom handle. When you are on target, the pointer will change into a verti- cal double arrow. Now you can drag the handle, up or down. Likewise you can move the other borders. You can also grab a corner, which allows you to change the graph size simultaneously in two directions.
If you like to nest the graph neatly inside the spreadsheet, you may want the borders to line up with cell boundaries. You can achieve this by depressing the Alt key while dragging the borders, in which case the graph boundaries will jump from line to line.
Copy this instruction all the way down to cell C83 by double-clicking on its handle. Now plot the second sine wave versus X, again embedding the graph in the spreadsheet. GotocellA3,andhighlighttherangeA3:A83 e. Then release the shift key and, instead, depress the Ctrl key, and keep it down. You will now have marked two non-adjacent columns.
When you prefer to type in the range rather than to point to it, this shows you the format to use, except that you can leave out the dollar signs: just type A1:A83,C1:C This textbook for undergraduate and entry-level graduate chemistry and chemical engineering students uses Excel, the most powerful available spreadsheet, to explore and solve problems in general and chemical data analysis. This is the only up-to-date text on the use of spreadsheets in chemistry.
The book discusses topics including statistics, chemical equilibria, pH calculations, titrations, and instrumental methods such as chromatography, spectrometry, and electroanalysis.
It contains many examples of data analysis, and uses spreadsheets for numerical simulations, and testing analytical procedures. It also treats modern data analysis methods such as linear and non-linear least squares in great detail, as well as methods based on Fourier transformation. The book shows how matrix methods can be powerful tools in data analysis, and how easily these are implemented on a spreadsheet and describes in detail how to simulate chemical kinetics on a spreadsheet.
It also introduces the reader to the use of VBA, the macro language of Microsoft Office, which lets the user import higher-level computer programs into the spreadsheet. No analytical chemistry teacher, no serious analytical chemistry student, should fail to acquire this book. Clark, Chem.
Educator ' It is not for the fainthearted, with many complex calculations examined, but that is how it should be, for the complexity comes not from the way it is written, but from the subject matter tackled. I would happily recommend it to those interested in applying mathematical methods to chemistry, but who perhaps lack full command of a programming language.
The author has written a 'how to' book dealing with typical analytical chemistry calculations and using one of the most popular and widely available spreadsheet programs, Microsoft Excel. It should also prove to be a valuable and useful supplement to courses in wet chemical or instrumental analysis.