Guide to Excel1
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 anabsolute 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, Customizefrom 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 Format, Cells, Font 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.
| Button | Function | Shortcut | Menu Command |
|---|---|---|---|
 | Align left | Format, Cells, Alignment | |
 | Align center | Format, Cells, Alignment | |
 | Align right | Format, Cells, Alignment | |
 | Bold | Ctrl-b | Format, Cells, Font |
 | Italic | Ctrl-i | Format, Cells, Font |
 | Underline | Ctrl-u | Format, Cells, Font |
 | Increase number of decimal palces | Format, Cells, Number | |
 | Decrease number of decimal places | Format, Cells, Number | |
 | Undo last action(s) | Ctrl-z | Edit, Undo |
 | Redo last action(s) | Ctrl-y | Edit, Redo |
 | Sort cells in ascending order | Data, Sort | |
 | Sort cells in descending order | Data, Sort |
Table 4. Common tool bar function buttons.
Clicking on any object and selecting Format 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 Format, Cellsand the 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 Insert, Symbol from the menu bar, clicking on the appropriate symbol and then clicking the Insert button.
The number of significant figures displayed can be changed using the increase/decrease decimal place button or the Format, Cells menu bar option and then selecting theNumber 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, Chartfrom 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 sheet when prompted for the chart location.
Once a graph has been created it can be edited by simply clicking on an object, and choosing Format from the menu bar. Selecting Chart on the menu bar allows you to change the fundamental properties of the chart (type, source data, options and location).
A 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 series box. Under the Type tab, select Linear. Under the Options tab, check Display equation on chart (displaying R2 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 onEdit in the menu bar and select Copy Picture. Select “As shown when printed” and clickOK. 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 barView, Toolbars or by “right clicking”, clicking with the right mouse button, on the picture and selecting Show Picture Toolbar).
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 Analysis ToolPak 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 Data Analysis option will only appear if the active window is a worksheet; it will not appear in the menu bar if the active window is a chart.
- If you are using Excel 2010 or Excel 2007, select the Data tab. If Data Analysisappears as an option, the Analysis ToolPak is already installed and the regression package can be accessed by clicking on Data Analysis and in the pop-up window selecting Regression and clicking OK. If Data Analysis is not an option on the Datatab, follow the instructions for Excel 2010 or for Excel 2007 to install the Analysis Toolpak .
- If you are using Excel 2003, select the Tools menu. If Data Analysis appears as an option, the Analysis ToolPak is already installed. To use the regression package, select Tools, Data Analysis from the menu bar and in the pop-up window selectRegression and click OK. If these statistical tools are not present, follow the instructions here to install the Analysis Toolpak.
- Mac Users: 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 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 X range (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 forConstant is Zero unselected. By clicking on the check box next to Confidence Level 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 “Output Options” set for a New WorksheetPly. Press 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 R2 (R Square), which is a measurement of how related the two measured quantities are (R2 = 1 is a perfect linear relationship). It should be noted that for many of the fits that you will encounter in chemistry R2 > 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 ofCoefficients, 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% andUpper 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 Preview. In the print preview you can do everything that you can do through the page setup menu bar selection (File, Page Setup) 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 Printbutton to print the page as shown. The print button, 
, on the tool bar will print whatever is currently displayed using the current printer settings.
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 Edit, Fill, Series from the menu bar. Select whether you want the created series to occupy Rows or Columnsunder the “Series in” heading. Under the heading “Type“, select Linear, which is the most commonly encountered way to fill a data series. Finally, select the Step value and the Stop value and hit 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 Format, 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 Fixed value option is good when all of the data have approximately the same uncertainty, while Percentage assigns the uncertainty as a certain percent of the y values. This author has never found the Standard deviationoption to give any useful depiction of the uncertainty, but it may be useful in certain situations. The Standard error assigns the uncertainty based on the standard error, which seems to be very similar to the fixed value option. The final option of having Custom 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.
1. 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.