Excel Basics

Here is a brief tutorial on some of the basic functions for Microsoft Excel. The instructions are based on Excel for Windows 98/NT/2000, but many of the functions are the same for other versions, and on a Macintosh. Follow the tutorial step-by-step, with Excel running in another window so you can switch back and forth as you try out these instructions on screen.

1. Starting Excel
2. Worksheet & Cell Layout
3. Entering Data
4. Entering A Formula
5. Using Functions
6. Fill Down / Across & Cell References
7. Copy / Paste / Paste Special
8. Formatting Cells
9. Graphing
10. Trend Lines / Regression
11. Miscellaneous Extras
12. Page Setup & Print
Here's a key to symbols and formatting used below:
• Words in Bold are used to define or highlight terms
• Characters in Red indicate items to be typed or clicked on
• After typing, you should hit Enter, indicated by the sign 
• Characters in Blue indicate an output on the screen
• Vertical lines between commands indicate a series of menu choices, so File | Open means click on the File menu, then on Open in the dialog box

Starting Excel

To Open Excel from windows:

• Double-click on the icon labeled Microsoft Excel
To use menus:
• Click on the desired menu item
• Click on selection in pull down menu

Worksheet & Cell Layout

When Excel is first opened:

• Across the top of the screen you will see File Edit View, etc. this is the menu
• On the next line is the tool bar with icons for commonly used tools that can be used with a single click
• The next line is the edit line, which indicates where we are in the spreadsheet and what is in each cell
Below all this is the worksheet. The worksheet is composed of cells. Each cell is designated with a letter and a number. The letters indicate which column the cell is, and the numbers indicate which row. A small portion of the worksheet is shown below with the cell name in the cell itself.
Moving from cell to cell can be done in two ways
• Use the arrow keys to move, cell by cell, left, right up or down.
• Use the mouse and click inside the cell of choice.
Usually, there are several worksheets within a workbook. Near the bottom of the window you will see several tabs labeled "Sheet 1" "Sheet 2" etc. Clicking on a tab will take you to the worksheet.

Entering Data

To enter data into a worksheet, a cell must first be selected. A selected cell is outlined in black (or possibly another color).

In the example above, the cell A1 is selected and ready for data entry, and typed data can be entered into cell A1 only.

Try entering your name in cell A1:

• If not already highlighted, click on A1
• Type your name, e.g., : Jane Doe


Notice that your name also appears on the edit line. This is where data is edited if changes need to made. Remember, for information to be entered, you must follow the data with enter (), or an arrow key, or by clicking the mouse in another cell. Now try editing your name:

• Select cell A1
• click the mouse in the edit line (above the worksheet and below the menus), and edit the information you want to change. Use the Delete or Backspace keys to delete items.


Numbers are entered in the same manner. A number will become a label (a non-integer text entry) if used in combination with letters. Try entering a number into cell B1:

• Click on B1
• Type a number, e.g., : 3

Entering A Formula

In order to enter a formula, you must first enter = ( + and - may also be used).

Let's try to add 2 + 2 and have Excel give us the answer of 4.

• In cell B3 type: 2 + 2 and notice the results
• Now go to cell C3 and type: = 2 + 2  and you should see the number 4.
A formula can also reference other cells:
• Go to cell C4 and type: = C3 + 5  and you should see the number 9 in cell C4 (value of 4+5)
• Go to cell B4 and write a similar formula with reference to cell B3 (e.g., = B3 + 5). You should see the error: #VALUE!
This means that cell B3 contains labels. Therefore, a referenced cell must contain a value.

Using Functions

Excel also has built-in functions such as square root, log, sum, average, etc.

• Click on cell C5
• Click on the Tool fx (function wizard). This should be on the tool bar.
• Highlight the category Math & Trig
• Click on the function SQRT
• Click on OK
You must now enter a value or a cell reference
• Enter the value 45
• Click on OK
• You should see output 6.708204
Let's try with referencing a cell
• Go to cell D1
• Click on the Tool fx
• Highlight the category Math & Trig
• Click on the function LOG10
• Click OK
Now enter a reference cell
• You may have to use your mouse to drag the dialog box out of the way
• Click inside cell C3 (which has a value of 4 from before)
• Click OK
• You should see output 0.60206

