Microsoft Office Specialist: Excel for Accounting Associate — Practice Set 1

Question 1

You are an accounting associate importing a CSV export from your company's point-of-sale system into Excel so you can analyze daily sales totals. Which ribbon command should you use to bring the CSV data into a new worksheet as a connected, refreshable table?

Accepted Answer

Data > Get Data > From Text/CSV launches Power Query, which imports the CSV as a connected table that can be refreshed if the source file changes. Insert > Table only formats existing worksheet data as a table; it does not import external files. Opening and copy/pasting the CSV brings in static values with no refresh connection, and Paste Special > Values likewise only pastes static data that was already copied.

Question 2

Your manager needs the quarterly expense workbook sent to an external auditor who uses an older accounting package that can only read comma-delimited text files. Which action should you take to satisfy this requirement while preserving the ability to also keep your original formulas intact for your own records?

Accepted Answer

Save As with the CSV (Comma delimited) file type creates a separate, properly converted text file while leaving your original .xlsx workbook (with formulas and formatting) untouched. Simply renaming the extension does not actually convert the binary file format and will corrupt the file. Asking the auditor to convert it shifts the requirement rather than meeting it, and a PDF cannot be read by an accounting package expecting delimited text.

Question 3

A vendor invoice list was imported from two different source systems and merged into one sheet, resulting in some invoices appearing twice with identical Invoice Number, Vendor, and Amount values. What is the most efficient way to remove these exact duplicate rows before reconciling the data?

Accepted Answer

Data > Remove Duplicates lets you specify which columns must match for a row to be considered a duplicate, then automatically deletes the extra occurrences, which is fast and accurate for large invoice lists. Manual scrolling is slow and error-prone. Conditional Formatting only highlights duplicates visually but doesn't remove them. Sorting alone reorganizes the data but does not eliminate duplicate rows.

Question 4

You need to restrict entries in a 'Payment Method' column so that users can only choose from Cash, Check, ACH, or Credit Card, preventing typos like 'Cach' or 'credit crd'. Which Excel feature should you configure on that column?

Accepted Answer

Data Validation configured with a List source restricts cell entry to a defined set of values (via a dropdown), directly preventing typos and invalid entries. Conditional Formatting only changes the appearance of cells after entry, it does not block invalid input. Cell Styles affects formatting only, and Freeze Panes only controls scrolling behavior.

Question 5

A column of imported transaction descriptions contains values like 'INV-2024-00815-NY' where you need just the store code ('NY') in its own column for every row, and the pattern length varies slightly across rows. Which approach is best suited to reliably extract this trailing code for the whole column at once?

Accepted Answer

Flash Fill recognizes a pattern from a small number of manually entered examples and automatically extrapolates it to fill the rest of the column, which handles the variable-length pattern well. Manual retyping is inefficient and error-prone for large datasets. Find & Replace on the original column would destroy the source data rather than create a new extracted column. Widening a column only changes display width, not the underlying data.

Question 6

You are cleaning up a general ledger export before analysis. Which two actions directly help transform the raw transaction dates and combined text fields into a more usable, analysis-ready structure? Choose 2.

Accepted Answer

Date functions extract usable components (year, month, period) from transaction dates for grouping and analysis, and Text to Columns or text functions split combined fields into discrete, analyzable columns — both are core data transformation techniques. Row height, font changes, and print areas are cosmetic or output-formatting settings that do not change or restructure the underlying data.

Question 7

Before compiling a trial balance, you need to arrange the general ledger transactions by account number, and within each account, by transaction date from oldest to newest. What is the correct way to accomplish this in one operation?

Accepted Answer

Data > Sort with multiple sort levels (Account Number as the primary key, Date as the secondary key) sorts by account first and then chronologically within each account in a single operation. Sorting in two separate passes risks the second sort undoing the grouping from the first. AutoFilter hides rows but doesn't reorder them, and manual dragging is impractical and error-prone for ledger-sized datasets.

Question 8

You need to temporarily view only the transactions posted to the 'Accounts Payable' account so you can verify their balances, without deleting or permanently reorganizing the rest of the ledger. What should you use?

Accepted Answer

AutoFilter lets you temporarily display only rows matching a chosen criterion (like a specific account) while keeping all other data intact and easily restorable. Deleting rows permanently destroys data, which is not appropriate for a temporary review. Sorting reorders the ledger but still shows all accounts rather than isolating just Accounts Payable. Hiding columns hides fields, not specific rows.

Question 9

In your trial balance worksheet, you need a formula in cell D2 that returns the account name from a separate Chart of Accounts table based on the account number in cell A2, where the account number could be located in any row of that reference table. Which function is best suited for this lookup?

Accepted Answer

