Course Information
Excel Functions Boot Camp
Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.
A course coordinator will email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.
Course Description
Microsoft Excel has over 480 functions divided into 14 categories. My 30+ years of experience in teaching Excel has shown me that most Excel students know less than 15 functions. The purpose of this class is to expose students to a variety of functions that could improve their daily use of Excel.
Microsoft continually introduces new functions. Many of the new Excel 365 functions “spill” or automatically return multiple results into neighboring cells. These are called dynamic array functions.
This class is not designed to teach all the functions in Excel, but to focus on functions that can be useful to accountants, auditors and heavy users of Excel. This class is designed for the intermediate to advanced Excel user that wants to expand their knowledge of Excel functions. You must have Office 365 to be able to follow along with the examples in this class.
Course Objectives
Upon completion of this course, participants will be aware of the new Excel 365 functions and many standard functions that can help in everyday use of Excel. Users will be able to implement many of the topics in the class right away in their worksheets. They can reference the book and class files to become more proficient in using these functions.
Outline:
FORMULA BASICS AND UNDERSTANDING FUNCTIONS
• Understanding the Structure of Functions
• Work with the Function Library and the Insert Function Dialog Box
• Learn How and When to use Absolute and Mixed References
• Use the Formula Auditing Features
LOGICAL FUNCTIONS
• How to write and IF statement and nest IF statements
• NEW IFS statement
• Using IFERROR
• USE the AND OR FUNCTIONS
LOOKUP AND REFERENCE FUNCTIONS
• Using VLOOKUP
• XLOOKUP functions (OFFICE 365 Users)
• UNDERSTAND INDIRECT and use it with Data Validation
TEXT FUNCTIONS
• Use TRIM to remove extra spaces
• USE TEXT to convert numbers
• Join Text using CONCATENATE, CONCAT and TEXTJOIN
• Work with FORMULATEXT
• Work with TEXTAFTER, TEXTBEFORE, and TEXTSPLIT
DATE FUNCTIONS
• Understand date serial values
• Use the DATE function to convert a date stored incorrectly
• Use DATEDIF to calculate age or hire dates
• Work with TODAY() AND NOW() for current date and time
• Use NETWORKINGDAYS to calculate whole workdays
• Understand WEEKDAY and how it can be used with conditional formatting
• Work with WORKDAY to calculate number of days from a start date excluding weekend and holidays
DYNAMIC ARRAY FUNCTIONS
• What is an Array?
• SORT - Sorts the contents of a range or array.
• SORTBY - Sorts the contents of a range or array based on the values in a corresponding range or array.
• FILTER - Filters a range of data based on criteria you define.
• UNIQUE - Returns a list of unique values in a list or range.
• DROP - Excludes a specified number of rows or columns from the start or end of an array.
• TAKE - Returns a specified number of contiguous rows or columns from the start or end of an array.
• TOCOL - Returns the array in a single column.
• TOROW - Returns the array in a single row.
• TRANSPOSE - Returns the transpose of an array.
MATH, TRIG AND LOGICAL FUNCTIONS
• SEQUENCE – How to use the Sequence Function instead of filling a series.
• RANDARRAY vs RAND or RANDBEWTWEEN
Prerequisites
This is an intermediate to advanced class. Users should have taken an Excel Level 1 course, or equivalent working knowledge of the software.
YB 4.23 (P)
Instructors
Darla Cloud has been teaching computer classes for over 27 years. She has earned the various Microsoft Office certifications acknowledging her expertise in Microsoft products. Darla is also a Certified Public Accountant and a Certified Technical Trainer. In addition to Darla’s years of teaching, she has over seven years of accounting experience. Darla’s accounting experience and love of teaching help make her an excellent trainer. She has spent years learning tips, tricks and shortcuts that she will pass on during her classes.
Additional Information
TAC Rule 523.142(g) requires the CPE Sponsor to monitor individual attendance and assign the correct number of CPE credits. Participants will be asked to document their time of arrival and departure in compliance with this Rule. Additionally, attendance will be monitored throughout the day and CPE certificates will reflect actual attendance of each participant.
If you are making travel plans to come to Austin, we recommend making "refundable" air and hotel reservations or waiting until 14 days before the class to actually book your reservations. Courses are occasionally canceled or rescheduled due to low enrollment. We determine whether a course has enough participants 16 days prior to the course date. If we cancel or reschedule, we will email the participant and his or her billing contact no later than 14 days before the original class date.
To see answers to our Frequently Asked Questions, visit Texas State Auditor's Office - Professional Development FAQs