Fill Down /Across & Cell References

Be sure you are able to highlight any given cell or group of cells (by clicking and holding the left mouse button and dragging to destination) before proceeding with this section.

Go to a new worksheet by clicking on a tab at the bottom of the window (e.g., Sheet 2)

• Go to cell A1 and type: 1
• Highlight cell A1, and point the mouse to the bottom right corner of the cell - Your cursor should turn into a black cross-hair (+) - then click and hold the left button and drag to cell A12. When cells A1 - A12 are highlighted, release the mouse button.
• You have now filled down the number 1 into cells A1-A12.
You can also fill across
• Point the mouse to the bottom right corner ("handle") of cell A1 to change your cursor into a black cross-hair (+) - then click and hold the left button and drag to cell E1. When cells A1 - E1 are highlighted, release the mouse button.
• You have now filled across the number 1 into cells A1 - E1.
• Delete cells B1 - E1 by highlighting them and hitting the Delete (Del) key on the keyboard.
You can also create a series of numbers:
• Go to cell B1 and type: 1
• Go to cell B2 and type: 2
• Now Highlight both cells B1 and B2, and fill down (as before, using bottom right corner "handle" of cell B2) to cell B12. You should now see a series of numbers from 1 -12 in cells B1-B12.
We can also fill down formulas. Let's go to cell C1 and write a formula to add cells A1 and B1.
• In cell C1 type: = A1 + B1
• Fill down the formula through cell C12
• You should see ascending values from 2-13 in cells C1-C12
Look at the edit line as you click on several of the cells in column C - the formulas should change corresponding to the row that you are in.
If a particular reference needs to remain the same regardless of row, the reference should be proceeded by a $ sign.
• Go to cell D1 and type: = A1 + $B$1
• Fill D1 down through D12
How is it different then column C? Look at several of the cells in column D. Notice that all cells in column D contain reference to cell B1.

Try putting a $ sign before only the B ($B1) or the 1 (B$1) and examine what happens after you fill down or fill across.

Copy / Paste / Paste Special

To copy a particular cell or group of cells, highlight the cell or cells and choose Edit | Copy. Move to the destination cell and choose Edit | Paste.

Let's copy cell A1 to cell E1:

• Click in cell A1
• Click on Edit | Copy
• Click in cell E1
• Click on Edit | Paste
The value from A1 should now appear in E1.

When copying formulas, the cell references in the formula change depending on the column and row to which you are pasting.

First, to copy a formula and have the cell references change:

• Click in cell C1
• Click on Edit | Copy
• Click in cell F1
• Click on Edit | Paste
Second, to change a referenced cell formula into an actual value when copying:
• Click in cell C1
• Click on Edit | Copy
• Click in cell G1
• Click on Edit
• Choose Edit | Paste Special... and click on Values then click OK
• The pasted cell is no longer a formula.

Compare Cell F1 and Cell G1 - look at the formulas on the edit line. This distinction is important when you want to move or copy results to another part of your spreadsheet, or to paste them into a word processor.

Formatting Cells

Close the current workbook and open a new workbook:
• Click on File | Close
• When asked if you would like to save, Click on No
• Click on File | New
• Highlight "Workbook" and click OK


Let's set up an example:

• In cell A2 enter the label: X Axis Values
• In cell B2 enter the label: Y Axis Values
• Below cell A2 make a list of numbers from 1 to 10 (use fill down)
• Click in cell B3 - we will insert a random number function
• Click on the fx (function wizard) button on the Toolbar
• Highlight the category Math & Trig
• Select the function RAND, click OK and click OK again on the dialog (no inputs are needed for this function)
• You should see a random number between 0 and 1, say 0.002381 or 0.634725
• Copy the formula down so you have 10 random numbers (use the mouse to drag and fill down as before)


