Several courses and your future work will require strong spreadsheet and graphing skills. This guide to Excel has been developed to describe some of the shortcuts, commands, and features that are available in Excel 97 and Excel 2000. Since Excel was designed as a general business application, many of the default settings are inappropriate for scientific use (this is especially true when graphing). Several of the commands described here are available as icon shortcuts. A pop-up description of the icon will appear if you place the cursor over the icon.

More detailed on-line help is available under the "Help" pull down menu. To search for help on a specific subject, select the "Contents and Index" option, enter the subject you need help with, and select the topic. A description of the command or function will be presented, along with any syntax required and an example of its use.

1. Tables and Spreadsheets

1.1. Formulas:

Spreadsheets are a great tool for calculating lengthy formulas or tables, especially when slight changes or repetition are required. Many formulas are available as functions in Excel, and can be inserted using the f_x icon at the top of the screen. When a function is selected, a brief description of the function and its syntax appears in a pop-up window.

Cells can be referenced in formulas by typing the cell name or by clicking on the cell while typing the formula. Absolute references can be established using a "$" before either the row or column, or both (e.g., $B$5 will reference cell B5 no matter where the formula is copied in the spreadsheet).

Be careful when using multiple parentheses, as the formula can change significantly. For some reason, the fundamental order of mathematics (power, parentheses, multiplication/division, addition/subtraction) is not always followed; parentheses may be needed ensure the calculation is done in the correct sequence. You should review your results to ensure they are reasonable!

1.2. Copying and Pasting Cells:

You can copy formats of cells using the yellow paintbrush icon on the toolbar: highlight the area to be copied, hit the paintbrush, and highlight the target areas. Be cautious, as all formats will be changed.

Excel’s default is to copy and paste formulas, keeping the relative reference from the original cell (unless absolute references are used as above). To paste values (not formulas) pull down the "Edit" menu, select the "Paste Special" command, and select the "Values" option in the "Paste" section.

1.3. Viewing Large Spreadsheets:

You can view multiple portions of a large spreadsheet using the "Split" command in the "Window" drop down menu. Click and drag the split to the desired position, and use the individual scrolls to change the cells viewed in each section. Click and drag the split bar to the very edge of the spreadsheet to eliminate the split. Similarly, you can freeze the titles using the "Freeze" command in the "Window" drop down menu. Be sure to have the cell below and to the right of the cells that you want to freeze as titles.

You can also change the magnification of the spreadsheet using the zoom command. To change the magnification, pull down the "View" menu, select the "Zoom…" command, and select the desired magnification.

1.4. Filling a Series:

Excel will automatically fill a series in a column or row when you have started it. To fill a column or row with the same number, type the number in the first cell. Click on the dot in the lower right corner of the active cell and drag it to the end of the series. To fill with an increment series, enter the first two or three numbers of the series in the first cells, highlight the cells, and then drag to the end of the series. You can also fill with days of the week or months of the year. You can also use the "Fill" command in the "Edit" drop down menu.

1.5. Formatting:

Numbers in cells or on the charts can be formatted into dates, times, decimals, scientific notation, percent, or a number of other options. In the spreadsheet, highlight the cells or column or row to format. Go to the "Format" pull down menu, select the "Cells" command, and select the "Number" tab for various options.

To change format from Julian days to month/day/year time, go to the "Format" pull down menu, select the "Cells" command, select the "Number" tab and the "Date" option. Scroll down for the "3/4/97 13:30" (or similar) "type". Note the first year is 1900, and that the decimal portion of the number is converted to time of day. To change format from regular date to Julian, select the "Number" option, and the number of decimal places as appropriate.

1.6. Sorting and Parsing:

Data in columns can be sorted using the "Data" pull down menu and selecting "Sort". Be sure to select the appropriate number of columns, sort order, and select the "Header Row" or "No Header Row" option. A good way to return to the original order is to insert a column and fill it with the initial rank and then sort this column with the others. When you want to return to the original order, re-sort all of the columns using the rank column as the "Sort by" column.

You can parse data (convert data from single column to multiple columns) using the "Text to Columns" command in the "Data" pull down menu. Be sure to have enough columns available in the output range to fit all of the new columns. Parsing is especially useful when importing data from ASCII files.

1.7. Spreadsheet Defaults:

You can change some default settings using the "Options" tab in the "Tools" pull down menu. To change default font or file directory click on the "General" tab. The settings will become the default the next time you open Excel on that computer.

1.8. Cumulative Frequency Distribution Data:

