Self-Study
5
Intermediate
Computer Software and Applications
Knowledge of Excel and familiarity with formulas and functions.

Course Description

Watch your efficiency and productivity increase with this vital course. Discussed in depth are the Excel features CPAs need to know such as selecting and navigating through data; copying, moving, and importing data; formatting techniques, and creating charts. You'll learn how to compare lists using arrays, create pivot tables, and use functions such as SUBTOTAL, SUMIF, INDEX MATCH, AGGREGATE, and IFNA. The course presents fundamental tips, tools, and techniques along with easy to follow instructions and screenshots making it a must for any CPA working with Excel.



Instructor

Patricia McCarthy, MBA

Patricia McCarthy, MBA, is a partner at CFO Resources LLC, a company focused on training and courseware for CPAs. Her focus is on Microsoft products, particularly Excel, and how to use these programs effectively in business. She’s also certified as an MOS expert in Excel. Patricia has been in software training for over 20 years, and her client list includes the Indiana CPA Society, which awarded her its Outstanding Discussion Leader award.

She has been a contributor to the Journal of Accountancy and has published articles in several CPA society magazines, including societies in Indiana, Missouri, Texas, and Arizona.

Patricia is a graduate of the College of the Holy Cross and also holds an MBA from Babson College. She was a full-time lecturer in Butler University’s School of Business for 6 years and has taught at several of Indiana’s other universities.

Patricia also has an extensive business background in audit and financial functions. She runs a blog called Chatting about Excel and More, and she writes self-study CPE courses on Microsoft Excel for Western CPE.



Course Specifics

Computer Software and Applications
Feb 12, 2018
Knowledge of Excel and familiarity with formulas and functions.
SS2164523
122
Excel 2007 or higher required.


Compliance Information

103220
Qualifies for CA Fraud: No


Learning Objectives

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

  • Recognize different shortcut keys and what they do
  • Recognize the steps needed to pin a file, noting display changes after a file is pinned
  • Identify valid range names, noting character limits
  • Recognize different ways to select data and navigate within and between Excel workbooks
  • Identify some of Excel's new features and functions
  • Recognize different copy and move methods
  • Identify different types of formatting and how to apply them
  • Identify different functions and formulas and when to apply them
  • Recognize the different components of a Pivot Table and how to create and edit them
  • Identify types of charts to use and how to create them
  • Identify features that allow you to evaluate the workings of formulas and functions


Table of Contents

Learning Objectives

Intro

Shortcut Keys

Review Questions

Selecting Data

Selecting too much or too little data

Preselect your area

Select Noncontiguous Data

Select All the Cells

Name Box – Range Names

Summing a number of columns at once

Filter by Selection

Quick Analysis – 2013 Feature

Selecting and Editing Formulas

Unhiding a Column

Unhiding Only Hidden Columns that Contain Data

Other Quick Tips on Selecting Data

Review Questions

Navigating

Navigating to Your Most Used or Favorite Files

Navigating around a Worksheet

Range Names

Navigate between Sheets

See Multiple Worksheets in the Same Workbook at Once

Navigate between Excel files

Go To Special

Create a Table of Contents

Review Questions

Copying / Moving/Grouping

Copying and Grouping

Copying - Drag Fill Handle

Copying - Double-Click Fill Handle

Copy – Fill

Copy - Flash Fill

Copy - Use a Table

Grouping Sheets - Copying the Same Information onto Multiple Sheets at the Same Time

Sum a Cell in Multiple Sheets at One Time

Copy a Sheet

Moving/Copying Sheets to a Different Workbook

Copying Filtered or Subtotaled Data

Review Questions

Miscellaneous Productivity Tips

Customizing Your Toolbar

Quick Access Toolbar

Creating a Multi-Tiered List

Watch Window

Removing Duplicates

Sorting

Sorting and Custom Lists

Sorting Chronologically

Creating Custom Lists

Subtotal

Create Nested Subtotals

Copying Subtotals and Grand Totals Only

Review Questions

Formatting

Custom Formats

Custom Number Formatting

Fixed Decimal Places

Formatting Subtotal Rows

Quick Formatting Tip

Conditional Formatting

Conditional Formatting – Rules

Shading Alternate Rows

Review Questions

Must Know Functions

Nested IF

AGGREGATE( )

SUBTOTAL( )

Conditional Functions

SUMIF

Using SUMIF

The Plurals

MATCH() and INDEX()

Handling Text

Text Import Wizard

Text Commands

LEFT(), RIGHT(), MID()

CONCATENATE( )

LEN( )

LEN( ) and LEFT( )

LEN CONCATENATE IF - Example

IFERROR( ) and IFNA( )

IFERROR( )

IFNA( )

Miscellaneous Formulas You May Actually Need One Day

Comparing Lists

Using Count Functions with Other Functions

TRIM

Convert a Date to a Calendar Quarter

Determining the Median and Mode

Determining Rank

TRUNC( )

No Decimals

Review Questions

Pivot Tables

Creating a Pivot Table

Report Filter

Drilling Down

Pivot Table Calculations

Create a Custom Calculation

Create a Calculated Field

Grouping by Date

Slicers

Miscellaneous Pivot Table Tips

Drilling Down

Change Column Heading in Pivot Table

Sorting

Missing Entries in Your Pivot Table Data

Review Questions

Charts

Quick Tips

Quick way to Create a Chart

Select a Chart Object

Hide Data

Move and Align

Display Units

Switch Rows/Columns – Reorder legend/ Empty Cells

Numbers on the X Axis

Create a Dynamic Version of Your Original Chart (or Data)

1st method: Paste as Linked Picture

2nd Method: Camera Tool

Create a Combination Chart

Add a Series into the Chart

Delete a Series in a Chart

Selecting Data for a Chart

Dynamic Chart Title

Select All Charts at Once

Review Questions

Miscellaneous

Sort Left to Right

Printing

Headers/Footers

Appendix

Worksheet Design Tips

Automation

Review Question Answers and Rationales

Glossary

Index

Qualified Assessment

Answer Sheet

Course Evaluation

 



Choose Your Preferred Format


Online Access

Get immediate access to a robust collection of learning and reference materials, allowing you to dive deep into the information you need. Our self-study materials are authored by top-quality, industry experts who focus on helping you grasp concepts quickly using real-life examples. Download your CPE to any device, and take it with you so you can learn when and wherever you want. Complete your CPE with an online exam, and enjoy instant grading with the option to print your certificate immediately upon passing.

online access
Hard Copy

Our self-study materials are authored by top-quality, industry experts. You’ll receive a bound notebook of all the course materials, shipped to you within one business day. In addition, you’ll also have full online access. Each self-study package includes a robust collection of learning and reference materials to help you cover the information efficiently and put it into practice immediately.

hard copy
Self-Study Video

Experience high-quality instruction with our Self-Study Videos, available to you 24/7. With more than 80+ videos currently in the Western CPE Self-Study Video library, you can see and hear our expert instructors deliver the information you need in a dynamic way that allows you to immediately apply your learning. Unlike a live course or webcast, you can complete the course on your own time, playing and pausing as needed. Self-Study Videos allow you to complete your CPE requirements on your own time.

hard copy


Back to top