Excel Intermediate Test Answers
Creating an intermediate-level test for Excel users involves a blend of functional application and critical thinking. This guide walks through essential topics, questions, and answer explanations for crafting a comprehensive Excel intermediate test.
To begin, let's outline the key areas to cover in an Excel intermediate test, including data manipulation, formulas, functions, and charts. Each of these sections should comprise multiple-choice questions and exercise-based problems.
Data Manipulation
Intermediate Excel users should be adept at managing, cleaning, and transforming data. Test candidates' understanding of filtering, sorting, and removing duplicate records.
Filtering & Sorting
Q1: In a dataset, filter records where 'Sales' is greater than 5000 and sort them in descending order. Use a multiple-choice format and provide the correct filter and sort criteria.
Answer: Filter: 'Sales' > 5000, Sort: Largest to Smallest
Removing Duplicates
Q2: Remove duplicate entries from a list of customer email addresses in column A without cellulose.
Answer: Home > Remove Duplicates > Select 'Entire column A' > Remove Duplicates
Formulas & Functions
Intermediate Excel users should be proficient in using various functions to perform complex calculations and operations.
IF & VLOOKUP
Q3: Write a formula to return 'In Stock' if the stock quantity (cell B2) is greater than zero, else 'Out of Stock'. Use a drop-down list to present answer choices.
Answer: =IF(B2>0,"In Stock","Out of Stock")
AGGREGATE & UNIQUE
Q4: Calculate the sum of unique sales values in a range (e.g., A2:A100) after removing zeros. Provide a text box for the answer.
Answer: =AGGREGATE(15, 6, (B2:B100)/((B2:B100)=0))
Charts & Data Visualization
Intermediate users should understand how to create and format charts to effectively communicate data insights.
Bar Chart
Q5: Create a stacked 100% bar chart comparing sales from two regions (east and west). Provide a drop-down list with chart type options and ask test-takers to select the incorrect one.
Answer: Line (incorrect)
PivotTable & PivotChart
Q6: Create a PivotTable from a table of sales data and sum the sales figures by year and by region. Then, insert a PivotChart to visualize the data.
Answer: Insert PivotTable > Drag 'Year' and 'Region' to Rows > Drag 'Sales' to Values > Insert PivotChart > Select 'Bar' and adjust subtypes & labels as needed
Excel intermediate test creators should ensure questions progressively challenge users from beginner-to-intermediate levels. Encourage candidates to apply logical thinking and explore Excel's vast functionalities when crafting problems. By following this comprehensive approach, you'll create tests that effectively evaluate users' technical skills and provide valuable insights for targeted training and development.