XLOOKUP (a lookup function) searches for a value in one range and returns a corresponding value from another range, which is exactly what's needed to retrieve an account name based on an account number. SUM performs arithmetic addition, not lookups. COUNTIF only counts matching occurrences rather than returning a related value. Nested IF statements for every possible account number would be impractical to build and maintain compared to a single lookup formula.

Question 10

You need a trial balance formula that returns 'Balanced' if the sum of all debit entries equals the sum of all credit entries, and 'Out of Balance' otherwise. Which combination of functions should you use in the result cell?

Accepted Answer

Nesting SUM inside an IF lets you compare the total debits against total credits and return a custom logical message, which is the standard math-plus-logical-function approach for trial balance checks. AVERAGE calculates a mean, not a balance check. VLOOKUP performs lookups, not summation or comparison. COUNTA counts non-blank cells, which is unrelated to verifying whether totals match.

Question 11

You are building an income statement and need a single cell that totals all revenue line items scattered across a non-contiguous set of cells (B4, B7, and B10) on the same worksheet. Which formula correctly aggregates just those three cells?

Accepted Answer

SUM accepts a comma-separated list of individual, non-adjacent cells and adds exactly those values together. SUM(B4:B10) would incorrectly include every cell in that range, not just the three revenue lines. Adding an extra +1 introduces an arithmetic error. AVERAGE calculates a mean rather than a total, which is not what's needed for an income statement aggregation.

Question 12

You are finalizing a balance sheet for external distribution and need negative values (such as a contra-asset or a net loss) to automatically display in red with parentheses, matching standard accounting presentation, without altering the underlying numeric value. What should you apply?

Accepted Answer

A custom Accounting or Number format (Format Cells) can display negative numbers in red and/or parentheses purely as a display style, while the underlying value remains a true, calculable negative number. Manually typing parentheses converts the value to text, breaking any formulas that reference it. Coloring the entire worksheet red would apply to all values, not just negatives. Find & Replace on minus signs would similarly corrupt the numeric data into text.

Question 13

You want to evaluate how efficiently a company converts sales into profit and need to calculate the net profit margin, one of the standard profitability ratios. Which formula correctly computes it, given Net Income in cell B10 and Total Revenue in cell B2?

Accepted Answer

Net profit margin is calculated as Net Income divided by Total Revenue (B10/B2), showing what percentage of revenue is retained as profit. Dividing Revenue by Net Income (B2/B10) inverts the ratio and produces a meaningless number for this purpose. Subtracting or multiplying the two values does not produce a profitability ratio at all.

Question 14

A controller asks you to calculate the current ratio to assess the company's short-term ability to pay its obligations, using Current Assets in cell B5 and Current Liabilities in cell B12. Which type of financial ratio is the current ratio, and what is the correct formula?

Accepted Answer

The current ratio (Current Assets divided by Current Liabilities) is a liquidity ratio, measuring a company's ability to cover short-term obligations with short-term assets. It is not a solvency ratio (which typically compares total debt to equity or assets for long-term stability) or a profitability ratio (which relates to income generation). Adding the two values together does not produce a meaningful ratio.

Question 15

You are calculating the debt-to-equity ratio to assess a company's long-term ability to meet its obligations, using Total Liabilities in cell B20 and Total Shareholders' Equity in cell B25. Which category of ratio is this, and how should it be calculated?

Accepted Answer

Debt-to-equity is a solvency ratio, calculated as Total Liabilities divided by Total Shareholders' Equity (B20/B25), and it evaluates a company's long-term financial stability and reliance on debt financing. It is not a liquidity ratio (which focuses on short-term obligations). Reversing the division (B25/B20) produces the inverse relationship, not the standard debt-to-equity ratio. It is also not a profitability ratio, so subtraction is inappropriate.

Question 16

While reviewing a financial statement, you want any expense cell that exceeds its budgeted amount in the adjacent column to automatically be highlighted in red, updating dynamically as figures change. Which feature should you use?

Accepted Answer

Conditional Formatting with a custom formula rule automatically re-evaluates and highlights cells whenever the underlying values change, making it ideal for flagging over-budget expenses dynamically. Manually applying fill color is a one-time, static action that will not update as values change. Data Validation restricts input but doesn't apply visual highlighting based on comparisons. Freeze Panes only affects scrolling visibility, not formatting.

Question 17

A cell in your financial statement is displaying #REF! and you need to visually trace which cells feed into that formula to identify where a referenced row was deleted. Which feature should you use?

Accepted Answer

Trace Precedents (in the Formula Auditing group) draws arrows to the cells that feed into the selected formula, helping you locate exactly where a broken reference originates, such as a deleted row causing a #REF! error. Freeze Panes only affects on-screen scrolling. Conditional Formatting changes cell appearance based on rules but does not trace formula relationships. Data Validation restricts input, unrelated to auditing existing formula errors.

Question 18

You need to build an interactive summary of quarterly revenue by region and product line for a management dashboard, allowing recipients to quickly change which time period is shown. Which two features should you combine to accomplish this? Choose 2.

