Guide to Excel1
Authors: K. N. Carter, B. D. Lamp, D. L. McCurdy and J. M. McCormick*
Last Update: October 3, 2008
Introduction
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 operate 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.
Cell References
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”.
Basic Mathematical Operations| = | Equals (starts all formulas entered in Excel®) |
| + | Addition |
| - | Subtraction |
| / | Division |
| * | Multiplication |
| ^ | Power |
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!
Built in Functions 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.
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 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 places | 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 |
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, Cells and 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 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.
Sorting
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.
Graphing 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).2,3 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 on Edit in the menu bar and select Copy Picture. Select “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 View, Toolbars or by “right clicking”, clicking with the right mouse button, on the picture and selecting Show Picture Toolbar).Regression Package In addition to the trendline, Excel also has a more complete regression package, which gives much more information than the simple trendline treatment. Select Tools, Data Analysis from the menu bar and in the pop-up window select Regression and click OK. If these statistical tools are not present, you can install them by selecting Tools, Add-Ins from the menu bar and then selecting the check boxes for Analysis ToolPak and Analysis ToolPak-VBA. 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 2007, follow the instructions here to install the Analysis Toolpak.
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 for Constant 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 Worksheet Ply. 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 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. 1 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 by clicking here.
Printing 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 Print button to print the page as shown. The print
button,
,
on the tool bar will print whatever is currently displayed using the current
printer settings.
Miscellaneous Features
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
Columns under 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 deviation option 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.
References