`Last updated: Sunday 5th October 2008, 10:13 PT, AHD`

Excel Hint Sheet for CSCI100 and BUSI237 Students

```

Read Me

This hint sheet is a work in progress...
A selection of useful features is described.

The hint sheet was tested using Microsoft Excel 97,
but most features work the same way in all versions of Excel.
Where there are differences in Excel versions,
these will eventually be described here.

<- go back

Screen Splitter

The screen splitter splits the Excel screen into two parts
(either horizontally, vertically, or both)
allowing you to view different parts of the same spreadsheet
at the same time. The screen splitter bar is located
in the right hand corners of the spreadsheet. Click and drag.

```

`An unsplit screen`

`A split screen`

<- go back

```

Entering formulas

The point-and-click method is the prefered method for entering a formula to a cell.

1. Select the cell where you want the formula's results to appear.

2. Type the = sign.

3. Point to, and click on the cell
whose address you want to appear first in the formula.

4. Type a math operator after the cell address.

5. Repeat 3. and 4. as required.

6. When finished, press the Enter key to accept the formula
and see the results.

<- go back

Using Absolute and Relative References in a Formula- F4 key

Relative and absolute cell addresses
are used in Excel formulas to facilitate copying.

You should aim to write your formula only one time,
then copy it to all the cells it should be applied to.

You have to understand how relative and absolute addresses work.

A dollar sign in an address ( e.g. B\$10 ) indicates that the address is absolute,
i.e. it will not change when copying.

Cells placed at an absolute address are often input values.

Absolute input values (cells) are used in formulas as 'constants'
- i.e. values which do not change during the processing of a formula.

Results of the formula are the calculated output,
which is displayed beneath the input area.

Absolute and references are indicated in formulas by dollar signs or the lack of them,
and they indicate what happens when you copy or move a formula to a range.
You typically want some parts of the formula to stay fixed (absolute)
and others to change relative to the cell position.
This is a crucial concept for efficiency in spreadsheet operations,
so you should take some time to understand it thoroughly.  Here are two keys:

1. The dollar signs are relevant only for the purpose of copying or moving;
they have no inherent effect on the formula.
For example, the formulas =5*B3 and =5*\$B\$3 in cell C3,
produce exactly the same result.
Their difference is relevant only if cell C3 is copied or moved to some range.

2. There is never any need to type the dollar signs.
This can be done with the F4 key.
To make a cell reference absolute or mixed absolute/relative using the F4 key:
Enter a cell reference such as B3 in a formula.  Then press the F4 key.
In fact, pressing the F4 key repeatedly cycles through the possibilities:
B3 (neither row nor column fixed), then \$B\$3 (both column B and row 3 fixed),
then B\$3 (only row 3 fixed), then \$B3 (only column B fixed), and back again to B3.

<- go back

AutoFilter

Click on a cell in a list, then select:

Data -> Filter -> AutoFilter

In the drop-down box for that column, select a field to filter on.
```

```<- go back

Custom AutoFilter

Make sure you can see the entire list with AutoFilter turned on.
Click on the down-arrow at the top of the list and select (Custom...)
```

```
Select a field and click on the OK button.

<- go back

Conditional Formatting
```
```Select an entire column (click on the column's letter).
Menu: Format -> Conditional Formatting ->
```

```               -> Format -> Pattern
```

```
```

```
<- go back

Pivot Tables

Make sure your lists have no conditional formatting
and the AutoFilter function is turned off.

Select the lists of data to be used in the pivot table:

```

```

Menu: Data -> PivotTable and PivotChart Report -> Next -> Next -> Finish

Drag and drop fields from the combined toolbar/field list to
the top, left and middle areas of the skeletal pivot table structure diagram.
You won't see any data in the pivot table unless you drag a field
to the "Drop Data Items Here" area.

<- go back

Format Cells

Click on the menu: Format -> Cells

Format -> Cells allows you to format the data in the cell as,
for example, a percentage, or as currency, a date, etc.
The same feature allows you to select a font, a border
and background colour (pattern) for the cell.

```

`The Format Cells window`

<- go back

```

<- go back

Topic

under development

<- go back

Topic

under development

<- go back

```