Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

MO-220 exam prep: Microsoft Excel for Accounting

Name: MO-220 exam prep: Microsoft Excel for Accounting
Rating: 4.6 (107 reviews)

Learn the skills for the new MO-220 Excel certification. Use Data Validation, Formula Auditing, create charts and more!

Created byPhillip Burton • over one million students so far, I Do Data Limited

Last updated 11/2025

English

What you'll learn

Perform Accounting Data for Analysis
Prepare a Trial Balance
Prepare and Analyze Financial Statements
Perform Routine Accounting Activities

Course content

10 sections • 80 lectures • 7h 17m total length

Introduction2:26
Welcome to Udemy0:38
How to get the best out of your Udemy course3:05
Do you want auto-translated subtitles in more languages?1:20
Curriculum5:26
Resources0:07

Microsoft Excel - a quick tour7:00
Inserting data and moving the cursor8:05
2. Save and export workbooks in various file formats7:26
save and export workbooks to formats such as xlsx, xlsm, xlsb, csv, txt, and pdf, using save or save as and considering macros, features, and cloud sharing.
1. Import data from various sources8:20
Moving data using cut, copy and paste3:41
3, 4. Remove duplicate data, and delete/hide/resize rows, columns and cells8:54
5. Format cells for consistency9:45
Format cells for consistency by applying font, size, bold, borders, fill color, and alignment, then use wrap text, decimals, accounting, currency, date formats, and the format painter to copy styles.
Practice Activity Number 11:14
Practice Activity Number 1 - The Solution9:58

6. Apply Data Validation rules to restrict input8:40
7. Identify data outside defined standards or expected values6:55
8. Split a single data column into multiple columns3:31
Learn to split a single data column into multiple columns in Excel with text to columns, using delimited or fixed width, and hyphen or space as delimiters.
11. Use Fill features to extend series and patterns4:46
Practice Activity Number 20:52
Practice Activity Number 2 - The Solution6:00

9. Using the & to combine values, and copying formulas to other cells6:06
9. Using text functions to combine values6:38
9. Using text functions to extract values8:02
Learn to use Excel text functions to extract values from an account number with left, right, and mid, and to locate, substitute, determine length, and trim texts.
10. Use date functions to transform transaction dates7:07
Practice Activity Number 30:54
Practice Activity Number 3 - The Solution5:36

12. Sort transactions8:19
13. Filter transactions10:10
14. Math functions10:19
Practice Activity Number 40:42
Practice Activity Number 4 - The Solution5:14
15. Using the logical functions IF, AND, OR and NOT5:46
15. Using more advanced logical functions5:36
16. The VLOOKUP function, and fixing references to rows and columns7:37
16. The XLOOKUP function5:16
Compare XLOOKUP with VLOOKUP and use flexible lookup and return arrays to fetch data. Handle not found values, dollar signs, spaces, and trimming to keep your spreadsheets up to date.
Practice Activity Number 50:41
Practice Activity Number 5 - The Solution7:20
Demonstrates creating a column titled 'is above $1,000', applying and/or logic, and using vlookup and xlookup to map countries to continents with duplicates removal.

17. Using formulas to aggregate data (SUM, COUNT, MAX, MIN, AVERAGE functions)7:51
17. Using conditional formulas to create a trial balance (SUMIF function)6:53
18. Formatting our trial balance for preparation8:39
17. Creating a trial balance based on a date using the SUMIFS function8:06
Practice Activity Number 61:11
Practice Activity Number 6 - The Solution8:09
17. Creating a Balance Sheet8:14
17. Create an Income Statement (Profit and Loss Account)4:14
Create an income statement in Excel by converting sales revenue and expenses into a profit and loss account, using subtotals and date filters for a six-month period.
Calculating profitability, liquidity and solvency ratios5:55
Practice Activity Number 70:51
Practice Activity Number 7 - The Solution5:23

22. Use Conditional Formatting to identify data anomalies5:14
22. More Conditional Formatting rules and managing Conditional Formatting6:41
23. Using Formula Auditing features to verify and correct calculations8:38
24. Creating PivotTables to summarize financial data10:00
25. Creating a dashboard7:48
25. Creating charts6:08
25. Creating PivotCharts5:50
Create a new pivot table with date in the rows, filter to current liabilities and equity, and generate a pivot chart to visualize liabilities and equity over time.
26. Evaluating and improving accessibility and usability6:41
Practice Activity Number 81:30
Practice Activity Number 8 - The Solution9:29
Practice Excel for accounting with data bars, unhide columns, and tracing dependents. Build and refresh pivot tables, fix balance discrepancies, and chart asset balances with alt text.

