Course Information
Intermediate/Advanced Excel Features Beneficial for Auditors & Accountants
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
This class is continually updated to include many of the new Excel features that have been added in recent years. Microsoft has added many new features to Excel that are especially helpful to accountants and auditors. This class is an “Excel buffet” that covers a large variety of the most popular capabilities of the Excel program and many of the new features that have been introduced in the recent software upgrades. The curriculum will explain how to accomplish a task using Excel 2013, 2016, 2019 and Office 365. Regardless of your version, this class will benefit you.
In this course you will learn how to work with formulas such as IF, IFS, VLOOKUP, XLOOKUP, COUNT, CONCATENATE, TEXTJOIN and more. You will learn how to use absolute addressing and range names in more complicated formulas. The class looks at Excel’s data tools features such as sorting, advanced sorting and filters. We will dive more deeply into the importance of using Table with PivotTables. You will learn to work with multiple sheet workbooks and create linking formulas. This class is loaded with shortcuts that will help you be more productive when using Excel.
You may bring your own laptop if desired. Participant laptops available for use have Office 2016, not Office 365.
Course Objectives
Upon completion of this course, participants will be able to:
Build more advanced formulas and use Excel’s data features such as sorting, filters, tables and PivotTables. You will also understand how to create and modify charts and learn shortcuts that will improve your efficiency when using Excel.
Course Outline:
Formula Basics and Absolute References
Review Basic Formulas
Work with Relative and Absolute References
Use Formula Auditing Tools
Work with Shortcuts and Common Keystrokes
Advanced Formulas
Identify Comparison Operators and Parts of a Function
Use Auto-Calculate
Create Subtotals and Grand Totals
Work with Order of Calculation
Learn how to use COUNT, COUNTA AND COUNTIF
Learn the IF and IFS Function; Nest IF Functions
Understand Named Ranges
Learn the VLOOKUP Function; Discuss XLOOKUP and HLOOKUP
Work with text functions like CONCATENATE, LEFT, and RIGHT
Look at NEW functions like CONCAT and TEXTJOIN
Learn how to change the case of text with UPPER, LOWER and PROPER
Use the Text-to-Columns feature
Look at the new Flash Fill feature
Use Paste Special
Protect your formulas
Password protect formulas and files
Work with Date Functions like TODAY, NOW, WEEKDAY, WORKDAY and DATEDIF
Working with Multiple Sheets and Links
Learn Excel’s sheet options
Learn to quickly navigate sheets
Learn to Copy and Insert sheets
Rename and Color sheet tabs
Group sheets and work with all sheets simultaneously
Ungroup sheets
Move sheets within the workbook and to new workbooks
Create three-dimensional references
View multiple sheets at the same time
Link workbooks and manage the links
Use Comments
Freeze Rows and Columns
Learn to Split Views
Excel’s Database Features
Setup your spreadsheet correctly
Learn to Sort alphabetically, numerically, chronologically, by colors, by icons and by custom list.
Use Data Subtotals
Learn various ways to Filter data
Create Tables and Understand their benefits
Create and Modify PivotTables
Work with PivotTable Timelines and Slicers
Learn to modify the Design of your PivotTable
Work with Recommended PivotTables
Additional Excel Features
Modify the Quick Access Toolbar
Customize the Status bar
Learn the benefit of using Styles
Work with Conditional Formatting
Modify the Ribbon
Prerequisites
Know Excel basic functions, formatting, and navigating the spreadsheet.
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.
A self-service vending area with beverages and snacks can be found on the first floor of our building next to the DPS office. There is also a refrigerator and microwave in our coffee bar area. Feel free to bring in your own drinks and food if you prefer.
You might want to bring a light sweater or jacket, as room temperatures vary.
To see answers to our Frequently Asked Questions, visit http://www.sao.texas.gov/training/faq.html