**Guide to Excel ^{1}**

Authors: K. N. Carter, B. D. Lamp, D. L. McCurdy and J. M. McCormick*

Last Update: August 27, 2011

This web page is designed to give the average student a brief introduction to Excel and provide a quick reference to those students who are more familiar with the program. It supplements, but does not replace, hands-on experience. __Note that this tutorial was written for Excel 2003, but later versions are similar, although where you find functions on the menu bar are different.__ If you encounter trouble, use the online help, or ask a more experienced user.

All cells in a spreadsheet are referred to by their row (number) and column (letter). So for example, cell A1 is the first cell in the upper left-hand corner of the worksheet.

Many times it is easier to enter a formula once and then copy and paste it into other cells. But when we do this, Excel will automatically change the row and column references. If we don’t want the reference to a cell to change, we need to use an*absolute reference*. An absolute reference is denoted with the “$”symbol, and may proceed the column or row designation, or both. For example, if we wanted subtract the value in cell B13 from that in cell A2, but keeping the reference to cell B13 constant, we might enter in cell C3 the formula “=A2-$B$13”. If we then copy cell C3 and paste it into cell C4, cell C4 would contain the formula “=A3-$B$13”. But if we pasted it into cell D3, cell D3 would contain the formula “=B2-$B$13”.

= | Equals (starts all formulas entered in Excel) |

+ | Addition |

- | Subtraction |

/ | Division |

* | Multiplication |

^ | Power |

** Table 1.** Symbols for basic mathematical operations in Excel

Formulas may be entered with any combination of numbers and cell references using the standard symbols shown in Table 1. Note that all formulas begin with the equals sign. So, entering “=A1+B1” in cell C1 would add the contents of the two cells and place that result in C1, while entering “=A1+5” in C1 adds 5 to the value in A1 and places it in C1.

The order of operation is important! Multiplication and division are performed before addition and subtraction. Use parentheses to ensure proper calculation. Convince yourself that the order of operations is important by entering “= (2+3)/4” in one cell and “=2+3/4” in another. These calculations do not give the same result!

Pushing the function button, , or selecting * Insert, Function* from the menu bar, will give access to all of the built-in functions. These functions all require an argument (i. e., the

*arg*in the parentheses), which can be either a list of numbers or cell references. Some of the more useful functions are shown in Table 2, and examples of how they are used are given in Table 3. Note that the argument of the function (

*arg*) can be either a string of numbers or cell references separated by commas, or it can be only two cell references separated by a colon.

*Table 2. * Some useful built-in Excel functions.

!ERROR! A1 -> A2 -> A2 -> Cyclic Reference in Formula | Calculates the average of cells A2, A3, A4 and A5. |

!ERROR! A2 -> A2 -> Cyclic Reference in Formula | Calculates the average of cells A2, A4 and A5. |

!ERROR! A3 -> A2 -> A2 -> Cyclic Reference in Formula | Adds 5 to cell A2. |

!ERROR! A4 -> A2 -> A2 -> Cyclic Reference in Formula | Raises e to the -A2 power (note that writing -A2, instead of -1*A2, will not work). |

!ERROR! A5 -> Formula Error: Expecting ')' | Calculates the value of t at the 95% confidence limit for the data contained in cells A2 to A10, inclusive. Note the use of the COUNT function to determine the degrees of freedom. |

*Table 3.* Examples of some Excel functions as they would appear in a spreadsheet cell.

To select a single cell, simply click (note that unless otherwise noted “click” means to momentarily depress the left mouse button) on it. To select more than one cell in the same row or column, click on the first cell, and while still holding the left mouse button down, drag the mouse to the last cell. The selected cells will be highlighted. An alternate way to do this is click in the first cell, hold the *Shift* key down and then select the last cell containing data. Once the cells are selected; let up the *Shift* key. If you want to select an entire column or row, click on the column or row header. To select multiple columns or row, simply click on the header and drag. This last method of selecting cells is very useful if you have a large amount of data that needs to be formatted.

