MO-220 exam prep: Microsoft Excel for Accounting

Open Excel, navigate the ribbon and tabs, preview templates, and adjust the color theme while learning file options and the search bar for features.

Learn how to manage workbooks and worksheets in Excel, navigate cells with the tab key and arrow keys, enter data, and use undo and basic data types.

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.

Import data from CSV, fixed-width, and text files into Excel using File Open or Get Data, choosing the right delimiter and date format for a clean workbook.

Combine data from CSV, tab-delimited, and fixed sources into one spreadsheet using the clipboard, then copy, cut, and paste with shortcuts (Ctrl+X, Ctrl+C, Ctrl+V) and save as Excel workbook.

Learn to remove duplicate data and manage rows, columns, and cells in Excel. Practice deleting, hiding, resizing, and using remove duplicates and autofit to keep accounting worksheets clean.

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.

Open and import text data into Excel, align columns, format dates, apply styling, remove duplicates, hide April data, save as xlsb, and export a pdf to verify data cleaning.

Explore how data validation restricts input to maintain accounting data integrity and transform accounting data. Set rules for whole numbers, four-digit account numbers, date ranges, and input messages.

Identify data outside of defined standards by enforcing data validation, highlighting invalid entries with circles, and using aggregations and filters to locate and correct data outside expected values.

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.

Use fill features to extend series and patterns in Excel by dragging the fill handle or using flash fill. Learn about flash fill options and when it might fail.

Apply data validation for decimal values under 10,000 and circle invalid data to flag errors. Use text to columns for hyphen delimiters and flash fill to transform data.

Learn to use the ampersand to combine values and copy formulas across cells, while managing relative references and string literals.

Learn how to combine data in Excel using ampersand, concat, and text join, handle delimiters and ignore empty cells, work with ranges, and navigate locale-specific syntax.

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.

Master date transformations in Excel by using date functions to transform transaction dates, including adding days or months, and extracting year, month, day, weekday, and week number.

Learn to use Excel in accounting through a practice activity: build concatenated accounts, locate first spaces, extract the first word, and compute transaction month and last day of month.

Sort transactions by date, account number, or amount using Excel's sort and filter tools. Use custom sort levels to sort by multiple columns, including color.

Learn how to filter transactions in Excel to hide non-matching rows, apply multiple criteria, and use number, date, and text filters with custom options.

Master Excel's math functions to manipulate numbers, apply operator precedence with brackets, and use int, mod, abs, sign, trunk, round, and ceiling or floor for decimals.

Sort and filter transaction data by date with multi-level sorting and a debit threshold of 1000, then compute dollar amounts with no cents using int, abs, and sign.

Explore Excel's logical functions with IF for true/false results, test greater than and not equal to, and combine conditions using AND, OR, NOT to identify ranges like 5,000 to 12,000.

The lecture demonstrates how to replace long nested if formulas with advanced logical functions such as IFS, switch, and choose, handling multiple conditions and default cases.

Learn to use vlookup to fetch account descriptions from a range, fix row and column references with dollar signs, and enforce exact matches with the false parameter for accounting data.

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.

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.

Learn to prepare and analyze financial statements in Excel using formulas to aggregate data: sum, max, min, average, count, counta, median, and mode.

Learn to build a trial balance in Excel using sumif to total debits and credits by account number, with autosum and absolute references.

Format and present a trial balance for accounting exams by applying currency formatting, bold headers, centered totals, and print-friendly layouts with page setup, scale to fit, and freeze panes.

Expand your trial balance to a specific as of date using sumifs with multiple criteria, then use countifs and averageifs, handling errors with iferror.

Master practice activity 6 in Excel for accounting by building a trial balance, calculating debits and credits with sumifs, removing duplicates, sorting by account type, and formatting for reporting.

Create a balance sheet from the trial balance by consolidating into assets, liabilities, and equity with a single balance column. Use formulas and subtotals to compute totals and retained profits.

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.

Calculate profitability, liquidity, and solvency ratios from the income statement and balance sheet in Excel, including gross and net profit, margins, ROA, ROE, and key liquidity and debt metrics.

Practice activity guides you to build a balance sheet from a trial balance, label assets, liabilities, and equity, and compute key ratios while setting print area and print preview.

Identify data anomalies in financial statements by applying conditional formatting in Excel, using data bars, color scales, and highlight cells rules, and manage formatting rules for accuracy.

Explore advanced conditional formatting in Excel for accounting, including managing rules, highlighting duplicates and date-based rules, top/bottom values, color scales, data bars, icon sets, and formula-based formats to spot anomalies.

Use Excel's formula auditing features to verify and correct calculations by tracing precedents and dependents, showing formulas, checking errors, and evaluating formulas for an auditable workflow.

Create pivot tables to summarize financial data by account numbers, totaling debits and credits, by defining ranges and arranging fields in rows, columns, and values.

Create a dashboard from report excerpts by linking balance sheet and income statement figures, showing assets, liabilities, equity, gross and net profit, and return on assets and equity.

Create and customize charts in your accounting dashboard with Excel. Build a 2D pie chart of assets, adjust labels and legend, and reposition the chart.

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.

Evaluate and improve workbook accessibility and usability by ensuring data accuracy, linking dates across sheets, adding hyperlinks, and enhancing alt text and pivot table and chart descriptions for all users.

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.

Learn to calculate fixed monthly loan payments with Excel's pmt function in an amortization schedule, using present value, rate per period, and number of periods.

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.

Create an amortization schedule in Excel for a 300,000 loan over 15 years at 5.25%, using PMT, IPMT, and PPMT to compute payments and balances.

Calculate depreciation for tangible assets using straight-line methods in Excel, including SLN and Edate techniques, with salvage value, useful life, and period projections.

Build an Excel depreciation schedule using multiple methods: SLN (straight-line), DB (fixed declining balance), DDB (double declining balance), SYD (sum of years), and VBD (variable declining balance), with optional arguments.

Learn to compute depreciation schedules in Excel using SLN and VDB functions, set salvage value and useful life, chart depreciation with an area chart, and format axes for clear visuals.

Perform payroll activities in Excel for U.S. employees, calculating gross and net pay for salaried and hourly staff, including bonuses, overtime, FICA, federal income tax withheld, and employer taxes.

Practice activity 11 guides you through calculating gross pay from annual salary plus bonus, applying FICA and state taxes, and computing net and employer FICA in Excel payroll scenarios.

Explore bank reconciliation in Excel by using countif and ifs to identify outstanding cheques and deposits in transit, and apply conditional formatting to highlight reconciling items.

Expand your bank reconciliation in Excel to calculate an adjusted cash balance, identify outstanding cheques and deposits in transit, and use countif, ifs, and sumifs.

Practice activity 12 demonstrates building a bank reconciliation in Excel, using countif, if, sumifs, and xlookup to verify cheques and deposits and compute the adjusted cash balance.

Explain how to build an accounts receivable and accounts payable aging report in Excel, using as of date, due dates, days outstanding, and the IFS formula to flag overdue items.

Classify days outstanding into aging buckets using vlookup with approximate matching, then apply sumif and bad debt estimates to project accounts receivable and payable in Excel.

Calculate due dates, match deposits to invoices, and classify accounts receivable as paid, overdue, or current using countifs, ifs, and aging analytics in Excel, with a pie chart.

Improve Excel for accounting by mastering data cleaning, validation, transformation, formulas, pivot tables, dashboards, trial balance prep, and reports like aging, payroll, and depreciation.

Review how you prepared accounting data by importing, exporting, cleaning, verifying integrity, and transforming it to analyze financial statements with pivot tables, dashboards, and charts.