UUS Practice Tests⌕ Search
IT Certifications

CompTIA Microsoft Excel Pro Competency Assessment Practice Test

The CompTIA Microsoft Excel Pro Competency Assessment practice page covers spreadsheet formulas, cell references, data summaries, lookup and text functions, number formatting, and worksheet protection. Questions focus on interpreting what a formula returns and choosing Excel features or settings to handle common calculation and data-management tasks.

Exam overview

Exam providerCompTIA
Exam codeNot listed
CategoryIT Certifications

About the CompTIA Microsoft Excel Pro Competency Assessment Exam

Work through decisions such as when to summarize tabular data with a PivotTable, how relative references change when formulas are filled, and which lookup approach retrieves a value from a specified column. Other practice scenarios involve extracting text, rounding values for later calculations, and distinguishing a cell’s displayed format from its stored value. Worksheet protection questions address locking formula cells while keeping designated input cells editable. These skills support building and maintaining workbooks where calculations remain consistent, results are readable, and users can update the intended data without overwriting formulas. The question bank also includes external workbook references and common display troubleshooting, such as widening a column when a numeric value appears as hash marks.

Sample Practice Questions

Review a selection from this exam's published US Practice Tests practice bank.

Question 1

In Excel, what does the formula =SUM(B2:B6) calculate?

  1. AThe average of the values in cells B2 through B6
  2. BThe total of the values in cells B2 through B6
  3. CThe number of nonblank cells in B2 through B6
  4. DThe largest value in cells B2 through B6
Show answerCorrect answer: B

SUM adds the numeric values in the specified range, including cells B2 through B6.

Question 2

Which Excel feature is designed to summarize and rearrange large sets of tabular data interactively, such as totaling sales by region?

  1. APivotTable
  2. BWordArt
  3. CGoal Seek
  4. DFlash Fill
Show answerCorrect answer: A

A PivotTable summarizes and analyzes tabular data by grouping fields into categories and calculating values such as sums or counts. Its layout can be rearranged interactively.

Question 3

A formula in C2 is =A2+B2. When you copy it down one row to C3, how will Excel normally adjust the formula?

  1. AIt becomes =A2+B2
  2. BIt becomes =A3+B3
  3. CIt becomes =$A$3+$B$3
  4. DIt becomes =A2+B3
Show answerCorrect answer: B

Relative cell references adjust by the same number of rows or columns as the formula is moved. Copying down one row changes A2 and B2 to A3 and B3.

Question 4

You need a formula to return the value from the third column of a lookup range for a matching item in its first column. Which function is traditionally designed for this lookup pattern?

  1. AVLOOKUP
  2. BLEFT
  3. CSUBTOTAL
  4. DTEXTJOIN
Show answerCorrect answer: A

VLOOKUP searches for a value in the first column of a table range and can return a value from a specified column to its right. Its column index identifies which column to return.

Question 5

Which formula extracts the first three characters from the text in cell D2?

  1. A=LEFT(D2,3)
  2. B=RIGHT(D2,3)
  3. C=MID(D2,3,1)
  4. D=LEN(D2,3)
Show answerCorrect answer: A

LEFT returns a specified number of characters starting at the beginning of a text string. LEFT(D2,3) returns the first three characters.

Question 6

You want to prevent a user from changing formulas in selected cells while still allowing edits in other cells on a protected worksheet. What is a suitable approach?

  1. AUnlock the cells intended for editing, then protect the worksheet
  2. BHide the worksheet tab, then save the workbook
  3. CApply a custom number format to the formula cells
  4. DTurn on Wrap Text for the entire worksheet
Show answerCorrect answer: A

Worksheet protection restricts editing of locked cells. Since cells are locked by default, first unlock the cells users may edit, then protect the worksheet to enforce that distinction.

Question 7

You want a cell to display a date as "15-Mar-2025" without changing the underlying date value. What should you change?

  1. AThe cell's number format
  2. BThe cell's formula
  3. CThe workbook's calculation mode
  4. DThe worksheet's tab color
Show answerCorrect answer: A

Number formatting changes how a stored value is displayed, not the underlying date serial value. Choose a date format that matches the desired appearance.

How to use this practice test

Access is free. Each session draws up to 20 random questions from the available 50-question bank and displays results after you finish. Use missed answers to identify which formula behavior, feature choice, or worksheet setting needs another review.

Preparation tips

  • Trace relative cell references before and after copying a formula to another row; compare them with absolute references when a calculation must stay anchored.
  • Practice SUM, ROUND, LEFT, and lookup formulas with small sample tables, then verify the returned value and the effect of the formula’s arguments.
  • Create a PivotTable from tabular data and summarize a numeric field by a category such as region; check that source fields are assigned to the intended areas.
  • Format a date and a numeric value without changing their underlying values, and distinguish formula rounding from changing displayed decimal places.
  • Unlock designated input cells, leave formula cells locked, and protect the worksheet; test which cells remain editable.
Official sourceOfficial exam page ↗