Accepted Answer

A PivotTable summarizes large datasets by chosen dimensions (region, product line), and a connected Slicer provides an interactive, clickable filter (such as by quarter) that updates the PivotTable instantly, together forming an effective interactive dashboard element. Freeze Panes only affects scrolling and doesn't summarize or filter data. Manually retyping totals is inefficient and not interactive. Scale to Fit is a print-layout setting, unrelated to building interactive summaries.

Question 19

You are finalizing a financial dashboard that will be viewed by colleagues using screen readers. Which action most directly improves the accessibility of the workbook?

Accepted Answer

The Accessibility Checker (Review > Check Accessibility) identifies issues such as missing alt text, poor color contrast, and unclear reading order, and adding descriptive alt text lets screen readers convey the content and purpose of charts and images. Uniformly increasing font size doesn't address screen reader compatibility. Merging cells can actually confuse screen readers and navigation. Converting the dashboard into a single image would make the data completely inaccessible to screen readers, which cannot interpret image content as data.

Question 20

You are building a loan amortization schedule and need to calculate the fixed monthly payment for a $50,000 loan at a 6% annual interest rate over 5 years (60 months), where the payment is entered as cell B4 in your model. Which function should you use?

Accepted Answer

PMT calculates the fixed periodic payment required to pay off (or fund) a loan, given a periodic interest rate, number of periods, and present value, making it the correct function for computing the fixed monthly loan payment. FV calculates the future value of an investment, not a periodic payment. NPV discounts a series of cash flows to present value and requires a stream of values, not a single loan amount. RATE solves for the interest rate given other known variables, which isn't what's being asked for here.

Question 21

You are reconciling the company checkbook against the bank statement and need to identify checks that have been written and recorded in the ledger but have not yet cleared the bank. Which approach best identifies these outstanding checks in Excel?

Accepted Answer

Using a lookup or COUNTIF-based comparison between the ledger's issued checks and the bank's cleared checks automatically identifies which check numbers exist in the ledger but not yet on the bank statement — these are the outstanding checks. Comparing only summed totals doesn't identify individual outstanding items. Manual visual scanning is slow and error-prone for a large volume of transactions. Deleting old checks destroys ledger data rather than identifying which ones are still outstanding.

Question 22

You are preparing an accounts receivable aging report and need to categorize each open invoice into buckets such as 'Current', '1-30 Days', '31-60 Days', and 'Over 60 Days' based on how many days have passed since the invoice date. Which function is best suited to assign each invoice to the correct bucket?

Accepted Answer

Nested IF or IFS statements can evaluate how many days have elapsed since the invoice date (TODAY() minus the invoice date) against defined thresholds and return the appropriate aging bucket label, which is exactly what an aging report needs. SUM only totals amounts and doesn't categorize by age. TRIM and UPPER are text-cleanup functions unrelated to date-based categorization.

Question 23

You are calculating gross pay for hourly employees and need a formula that pays regular time for up to 40 hours per week and time-and-a-half for any hours beyond 40, using Hours Worked in B2 and Hourly Rate in C2. Which formula correctly implements this rule?

Accepted Answer

This formula correctly pays straight time (regular rate) for the first 40 hours and 1.5 times the rate for any hours over 40, matching standard overtime calculation rules. Simply multiplying all hours by 1.5 overpays regular hours. The third option pays straight time even when overtime hours exist, missing the overtime premium entirely. The fourth option (MIN(B2,40)*C2) only calculates pay for up to 40 hours and completely ignores any overtime hours worked.

Question 24

You are creating a straight-line depreciation schedule for a piece of equipment and need the periodic (annual) depreciation amount, given Cost in B2, Salvage Value in B3, and Useful Life in years in B4. Which function should you use?

Accepted Answer

SLN (Straight-Line depreciation) calculates equal periodic depreciation over an asset's useful life given cost, salvage value, and life, which is exactly what's required here. DB calculates depreciation using the fixed-declining balance method, which produces uneven, accelerated depreciation rather than straight-line amounts. NPER calculates the number of periods for an investment/loan, not depreciation. IRR calculates internal rate of return for a series of cash flows, unrelated to depreciation scheduling.

Question 25

You are reviewing payroll data for irregularities before final processing. Which two techniques would help you flag unusual entries, such as an employee with an unusually high number of hours or a pay rate that doesn't match the standard rate table? Choose 2.

Accepted Answer

Conditional Formatting can visually flag values (hours, rates) that fall outside defined normal ranges, and a lookup-based comparison against a standard rate table can catch rate mismatches for individual employees — both directly help identify payroll irregularities. Column width, workbook theme color, and Freeze Panes are display/navigation conveniences that do not analyze or flag anomalies in the underlying data.

MO-220 — Microsoft Office Specialist: Excel for Accounting Associate