1

Spreadsheet Fundamentals

  • Standard and Formatting toolbars and pull-down menus
  • Enter labels, numbers and formulas

Edit and delete cell entries

Format cells

Left justify, right justify, center and center across columns

Change column widths and row heights

Insert/delete and hide/unhide rows and columns

Insert, delete and change the names of worksheets in a workbook

Save your workbook to disk

  • How to move and copy information on a spreadsheet

How to name ranges

  • Protection of Cells, Worksheets, and Workbooks

Understand the difference between relative and absolute cell addresses

Understand the following functions:

=IF(logical_test,value_if_true,value_if_false)

=SUM(number1,number2,...) or =SUM(range1, range2, ...)

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

=MIN(a,b,c,...,z)

=MAX(a,b,c,…,z)

=SUMIF(range,criteria,sum_range)

=SUMPRODUCT(array1,array2,array3, ...)

=PRODUCT(number1,number2, ...)

=AVERAGE(number1,number2, ...)

=MMULT(array1,array2)

=SUMXMY2(array_x,array_y)

  • Printing your work
  • Macros/VBA

Enter Labels, Numbers, and Formulas

It is easy to enter data (numbers) in Excel. Just move to the cell in which you want to enter the data, type the data and press Enter. The selection (active cell) moves down, ready for the next entry.

Microsoft Excel will allow two types of data entry:

  • Constants - data typed directly into the cell; it can be numeric (date, time, currency, etc.) or text.
  • Formulas - a sequence of values, cell references, names of functions or operators (+, -, *, etc.) that produces a new value from existing values. Formulas always begin with an equal sign (=).

If you see "####", this means that the number is too long to be displayed in the cell. Just widen the column to accommodate the width of the number.

Text can be characters or any combination of numbers and characters. Any set of characters entered a cell that is not interpreted as a number, formula, date, time, logical value or error, is interpreted as text.

Text wrapping can also be a useful tool when using long strings in a cell. This allows you to display multiple lines in a cell. Select the Cells command from the Format menu, click the Alignment tab and check the Wrap text box.

Logical values are the result of a logical function or equation. Valid logical values are TRUE and FALSE.

