Links Launch Outlook Place Skype on Do Not

Links • Launch Outlook • Place Skype on Do Not Disturb

TECHNOLOGY INSTRUCTION & ENGAGEMENT Excel Intermediate Please log in to your workstation with your KU Online ID and password.

EXCEL = INTERMEDIATE COURSE INSTRUCTOR Tennille Fincham Microsoft Certified Trainer Technology Instructor & Learning Enthusiast Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE COURSE OVERVIEW Overview: Introduction | Tour | Terminology | Navigation | Selection Activity 1: Sort & Filter Cell Ranges Find & replace data Split screens Activity 3: Create a table Sort & Filter table data Use table references in formulas & functions Activity 2: Create Named Ranges Using Data Validation Introduction to Logical Statements Conditional formatting Activity 4: Find and remove duplicate records Activity 5: Group rows and columns Create Charts Create Sparklines Create Smart. Art Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

WHAT IS EXCEL? EXCEL = INTERMEDIATE Microsoft Excel is a spreadsheet program that allows you to calculate and graph data sets. Excel can be used to create a number of items including: • Work Orders • Budgets • To-do lists • Timesheets • Financial/Account Reports • Sales Reports Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE OPENING EXCEL Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE TERMINOLOGY • Workbook – comprised of one or many worksheets containing data • Worksheet – contains a series of rows/columns containing data, charts, tables, etc. • Column – comprised of vertical cells • Row – comprised of cells running horizontally • Cell – individual space that contains formatted data, formulas, functions, etc. • Cell address – the location of particular cells within a Workbook/Worksheet. A cell’s address may be relative or absolute. Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

REVIEW EXCEL = INTERMEDIATE WHAT ARE FORMULAS? • A statement written by the user to be calculated • Can contain values, cell references, named ranges, and functions • Start with the equals sign, for example =1+2+3 • Order of operations: • Operations enclosed in parentheses • Exponential calculations (3^2, for example) • Multiplication and division, whichever comes first • Addition and subtraction, whichever comes first *A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Aunt Sally. Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE REVIEW WHAT ARE FUNCTIONS? • Functions are preprogram operations in Excel • Common ones include SUM, AVERAGE, • Always start with equals sign and have parenthesize where arguments are listed • For example: • =SUM(1, 2, 3) • =SUM(A 2: A 15) • =SUM(1, 2, 3)*2 Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE REVIEW RELATIVE, ABSOLUTE, AND MIXED REFERENCES • Affects your formulas and functions if you use auto fill or copy/paste to reuse the statement Reference Type Description A 4 Relative This is the default cell reference. When used in a formula that is copied, pasted, or auto filled, it will change to be relative to the new position. $A$4 Absolute When used in a formula, it will never move or change. $A 4 Mixed When used in a formula, the row will change, but the column never will. A$4 Mixed When used in a formula, the column will change but the row never will. • Caveats: • Named ranges are always absolute • Tables are absolute when referring to the entire table Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE LOGICAL FUNCTIONS WHAT ARE THEY? • Consist of IF, AND, OR, and NOT functions • Also, IFERROR and IFNA functions • Test whether a statement is true or false • Can be used on number, currency, date, and text cells Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE LOGICAL FUNCTIONS IF STATEMENTS =IF(LOGICAL_TEST, VALUE_IF_TRUE, [VALUE_IF_FALSE]) Argument name Description logical_test (required) The condition you want to test. value_if_true (required) The value that you want returned if the result of logical_test is TRUE. value_if_false (optional) The value that you want returned if the result of logical_test is FALSE. Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

LOGICAL FUNCTIONS EXCEL = INTERMEDIATE IF STATEMENTS EXAMPLE =IF(D 2>500, 20, 40) Argument name Description Example logical_test The condition you want to test. If the value in D 2 is greater than 500 value_if_true The value that you want returned if the result of logical_test is TRUE. Show value of 20 value_if_false The value that you want returned if the result of logical_test is FALSE. Show value of 40 Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

LOGICAL FUNCTIONS EXCEL = INTERMEDIATE OPERATORS Condition Operator Example Description Equal to = A 1=B 1 returns TRUE if a value in cell A 1 is equal to the values in cell B 1; FALSE otherwise. Not equal to <> A 1<>B 1 returns TRUE if a value in cell A 1 is not equal to the value in cell B 1; FALSE otherwise. Greater than > A 1>B 1 returns TRUE if a value in cell A 1 is greater than a value in cell B 1; otherwise it returns FALSE. Less than < A 1<B 1 returns TRUE if a value in cell A 1 is less than in cell B 1; FALSE otherwise. Greater than or equal to >= A 1>=B 1 returns TRUE if a value in cell A 1 is greater than or equal to the values in cell B 1; FALSE otherwise. Less than or equal to <= A 1<=B 1 returns TRUE if a value in cell A 1 is less than or equal to the values in cell B 1; FALSE otherwise. Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE DUPLICATE RECORDS KEEP IN MIND… • You’ll want to have a column that could contain unique values to search for duplicates • This may be a column you end up setting up on your own to contain both the first name and last name of each person listed, for example • Before you remove duplicates, use conditional formatting to identify them • Filter the column based off the cell color to see only duplicate values • Check duplicate records carefully as names can be repeated, but not necessarily be a duplicate record (ex: John Smith) • When you remove duplicates, it will keep the first record and delete the others • You may want to specify that a “duplicate” has to have multiple columns in common. Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE WHAT WE LEARNED Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

EXCEL = INTERMEDIATE ? WRAP UP/QUESTIONS Instructor follow-up After the workshop, the instructor will send an email to all users that have attended. The email may include: • Additional learning resources • Answers to outstanding questions Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology

ADDITIONAL TRAINING EXCEL = INTERMEDIATE IT training opportunities There a number of training resources that the Technology Instruction department provides: • Departmental training • Interested in having us over to speak to your department about this topic or another technology? – Visit technology. ku. edu/request-instruction request a training session for your department or email training@ku. edu to speak to one of our instructors. • Open workshops and Quick Learn webinar series • Search and sign up for sessions at workshops. ku. edu. • Desk-side coaching • Want some one-on-one time? – Fill out our desk-side coaching request at technology. ku. edu/request-instruction to meet with an instructor individually. Technology Instruction & Engagement | training@ku. edu | 785 -864 -5155 Opening doors with technology