Microsoft’s MO-220 exam: Perform Accounting Data for Analysis

In this article, we’ll explore the requirements for the first section in Microsoft’s MO-220 “Microsoft Office Specialist: Excel for Accounting Associate” exam, and have a look at some things you need to know, together with links for future learning:

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:
  • =LEFT(B2, 1)  
  • =RIGHT(B2, 3)  
  • =MID(B2, 2, 1)  
  • =FIND(“-“, C2)  
  • =LEN(L2)  
  • =TRIM(L2)

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.

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, or see the full list of Microsoft MO-220 requirements.