excel intermediate test answers

Excel Intermediate Test Answers

By Christopher Lewis
AD

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.

AD
Microsoft Excel Test Questions & Answers
Microsoft Excel Test Questions & Answers
How to Pass INTERMEDIATE EXCEL TEST FOR JOB INTERVIEW - Questions & Answers with Solutions
How to Pass INTERMEDIATE EXCEL TEST FOR JOB INTERVIEW - Questions & Answers with Solutions
Free Excel Test – Intermediate Excel Shortcuts Test – Excel 2010 & Excel 2013 – 10 Question Multiple Choice Quiz with Answers
Free Excel Test – Intermediate Excel Shortcuts Test – Excel 2010 & Excel 2013 – 10 Question Multiple Choice Quiz with Answers

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

the top 10 most asked excel interview questions and answers answer sheet for each question, click here
the top 10 most asked excel interview questions and answers answer sheet for each question, click here
Top 5 Advanced Excel Interview Questions and Answers
Top 5 Advanced Excel Interview Questions and Answers
Excel Skill Tests
Excel Skill Tests

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

AD

Formulas & Functions

Intermediate Excel users should be proficient in using various functions to perform complex calculations and operations.

the excel formulas chart with numbers and symbols for each item in this table, you can
the excel formulas chart with numbers and symbols for each item in this table, you can
a paper with the words ms word practical question
a paper with the words ms word practical question
the next 10 excel formulas are available for use on any computer or other device
the next 10 excel formulas are available for use on any computer or other device
the top 15 interview questions and answers for students to use in an exam or workbook
the top 15 interview questions and answers for students to use in an exam or workbook
the excel text formula is shown in green and white, as well as an excel
the excel text formula is shown in green and white, as well as an excel
How to Pass BASIC EXCEL TEST FOR JOB INTERVIEW - In 25 Simple Steps
How to Pass BASIC EXCEL TEST FOR JOB INTERVIEW - In 25 Simple Steps

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")

AD

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.

How to Pass EXCEL TEST FOR JOB INTERVIEW - VLOOKUP Questions and Answers with Solutions
How to Pass EXCEL TEST FOR JOB INTERVIEW - VLOOKUP Questions and Answers with Solutions

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.

AD

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.

AD