Non-adjacent cells can be selected by holding the *Ctrl* key while clicking on each cell. Highlighting non-adjacent rows or columns is a combination of the steps for selecting non-adjacent cells and rows/columns. Once the first column (or row) has been highlighted, the second column is selected by pressing and holding the *Ctrl* key while you click with the left mouse button in the first cell. While holding the *Ctrl* key and the left mouse button, drag to select the values in this column. The other way to select the second column is to press and hold the *Ctrl* key while clicking in the first cell, then let up the *Ctrl* key and press and hold the *Shift* key. Click on the last cell in the column that contains data and all the cells in between will be highlighted.

**Formatting and Toolbar Functions**

Many formatting functions are depicted as icons on one of the toolbars along the top of the spreadsheet. If they are not visible, they may be added selecting * Tools, Customize*from the menu bas and then checking the

*Standard*and

*Formatting*options. Some of the more commonly encountered formatting and toolbar functions are given in Table 4. You will note that some of these tools are the same as in word processing programs and that some have shortcut keys or menu bar commands (e. g., to make something bold you can either click the bold icon, use the key sequence

*Ctrl-b*or select

*F*from the menu bar). To format an object or cell that you wish to format and click on the appropriate button, use the shortcut key sequence or access the menu bar. It is often more efficient to select multiple objects or cells to format before performing the actual formatting.

__o__rmat,__C__ells, Font*Table 4. * Common tool bar function buttons.

Clicking on any object and selecting *F ormat* from the menu bar allows you to change the formatting of the object. Right clicking on a selected object and choosing the format option will also bring up the format window. To get the superscript (or a subscript); select the cell you wish to edit. The contents of the cell will appear in the formula bar. Use the mouse to highlight the text to be superscripted (or subscripted), then select

*F*and the

__o__rmat,__C__ells*Font*tab. Click on the superscript (or subscript) box. To put symbols in axis labels or titles, use the same procedure, except change the font to Symbol font. The correspondence between the Greek letters (Symbol font) and the Roman alphabet (Normal font) for selected symbols is given in Table 5. Symbols that do not appear in Table 5 (such as Âº, Ã…, Â¥, etc.) can be added selecting

*from the menu bar, clicking on the appropriate symbol and then clicking the Insert button.*

__I__nsert,__S__ymbolThe number of significant figures displayed can be changed using the increase/decrease decimal place button or the *F ormat, Cells* menu bar option and then selecting the

*Number*tab. The latter method also allows you to change all aspects of how the numbers are displayed. One should note that Excel uses the format “0.00E+00” for scientific notation, not “0.00 x 10+00”. If you enter a number in the latter format, Excel will treat it as text.

*Table 4.* The relationship between symbol and Roman fonts.

To sort data you must first select the cells to sort. Click on one of the sort buttons on the toolbar or select * Data, Sort* from the menu bar. If you have selected one column and there are data in adjacent cells Excel will give a warning message. If you continue, the selected cells will be sorted and the adjacent cells will

__not__be sorted. Do

__not__select this option if your data are (

*x*,

*y*) data pairs, because this will ruin the pairs. If you want to sort data pairs, select the cells containing both values and then sort. When several rows or columns have been selected, using the toolbar buttons causes the left-most column or top-most row to be sorted. You will be prompted for which row/column to sort, if you use the menu bar command.

To graph data in the spreadsheet, first select the data. Be sure that the *x*-values are in the first column/row selected and that the *y*-values are second. Now select * Insert, Chart*from the menu bar. Select “XY (Scatter)” as the graph type and follow the Chart Wizard to select other graph options (click

**here**to see more information on graphing). Select

*As new*when prompted for the chart location.

__s__heetOnce a graph has been created it can be edited by simply clicking on an object, and choosing *F ormat* from the menu bar. Selecting

*on the menu bar allows you to change the fundamental properties of the chart (type, source data, options and location).*

__C__hartA trendline can be added to a graph by selecting * Chart, Add Trendline* from the menu bar. If there is more than one data set on the graph, you can choose which one to add the trendline to in the

*Based on*box. Under the

__s__eries*Type*tab, select

*. Under the*

__L__inear*Options*tab, check