27. Calculating fixed monthly payments5:12
28. Creating amortization schedules6:57
Create amortization schedules in Excel by using PMT, IPMT, and PPMT to split a fixed loan payment into interest and principal, track remaining balances, and visualize with a chart.
Practice Activity Number 90:36
Practice Activity Number 9 - The Solution5:43
38, 39. Calculating periodic and projecting future straight-line depreciation6:17
38, 39. Calculating depreciation using other depreciation methods6:14
Practice Activity Number 100:18
Practice Activity Number 10 - The Solution3:45

35, 36, 37. Performing payroll activities in Excel10:08
Practice Activity Number 110:51
Practice Activity Number 11 - The Solution3:18
29, 30. Preparing bank reconciliations in Excel7:18
29, 30, 31. Expanding our reconciliation, and calculating adjusted cash balances5:55
Practice Activity Number 120:50
Practice Activity Number 12 - The Solution5:29
32, 33. Managing accounts receivable and accounts payable3:13
32, 33, 34. Creating an aging report and estimating bad debt9:45
Practice Activity 130:59
Practice Activity Number 13 - The Solution8:34

Requirements

Before beginning, you will need to know how to use a Windows computer, including a mouse.
It would be good if you have used Microsoft Excel before, but this is not essential.
You should have MS Excel (or Office) 2021 or later installed on your PC computer. This program is also known as a Microsoft 365 App.

Description

The Microsoft MO-220 certification is the new certification for Excel for Accounting. Microsoft says that this certification demonstrates that you have the skills needed to get the most out of Excel by earning a Microsoft Office Specialist: Excel for Accounting Associate certification.

In this course, we will look at all of the topics in the MO-220 certification.

Please note: This course is not affiliated with, endorsed by, or sponsored by Microsoft.

Please note: I will not be able to comment on accounting rules to your specific situation, as they can vary from country to country and from state to state. If you want any comments about accounting for your specific situation, please contact an accounting professional near you.

In this MO-220 exam preparation course:

We'll start by preparing accounting data for analysis. We'll import and export data, clean data, verify accounting data integrity, and transform accounting data.
We'll then Prepare a Trial Balance and Prepare and Analyze Financial Statements. These are Balance Sheets and Income Statements, also known as Profit and Loss Statements (P&L) or Statements of Earning. We'll organize accounting transactions and financial data using Excel functions, prepare and analyze financial statements, correct errors, and present information visually using PivotTables, dashboards and charts.
Finally, we'll perform accounting activities. We'll prepare amortization schedules and bank reconciliations, manage accounts receivable and payable, perform payroll activities, and create depreciation schedules.

No prior knowledge is required. This course completes with practice activities, so you can be sure that you are learning.

Once you have completed this course, you will have an expanded knowledge of Microsoft Excel. With some practice, you could even take the official Microsoft MO-220 exam, which gives you the "Microsoft Office Specialist: Excel for Accounting Associate" certificate. This would look good on your CV or resume.

Who this course is for:

This course is for you if you want to develop your Microsoft Excel skills with regards to accounting data.
This course will use the latest version of Excel, but is also for you if you use any modern version - Excel 2007, 2010, 2013, 2016 or 2019.
This course is for you if you want to become certified in Microsoft Excel, as by the end you should the knowledge to because certified as a Microsoft specialist with accounting data, if you wish to do so.
You will cover the content tested in the Microsoft Office Specialist Exam MO-220 "Excel for Accounting Associate"
This course may not be for you if you want to learn Microsoft Excel for Macintosh computers.

MO-220 exam prep: Microsoft Excel for Accounting

What you'll learn

Explore related topics

Course content

Introduction6 lectures • 13min

Importing/exporting data, and cleaning accounting data9 lectures • 1hr 4min

Verify accounting data integrity, and transform accounting data6 lectures • 31min

Using text and date functions6 lectures • 34min

Manipulating accounting data11 lectures • 1hr 7min

Preparing and analyzing financial statements in Excel11 lectures • 1hr 5min

Correct errors and presenting information visually10 lectures • 1hr 8min

Preparing amortization and depreciation schedules8 lectures • 35min

Payroll activities, bank reconciliations, managing accounts receivable/payable11 lectures • 56min

Well done for finishing the Excel course2 lectures • 3min

Requirements

Description

Who this course is for: