Skip to main content

Intermediate Advanced Excel Features Beneficial for Auditors and Accountants

Back to Course Schedule
Date(s): May 17, 2021 - May 18, 2021
Time: 8:00AM - 4:30PM
Registration Fee: $289.00
Cancellation Date: May 10, 2021
Location: Online

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, COUNT, CONCATENATE and more. We will discuss new functions like XLOOKUP and SWITCH. 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 create charts, 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.

 


Potential CPE Credits: 16.0

Instruction Type: Live
Experience Level: INTERMEDIATE
Category: 02 Auditing

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.

 

 

Formula Basics, Absolute References and Named Ranges

         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

         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

 

Charting

         Create a Chart

         Using the Chart Ribbon

         Use the new Recommended Charts

         Changing the Chart Type

         Modifying charts

         Work with Chart Elements

         Create and use Chart Templates

         Look at the new Sparkline feature

 


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 and use Text to Speech

         Work with Custom Views

 


Prerequisites

Student should have a working knowledge of Excel basic functions like the SUM function and be comfortable with tasks such as inserting rows, basic formatting and navigating the spreadsheet.

 


Instructors

Darla Cloud

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.

The course coordinator will contact you with parking information. Handicapped parking is free at the meters around the downtown area.

Vending machines with Coca-Cola products and various snack items are available. 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.


Back to Course Schedule