*Display*on chart (displaying

__e__quation

*R*^{2}is optional). Generally we will

__not__force the line to go through a particular value on the y-axis, so leave this box unchecked. Once you hit

*OK*, Excel will perform a linear least squares fit of the selected data. Note that while a trendline will give you the equation of the best-fit line through the data, it is usually insufficient for the data analysis that is often required in chemistry. Please see the

**Regression Package**section for a more powerful means of fitting data.

It is possible to copy a chart from Excel and paste it into another program. Make the chart that you wish to copy be the active chart, and while holding the shift key, click on* Edit* in the menu bar and select

*. Select*

__C__opy Picture*“As shown when printed”*and click

*OK*. You can then paste it into another document. Once in Word, the picture can be reduced in size, and further edited by selecting the picture toolbar (from the menu bar

*or by “right clicking”, clicking with the right mouse button, on the picture and selecting*

__V__iew,__T__oolbars*Show Picture Toolba*).

__r__

In addition to the trendline, Excel also has a more complete regression package, which gives much more information than the simple trendline treatment. This package is part of the * Analysis ToolPak *add-in that can be activated in Excel. Depending on your computer, you may need to install the

*before using the regression tools described here. The installation varies depending on the version of Excel that you are using. Version-specific instructions are below. NOTE: The*

*Analysis ToolPak**option will only appear if the active window is a worksheet; it will*

__D__ata Analysis__not__appear in the menu bar if the active window is a chart.

- If you are using
, select the**Excel 2010 or Excel 2007**tab. If__D__ataappears as an option, the__D__ata Analysisis already installed and the regression package can be accessed by clicking on*Analysis ToolPak*and in the pop-up window selecting__D__ata Analysisand clicking OK. If*Regression*is not an option on the__D__ata Analysistab, follow the instructions__D__ata**for Excel 2010**or**for Excel 2007**to install the.*Analysis Toolpak*

- If you are using
, select the**Excel 2003**menu. If__T__oolsappears as an option, the__D__ata Analysisis already installed. To use the regression package, select*Analysis ToolPak*,__T__oolsfrom the menu bar and in the pop-up window select__D__ata Analysisand click OK. If these statistical tools are not present, follow the instructions*Regression***here**to install the.*Analysis Toolpak*

The most recent version of Excel for the Mac do not have the Analysis Toolpak built in. Microsoft suggests the following alternative, which functions nearly indentically to the Analysis Toolpak. You can use StatPlus:mac LE to perform many of the functions that were previously available in the Analysis ToolPak, such as regressions, histograms, analysis of variance (ANOVA), and t-tests. First, visit the**Mac Users:****AnalystSoft Web site**, and then follow the instructions on the download page. After you have downloaded and installed StatPlus:mac LE, open the workbook that contains the data that you want to analyze.Then open StatPlus:mac LE. The functions are located on the StatPlus:mac LE menus.

*Figure 1.* Pop-up window for configuring the regression routine in Excel.

When the regression package is selected, the window shown in Fig. 1 will appear. Input the * Y range* and

*(if you push the button in the regression window, the window will minimize and you can choose the desired range by clicking and dragging). Because we do not generally want to force a fit to go through zero, leave the check box for*

__X__range*Constant is*unselected. By clicking on the check box next to

__Z__ero*Conf*and entering a new number in the input box, one can select a new confidence limit. For our purposes this is best left at 95%. Leave the “

__i__dence Level*Output Options*” set for a

*New Worksheet*. Press

__P__ly*OK*, and Excel will perform the regression and output the results to a new worksheet, an example of which is shown as Fig. 2.

The new worksheet contains a good deal of statistical information, but for a basic analysis we need be concerned with only a few items. The first is the value of *R*^{2} (*R Square*), which is a measurement of how related the two measured quantities are (*R*^{2} = 1 is a perfect linear relationship). It should be noted that for many of the fits that you will encounter in chemistry *R*^{2} > 0.999 are common, and so the use of this parameter to determine how good the fit is or to compare two fits, is somewhat limited. In this case, the standard deviation about the regression, which is given as the *Standard Error* under the *Regression Statistics* heading is more useful

