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

Excel Hint Sheet for CSCI100 and BUSI237 Students

ReadMe | Screen Splitter | Formula Entry | Excel References | AutoFilter | Custom AutoFilter | Conditional Formatting | Pivot Tables | Format Cells | Home












































































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
Excel screen



A split screen
Excel 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.


Excel AutoFilter


Excel AutoFilter

<- 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...)

Excel Custom AutoFilter

Excel Custom toFilter


Excel Custom toFilter


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 ->


Excel Conditional Formatting

               -> Format -> Pattern


Excel Conditional Formatting



Excel Conditional Formatting


<- 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:


Creating a 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
Excel Format Cells



<- go back














<- go back
Topic under development

<- go back
Topic under development

<- go back

Valid XHTML 1.0!