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 > Quizzes for Business > Technology

Excel Intermediate Skills Assessment Quiz

Sharpen Your Excel Techniques In Minutes

Difficulty: Moderate
Questions: 20
Learning OutcomesStudy Material
Colorful paper art depicting a quiz on Excel Intermediate Skills Assessment.

This Excel Intermediate Skills Assessment helps you see how well you handle pivot tables, formulas, and data analysis across 15 quick questions. Get a clear score, spot gaps to practice, and if you want more focused drills, try the Excel skills quiz or the formula evaluation quiz .

On which Excel tab do you find the PivotTable command?
Data
Review
Formulas
Insert
The PivotTable command is located under the Insert tab in Excel. This tab contains tools to add tables, charts, and other elements.
Which argument of VLOOKUP specifies the column number to return?
range_lookup
table_array
lookup_value
col_index_num
The col_index_num argument tells VLOOKUP which column to return in the result. It must be a number relative to the leftmost column in the table array.
Which Conditional Formatting rule category should you use to highlight cells greater than a specific value?
Highlight Cells Rules
Color Scales
Data Bars
Icon Sets
The Highlight Cells Rules category includes the 'Greater Than' rule for formatting cells above a specific threshold. Other categories apply bars, scales, or icons.
Which Excel tool steps through a formula evaluation one operation at a time?
Trace Dependents
Show Formulas
Evaluate Formula
Error Checking
The Evaluate Formula tool walks through each part of a formula in sequence. It helps diagnose complex calculations by showing intermediate results.
Which Data Validation setting restricts entries to predefined items in a dropdown list?
Whole Number
List
Decimal
Date
The List validation type enforces that entries match one of the specified items and provides a dropdown. Other types limit numbers or dates.
To insert a slicer connected to a PivotTable, which tab do you use?
Insert
Formulas
PivotTable Analyze
Data
Slicers for PivotTables are added via the PivotTable Analyze tab in recent Excel versions. This tab provides tools specific to pivot analysis.
What issue occurs when VLOOKUP with range_lookup set to FALSE doesn't find the lookup_value?
Returns blank
Returns closest match
Returns 0
Returns #N/A
When range_lookup is FALSE (exact match), VLOOKUP returns #N/A if it cannot find the lookup value. This error indicates no exact match exists.
What does the MATCH function return when a lookup_value is found?
Matching cell value
Cell reference
Table range
Position within lookup array
MATCH returns the relative position of the lookup value in the lookup array. It does not return the cell's content or reference.
To shade the top 10% of numeric values in a range, which Conditional Formatting category do you choose?
Color Scales
Top/Bottom Rules
Highlight Cells Rules
Data Bars
The Top/Bottom Rules category includes an option to format the top 10% of values. Other categories apply gradient scales or bars.
Which auditing tool displays arrows pointing to cells that supply data to the selected cell?
Evaluate Formula
Show Formulas
Trace Dependents
Trace Precedents
Trace Precedents shows which cells feed into the selected cell's formula. Dependents show which cells rely on the active cell.
Where do you find the option to insert line sparklines?
Page Layout > Sparklines
View tab > Show Sparklines
Data tab > Analysis group > Sparklines
Insert tab > Sparklines group > Line
Line sparklines are inserted from the Insert tab under the Sparklines group. This group holds Line, Column, and Win/Loss sparkline types.
Which chart type is most appropriate for displaying trends over time?
Scatter Plot
Pie Chart
Histogram
Line Chart
Line charts are ideal for visualizing data trends across time periods. Pie charts show parts of a whole, while histograms show distributions.
When setting Data Validation to only allow whole numbers between 1 and 100, which validation type and limits should you choose?
List with values 1 - 100
Whole Number, between 1 and 100
Custom formula =AND(A1>=1,A1<=100)
Decimal, between 1 and 100
Choose Whole Number and set the minimum to 1 and maximum to 100. Other types either allow decimals or require list entries.
How do you group a PivotTable's date field by quarters?
Format cell as quarter
Right-click date field > Group > select Quarters
Use slicer with quarter grouping
Apply a date filter for each quarter
Right-clicking the date field and choosing Group lets you select Quarters. Filters and slicers do not perform grouping.
Which MATCH function match_type value finds an exact match and returns #N/A if none is found?
1
Omitted
0
-1
A match_type of 0 instructs MATCH to find an exact match and return #N/A if it cannot find one. Other values allow approximate matches.
For a two-dimensional lookup (matching both row and column headers), which function combination is best?
INDIRECT with ADDRESS
VLOOKUP with approximate match
HLOOKUP inside VLOOKUP
INDEX with MATCH nested twice
Using INDEX with two MATCH functions lets you lookup both row and column positions dynamically. Other methods are less efficient or more error-prone.
In Conditional Formatting, what formula would highlight cells in A2:A100 that are more than 30 days old?
=TODAY()-A2>30
=A2>30
=NOW()-A2>30
=A2
The formula =TODAY()-A2>30 flags cells older than 30 days. TODAY() returns the current date, so subtracting compares the age of each date.
When "Show Formulas" is enabled in Excel, what change occurs in the worksheet?
Formulas are displayed in cells instead of results
All errors are auto-fixed
Values are converted to text
Cell references are highlighted
Show Formulas toggles the display so you see the actual formulas in each cell. It does not change values or fix errors.
To display PivotTable values as a percentage of the column total, which Value Field Settings option do you choose?
Show Values As > % of Column Total
% of Grand Total
% of Row Total
Normal
Choosing % of Column Total calculates each cell as a percentage of its column's sum. Other options show different baselines.
Which Data Validation custom formula ensures each entry in column A is unique?
=UNIQUE(A:A)
=COUNTIF(A:A,A1)=1
=EXACT(A1,A:A)
=A1<>A2
The formula =COUNTIF(A:A,A1)=1 checks that the value in A1 appears only once in the column. Violations trigger a validation error.
0
{"name":"On which Excel tab do you find the PivotTable command?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"On which Excel tab do you find the PivotTable command?, Which argument of VLOOKUP specifies the column number to return?, Which Conditional Formatting rule category should you use to highlight cells greater than a specific value?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Learning Outcomes

  1. Analyse data sets using pivot tables and slicers
  2. Apply advanced formulas including VLOOKUP and INDEX/MATCH
  3. Master conditional formatting rules for visual insights
  4. Identify and fix formula errors with auditing tools
  5. Evaluate data trends using charts and sparklines
  6. Demonstrate data validation to control cell inputs

Cheat Sheet

  1. Master Pivot Tables and Slicers - Pivot tables are like your data's superhero, summarizing huge datasets into bite-sized reports in seconds. Slicers add a dash of interactivity by letting you filter results with a single click and some flair. Unleash their power and turn raw numbers into instant insights! Dive into Pivot Tables
  2. Understand VLOOKUP - VLOOKUP acts like a treasure hunter, scanning the first column for your target value and hauling back the corresponding prize from another column. Don't forget to switch range_lookup to FALSE for a perfect match every time. With VLOOKUP, your spreadsheets become a data discovery zone! Explore VLOOKUP
  3. Learn INDEX and MATCH - Think of INDEX & MATCH as the dynamic duo of data retrieval: INDEX grabs the value at a certain row and column, while MATCH finds where your value lives. Pair them up and you'll unlock more flexibility than VLOOKUP ever dreamed of. Level up your lookup game now! Master INDEX & MATCH
  4. Apply Conditional Formatting - Conditional formatting is like giving your spreadsheet X-ray vision, highlighting cells that meet your criteria in vibrant colors. Whether you're spotting high sales, overdue dates, or outliers, it's an instant visual upgrade. Play around with rules and watch patterns jump off the screen! Try Conditional Formatting
  5. Utilize Formula Auditing Tools - Formula auditing tools turn you into a formula detective, tracing precedents and dependents to reveal who's talking to whom in your sheet. Use Error Checking to catch sneaky mistakes before they cause chaos. Keep your calculations clean, accurate, and mystery-free! Audit Your Formulas
  6. Create Effective Charts - Charts are your storytelling toolkit, transforming dull numbers into eye-catching visuals like bar, line, or pie charts. Pick the right type, label axes clearly, and add a splash of color to guide your audience. Make every data point pop for maximum impact! Build Effective Charts
  7. Implement Sparklines - Sparklines are tiny, in-cell charts that pack a big punch, showing data trends at a glance without cluttering your sheet. Choose from line, column, or win/loss styles and embed them next to your numbers. They're perfect for quick comparisons and trend spotting! Add Sparklines
  8. Set Up Data Validation - Data validation is your guardrail against typos and wrong entries, letting you restrict cells to specific inputs like whole numbers, dates, or lists. Make dropdown menus for tidy choices or set custom formulas for extra checks. It's quality control with a click! Enforce Data Rules
  9. Practice Nested Functions - Nesting functions like IF, AND, OR, and NOT lets you build complex logic tests that adapt to multiple conditions. Mix them up to create custom outcomes - think "if sales >100 and region = North, show bonus." Get creative and watch your formulas flex! Try Nested Functions
  10. Explore Advanced Chart Features - Take your charts from good to great by adding trendlines, error bars, or secondary axes to highlight deeper insights. Simply right-click on elements to unlock these extra features. It's like giving your visuals a secret upgrade kit! Enhance Your Charts
Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Technology Quizzes

Powered by: Quiz Maker