Unlock hundreds more features
Save your Quiz to the Dashboard
View and Export Results
Use AI to Create Quizzes and Analyse Results

OR
Sign inSign in with Facebook
Sign inSign in with Google
Quizzes > Computers & Technology > Programming & Software

Advanced Excel Mock Test: Test Your Expertise!

Ready for a free online Excel test? Take this Excel mock test and show off your advanced skills!

Difficulty: Moderate
2-5mins
Learning OutcomesCheat Sheet
Online Excel mock test paper art illustration on a dark blue background

Use this free Excel mock test to check your skills with advanced formulas, data analysis, and key functions. Get instant feedback and simple progress tracking to spot gaps before an exam or work task. For extra practice, try this practice exam or a quick practice quiz .

What function counts the number of cells within a range that meet a single criterion?
COUNTIF
COUNTA
COUNT
SUMIF
The COUNTIF function counts cells that satisfy a specified condition within a given range. It requires two arguments: the range to search and the condition to apply. For example, =COUNTIF(A1:A10,">20") returns the number of cells greater than 20 in that range. Learn more at .
Which function sums all values in a range that meet a single criterion?
SUM
SUMIF
AVERAGEIF
COUNTIF
SUMIF adds up all numbers in a range that meet a specified condition. It takes three arguments: the range, the criterion, and the sum_range (which can be the same as the range). For example, =SUMIF(B1:B10,">100",C1:C10) sums C1:C10 where B1:B10 exceed 100. See details at .
Which of the following represents a valid absolute cell reference in Excel?
$A1
$A$1
A1
A$1
An absolute reference locks both the column and row with dollar signs. $A$1 always points to cell A1, even if the formula is copied elsewhere. A1 is a relative reference, and $A1 or A$1 lock only one dimension. More at .
Which feature allows you to keep specific rows or columns visible while scrolling through a worksheet?
Protect Sheet
Auto Filter
Split Cells
Freeze Panes
Freeze Panes locks selected rows and/or columns so they remain visible when you scroll. You can freeze the top row, the first column, or a combination by selecting a cell and choosing Freeze Panes. Visit for more.
Which function concatenates (combines) text from multiple cells into one string?
TEXTJOIN
& operator
CONCATENATE
JOIN
CONCATENATE joins text from different cells into one text string. Although newer versions offer CONCAT or TEXTJOIN, CONCATENATE is widely used for backward compatibility. For example, =CONCATENATE(A1," ",B1) merges A1 and B1 with a space. More info at .
Which function returns the current date in Excel?
DATE()
NOW()
DATEVALUE()
TODAY()
TODAY() returns the current date without a time component. NOW() returns both date and time. DATE() constructs a date from year, month, and day inputs, while DATEVALUE() converts a date text into a serial number. Read more at .
Which feature applies formatting rules to cells based on their values?
Data Validation
Conditional Formatting
Format Painter
Cell Styles
Conditional Formatting changes cell formatting automatically based on value-based rules you define. You can highlight cells greater than a threshold, apply data bars, color scales, and icon sets. Data Validation controls data entry rather than formatting. See .
Which keyboard shortcut inserts a new worksheet into the current workbook?
Shift+F11
Ctrl+N
Ctrl+Shift+N
Alt+F1
Pressing Shift+F11 inserts a new worksheet before the active sheet. Ctrl+N creates a new workbook, Alt+F1 inserts a chart, and Ctrl+Shift+N has no default action in Excel. A full list is at .
Which function returns the relative position of an item within an array or range?
MATCH
FIND
LOOKUP
INDEX
MATCH returns the position of a lookup value in a range, with options for exact or approximate match. INDEX retrieves a value by row and column but does not return position. FIND locates text within a string, while LOOKUP has different syntax. More at .
To perform a two-dimensional lookup by matching both row and column headings, which combination is most appropriate?
SUMIFS
INDEX and MATCH
VLOOKUP only
HLOOKUP only
Combining INDEX with two MATCH functions allows a lookup based on both row and column criteria. VLOOKUP and HLOOKUP only handle one-dimensional searches. SUMIFS sums values but doesn't return specific intersection cell values. See .
Which dynamic array function in Excel 365 returns the unique values from a range?
DISTINCT
FILTER
UNIQUE
SORT
UNIQUE returns a list of distinct values from the input range, automatically spilling results into adjacent cells. FILTER extracts values based on criteria, SORT orders values, and DISTINCT is not a built-in Excel function. More at .
In XLOOKUP, which argument specifies what to return if no match is found?
search_mode
match_mode
return_array
if_not_found
The optional if_not_found parameter lets you provide a custom return when XLOOKUP doesn't find a match. match_mode controls match type, search_mode sets search order, and return_array defines the return range. More details at .
Which What-If Analysis tool creates a table of results by varying one or two inputs?
Solver
Data Table
Goal Seek
Scenario Manager
Data Tables allow you to see how changing one or two variables affects results in a formula. Goal Seek solves for a single target value, Solver handles multi-variable optimization, and Scenario Manager stores sets of input values. Learn more at .
Which feature automatically fills in data when it detects a pattern as you type?
Text to Columns
AutoFill
Fill Series
Flash Fill
Flash Fill identifies patterns in data entry and completes the remaining cells automatically. AutoFill copies or extends cell contents following simple series patterns. Fill Series handles numeric lists, and Text to Columns splits cell content. Details at .
Which tool removes duplicate rows from a selected range or table?
Text to Columns
Advanced Filter
Remove Duplicates
Consolidate
Remove Duplicates checks selected columns and deletes duplicate rows, leaving only unique entries. Text to Columns splits cell content, Consolidate merges data, and Advanced Filter can extract unique records but requires extra setup. More at .
Which function multiplies corresponding components in given arrays and returns the sum of those products?
SUMPRODUCT
SUM
PRODUCT
MMULT
SUMPRODUCT multiplies elements of matching positions in provided arrays and sums the results. MMULT performs matrix multiplication but returns an array, not a scalar sum. PRODUCT multiplies all arguments, and SUM simply adds them. See .
Which function is commonly used to create a dynamic named range that expands as data is added?
COUNTA
OFFSET
INDIRECT
INDEX
OFFSET returns a range reference that can change size based on height and width arguments, making it ideal for dynamic ranges. INDEX returns a static reference, INDIRECT converts text to a reference, and COUNTA counts non-blank cells. For more, visit .
Which function returns a reference based on a text string, allowing dynamic sheet or range references?
INDIRECT
CELL
ADDRESS
HYPERLINK
INDIRECT interprets a text string as a cell or range reference, enabling formulas to refer to dynamically generated names or sheets. ADDRESS returns a cell address as text, CELL returns info about a cell, and HYPERLINK creates clickable links. See .
Which Excel 365 function generates an array of sequential numbers based on specified rows and columns?
RANDARRAY
SEQUENCE
ROW
COLUMN
SEQUENCE returns an array of sequential numbers in spill range form, with parameters for rows, columns, start, and step. RANDARRAY generates random numbers, while ROW and COLUMN return row or column numbers of a reference. More at .
Which function lets you assign names to calculation results inside a formula for reuse and readability?
LAMBDA
DEFINE
LET
NAME
LET allows you to define variables inside a formula, improving performance and readability by reusing expressions. LAMBDA creates custom functions, while NAME and DEFINE are not Excel functions. Details at .
What does the formula =SUM(Jan:Mar!B2) accomplish in Excel?
Adds values in B2 on the Jan, Feb, and Mar sheets only if they are consecutive
Counts nonblank cells in cell B2 for sheets Jan, Feb, and Mar
Sums cell B2 across all worksheets from Jan through Mar
Calculates the sum of range Jan to Mar in the active sheet
Using a 3D reference like Jan:Mar!B2 sums cell B2 across all sheets between and including Jan and Mar. This is useful for consolidating similar layouts across multiple tabs. More details at .
Which formula returns the median of values in A1:A10 but ignores zeros by using an array calculation?
=MEDIAN.A(A1:A10)
=MEDIAN(A1:A10,0)
=MEDIAN(IF(A1:A10>0,A1:A10))
=AGGREGATE(12,6,A1:A10)
An array formula like =MEDIAN(IF(A1:A10>0,A1:A10)) filters out zeros before calculating the median. MEDIAN(A1:A10,0) includes zeros, MEDIAN.A is not valid, and AGGREGATE with function number 12 returns median but cannot exclude zeros conditionally. See .
Which function allows you to create custom, reusable functions directly in a cell without VBA?
LAMBDA
MAKEFUNCTION
DEFINE.NAME
LET
LAMBDA lets you define custom functions with parameters in a cell or named range, eliminating the need for VBA. LET only defines variables inside a formula, and DEFINE.NAME or MAKEFUNCTION are not valid Excel functions. Learn more at .
In structured references, what does the '@' symbol denote in a table formula?
Implicit intersection with the current row
Union of two ranges
Absolute reference to the column
Error in the formula
The '@' in a structured reference forces implicit intersection, returning the value from the same row in the specified column. It is not an absolute reference or error indicator. You can read more at .
In Power Query's M language, which function merges two tables based on matching columns?
CombineTables
MergeTables
Table.Join
Table.Merge
Table.Join combines two tables by matching rows in specified columns, similar to SQL joins. Table.Merge is not a direct M function, and MergeTables or CombineTables are not valid. Documentation is available at .
0
{"name":"What function counts the number of cells within a range that meet a single criterion?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"What function counts the number of cells within a range that meet a single criterion?, Which function sums all values in a range that meet a single criterion?, Which of the following represents a valid absolute cell reference in Excel?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Study Outcomes

  1. Understand advanced formula construction -

    Master nested functions and dynamic arrays to solve complex calculations quickly during the Excel mock test.

  2. Apply data analysis techniques -

    Use tools like pivot tables, scenarios, and data tables to perform in-depth analysis on large datasets in a free online Excel test environment.

  3. Analyze and visualize data insights -

    Implement conditional formatting, charts, and Power Query to reveal trends and patterns in your data for any Excel test online.

  4. Leverage advanced Excel functions -

    Utilize LOOKUP, statistical, and financial functions to enhance your problem-solving speed and accuracy in the Excel exam online.

  5. Evaluate your proficiency -

    Assess your strengths and identify improvement areas through detailed scoring feedback in this excel online test.

  6. Navigate test interface confidently -

    Familiarize yourself with the Excel mock test layout and timing to optimize performance under exam conditions.

Cheat Sheet

  1. Advanced Lookups with XLOOKUP -

    XLOOKUP replaces VLOOKUP with more flexibility, allowing searches in any direction and returning entire arrays. For example, =XLOOKUP(A2,Products[ID],Products[Name],"Not Found",0) finds IDs left or right. As per Microsoft documentation, XLOOKUP also handles exact, approximate, and wildcard matches seamlessly.

  2. Harnessing Dynamic Array Functions -

    Dynamic arrays like FILTER, UNIQUE, and SORT auto-spill results into adjacent cells, removing manual range management. For instance, =FILTER(Table1,Table1[Sales]>1000) instantly extracts all high-value sales rows. According to Microsoft 365 docs, these spill functions revolutionize data analysis by keeping reports current without extra formulas.

  3. Power Pivot and DAX Fundamentals -

    Power Pivot lets you build relational data models and write DAX measures for robust calculations; e.g., =CALCULATE(SUM(Sales[Amount]),YEAR(Sales[Date])=2023) returns 2023 revenue. Research from SQLBI emphasizes that CALCULATE is pivotal for context-aware aggregations. Creating proper table relationships in the Data Model dramatically speeds up reporting.

  4. Leveraging SUMPRODUCT for Conditional Analysis -

    SUMPRODUCT performs multi-criteria sums without array formulas; for example, =SUMPRODUCT((Region="West")*(Sales>5000)*Quantity) totals units sold in the West above $5,000. Cornell University's business analytics materials highlight SUMPRODUCT's power in cross-criteria reporting. Recall "Sum up product of conditions" as a handy mnemonic.

  5. Power Query M Language Tips -

    Use Power Query's M language to clean and transform data before loading into Excel; e.g., =Table.SelectRows(Source, each [Status]="Active") filters only active records. According to Microsoft Power Query docs, learning functions like Table.Group automates complex summarizations. A great trick is reviewing recorded query steps to master advanced M syntax.

Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Programming & Software Quizzes

Powered by: Quiz Maker