Excel Tutorial Week 1 Absolute and relative cell

Excel: Tutorial Week 1 • • Absolute and relative cell references Within worksheet and cross worksheet references Data validation Using pre-created functions Official resource for MS-Office products: https: //support. office. com

Activities In Tutorial • TA demos: – Used for more complex features (typically multiple steps are required). – The tutorial instructor will show on the projector/instructor computer each step for running the feature in Excel. – Unless otherwise specified the tutorial material will take the form of a TA demonstrating the use of features in Excel. – Slides titled “Lecture Review” are covered for the second time and dealing with less complex material. • For this reason they will only be covered briefly in tutorial. • Student exercises: – Used instead of TA demos for simpler features. – You will have already been given a summary of how to invoke the feature and the purpose of the exercise is to give you a chance to try it out and get help if needed. MS-Excel tutorial notes by James Tam

Review: Cell References • Absolute: – Uses the ‘$’ sign – Examples: =$C$3, =sum($A$3: $D$15) – Employ when the same absolute cell or range of cells will always be referenced regardless of where the formula is copy-pasted or cut-andpasted to e. g. when referring to a lookup table (e. g. GST rate) • Relative: – The default (no dollar sign) – Examples: =C 3, =sum(A 3: D 15) – Employ when a different cell or range cells will be referenced (relative to the distance that the formula is copied or moved) e. g. when referring to a student’s assignment grade, the retail price of a product MS-Excel tutorial notes by James Tam

Cell References: Student Exercise #1 • Starting spreadsheet: cell reference exercise #1 • Specify the formula in Cells C 2 and D 2 such that the formula can be correctly copy-pasted in the cells from C 3: C 4 (for C 2) and from D 3: D 4 (for D 2). MS-Excel tutorial notes by James Tam

Cell References: Student Exercise #2 • Based on the spreadsheet below determine: – 1) the resulting formula after the copy-paste from the source to the destination – 2) what the formula evaluates to (works out) in the destination cell MS-Excel tutorial notes by James Tam

Lecture Review: Terminology • Spreadsheet (referred to as a “workbook” by Microsoft) – A Microsoft Excel file • Worksheet – A part of a spreadsheet MS-Excel tutorial notes by James Tam

Formula References To Other Worksheets • Example spreadsheet: “references_V 1_10%tax” “Employees” worksheet “Rates” worksheet References to same worksheet Reference to another worksheet MS-Excel tutorial notes by James Tam

Data Validation • There are tools in Excel to ensure the validity of data: – The type of information entered is of the correct type e. g. whole numbers only, no fractions allowed. – The data entered falls within a valid range e. g. no negative values can be entered for age. • Invoking data validation: – Data -> Data Tools : Data Validation For more information: https: //support. office. com/en-us/article/Apply-data-validation-to-cells-29 FECBCC-D 1 B 9 -42 C 19 D 76 -EFF 3 CE 5 F 7249 MS-Excel tutorial notes by James Tam

Data Validation: Specifying The Type Of Data Options that are largely self explanatory • Whole number • Decimal • Date • Time No restrictions on data entry • Any value is valid Restrictions on the number of characters • e. g. login user name must be 3 – 50 characters in length MS-Excel tutorial notes by James Tam

Data Validation: Input Length Restrictions • Steps: Data -> Data Tools : Data Validation and then select the ‘Settings’ tab (‘Settings’ should be selected by default). MS-Excel tutorial notes by James Tam

Data Validation: Guiding The User Beforehand Select the Input Message tab. Helpful message appearing when a cell mouse-over occurs

Data Validation: Letting The User Know After The Fact Select the Error Alert tab. Popup error message appearing when the validation rule has been violated.

Using Pre-Created Formulas • Entering pre-created formulas, refer again to a previous example: functions MS-Excel tutorial notes by James Tam

Data Validation • Spreadsheet with solution (Cell B 1): data validity solution MS-Excel tutorial notes by James Tam

Sample Data: Excel Functions Name of the Excel spreadsheet: functions MS-Excel tutorial notes by James Tam

SUM (Col F), ROUND (Col G) MS-Excel tutorial notes by James Tam

AVERAGE, TRUNC (Average: Row 9, Trunc: Row 10 – Truncates the averages from Row 9) MS-Excel tutorial notes by James Tam

Counting functions (Col B, Rows 14 - 16) (Count: Row 14, Count. A: Row 15, Count. Blank: Row 16) MS-Excel tutorial notes by James Tam

MIN, MAX (Row 19 – 20, Col B - D) MS-Excel tutorial notes by James Tam

Time Information: TODAY (B 23), NOW (B 24) MS-Excel tutorial notes by James Tam