Error values are displayed when a formula cannot be properly calculated. Error values always begin with an number sign (#). Below is a list of the errors in Excel.

This error value / Means that a formula
#DIV/0! / Is trying to divide by zero.
#N/A / Refers to a value that is not available.
#NAME? / Uses a name that Excel does not recognize
#NULL! / Specifies an invalid intersection of two areas.
#NUM! / Uses a number incorrectly.
#REF / Refers to a cell that is not valid.
#VALUE / Uses an incorrect argument or operand.

If a formula includes a reference to a cell that contains an error value, that formula also produces an error value.

When entering numbers as constants, you can use 0-9 and any of the following operators:

+, -, ( ), /, $, %, E, e, .,and ,.

Also, when entering numbers in formulas, DO NOT use ( ) to represent negative, comma to separate thousands or $ to represent currency.

Formulas can take on many forms using references, functions, text and numbers to perform a variety of tasks. Simply think of the formulas as one side of the equation with the result shown in the cell. We can enter a formula into Excel by using the formula bar or editing within a cell.

The basic arithmetic operators are: +, -, /, *, %, and ^.

Comparison operators (which compares two values and produces a logical TRUE or FALSE) are: =, >, <, >=, <=, and > (not equal to). For example, the formula =A1<25 produces TRUE if A1 is less than 25; otherwise it produces a value of FALSE.

Also, if you want to combine expressions or change the order of evaluations, use parentheses in your formula. Excel first calculates the expressions in parentheses and then calculates the formula.

Edit and Delete Cell Entries

To edit a cell entry, click onto the cell and press the F2 function key. Then the current contents of the cell will be displayed in the formula bar. Now you can edit the cell.

To delete a cell entry, click on the cell and press the delete key.

Formatting Cells

Formatting your data will make the worksheet easier to read and more visually appealing. You can format your worksheet cells before or after you have entered the data. To format cells in Excel, you can use either the Formatting toolbar or the Format menu.

Some examples of formatting would be:

  • Change font or font size
  • Change width of column
  • Change height of rows
  • Change numeric format
  • Alignment (left, right, center)

To change font, simply click on the Font window in the formatting toolbar and select the desired font. Alternatively, you can select the Cells command from the Format menu, selecting the Font tab and choose the font.

To change column width, we can do one of the following:

  • Drag the right column heading border to the right (to increase width) or left (to decrease width.
  • Select the Column command from the Format menu and choose one of the following:
  1. Set a numeric column width (allows you to set a column width)
  2. AutoFit Selection (adjust width to fit longest cell value)
  3. Set to Standard (allows you to set a standard width)
  4. Hide/Unhide columns

To adjust the row height, you can do one of the following:

  • Drag the bottom of the row border of the row heading to the selected height
  • Select the Row command from the Format menu and choose:
  1. Set a numeric row height
  2. AutoFit (will adjust the row to fit the largest font)
  3. Hide/Unhide rows

Hiding rows or columns does not delete them from your worksheet; it just puts them out of sight. You can hide a row or column using the steps mentioned above. To unhide rows or columns, select a range that includes the hidden rows or columns. Then choose the Unhide option after selecting the Row or Column command from the Format menu.

Inserting and deleting rows and columns is also easy to do in Excel.

To insert a row (column), select the number of rows (columns) that you want to insert, and then choose Rows (Columns) command from the Insert menu and the rows (columns) will be inserted above (to the left of) your selection.

Insert, Delete, and Change Names of Worksheets

Recall that a workbook is the place in which all of your data is stored. So the relationship between the worksheet and the workbook is similar to pages in a notebook.

By default, the workbook opens with 16 worksheets named Sheet1 - Sheet 16. The sheet name appears at the bottom of the workbook window.

We can do any of the following with worksheets:

  • Insert new sheets
  • Delete sheets
  • Rename sheets
  • Move or copy sheets within a workbook or to another workbook
  • Hide/Unhide sheets

NOTE: If you don’t see the sheet tabs in your workbook, choose the Options command from the Tools menu and select the View tab. Click on the Show Tabs box.

To insert a new sheet, select a sheet by clicking on the sheet tab. Then, from the Insert menu, select the Worksheet command. A new sheet is inserted and becomes the active sheet.

To delete a sheet, select the sheet by clicking on the sheet tab. Then from the Edit menu, select the Delete Sheet command.

You can also change the name of any sheet to a name of up to 31 characters, including spaces. To do so, double-click on the tab of the sheet you want to rename. In Excel for Office 97, the sheet name is highlighted and you just have to type in the new name. In Excel for Office 95, a Rename Sheet box appears; just type in the new name and press Enter or click on the OK box. Alternatively, you can choose the Sheet command from the Format menu and then select the Rename options.

Moving and Copying Information

You can change the location of cells on a worksheet by copying or moving the cells to a different part of the worksheet. This can be done using the Cut, Copy, and Paste commands.

When you copy a cell, the cell's contents are duplicated into a new location. When you move a cell, the contents are posted into a new location after being moved from the original location. You can insert the cells that you are copying between existing cells or overwrite existing cell contents.

To Copy:

Select the cells that you want to copy. Then choose the Copy command from the Edit menu (or click on the Copy toolbar button). The copy area is marked with a moving border.

Select the destination cell(s) -- the upper left hand corner of the paste area.

Then choose the Paste command from the Edit menu (or click the Paste toolbar button).

To Move:

Select the cells that you want to move. Choose the Cut command from the Edit menu (or click the Cut toolbar button). The cut area is marked with a moving border.

Select the destination cell(s).

Then choose the Paste command from the Edit menu (or click the Paste toolbar button).

Naming Ranges

Excel has the capability of naming ranges of cells. Naming the cell or group of cells, values, or formula has the primary advantage of making formulas easier to read. For example, the formula "= Sales - Expenses" is easier to read than the formula "= B6 - C6".

Other advantages are:

  • Cut and paste formulas quickly
  • If the sheet structure changes, then the formulas using that name are updated automatically
  • Each workbook can share a single set of names

To define names, use the Name command on the Insert menu and select Define (other options are Paste, Create, Apply, or Label). You can also use the Name box in the Formula Bar to define a name.

Rules for Names

  • Valid characters: The first character must be a letter or an underscore, other characters can be letters, numbers, periods, and underscores. The name cannot look like a reference (A$1).
  • Separators: Spaces are not allowed.
  • Length: A name can be up to 255 characters long.
  • Capitalization: You can use upper- or lower-case letters.

Protection of Cells, Worksheets, and Workbooks

A worksheet can be protected so that no information on that worksheet can be altered. The steps to protect a worksheet are as follows:

ToolsProtectionProtect Sheet, Protect Workbook, or Protect & Share Workbook  Password

But note that this method locks ALL of the cells in the worksheet. Often times, you may want to prevent changes to some cells and allow changes to others; for example, a form that you want filled in by the user… but you don’t want the labels or any other information changed.

So, before protecting the worksheet, you must specify that some cells (the cells that you don’t mind being changed) remain unlocked.

This can be done as follows:

FormatCellsProtection Tabclear Locked check boxOK

This must be done before you protect the worksheet; you CANNOT unlock cells on a protected sheet.

Once you have specified the cells to remain unlocked, you can now activate protection of the worksheet.

References: Relative vs. Absolute

Excel uses a technique called relative cell address when it copies a formula from one cell to another. When Excel copies or moves a formula, it may adjusts the column letter(s) for each column. Formulas copied to different rows will also have the row addresses adjusted properly.

Using the relative cell address in copied formulas is appropriate most of the time and that is the default treatment. However, there are some instances when you do not want the cell address to be adjusted automatically. For example, when calculating percentages of a total, you want to divide each item by the same amount.

With references, we can:

  • Identify cells or groups of cells
  • Tell Excel which cells to look in to find values to be used in a formula

There are three types of cell references used in Excel:

  • Relative
  • Absolute
  • Mixed

Because references are based on row and column headings in a worksheet, the dollar sign ($) preceding the row or column reference indicates which absolute cell reference is desired. Let C denotes a column letter, and R denotes a row number.

CRRelative Reference. Both the row and column are adjusted when the expression is copied. This reference tells Excel how to find another cell, starting from the cell containing the formula.

$C$RAbsolute Reference. Neither row nor column is adjusted when the expression is copied. Gives the exact location of the cell.

$CRMixed Reference. The column is not adjusted when the expression is copied, BUT the row is adjusted.

C$RMixed Reference. When the expression is copied, the row is NOT adjusted BUT the column is adjusted.

Besides typing in the references, you can select the reference in the formula bar and press the F4 function key to toggle through the reference types (R, A, M_r, M_c)

Reference Operators

Additionally, there are also three types of reference operators;

  • Range (colon): Produces one reference to all the cells between and including the two references (e.g.: B2:D2).
  • Union (comma) Produces one reference that includes the two references (e.g.: B4, D4).
  • Intersection (space) Produces one reference to cells common to the two references.

Referring to Other Sheets

You can also use references to cells located on other sheets in the same workbook. You can even refer to a range of cells three-dimensionally through a range of sheets in a workbook. Following is the syntax for referring to another sheet in a workbook:

sheetname!cellref

where:(1) the name of the worksheet is always an absolute reference;

(2) an exclamation point separates the sheet reference from the cell reference;

(3) the cell reference can be any of the three types.

Note that if the sheetname includes a space, you must enclose the entire sheet reference in quotation marks (i.e., 'sheet name').

3-D References

A 3-D reference is a range that spans two or more sheets in a workbook. The following illustration shows a formula using a reference to a range of cells on a range of sheets in a workbook.

=SUM(Sheet2:Sheet6!$A$2:$C$5)

where the sheet range is specified with a colon between the names of the beginning and ending sheets. This formula adds the values contained in the range $A$2:$C$5 in each of the sheets between and including Sheet2 and Sheet6.

To enter a reference to a range of worksheets into a formula, you can type the reference directly, or you can select the worksheet tabs that indicate the beginning and ending sheets from which you want to include references.

For example, to enter the reference shown above, we must do the following:

  1. Begin entering our formula on a worksheet
  2. Click the Sheet2 tab to begin the 3-D reference
  3. Press SHIFT and click the Sheet6 tab
  4. Select the range of cells we want

Effects of Moving and Copying on References

Moving

When you move cells, the values or references they contain never change. However, moving does affect formulas that refer to the moved cells with the following effects:

  • Excel automatically adjusts all references to cells that are moved so that the references designate the same cells in their new location.
  • Formulas that previously referenced the area to which you move the cells produce the #REF! error value because you effectively delete the existing cells and replace them with the moved cells.

Copying

Copying cells has a different effect on relative references and absolute references.

  • Relative References When you copy cells, Excel automatically adjusts relative references and the relative parts of mixed references in the area where you paste the copied cells.
  • Absolute and Mixed References When you copy a formula and use the same cell references from the original formula in the duplicate, use absolute or mixed references in your formula.

Functions for Decision Making

There are many built-in functions available in Excel that aid in the development of complicated worksheets. These functions accomplish various tasks that might ordinarily require a sophisticated calculator. The Function Wizard includes descriptions of all worksheet functions. Using the Function Wizard, you can select a function, assemble the arguments correctly and insert the function into your formula.

We can group the functions into five broad categories which include --among others--

  1. Statistical functions
  2. Mathematical functions
  3. Financial functions
  4. Date and time functions
  5. Logical functions

Logical Functions: Conditional Values