Managing Data with Excel (Course Id 2714)

• Who is this course for?
  This course is for CPAs, accountants, and other professionals needing Continuing Professional Education (CPE) who want to enhance their Excel skills for data management.

• What is this course about or what problem does this course solve?
  This course teaches how to transform raw data in Excel into actionable insights using features like sorting, filtering, subtotals, pivot tables, database functions, and conditional formatting.

• How can the knowledge from this course be used?
  The knowledge can be used to organize, analyze, and present data more effectively in Excel to support better decision-making.

• Why is this course important to a CPA or Accountant?
  It equips professionals with practical Excel data management skills that are essential for accurate reporting and efficient workflow in financial roles.

• When is this course relevant or timely?
  The course is relevant now, especially with the continued use of Excel 2010 through Office 365 in professional environments and the growing demand for data analysis skills.

• How is a course like this consumed or used?
  This is a self-study, text-based online course that includes reading material, review questions, and a final exam, completed independently by the participant.

This course shows you how to take basic rows and columns of data and turn it into useful information that can be incorporated into the decision-making process. We start with Excel’s simple basic Database features of sorting and filtering data and then look at subtotals and nested subtotals. From there, we explore one of Excel’s most exciting features – the pivot table. The course then discusses how to use Database functions and apply conditional formatting to data.

NASBA QAS - Text - NASBA Registry - 2714

Course Learning Objectives

• Recognize basic components of a database
• Identify and apply different ways to sort data
• Define and apply filtering techniques
• Recognize the steps required to create and copy subtotals and nested subtotals
• Recognize when it is appropriate to use a pivot table
• Recognize the steps to create, edit and format pivot tables
• Recognize how components of a database function and how to apply them
• Recognize how to apply and remove conditional formatting
• Recognize and apply an Outline format

Chapter 1 - Managing Data with Excel

Excel’s Data Features

Sorting Data

Ascending/Descending

Sorting Chronologically

Customized Sorts

Review Questions

Filtering Data

Review Questions

Create an Outline

Automatic Outline

Remove an Outline

Manual Outline

Review Questions

Subtotals

Nested Subtotals

Copying the Summarized Subtotal Information

Review Questions

Pivot Tables

Rearrange Data in a PivotTable

Add Data to the Report Filter

Refresh your data

Filtering (Hiding) Data

Detail Drill Down

Displaying Data

Subtotals

Formatting

Renaming Fields

Custom Calculations

Grouping

Review Questions

Database Functions

Review Questions

Conditional Formats

Adding a Condition to Existing Rule

Delete Conditional Formatting

Finding conditional formatted cells

Review Exercise -On Your Own

Review Questions

Links for Spreadsheets

Glossary