To create a cumulative frequency distribution, first enter the data in a column of the spreadsheet. Count the total number of observations in the column (either manually or by using the "=count()" command. Sort the data in ascending order. Enter the rank in a second column. The cumulative frequency will be the rank divided by the total count (see Table 1). The data can then be charted using the observations and the cumulative count, or percent smaller than, as shown in Figure 1.

Particle Size (mm)	Count	Cumulative Frequency (%)
2	1	10
4	1	20
16	1	30
35	1	40
50	1	50
70	2	70
90	1	80
160	1	90
500	1	100

Table 1. Cumulative Frequency Distribution



Figure 1. Cumulative Frequency Plot

1.9. Linear Regression Function:

Simple regression can be performed on two columns of data. For this function, you must have the "Data Analysis" option in the "Tools" pull down menu. If this is not available, pull down the "Tools" menu, select the "Add Ins…" command, check the "Analysis ToolPak" option, and click on "OK".

Pull down the "Tools" menu and select the "Data Analysis…" command. In the pop-up menu, scroll down to and highlight the "Regression" tool and select "OK". Enter the X data range and the Y data range (or use the cell reference icons). The defaults in the input section are the most common, but you can change these to fit your needs. In the "Output Options" section, enter a range for the output to be stored in (it will overwrite existing data), or select a new worksheet or workbook. Select the "Line Fit Plots" if you want to see the regression line plotted with the original data. Regression lines can also be added to charts (see section 2.6 below).

The output contains a lot of information (!) useful for statistical analysis. The coefficients for the regression line are identified as "Coefficients" in the third output table. The "Intercept" is the Y-intercept value, and the "X-Variable 1" is the coefficient for the x variable.

The R squared value is found in the first table labeled as "R-Square". The standard error of the regression is also found in the first table, and this is labeled as "Standard Error".

2. Charting (Excel’s term for Graphing)

A chart can be created using the "Chart" command in the "Insert" pull down menu. Follow the steps in the pop-up window. Data series can be selected manually in step two by clicking the "Series" tab. Add or remove series as necessary and type the information into the appropriate boxes or use the cell reference icon. Change the titles, legend position, gridlines, and data labels as appropriate in the third pop-up box.

In the fourth pop-up box, two options are available for inserting the chart. Inserting the chart as a new sheet will give the chart its own page, but may affect the scaling of the chart. Inserting the chart as an object into the current (or other) worksheet allows you to adjust the size of the chart.

For most graphing purposes an X-Y plot should be used. This produces accurate scaling and plotting rather than equally spaced points. Since curved-line connections between points implies you know something about the shape of the function, you should use either the scatter plot (no lines connecting points) or the straight-line option. The "Line" plot option does not treat the x-axis as a numerical series, but as a series of labels.

Categorical data and frequency plots should be created using column charts. Use two-dimensional plots for the most accurate and effective portrayal of the data, and three-dimensional plots only when necessary. For frequency plots, eliminate the spaces between columns by double clicking on the series, selecting the "Options" tab, and changing the "Gap Width" setting to zero.

The Excel default for chart formatting is to use a border and colored background for the chart, legend, title, and plot area. The charts look less cluttered without the borders and with a white background (no fill). To change from the default, double click in the area to change, and select "none" for border and area options.

Gridlines should be used sparingly, if at all. To add or remove gridlines, pull down the "Chart" menu, select "Chart Options", and click on the "Gridlines" tab. Change the spacing of the gridlines using the format axes pop-up window and changing the major or minor units in the "Scale" tab (see below).

The default axis scale in Excel always includes more points than necessary. To change this, double click on the axis, select the "Scale" tab, and enter the range of numbers for the scale. Log scales generally should be used when the range of data is more than two orders of magnitude. To change to log scale, double click the axis, go to the "Scale" tab, and check the log scale box. Note that you can’t plot "0" on a log scale; use a small value (e.g., 0.0001) as the minimum value in the scale tab.

In some cases the x-axis appears in the center of the chart (e.g., when both negative and positive values are being plotted). To move the axis to the bottom of the chart, double click on the axis, select the "Patterns" tab, select "None" for major and minor tick marks, and select "Low" for tick mark labels.

The labels can also be rotated so that the axis looks less crowded. Double click the axis, select the "Orientation" tab, and change the angle of the text by entering a number in the box or by dragging the orientation line. Another option to eliminate crowding is to reduce the font size.

To change the font type, size or other formatting, single click on the area to be changed (titles, legend, axes, text box, etc.) and use the font type and size boxes at the top of the screen. Axes should have appropriate number of decimal places (note that this is not necessarily the same as the number of significant figures). Change this by double clicking the axis and going to the "Number" tab.

When charting multiple series, it is important to show the series in different colors, or to use different line styles and symbols (called markers in Excel) when printing in black and white or when the original will be photocopied. To change the line and symbol for a specific series, double click that series, click on the "Patterns" tab in the pop-up window, and change the color, line style, or shape of the symbol for that series. Click on the "Axis" tab to change between the primary and secondary axes for that series. Use the "X error bars" and "Y error bars" to show error bars for a specific series of data.

Regression functions are used to predict values from a specified relationship. Regression functions and lines can be shown on charts in the X-Y plots. To show a regression function, single click on the series, go to the "Chart" drop down menu, select "Add Trendline", and select the appropriate function. To show the equation of the trend line, click the "Options" tab while in the trend line box and click the "Display equation on chart " box. Be careful not to use the regression function to predict values outside the range of data used to create the relationship (i.e., unwarranted extrapolation).

If you are creating multiple charts of the same type, you can keep the same format by copying and pasting the chart, and then change the source data and titles.

Text, arrows, and drawn objects can be added to the chart or a spreadsheet using the "Drawing" toolbar at the bottom of the page. If the toolbar is not at the bottom of the page, go to the "View" pull down menu, move to "Toolbars" and select the "Drawing" option. Alternately, go to the "Tools" pull down menu, select the "Customize" command and select the "Drawing" option.