Start from here. Advanced Excel · 12 questions, about 8 minutes.
A starting point for the workshop. Answer without looking things up: “I do not know” is a useful answer.
The first 9 questions cover foundations. The last 3 preview the course and have a separate score. This is guidance, not a grade.
No name or email. At the end you submit answers and your self-rating to update your group’s counts. The server keeps aggregate counts and a hash of a random code to prevent duplicate submissions, not an individual answer record. Your browser stores the attempt code. Class counts are public.
Enable JavaScript for the interactive quiz, or use these questions to review.
Calculate revenue B2 holds the quantity sold (10) and C2 the unit price (€5). Which formula returns revenue?
Answer =B2*C2. Revenue = quantity × unit price: 10 × 5 = €50. Cost is not needed to calculate revenue.
Copy a formula You enter =B2*$F$1 in D2 and copy it one row down to D3. What does it become?
=B2*$F$1 =B3*$F$2 =B3*$F$1 =B2*$F$2 Answer =B3*$F$1. B2 is relative and becomes B3. Both $ symbols lock F1's column and row, so $F$1 stays unchanged.
Read a range A2, A3 and A4 contain 10, 20 and 30 respectively. What does =SUM(A2:A4) return?
60 40 3 An error: A3 is missing from the formula Answer 60. The colon includes the full range and both endpoints: A2, A3 and A4. The sum is 60.
Organise sales data You want to analyse sales by date, customer and product. Which source structure works best?
A sheet per customer with dates in headings Detail rows mixed with subtotals and merged cells One row per sales line, consistent columns and one header block A summary table with only the annual total Answer One row per sales line, consistent columns and one header block. A consistent rectangular source supports filtering, aggregation and imports. Subtotals and merged cells may belong in the presentation, not in the source detail.
Sort safely A table contains Customer, Date and Amount. You want to sort by Amount. How do you preserve the relationships?
Sort only the Amount cells Sort the whole table using Amount as the key Sort each column separately First copy all amounts into a new column Answer Sort the whole table using Amount as the key. Cells in each row must move together. Sorting one column alone can associate an amount with the wrong customer.
An unexpected total A2 holds the number 10. A3 holds '20, with a leading apostrophe: it is text. =SUM(A2:A3) returns 10. What should you check first?
Change A3's colour Round the total Increase the number of decimals Check and convert A3's data type Answer Check and convert A3's data type. SUM ignores text in cell references. A value can look numeric while being stored as text; changing its appearance alone does not guarantee conversion.
Filter data You filter a table to show January sales only. What happens to February rows?
The filter hides them, but they remain in the source They are permanently deleted They move to another sheet They become empty rows Answer The filter hides them, but they remain in the source. A filter changes what you see without deleting rows. Clear it to see February again.
Sum one category A2:A100 holds categories and B2:B100 holds amounts. Which function is designed to sum amounts for the category “Books” only?
COUNTA AVERAGE SUMIF COUNTIF Answer SUMIF. SUMIF sums values matching a criterion. COUNTIF counts matching rows rather than summing their amounts.
Choose a summary You want total revenue by category in a PivotTable. Which setup answers the question?
Category in Values as Count; Revenue in Rows Category in Rows; Revenue in Values as Sum Revenue in Values as Average, with no category Only a customer-name filter Answer Category in Rows; Revenue in Values as Sum. Category groups the rows, and Sum of Revenue gives each group's total. Count and Average answer different questions.
Link two tables You want to bring costs from Products into Sales. Names may change and two products can have similar names. Which key is best?
The row position in each table The product cell's colour An abbreviated product name A stable product ID, unique in Products and present in Sales Answer A stable product ID, unique in Products and present in Sales. A stable key identifies the product even when its name or position changes. It should have one match in the Products catalogue.
Add a month January and February have the same columns. You want one list with all sales, stacked vertically. Which operation do you need?
Append the rows Merge columns using an ID Average the two months Transpose February's columns Answer Append the rows. Append adds rows from tables with compatible structures. Merge links information through a key, which is a different operation.
One sale becomes two A sale has product ID P01. P01 appears twice in the catalogue. After a left merge and expanding the matches, what can happen?
The merge automatically removes the duplicate catalogue row Excel automatically adds both costs The sale may appear twice and inflate totals A duplicate can never affect the result Answer The sale may appear twice and inflate totals. The sale has two matches. Expanding them can create two rows: verify key uniqueness and compare row counts and totals before and after the merge.