UBC Home Page
UBC Home Page - UBC Home Page UBC Home Page - - -
-
 
Excel 2000 Tutorial
 

Spreadsheet Basics

  • Screen elements
  • Adding and renaming worksheets
  • The standard toolbar - opening, closing, saving, and more.

Modifying A Worksheet

  • Moving through cells
  • Adding worksheets, rows, and columns
  • Resizing rows and columns
  • Selecting cells
  • Changing cell format
  • Moving and copying cells
  • Fill Down, Fill Right
  • Autofill
  • Paste Special
       

Formulas and Functions

  • Formulas
  • Linking worksheets
  • Relative, absolute, and mixed referencing
  • Basic functions

Charts

  • Chart Wizard
  • Resizing a chart
  • Moving a chart

Spreadsheet basics

Screen elements

Excel allows you to create spreadsheets much like paper ledgers that can perform automatic calculations. Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns (designated by letters) and rows (designated by numbers). The letters and numbers of the columns and rows (called labels) are displayed in gray buttons across the top and left side of the worksheet. The intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. For example, in the image below the cell address of the cell resulting from the intersection of column E and row 4 is "E4". Cells can contain either text, numbers, or mathematical formulas.

Adding and Renaming Worksheets

The worksheets in a workbook are accessible by clicking the worksheet tabs on the bottom of the screen. By default, three worksheets are included in each workbook. To add a sheet, select Insert|Worksheet from the menu bar. To rename the worksheet tab, right-click on the tab with the mouse and select Rename from the shortcut menu. Type the new name and press the ENTER key.

The Standard Toolbar

This toolbar is located just below the menu bar at the top of the screen and allows you to quickly access basic Excel commands.

Modifying a Worksheet

Moving Through Cells

Use the mouse to select a cell you want to begin adding data to and use the arrow keys to move through the cells of a worksheet. Examples of movements and corresponding key strokes:

One cell up: up arrow key
One cell down: down arrow key or ENTER
One cell left: left arrow key
One cell right: right arrow key or TAB
Top of the worksheet (cell A1): CTRL+HOME
End of the worksheet (last cell containing data): CTRL+END
End of the row: CTRL+right arrow key
End of the column: CTRL+down arrow key
Any cell: File|Go To menu bar command

Adding Worksheets, Rows, and Columns

Worksheets - Add a worksheet to a workbook by selecting Insert|Worksheet from the menu bar.

Row - To add a row to a worksheet, select Insert|Rows from the menu bar, or highlight the row by clicking on the row label, right-click with the mouse, and choose Insert.

Column - Add a column by selecting Insert|Columns from the menu bar, or highlight the column by click on the column label, right-click with the mouse, and choose Insert.

Resizing Rows and Columns

There are two ways to resize rows and columns.
  • Resize a row by dragging the line below the label of the row you would like to resize. Resize a column in a similar manner by dragging the line to the right of the label corresponding to the column you want to resize.
    - OR -
  • Click the row or column label and select Format|Row|Height or Format|Column|Width from the menu bar to enter a numerical value for the height of the row or width of the column.

Selecting Cells

Before a cell can be modified or formatted, it must first be selected (highlighted). Examples for selecting groups of cells:

One cell: click once in the cell
Entire row: click the row label
Entire column: click the column label
Entire worksheet: click the whole sheet button
Cluster of cells: drag mouse over the cells or hold down the SHIFT key while using the arrow keys

To activate the contents of a cell, double-click on the cell or click once and press F2.

Changing Format

To change the format of cell contents, highlight cells and select Format|Cells from the menu bar or right-click with mouse and choose Format Cells. The dialog box allows you to change:

  • the display of numbers (e.g. number of decimal displayed, date format, etc.)
  • the alignment of text in a cell, including text crontol options such as wrapped text and text spanning multiple columns
  • the font of text in a cell, including font size and style
  • the cell border style to manipulate cell borders for individual cells and for regions of cells
  • the cell shading for changing colors of cells

Moving and Copying Cells

Moving Cells
To cut cell contents that will be moved to another cell, highlight contents, select Edit|Cut from the menu bar or click the Cut button on the standard toolbar.

Copying Cells
To copy the cell contents, highlight contents, select Edit|Copy from the menu bar or click the Copy button on the standard toolbar.

Pasting Cut and Copied Cells
Highlight the cell you want to paste the cut or copied content into and select Edit|Paste from the menu bar or click the Paste button on the standard toolbar.

Drag and Drop
If you are moving the cell contents only a short distance, the drag-and-drop method may be easier. Simply drag the highlighted border of the selected cell to the destination cell with the mouse.

Fill Down, Fill Right

To fill a set of adjacent cells in a column by copying the content of the cell at the top of the set, highlight the set of cells that need filling, then select Edit|Fill|Fill Down or press CTRL+D.

To fill a set of adjacent cells in a row by copying the content of the cell at the far left of the set, highlight the set of cells that need filling, then select Edit|Fill|Fill Right or press CTRL+R.

Autofill