We now want to format the cells in several different ways, to look something like the example above. All the format functions can be done at one time by clicking between the different dialog boxes under the Format menu (Cells, Row, Column, etc.), or from some of the shortcut buttons on the Toolbar.

• We need to adjust the width of the column so all the letters in our cell can be seen. Go to the column designations and point the mouse between the columns A and B ( your cursor should change to )
• Click and hold the left mouse button and drag to the right ( you should see the column width growing as you move the mouse). Adjust the column width until all labels can be seen.
• Repeat this process for column B.

• Highlight the cell or column to be aligned (left, center, or right justified)
• Click on Format
• Click on Cells
• Click on the Alignment tab
• Click on choice (i.e. Horizontal | Center)
• Click OK.

• Select column A by clicking on the "A" button at the top of the column
• Click on Format
• Click on Cells
• Click on the Number tab
• Highlight Number, or Scientific, and select the number of decimal places (say, 2)
• Click OK

• Highlight cells A2 - B12 ( all data in our current worksheet should be highlighted)
• Click on Format
• Click on Cells
• Click on the Border tab
• Click Outline (or select another)
• You may also select the Style (weight) of the line and other choices
• Click OK - A border has been added to the group of cells.

Graphing

To begin this section, you should still have the information from the Formatting Cells section above entered in your spreadsheet.

X-Y plots need at least 2 columns of data. When creating a graph in Excel the columns of data must be ordered with X-axis values in the first column, and then one or more Y-axis columns to the right (e.g.,   X   Y1   Y2   Y3   ...):

• Highlight A2 - B12
• Select Insert | Chart | XY (Scatter)
• Pick the first sub-type (scatter without lines) and click on Next >
• Click Next > again
• Fill in My Test Graph for a chart title, and axis titles if you wish, then click on Next >
• Select As New Sheet, click Finish and a chart will appear on a new sheet (see tabs at bottom of screen).
We want to get rid of the central gray plot area:
• Double click anywhere in the gray area (not on any line or square)
• Under Area click on None
• Click OK
When your chart was opened a Legend automatically appeared (far right hand side of chart). To remove the legend:
• Click on the Legend box
• Hit Delete (on the keyboard)
• The legend disappears
To get the legend back:
• Right-Click near the edge of the chart, select Chart Options | Legend, check Show legend
• Click OK

A line graph connects data points with a line (which can be also done in an X-Y plot). It plots one column or more of data, in sequential order. Line graphs should be used when an actual connection between the points is implied in reality (e.g., monthly temperatures, stock market index).
• To return to your worksheet, Click on the Sheet1 tab at the bottom left hand corner (the chart should be on the Chart1 tab).
• Highlight A2 - B12 if not already done
• Select Insert | Chart | Line
• Select the default format (should be Line with Markers) and click on Next >
• Click Next > and complete the chart options as you go
• Select As New Sheet, click Finish and the chart will appear on a new sheet (see tabs at bottom of screen).
Bar/Column charts can plot the same data (one or more columns), but they are used for categories or totals that come in discrete amounts (e.g., monthly rainfall totals, number of students with grades of A, B, C, etc.).
• To return to your worksheet, Click on the Sheet1 tab at the bottom left hand corner
• Highlight A2 - B12 if not already done
• Select Insert | Chart | Column
• Select the default format (should be top left option) and click on Next >
• Select the Series tab, highlight the X series and click Remove
• Click Next > and complete the chart titles
• Click Next > again, but this time select As object in and Finish
• The chart will be placed as a small area in your spreadsheet, so you can see the chart and the numbers together
You can edit any part of a chart by double-clicking on the particular feature you wish to change.

Notice another important feature. If you change the values on the worksheet, the graphs will automatically reflect the changes.
For example,

• hit the F9 button
This recalculates all formulas, which in this case also regenerates new random numbers in column B. Look at how the values change on the bar chart each time you hit F9.

Trend Lines / Regression

