The MO-220 “Microsoft Office Specialist: Excel for Accounting Associate” exam is going to be released by the end of 2025. It tests the end user on their ability to manage financial information, and create calculations and reports.
The requirements which are needed for this exam are as follows:
Perform Accounting Data for Analysis
Import and export accounting data in Excel
Import data from various sources
- File – Open and change the File Type dropdown box.
- Use Data → Get Data to import data into an existing workbook.
Save and export workbooks in various file formats
- File – Save As.
- Options include.xlsx, .csv, .txt and .pdf.
Clean accounting data in Excel
Remove duplicate data to ensure accuracy
- Data – Remove Duplicates.
Delete or hide rows, columns, and cells to simplify data
- Right-hand click on a row, column or cell
- Go to Home – Format
Format cells for consistency
- Use Home – Font, Home – Alignment and Home – Number.
- Use Format Painter to standardize styles.
Verify accounting data integrity in Excel
Apply Data Validation rules to restrict input
- Data – Data Validation – Data Validation
- Use the Settings tab to define the correct values.
- Use the Error Alert tab to define the error message and severity.
Identify data outside defined standards or expected values
- Data – Data Validation – Circle Invalid Data
- Use the Status Bar to see aggregations
- Use filters to explore data.
Transform accounting data in Excel
Split a single data column into multiple columns
- Data –Text to Columns.
Use text functions to extract or combine values
- Combine values:
- =D2 & “-” & E2
- =CONCAT(D2, “-“, E2)
- =TEXTJOIN(“, “,TRUE,D2:G2)
- Extract values:
Use date functions to transform transaction dates
- =YEAR(A2)
- =MONTH(A2)
- =DAY(A2)
- =WEEKDAY(A2)
- =WEEKNUM(A2)
- =DATE(2029, 1, 31)
- =DATEVALUE(“31-January-2029”)
- =TEXT(A2, “mmm-yy”)
- =EDATE(A2, 6)
- =EOMONTH(A2, 0)
- =TODAY()
- =NOW()
- =DATEDIF(A2, B2, “D”)
Use Fill features to extend series and patterns
- Use Fill Handle to extend months Jan–Dec.
- Use Data – Flash Fill or the auto Flash Fill for patterns.
Prepare a Trial Balance
Organize accounting transactions in Excel
Sort transactions
Filter transactions
Organize financial data using Excel formulas
Use math and statistical functions
Use logical functions
Use lookup functions
Prepare and Analyze Financial Statements
Prepare financial statements in Excel
Use formulas to aggregate data
Format data for presentation
Analyze financial statements in Excel
Calculate profitability ratios
Calculate liquidity ratios
Calculate solvency ratios
Correct financial statement errors in Excel
Use Conditional Formatting to identify data anomalies
Use Formula Auditing features to verify and correct calculations
Present financial information visually in Excel
Create PivotTables to summarize financial data
Create dashboards and charts
Evaluate and improve accessibility and usability
Perform Routine Accounting Activities
Prepare amortization schedules in Excel
Calculate fixed monthly payments
Create amortization schedules
Prepare bank reconciliations in Excel
Use functions to identify outstanding checks and deposits in transit
Use Conditional Formatting to identify reconciling items
Calculate adjusted cash balances
Manage accounts receivable and accounts payable in Excel
Use functions to organize sales and purchase data
Create accounts receivable or accounts payable aging reports
Use functions to calculate bad debt expense
Perform payroll activities in Excel
Calculate gross and net pay for multiple employees
Calculate employer payroll tax liabilities
Use functions to assess irregularities
Create depreciation schedules in Excel
Calculate periodic depreciation
Project future depreciation
Conclusion
We hope that the above information will allow you to be able to prepare for the MO-220 “Microsoft Office Specialist: Excel for Accounting Associate” exam. For more information about this exam, please read our blog article about this exam.
