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.


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.
Excel 2007 or higher required.

Compliance Information

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


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 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 and Custom Lists

Sorting Chronologically

Creating Custom Lists


Create Nested Subtotals

Copying Subtotals and Grand Totals Only

Review Questions


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



Conditional Functions



The Plurals


Handling Text

Text Import Wizard

Text Commands



LEN( )

LEN( ) and LEFT( )


IFERROR( ) and IFNA( )



Miscellaneous Formulas You May Actually Need One Day

Comparing Lists

Using Count Functions with Other Functions


Convert a Date to a Calendar Quarter

Determining the Median and Mode

Determining Rank


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


Miscellaneous Pivot Table Tips

Drilling Down

Change Column Heading in Pivot Table


Missing Entries in Your Pivot Table Data

Review Questions


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


Sort Left to Right




Worksheet Design Tips


Review Question Answers and Rationales



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