Skip to main content

Intermediate/Advanced Excel Features Beneficial for Auditors and Accountants

Back to Course Schedule
Date(s): Aug 20, 2018 - Aug 21, 2018
Time: 8:30AM - 4:30PM
Registration Fee: $299.00
Cancellation Date: Aug 02, 2018
Location: SAO COMPUTER TRAINING ROOM
City: Austin

Course Description

Microsoft added so many new features in Excel 2013 and 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 2007, 2010, 2013 and 2016.  So regardless of your version this class will benefit you.  However, the class will also focus on new features.

In this course you will learn how to work with formulas such as IF, VLOOKUP, COUNT, CONCATENATE and more.  You will learn how to use absolute addressing and range names in more complicated formulas.  The class also takes a look at Excel’s database features such as sorting, filters, tables, PivotTables and automatic subtotaling.  You will learn to create charts, work with multiple sheet workbooks and more.  This class is loaded with shortcuts that will help you be more productive when using Excel.


Potential CPE Hours: 15.0
Govt Hours: This class meets 15.0 hours of the 24-hour requirement for governmental CPE under Government Auditing Standards (yellow book), in most cases.
Technical Hours: This class meets 15.0 CPE credits of technical training in compliance with Texas Admin. Code Rule 523.102.

Instruction Type: Computer Based
Experience Level: INTERMEDIATE
Category: Auditing

Course Objectives

 

Upon completion of this course, participants will be able to:

Build more advanced formulas, create charts and use Excel’s database features such as sorting, filters, tables and PivotTables. You will also have a basic understanding of macros 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

·         Create and use Named Ranges

·         Understand Guidelines and Scope of Named Ranges

·         Work with Shortcuts and Common Keystrokes

Advanced Formulas

·         Understand the Parts of a Function

·         Use Auto-Calculate

·         Create Subtotals and Grand Totals

·         Work with Order of Calculation

·         Learn the IF Function

·         Learn the VLOOKUP Function

·         Work with text functions like CONCATENATE, LEFT, RIGHT,etc.

·         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 Function

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 you spreadsheet correctly

·         Learn to Sort alphabetically, numerically, chronologically, 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

·         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

Macros and VBA

·         Learn About Macros

·         Add the Developer tab

·         Record Macros Absolute and Relative

·         Edit Macros

·         Delete Macros


Prerequisites

No prerequisites required.

Basic Excel skills such as begin able to create a SUM function, insert rows and columns, copy formulas, etc.


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