The Autofill feature allows you to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text.

  • Type the beginning number or date of an incrementing series or the text that will be repeated into a cell.
  • Select the handle at the bottom, right corner of the cell with the right mouse button and drag it down as many cells as you want to fill.
  • Release the mouse button.
  • Select the action required (Copy Cells, Fill Series, Fill Formats, Fill Values)
Autofill can also be used to copy functions by selecting Fill Values in the sequence of steps described above.

Paste Special

Instead of copying entire cells, you can copy specified contents from the cells. For example, you can copy the resulting value of a formula without copying the formula itself.

  • Select the cells you want to copy
  • Click Copy
  • Select the upper-left cell of the paste area.
  • Right-click with the mouse and choose Paste Special. The Paste Special Dialog box will open. Click the desired option. For example, to copy the resulting value of a formula without copying the formula itself, click Values.

Formulas and functions

The distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions.

Formulas

Formulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar. An example is given below.

The following image shows a worksheet used to create an array of equally-spaced points within the interval [2,4] using a fixed increment of 0.5. Cell D1 (column D row 1) contains the value of the increment; the cells in column A show the point labels; Cell B2 contains the value of the start point of the interval; Cell B3 contains the value of the first point in the interval; and so forth. To obtain the value in cell B3, the formula "=B2+D1" is used, as shown in the formula box. This means that the value "2.5" shown in cell B3 is calculated by adding the value stored in cell D1 (the increment) to the value in cell B2 (point x0).

Relative, Absolute, and Mixed Referencing

Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if the simple addition formula used in cell B3 "=B2+D1" in the image above is copied to cell B4, which is a cell in the same column but one row down, the formula would change to "=B3+D2" to reflect the new row.

To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, if the formula in cell B3 read "=B2+$D$1", when copied to cell B4, the formula would change to "=B3+$D$1", allowing the value of cell B4 to be the sum of cells B3 and D1. Both the column and row of cell D1 are absolute and will not change when copied.

Mixed referencing can also be used where only the row OR column is fixed. For example, the formula in cell B3 could read "=B2+D$1", so that only the row of cell D1 is fixed. Then, when copied to cell B4, only the row in column B would change and the new formula in cell B4 would read "=B3+D$1". In this case, the calculated value in cell B4 would still be "3", as before, because the formula was copied in the same column. If, instead, for some reason the formula were copied to cell C3, one column to the right and one row down, the new formula in C3 would read "=C3+E$1" because only the row of the second term in the addition is absolute and would not change when copied.

Basic Functions

Functions can be a more efficient way of performing mathematical operations than formulas. For example, if you wanted to add the values of cell B2 through B6 in theexample above, you would type the formula "=B2+B3+B4+B5+B6". A shorter way would be to use the SUM function and simply type "=SUM(B2:B6)". A few examples of functions are given in the list below:

Function Example Description
SUM =SUM(A1:A100) finds the sum of cells A1 through A100
AVERAGE =AVERAGE(B1:B10) finds the average value of cells B1 through B10
MAX =MAX(C1:C100) returns the highest number from cells C1 through C100
MIN =MIN(D1:D100) returns the lowest number from cell D1 through D100
SQRT =SQRT(D10) finds the square root of the value in cell D10
All functions available in Excel can be found by using the Function Wizard: Activate the cell where the function will be placed and click the Function Wizard button on the standard toolbar, or select Insert|Function from the menu bar.

Charts

Charts allow you to present data entered into the worksheet in a visual format using a variety of graph types. You can create a chart on its own sheet or as an embedded object on a worksheet. To create a chart, you must first enter the data for the chart on the worksheet. Then select that data and use the Chart Wizard to step through the process of choosing the chart type and various chart options.

Chart Wizard

The Chart Wizard brings you through the process of creating a chart by displaying a series of dialog boxes. The example below shows how to create a chart to plot the values of the function f(x)=x^2 over the interval [2,4].

  • Enter the data into the worksheet, as shown in the image below, and highlight all the cells that will be included in the chart. The values in cell B2 through B6 will be used as points along the x axis, the values in cell F2 through F6 will be displayed along the y axis.
  • Click the Chart Wizard icon on the toolbar to view the first Chart Wizard dialog box.
  • Choose the Chart Type and the Chart subtype if necessary. In this example, to plot the values in cell F2 through F6 as a function of the values in cell B2 through B6, choose XY (Scatter) as a Chart Type and Scatter with data points connected by a smoothed Lines as a Chart sub-type. Click Next.
  • Select the data range (if different from the area highlighted in step 1) and click Next.
  • Enter the name of chart and titles for the (X) and (Y) axes, if desired. Other options for the axes, gridlines, legend, data labels can be changed by clicking on the tabs. Click Next.
  • Click As new sheet if the chart should be placed on a new, blank worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu.
  • Click Finish to creat the chart.

Resizing the Chart

To resize the chart, click on its border and drag any of the nine black handle to change the size. handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart.

Moving the Chart

Select the border of the chart, hold down the left mouse button, and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag the element to move it.