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 > Programming & Software

Intermediate Excel Test: VLOOKUP and Pivot Tables

Quick, free intermediate Excel test with instant results and feedback.

Editorial: Review CompletedCreated By: Esa AndikaUpdated Aug 26, 2025
Difficulty: Moderate
2-5mins
Learning OutcomesCheat Sheet
Paper art illustration for Excel skills quiz on coral background, testing formulas, functions, data tools.

This intermediate Excel test helps you practice VLOOKUP and Pivot Tables, plus filters, charts, and more. Work through real tasks and get instant feedback to spot gaps and build speed. For extra practice, try the pivot table practice questions, take our intermediate excel skills test, or review basics with a free excel practice test.

What Excel function is used to sum a range of cells?
SUM
MIN
AVERAGE
COUNT
The SUM function is used to add together all numbers in a specified range of cells. It is the most direct way to calculate total values without manual addition. Other functions like MIN, COUNT, and AVERAGE serve different purposes such as finding the smallest value, counting numeric entries, or computing the mean, respectively. More on SUM function
What does the cell reference $A$1 represent?
Absolute reference
Absolute row and relative column
Relative row and absolute column
Relative reference
A reference with dollar signs before both the column and row locks that reference when a formula is copied or filled. This is called an absolute reference, ensuring the cell always points to A1 regardless of where the formula moves. Without dollar signs, references adjust relative to the formula's new position.
Which keyboard shortcut inserts a new Excel table?
Ctrl+T
Ctrl+Shift+T
Alt+N+T
Ctrl+L
Pressing Ctrl+T opens the Create Table dialog, converting a selected range into an official Excel Table. Excel Tables offer benefits like structured references, automatic filtering, and styled formatting. While older versions sometimes accepted Ctrl+L, Ctrl+T is the standard shortcut in modern Excel.
Which chart type is best suited for illustrating the parts of a whole in Excel?
Line chart
Scatter chart
Pie chart
Bar chart
A pie chart displays each category's proportion of the total as slices of a circle, making it ideal for part-to-whole visualization. Line charts show trends over time, bar charts compare values across categories, and scatter charts display relationships between two numeric variables. Choosing a pie chart clearly illustrates how each component contributes to the whole.
You want to sum values in column B only where column A equals "East". Which function correctly achieves this?
=SUMIF(A:A,"East",B:B)
=SUMIF(B:B,"East",A:A)
=SUM(A:A,"East",B:B)
=SUMIF("East",A:A,B:B)
The SUMIF function uses the syntax SUMIF(range, criteria, sum_range). Here, range is A:A (where you look for "East"), criteria is "East", and sum_range is B:B (values to sum). Placing these arguments in the correct order ensures only the matching B column values are added.
In VLOOKUP, what does setting the range_lookup argument to FALSE enforce?
Approximate match
Exact match lookup
Sorted lookup only
Wildcard match
When range_lookup is set to FALSE in VLOOKUP, Excel searches for an exact match of the lookup value. If no exact match is found, the function returns an #N/A error. Using TRUE or omitting this argument enables approximate matching, requiring sorted data.
Which Excel function conducts a case-insensitive search for a substring within text?
FIND
LEN
MID
SEARCH
The SEARCH function returns the position of a substring within text without distinguishing case. In contrast, FIND is case-sensitive and will only locate exact case matches. MID extracts text at a given position and LEN returns text length, so they don't perform searches. SEARCH function guide
In a PivotTable Field List, to display a field's unique items as row labels, you would drag the field into which area?
Rows area
Columns area
Values area
Filters area
Dragging a field into the Rows area of the PivotTable Field List arranges each unique item in that field as a separate row label. The Columns area does the same horizontally, while the Filters area applies top-level filtering, and the Values area is for aggregations. Placing fields correctly ensures your data is displayed in the desired orientation.
What is a key advantage of the XLOOKUP function compared to VLOOKUP?
It only supports approximate matches
It requires data to be sorted
It can look up values to the left of the lookup column and defaults to exact match
It returns only the first match in a range
XLOOKUP overcomes several VLOOKUP limitations: it can search both left and right of the lookup column and defaults to exact match when the match_mode argument is omitted. It also offers built-in support for partial matches and error handling. This makes it a more versatile and robust lookup function.
What result does the formula =SUMPRODUCT((A1:A10>5)*(B1:B10<10)) return?
The sum of all values in A1:A10 and B1:B10
A #VALUE! error due to incorrect syntax
The product of all values satisfying the conditions
The number of rows where A1:A10 is greater than 5 and B1:B10 is less than 10
SUMPRODUCT multiplies corresponding elements of its array arguments and then sums the results. By using Boolean expressions (which evaluate to 1 or 0), the formula counts rows meeting both criteria: A>5 and B<10. The multiplication of the two arrays yields 1 only for rows that satisfy both conditions.
Which function would you use to extract the third largest value from a range in Excel?
SMALL(range, 3)
RANK.AVG(range, 3)
LARGE(range, 3)
RANK.EQ(range, 3)
The LARGE function returns the nth largest value in a data set; specifying 3 returns the third largest. The SMALL function returns the nth smallest value, while RANK functions return the rank of a particular number rather than the value itself. Using LARGE(range, 3) directly fetches the desired entry. Large function guide
Which function introduced in recent Excel versions allows you to assign names to calculation results within a single formula to improve readability?
DEFINE.NAME
NAMES
LET
LAMBDA
The LET function enables you to declare variables and assign them names within a formula, making complex calculations clearer and often more efficient. These named variables can then be reused multiple times in the same formula. LET enhances readability and can improve performance by reducing redundant calculations. LET function reference
0
{"name":"What Excel function is used to sum a range of cells?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"What Excel function is used to sum a range of cells?, What does the cell reference $A$1 represent?, Which keyboard shortcut inserts a new Excel table?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Study Outcomes

  1. Apply Advanced Excel Formulas -

    Use functions like VLOOKUP, INDEX/MATCH, and nested IF statements to manipulate and analyze data effectively.

  2. Create and Customize PivotTables -

    Build and format PivotTables to summarize large datasets and gain insights with dynamic data views.

  3. Use Data Analysis Tools -

    Leverage features such as Goal Seek, Data Tables, and Solver to perform what-if analyses and optimize solutions.

  4. Interpret and Troubleshoot Complex Formulas -

    Identify errors in formulas, use auditing tools, and refine calculations for accurate results.

  5. Analyze Data with Conditional Formatting -

    Apply rules and custom formats to highlight trends, outliers, and key metrics in your spreadsheets.

  6. Evaluate and Optimize Spreadsheet Performance -

    Assess workbook efficiency, remove redundancies, and implement best practices for faster calculations.

Cheat Sheet

  1. SUMIFS and COUNTIFS for Conditional Aggregation -

    SUMIFS and COUNTIFS let you perform multi-criteria sums and counts in your excel test intermediate scenarios with precision. For example, =SUMIFS(C2:C100, A2:A100, "East", B2:B100, ">1000") sums sales over 1000 in the East region. According to Microsoft support, these functions are essential for advanced data filtering.

  2. INDEX-MATCH for Dynamic Lookups -

    INDEX-MATCH is a powerful alternative to VLOOKUP, offering left-side lookups and better performance on large tables. Use =INDEX(B2:B100, MATCH("Widget", A2:A100, 0)) and remember the mnemonic "Match first, then Index." Leading university Excel courses (e.g., Harvard's data science toolkit) recommend this duo for reliability.

  3. Pivot Tables for Rapid Data Summaries -

    Pivot Tables transform raw data into meaningful reports in seconds - just drag fields to Rows, Columns, Values, and Filters. They power your excel test intermediate by enabling dynamic grouping, subtotals, and drill-downs. The official Microsoft Excel guide highlights Pivot Tables as a cornerstone of data analysis.

  4. Data Validation for Input Control -

    Use Data Validation to restrict cell entries and reduce errors, such as creating a drop-down list with =INDIRECT("ListRange"). This tool is crucial for maintaining data integrity in intermediate to advanced spreadsheets. Top institutions like Stanford emphasize its role in professional reporting.

  5. Conditional Formatting with Custom Formulas -

    Conditional Formatting lets you highlight trends or outliers using formulas, e.g., =B2>AVERAGE($B$2:$B$100) to flag above-average values automatically. It adds visual cues to your excel test intermediate exercises and speeds up pattern recognition. Industry best practices from Gartner cite its impact on decision-making speed.

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

Programming & Software Quizzes

Powered by: Quiz Maker