Must Know Excel Tips Tricks and Tools for CPAs - v16 (Course Id 2803)

• Who is this course for?
  This course is designed for intermediate Excel users, particularly CPAs and accounting professionals seeking to enhance their Excel skills and productivity for professional use.
• What is this course about or what problem does this course solve?
  This course teaches a wide range of Excel tips, tools, and functions—from basic navigation and data handling to advanced formulas, pivot tables, and data analysis techniques—to improve efficiency and effectiveness in everyday tasks.
• How can the knowledge from this course be used?
  The knowledge can be applied to streamline data management, perform complex calculations, analyze financial information, and create professional reports using advanced Excel features.
• Why is this course important to a CPA or Accountant?
  This course is important because it equips CPAs and accountants with practical Excel skills that enhance accuracy, efficiency, and productivity in handling financial data and reporting tasks.
• When is this course relevant or timely?
  This course is relevant when professionals want to upgrade their Excel capabilities, especially with newer functions like XLOOKUP and TEXTJOIN, and improve their workflow using modern Excel tools such as those in Microsoft 365.
• How is a course like this consumed or used?
  This course is completed as a self-study text-based program with step-by-step instructions, optional exercise files, review questions, and a required final exam to earn CPE credit.

This course is designed for intermediate users of Excel who are looking to improve their Excel skills and productivity. The course covers some basics, along with some advanced tips and tricks. It starts with how to select and navigate through data and worksheets as well as copying and moving data efficiently. It then details a wide spectrum of functions that can be used in everyday practice. Functions discussed include creating nested Ifs and exploring a variety of text functions  such as RIGHT(), MID() and LEN(). SUMIF() and INDEX MATCH are also discussed. New functions, such as XLOOKUP(),AGGREGATE(), IFNA(),TEXTJOIN() , CONCAT() and IFS() are covered. Learn how to comparing lists using arrays. Pivot tables are discussed in detail. Other topics include:  Charts, Conditional formatting, and tools for importing data into Excel. You will definitely impress your coworkers with all your new knowledge! 

An exercise file containing a few of the examples in the eBook are included. All topics are gone over in  a detailed step by step fashion that is easy to follow whether you wish to use the optional exercise files or not.

Version: Microsoft 365 users. Users of earlier versions should not have a lot of difficulty; however, some features may not be available or may be in a slightly different location.

NASBA QAS - Text - NASBA Registry - 2803

Course Learning Objectives

1. Recognize different short cut keys and their function
2. Recognize the function of pinning a file to Favorites
3. Identify and create range names
4. Recognize different ways to navigate within and between Excel workbooks
5. Identify some of Excel’s newer features and functions
6. Recognize different copy and move methods
7. Identify different types of formatting and how to apply them
8. Recognize different functions and formulas and how to apply them
9. Recognize the different components of a Pivot Table and how to create and edit them
10. Identify types of charts to use and how to create them
11. Identify features that allow you to evaluate the workings of formulas and functions

Chapter 1 - Must Know Excel Tips Tricks and Tools for CPAs

Course Learning Objectives

Intro

Shortcut Keys

Review Questions – Shortcut Keys

Selecting Data

Quick Access Toolbar

Quick Analysis

Selecting and Editing Formulas

Unhiding

Other Quick Tips on Selecting Data

Review Questions – Selecting Data

Navigating

Navigating to Your Most Used or Favorite Files – Pin It/Add it to Favorites!

Navigating Around a Worksheet

Range Names

Navigate Between Sheets

See Multiple Worksheets in the Same Workbook at Once

Navigate Between Excel Files Using Side by Side

Navigate Between Excel Files

Go To Special

Create a Table of Contents

Review Questions- Navigating

Copying / Moving/Grouping

Grouping Sheets-

Sum A Cell in Multiple Sheets at One Time

Copy a Sheet

Moving/Copying Sheets to a Different Workbook

Copying Filtered or Subtotaled Data

Transposing Columns

Review Questions- Copying/Moving/Grouping

Miscellaneous Productivity Tips Customizing Your Toolbar

Watch Window

Removing Duplicates

Sorting

Sorting and Custom Lists

Subtotal()

Create Nested Subtotals

Copying Subtotals and Grand Totals Only

Review Questions- Miscellaneous Productivity

Formatting

Custom Formats

Fixed Decimal Places

Formatting Subtotal Rows

Conditional Formatting

Review Question- Formatting

Must Know Functions

Logical, Conditional and Lookup Functions

Nested IF

IFS()

SUMIF()

The Plurals

Error Functions

IFERROR()

MATCH() and INDEX()

XLOOKUP()

TEXT Functions and Import Wizard

Text Functions

LEFT(), RIGHT(), MID()

CONCATENATE()

CONCAT()

TEXTJOIN()

LEN()

LEN() & LEFT()

LEN() CONCATENATE() IF()

Miscellaneous Formulas You May Actually Need One Day

Comparing Lists

AGGREGATE()

Using Count Functions with Other Functions

TRIM()

Convert a Date to a Calendar Quarter

Determining the Median and Mode

Determining Rank

TRUNC() and INT()

Review Questions- Functions

Pivot Tables

Creating a Pivot Table

Report Filter

Drilling Down

Pivot Table Calculations

Group by Date

Slicers

Miscellaneous Pivot Table Tips

Review Question- Pivot Tables

Charts

Quick Tips

Display Units

Switch Rows/Columns – Reorder Legend/ Empty Cells

Numbers on the X axis

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

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- Charts

Miscellaneous Tips

Sort Left to Right

Printing

Headers/Footers

Conclusion

Link for Spreadsheet

Glossary