The values of the intercept and slope (*X Variable 1*) are under the heading of*Coefficients*, while the estimated standard deviation of each parameter is given under the heading *Standard Error*. The *Lower 95%* and *Upper 95%* entries give the confidence interval for a parameter at 95% confidence. If you selected a confidence level other than 95% the last two columns (the second set of columns in Fig. 2 labeled *Lower 95.0%* and*Upper 95.0%*) will contain the confidence interval at the level. You can calculate the uncertainty (at the *xx%* confidence level) by subtracting the entry under *Lower xx%* from the *Upper xx%* and dividing by 2. For the example given in Fig. 2, the uncertainty in the slope is Â±0.5 (More Info).

** Important!** If you are performing a

**propagation of error analysis**on a value calculated from a regression analysis, you do

__not__use the uncertainty! Instead, you must use the standard error (i. e., the standard deviation),

__not__the confidence interval. For example, if we used the slope shown in Fig. 2 in a calculation and we wanted to see how its uncertainty affected the calculated value, we would use Â±0.2 (the standard error in the slope rounded to one significant figure)

__not__Â±0.5!

If an intercept of zero was specified in the regression window, or if an error occurred, the error “#N/A” will appear for some entries.

*Figure 2.* Typical output of the Excel regression package.

Excel’s regression package also has a number of **advanced features** that you may read about, if you wish.

The appearance of hardcopy can be viewed using the print preview button, , on the tool bar or from the menu bar using * File*,

*Print Pre*. In the print preview you can do everything that you can do through the page setup menu bar selection (

__v__iew*) and see immediately its effect on what will be printed. You will need to experiment with combinations of page orientation (portrait or landscape), scaling, margins, etc., until you have the page that matches what you want. In the print preview, just click on the*

__F__ile, Page Set__u__p

Excel has a “fill” feature that is very useful when one wants to construct a set of data where the *x* values are all equally spaced between some starting and ending value. For example, if we wanted a graph of the function *e ^{-x}*, we could create the

*x*values for the graph using the fill function. To fill a series first type the starting value in the first cell of the series and select it by clicking on that cell. Next, select

*from the menu bar. Select whether you want the created series to occupy*

__E__dit, F__i__ll,__S__eries*or*

__R__ows*under the “*

__C__olumns*Series in*” heading. Under the heading “

*Type*“, select

*, which is the most commonly encountered way to fill a data series. Finally, select the*

__L__inear*and the*

__S__tep value*St*and hit

__o__p value*Okay*. Excel will fill in the column or row starting at the value initially by adding the step value to it until the stop value is reached.

Error bars, which show the uncertainty in a given point, may be added to a graph in Excel, by first selecting the data set and then *F ormat, Selected Data Series* from the menu bar. Click on the appropriate tab for

*X error bars*or

*Y error bars*. You will then be able to select among various options for how the error bars appear (both sides, one side, or none) and how large to make them. The

*value option is good when all of the data have approximately the same uncertainty, while*

__F__ixed*assigns the uncertainty as a certain percent of the*

__P__ercentage*y*values. This author has never found the

*option to give any useful depiction of the uncertainty, but it may be useful in certain situations. The*

__S__tandard deviation*Standard*assigns the uncertainty based on the standard error, which seems to be very similar to the fixed value option. The final option of having

__e__rror*error bars is very useful when each point has a different uncertainty. You can always simply enter whatever value you want in the boxes, but by pushing the button you can specify a range of cells in your spreadsheet that contains your uncertainties. For example, if you have a set of volumes in cells B3 through B10 and their estimated uncertainties from a propagation of error analysis in cells C3 through C10, you would only need to enter the cell references for the uncertainties in the boxes, and each point has its own error bar.*

__C__ustom1. Click here to obtain this file in PDF format (suitable for printing).

2. Dodd, J. S., Ed. *The ACS Style Guide: a Manual for Authors and Editors*; The American Chemical Society: Washington , DC, 1986.

3. The *Guide to Preparing Graphs* is at **here**.