Excel Competency Pre-test

30 question Excel assessment of skills learned.

This Excel Competency Pre-Test is designed to evaluate your current proficiency with a broad range of Microsoft Excel skills prior to instruction. The assessment measures your ability to analyze data using worksheet functions, create and manage lists, perform lookups, work with tables, PivotTables, and charts, apply text, date, logic, and financial functions, and format data appropriately. The results are used to identify strengths and skill gaps so that instruction can be paced and targeted effectively. This pre-test is diagnostic in nature and is not intended to reflect mastery or final performance.

Basic Analysis with Worksheet Functions
- Calculating totals, averages, minimums, and maximums using worksheet functions
- Performing percentage-based calculations (commission amounts) using cell references

Creating Lists and Data Validation
- Extracting unique values from a dataset
- Creating in-cell drop-down lists using data validation
- Selecting values from validated lists

Lookup Functions
- Using VLOOKUP to return related values from a data table
- Using XLOOKUP to retrieve matching records based on a lookup value

Excel Tables, Slicers, and Structured Data
- Converting a data range into an Excel table
- Adding and using a Total Row
- Applying slicers to filter data by category
- Summarizing table data using SUMIF

PivotTables and PivotCharts
- Creating PivotTables from source data
- Summarizing data by multiple dimensions (platform and genre)
- Adding slicers to PivotTables
- Creating PivotCharts to visualize summarized data

Date, Text, and Formatting Functions
- Displaying the current date and time using a formula
- Converting text to uppercase
- Combining text values with separators
- Centering text across a selection without merging cells
- Applying built-in cell styles

Financial Functions and Real-World Scenarios
- Calculating monthly loan payments
- Computing present value (PV) and future value (FV)
- Identifying required inputs for financial functions

Logic, Error Handling, and Randomized Inputs
- Writing IF formulas with multiple outcomes
- Categorizing numeric values based on thresholds
- Handling calculation errors with custom messages
- Rounding random values to whole numbers
- Generating numeric sequences

File Management and Submission
- Saving a workbook using the correct Excel file format (.xlsx)
- Preparing and submitting the completed file for grading

Step 1: Download the Excel Template

If you already downloaded the exercise from your dashboard, you may skip this step.

↓ Download the Excel Template

Most cells in the template are intentionally protected.

You do not need a password—only the color-coded cells are meant to be edited.

Step 2: Complete the assignment

Open the Excel Competency Pre-Test template and work through each task in order. For each item, either enter the required formula or result in the highlighted cells, or follow the instructions provided. If you are unsure how to complete a task, you may select “I’m not familiar with how to do this yet,” which counts as a valid response. Be sure to save your file as an Excel Workbook (.xlsx) as you work.

Step 3: Submit your completed spreadsheet via your dashboard

return to your dashboard

Keep these tips in mind to ensure clean submission of your work:

Double-check that your spreadsheet is not an .XLTX workbook.
Close Excel completely before attempting to upload your work.
Make use of the alternate submission methods if needed.

How This Assignment Is Graded

An automated tool is used to grade your work based on completion and accuracy, including partial points for incorrect steps. Your instructor determines how scores are applied toward course credit.

Need help?

Support

Support is email-based. In rare instances Zoom meetings are available when issues cannot be resolved otherwise.

Excel Competency Pre-Test Assignment