We can fit a trend line (or various kinds of curves) to a scatter plot of data. This is not the same as connecting the points in the graph. Use the spreadsheet and scatterplot chart from the graphing section.

• Click on the tab for Chart1
• Right-click on one of the data points, and select Add Trendline...
• Highlight Linear
• Click on the Options tab
• Click on the two checkboxes for displaying the Equation and the R-squared Value on the chart
• Click OK
You will see a line and an equation in a text box. You can move the text by dragging it (to an uncluttered part of your chart).
• The equation describes the line in the classic form y = bx + a where x and y are the values on those axes, a is where the line crosses the y-axis and b is the slope of the line.
• The R-squared value (variance explained) indicates how good the relationship is, and it ranges between 0.0 (no relationship, looks like a shotgun blast) and 1.0 (perfect relationship, all the points along the straight line).

Miscellaneous (but important) Extras

Here are some other common things you might do. We'll make a few changes to the two columns of data from earlier sections.

• Highlight the data by clicking in the top left cell and dragging the mouse to the bottom right cell of the two columns
• Click on the edge of the highlight box (not the handle in the bottom right corner) and drag the box to a new location
• All cell contents will stay the same as before, including any references if a cell contained a formula. (The random numbers will change however, as they are recalculated each time a cell move or copy is performed.)
• Now, move the block of cells so that they start at the top left, in cell A1

• Highlight the two columns of data
• Do Edit | Copy and then an Edit | Paste Special... | Values (as you did earlier), pasting the values exactly over the same cells they came from so you overwrite the formulas
• Click where a formula used to be and see that it is now just a number value
• Highlight the two columns of data again, including the title/label at the top of each
• Go to the menu and select Data | Sort...
• This will open a dialog box that will ask what column to sort. In the top box, highlight the second column (Y-axis values)
• Next to this, select the Ascending checkbutton, and click OK.
Now that we have a list sorted by the second column, let's calculate an average of the top 5 rows of numbers. We'll enter a function slightly differently to before:

• Click on a cell where you want the average to go (choose the bottom of the B-column values, B13, which skips a row for readability)
• Type: =AVERAGE(
• Do not hit the second parenthesis or Enter yet
• Highlight the data cells in the B column using the mouse
• You should see something like B2:B11 (but referencing your cells) appear right after the parenthesis
• Type the closing parenthesis and hit Enter: )
• You should have an average value in the cell where you started
• Copy the formula by dragging the handle (cursor changes to +) one cell over to the left into column A
This formula for AVERAGE is useful because we don't have to enter =(B2+B3+B4+B5+B6+B7+B8+B9+B10+B11)/10 and instead, Excel figures out that there are 10 cells. We might have used =SUM(B2:B11)/10 to get the same result more efficiently than adding in each cell. Here is why AVERAGE is more useful still:
• Click on one of the numeric cells (e.g., A9)
• Hit the Delete key
You can see that the average was adjusted automatically to add the remaining 9 values and divide by 9. The two alternate methods above would have divided by 10 and assumed the missing cell was a zero.

Page Setup and Print

The size and orientation of the paper are in the Page Setup.
• Click on File | Page Setup
• Click on the Page tab
• Click on orientation desired (default is Portrait; Landscape should be used if a wide table is produced)
If you want only the borders that you have added to appear:
• Click on the Sheet tab
• Click to check/uncheck printing Gridlines (uncheck for only your cell borders)
If you want to center the data on the page:
• Click on the Margins tab
• Under Center on Page, click the checkboxes for Horizontally and Vertically
You may also change the default Header/Footer by clicking on that tab
• Click OK when done with your selections

You can print worksheets or charts. Whatever window is currently viewed is what you are printing.

If you wish to look at the whole document before printing:

• Click on File | Print Preview
If you want to print just a chart, highlight the chart (if an object in the worksheet) or select a chart tab at the bottom of the window. When your document is complete and to your satisfaction, you may print the document:
• Click on File | Print
• Click OK


You can also cut and paste charts or parts of worksheets into a word processor.