Choose the right container
You want Sales, Products and Summary as separate tabs in a single saved file. What should you create?
Try this exercise ↗Learn by doing
Make your next mistake here.
It’s a good place for one. Try an exercise, get a useful hint and see why the answer works. Every exercise connects to a complete lesson.
0 of 165 exercises completed in this browser
Look behind a result
A cell displays 32. Where can you see whether it contains a formula?
Try this exercise ↗Build an analysis-friendly list
Which structure is best for a list of customer orders?
Try this exercise ↗Find the number of pens
Select the cell that contains the Units value for Pen.
Try this exercise ↗Write a range
Write the range for all three Units values, excluding the heading.
Try this exercise ↗Count a rectangle
How many cells are in A1:C4?
Try this exercise ↗Enter a real number
A customer buys twelve units. Enter the numeric quantity in B2. Do not include a unit or any words.
Try this exercise ↗Show a discount as a percentage
B2 stores 0.15. Choose the format that displays it as 15.0%.
Try this exercise ↗Preserve a product code
You need to enter code 00123 and preserve both leading zeros. What should you do before typing?
Try this exercise ↗Read the stored value
A cell stores 12.345 and displays 12.35. Which value does a formula normally use?
Try this exercise ↗Calculate a line total
Write a formula for D2 that multiplies Units in B2 by Price in C2. Use cell references.
Try this exercise ↗Calculate profit margin
Revenue is in B2 and cost is in C2. Write a formula for D2 that returns (revenue − cost) ÷ revenue. Enter a decimal result, not a value multiplied by 100.
Try this exercise ↗Check the order
What does =2+3*4 return?
Try this exercise ↗Find the cost per unit
B2 contains total cost and C2 contains units. Write a formula for D2 that calculates cost per unit.
Try this exercise ↗Total the recorded orders
Use SUM to calculate total orders in B2:B6. Friday is empty. Enter the formula for B8.
Try this exercise ↗Find the average
Use AVERAGE on B2:B6 to find the mean across recorded numeric days. Enter the formula for B9.
Try this exercise ↗Find the busiest day’s order count
Use MAX to return the largest numeric order count from B2:B6. Enter the formula for B10.
Try this exercise ↗Find the quietest recorded day
Use MIN to return the smallest numeric order count in B2:B6. Enter the formula for B11.
Try this exercise ↗Predict a relative copy
Copy =B2*C2 from D2 to D3. Which formula appears in D3?
Try this exercise ↗Keep the tax rate fixed
Write the tax formula for C2 using net price in B2 and the shared rate in E2. It must work when copied to C3.
Try this exercise ↗Read a mixed reference
Copy =B$1*$A2 from B2 to C3: one column right and one row down. Enter the copied formula.
Try this exercise ↗Fill a two-way grid
Prices are in B2:B3 and rates in C1:D1. Write C2 so it works when copied right to D2 and down to C3:D3.
Try this exercise ↗Count numeric quantities
Use COUNT on B2:B6 to count numeric quantity observations. Enter the formula for B8.
Try this exercise ↗Count populated quantity cells
Use COUNTA on B2:B6 to count all nonempty entries. Enter the formula for B9.
Try this exercise ↗Count order identifiers
Count the populated text identifiers in A2:A6 using COUNTA. Enter the formula for A8.
Try this exercise ↗Interpret a zero
A range contains the number 0, the text Pending and one truly empty cell. What does COUNT return?
Try this exercise ↗Label free delivery
B2 holds order value. Use IF to return Free when value is at least 100, otherwise Paid. Enter C2.
Try this exercise ↗Calculate the delivery charge
Use IF to return a numeric charge of 0 for values of at least 100, otherwise 5. B2 is order value; output is C2.
Try this exercise ↗Check payment status
B2 holds payment status. Return Release for Paid, otherwise Hold. Use IF in C2.
Try this exercise ↗Include the boundary
A score of 50 or above passes. Which test includes a score of exactly 50?
Try this exercise ↗Rank East orders
Filter Region to East, then sort Sales from largest to smallest. Check the resulting order list.
Try this exercise ↗Combine two conditions
Show East orders with Sales of at least 100, sorted by Sales descending.
Try this exercise ↗Protect your records
You select only the Sales column before sorting. Excel offers to expand the selection. What is the safe choice for this dataset?
Try this exercise ↗Understand a filtered total
After filtering Region to East, what does =SUM(D2:D6) normally include in Excel?
Try this exercise ↗Select the table range
Write the range for the entire displayed order list, including the heading row.
Try this exercise ↗Create the object
Which action creates an Excel table with structured references and filter controls?
Try this exercise ↗Explore a table filter
Filter Category to Stationery and sort Sales descending. Observe how complete records move together.
Try this exercise ↗Recognize a current-row reference
Inside a table calculated column, what does [@Units] refer to?
Try this exercise ↗Build a monthly trend
Choose a Line chart and set its title to Monthly orders. The preview updates as you work.
Try this exercise ↗Compare spending categories
Choose a Column chart with the title Actual spending to compare these four categories.
Try this exercise ↗Select the right range
Your chart source has headings in row 1, four monthly records in rows 2–5 and a grand total in row 6. Which range should a monthly trend chart use?
Try this exercise ↗Avoid exaggerating a comparison
You compare two categories, 95 and 100, in a column chart. Which value-axis baseline is normally appropriate?
Try this exercise ↗Find an incompatible input
B2 contains the text ten and C2 contains 4. =B2*C2 returns #VALUE! in Excel. What should you inspect first?
Try this exercise ↗Repair a misspelled function
The intended total formula was typed as =SMU(B2:B4). Write the corrected SUM formula for B6.
Try this exercise ↗Guard a zero divisor
B2 is total cost and C2 is units. Use IF to return Not ready when units are zero; otherwise return cost per unit. Output is D2.
Try this exercise ↗Investigate #####
A date cell shows ##### after you narrowed its column. What should you try first?
Try this exercise ↗Preserve the working model
A colleague needs all workbook sheets and editable formulas. Which format should you send?
Try this exercise ↗Define a print area
Your report occupies columns A through D and rows 1 through 8. Write the print-area range.
Try this exercise ↗Keep a long report readable
A report has 300 rows and six columns. It becomes tiny when fitted to one page. Which setup is usually more readable?
Try this exercise ↗Share a read-only result
A recipient should read the online workbook but not change it. Which permission should you choose?
Try this exercise ↗Total the budget
Use SUM to total Budget in B2:B5. Enter the formula for B8.
Try this exercise ↗Total actual spending
Use SUM on Actual in C2:C5. Enter the formula for C8.
Try this exercise ↗Calculate food variance
For Food on row 3, calculate Actual minus Budget in D3. A positive result means over budget.
Try this exercise ↗Label the category
For the Food category on row 3, use IF to return exactly "Over" when Actual in C3 exceeds Budget in B3, otherwise "Within". Food is the category name, not part of the returned label. Enter the formula in E3. Uppercase and lowercase labels are both accepted.
Try this exercise ↗Find money remaining
Income is in E2. Subtract total Actual spending in C2:C5. Enter the formula for E8.
Try this exercise ↗Present actual spending
Choose a Column chart and set the title to Actual spending. Use the category list without a grand total.
Try this exercise ↗Sum East revenue
Use SUMIF to sum Revenue for Region East. Enter the formula for G9.
Try this exercise ↗Count East orders
Use COUNTIF to count records with East in Region. Enter G10.
Try this exercise ↗Include the threshold
Count Revenue values of at least 100 with COUNTIF. Enter G11.
Try this exercise ↗Identify what was counted
A source has two lines for order S101 and both lines are East. What does COUNTIF(Region,"East") count?
Try this exercise ↗Sum East Online revenue
Use SUMIFS to total Revenue for rows that are both East and Online. Enter G9.
Try this exercise ↗Count a numeric band
Use COUNTIFS to count Revenue values from 80 through 150 inclusive. Enter G10.
Try this exercise ↗Add a third condition
Count East Online records whose Revenue is at least 100. Enter G11 using COUNTIFS.
Try this exercise ↗Spot double counting
You add East revenue to Online revenue to answer “East or Online.” Which records are counted twice?
Try this exercise ↗Retrieve the requested price
Use XLOOKUP with the requested SKU in E2. Return Price, or Not found for an unknown SKU. Enter G2.
Try this exercise ↗Preserve a real zero
Return Stock for E2 with XLOOKUP. Use Unknown SKU only when no product matches. Enter G3.
Try this exercise ↗Understand duplicates
Two source rows share SKU P200. With the default search mode, which matching row does XLOOKUP return?
Try this exercise ↗Choose a compatible method
Your recipient must edit the file in Excel 2019. Which plan is appropriate?
Try this exercise ↗Look up an exact price
Use VLOOKUP to return Price for the SKU in E2. Use exact matching. Enter G2.
Try this exercise ↗Return stock instead
Use exact VLOOKUP to retrieve Stock for E2 from the same table. Enter G3.
Try this exercise ↗Count inside the selected range
A VLOOKUP table starts in worksheet column D and ends in G. What column index returns worksheet column F?
Try this exercise ↗Avoid an accidental approximate lookup
You search a product code with VLOOKUP but omit range_lookup. What is the main risk?
Try this exercise ↗Release only ready orders
For row 2, return Release only when Paid and Stock available are both Yes; otherwise Hold. Enter F2 using IF and AND.
Try this exercise ↗Flag either reason for review
Return Review if Value is below 100 OR Paid is No; otherwise OK. Enter F3 using OR and IF for row 2.
Try this exercise ↗Respect decision order
If Paid is not Yes return Hold. Otherwise return Priority when Value is at least 100, or Standard below 100. Enter F4 for row 2.
Try this exercise ↗Choose meaningful test cases
A rule is AND(Value>=100,Paid="Yes"). Which pair is most useful for checking that both conditions are necessary?
Try this exercise ↗Clean an ordinary-spaced name
Use TRIM to clean Raw name in A2. Enter the formula for G2.
Try this exercise ↗Read the country prefix
Use LEFT to return the first two characters of Code in B2. Enter G3.
Try this exercise ↗Preserve the leading zero
Use MID to return the four-character identifier beginning at character 4 of B2. Enter G4.
Try this exercise ↗Join a name with an optional middle
Use TEXTJOIN with a single-space delimiter to join First C2, optional Middle E2 and Last D2 in that order. Skip empty cells. Enter G5.
Try this exercise ↗Measure after cleaning
Count the characters in A2 after TRIM removes extra ordinary spaces. Enter G6 using LEN.
Try this exercise ↗Choose a stable extraction rule
Codes sometimes have two-character country prefixes and sometimes three. What should you do before applying MID(code,4,4) to every row?
Try this exercise ↗Construct an unambiguous date
Use DATE with Year A2, Month B2 and Day C2. Enter G2. The browser will show the date serial; format it as a date in Excel.
Try this exercise ↗Find the month length
Use DAY and EOMONTH with DATE(A2,B2,C2) to return how many days are in that month. Enter G3.
Try this exercise ↗Count inclusive weekdays
Use NETWORKDAYS between the date on row 2 and the date on row 3, constructing each with DATE. No holidays apply. Enter G4.
Try this exercise ↗Build a time fraction
Convert Hours D2 and Minutes E2 into a fraction of one day. Enter G5 using cell references.
Try this exercise ↗Keep elapsed hours visible
An elapsed task lasts 27 hours. Which Excel format can display the total as 27:00 instead of wrapping after 24 hours?
Try this exercise ↗Check the status logic
For row 2, use IF to return Over when Actual exceeds Budget, otherwise Within. Enter E2.
Try this exercise ↗Anchor the comparison columns
A formula rule applies to A2:C4 and should compare Actual C with Budget B on each row. Which formula fits?
Try this exercise ↗Define the rule scope
The complete report has headers on row 1 and records in A2:C4. Enter the Applies to range for the data rows, without $ signs.
Try this exercise ↗Keep meaning accessible
You use an amber fill for over-budget rows. What useful addition avoids relying only on color?
Try this exercise ↗Check the numeric interval
Use IF and AND to label B2 In range when it is at least 1 and at most 100; otherwise Out of range. This task checks the interval only, not whether the number is whole. Enter F2.
Try this exercise ↗Choose the right rule type
A quantity must be an integer from 1 through 100. Which Excel validation setting fits?
Try this exercise ↗Reference the status list
Allowed statuses occupy D2:D4. Enter the plain A1 range for the list source, without $ signs.
Try this exercise ↗Review pasted data
Someone pasted quantities into validated cells. What should you do before relying on the data?
Try this exercise ↗Independently verify one PivotTable total
Use SUMIFS on the source to verify East Online Revenue. Enter G9. This calculates a check total; it does not create a native PivotTable.
Try this exercise ↗Choose field placement
You want regions down the page, channels across it and total revenue in the cells. Which setup fits?
Try this exercise ↗Investigate the wrong aggregation
Your PivotTable shows Count of Revenue, but the question asks for revenue amount. What should you check?
Try this exercise ↗Include new source records
A new order sits below a fixed A1:E7 source range. Refresh does not include it. What is the appropriate repair?
Try this exercise ↗Reconcile the revised grand total
Original Revenue totals 650. S101 changes from 120 to 140, with no other change. What grand total should the refreshed PivotTable show?
Try this exercise ↗Calculate East’s revenue share
Using the regional summary, divide East Revenue B2 by total Revenue B2:B4. Enter D2. Return a fraction, not a multiplied-by-100 value.
Try this exercise ↗Build a category comparison
Create a Column preview titled Revenue by region. Keep the three regions as categories.
Try this exercise ↗Build the monthly trend
Choose a Line chart titled Monthly revenue for the four chronological months.
Try this exercise ↗Choose a numeric relationship chart
You want to explore whether larger numeric order values are associated with longer delivery times. Which chart is the best starting point?
Try this exercise ↗Respect a meaningful denominator
You need revenue share, but the total revenue for a filtered period is zero. What should you do?
Try this exercise ↗Use a meaningful fallback
Return Cost B2 divided by Units C2, using IFERROR to return Check units on an error. Enter E2.
Try this exercise ↗Round a unit cost
Use ROUND to calculate B2/C2 to two decimal places. The test units are nonzero. Enter E3.
Try this exercise ↗Combine precision and error handling
Round unit cost B2/C2 to two decimals and return Check units if the calculation errors. Enter E4 using ROUND and IFERROR.
Try this exercise ↗Explain a rounding difference
You format =100/3 to show two decimals, but the stored result still sums differently from three values of 33.33. Why?
Try this exercise ↗Follow the selected segment
Sum Revenue for Region G2 and Channel G3 using SUMIFS. Enter I9.
Try this exercise ↗Calculate selected gross profit
For Region G2 and Channel G3, subtract selected Cost from selected Revenue. Enter I10 using SUMIFS.
Try this exercise ↗Calculate overall gross margin
Return overall gross profit divided by overall Revenue. Use the full source, not the selected segment. Tests have positive total revenue. Enter I11.
Try this exercise ↗Count selected records
Use COUNTIFS to count orders matching Region G2 and Channel G3. Enter I12.
Try this exercise ↗Compare overall margin with the target
Return On target when overall gross margin is at least target G4; otherwise Review. Tests have positive total revenue. Enter I13 using IF.
Try this exercise ↗Present the regional comparison
Use a Column chart titled Revenue by region for this summary. Do not add a grand-total category.
Try this exercise ↗Return the selected price
Use INDEX and exact MATCH to return the price for E2 from the product list. Enter F2.
Try this exercise ↗Look to the left
E2 contains the product name Cable pouch. Return its SKU from column A using INDEX and MATCH. Enter F2.
Try this exercise ↗Match a row and a month
Use INDEX with two exact MATCH functions to return demand for product F2 and month F3. Enter G2.
Try this exercise ↗Investigate a plausible wrong price
MATCH searches A2:A20 but INDEX returns from C3:C21. All formulas calculate. What is the main problem?
Try this exercise ↗Find the final listed price
Rows are oldest to newest. Find the last listed price for SKU E2, searching A2:A5 and returning C2:C5. Use Missing when absent. Enter G2.
Try this exercise ↗Match SKU and region
Find the first price where SKU equals E2 AND Region equals F2. Use Missing when absent. Enter G2.
Try this exercise ↗Apply a minimum-quantity schedule
Return the charge for quantity E2 using exact or next-smaller minimum quantity. If below every threshold, return Below schedule. Enter F2.
Try this exercise ↗Check what reverse search proves
A file has dates out of order. What does XLOOKUP with search_mode -1 return for duplicate IDs?
Try this exercise ↗Return a dynamic sorted column
Use SORT to return B2:B4 in descending order. Enter the spill formula at D2.
Try this exercise ↗List distinct regions
Return one of each region from A2:A5 in first-appearance order using UNIQUE. Enter C2.
Try this exercise ↗Clear an obstruction deliberately
A SORT formula at D2 needs D2:D8, but D5 contains a note. Excel reports #SPILL!. What should you do?
Try this exercise ↗Predict the occupied range
An array formula at F3 returns four rows and two columns. Write the complete spill range.
Try this exercise ↗Return East orders
Use FILTER to return complete East records from A2:C6. Use No orders when none qualify. Enter E2.
Try this exercise ↗Select with two conditions
Return complete records where Region is East AND Sales is at least 100. Use No orders when none qualify. Enter E2.
Try this exercise ↗Return sorted distinct regions
Return an alphabetically sorted distinct list from B2:B6 using SORT and UNIQUE. Enter E2.
Try this exercise ↗Choose the right uniqueness level
A2:C6 contains Order ID, Region and Sales. You need a distinct list of regions. What should UNIQUE receive?
Try this exercise ↗Name stock cover
Use LET to name stock cover from B2/C2 as cover, then return cover. Enter E2.
Try this exercise ↗Name net revenue
B2 is gross sales, C2 is discount rate and D2 is fixed cost. Use LET to calculate net=B2*(1-C2), then return net-D2 in F2.
Try this exercise ↗Guard and label cover
If C2 is zero, return No demand. Otherwise use LET to name cover=B2/C2 and return Review below 3 weeks, or Enough at 3 or more. Enter E2.
Try this exercise ↗Choose a safe local name
Which is an appropriate local LET name for inventory divided by weekly demand?
Try this exercise ↗Call a cover function
Write an immediately invoked LAMBDA with parameters stock and demand. Return stock/demand using B2 and C2 as its arguments. Enter E2.
Try this exercise ↗Handle zero demand
Write a directly called LAMBDA that returns No demand for demand zero, otherwise stock/demand. Supply B2 and C2. Enter E2.
Try this exercise ↗Parameterize a variance
B2 is budget and C2 is actual. Write an immediately invoked LAMBDA with parameters budget and actual returning actual-budget. Enter E2.
Try this exercise ↗Diagnose an uncalled function
You typed =LAMBDA(stock,demand,stock/demand) into a cell and see #CALC!. What is missing from a direct test?
Try this exercise ↗Correct the reusable process
You correct Eastt to East only in the query’s loaded worksheet output. What is the main risk on refresh?
Try this exercise ↗Preserve an identifier
A customer code is 00123 and the leading zeros identify the account. Which Power Query data type should you choose?
Try this exercise ↗Reconcile the initial result
The prepared table below shows Units and Unit price. Enter total Sales in F2; Sales equals Units × Unit price for each row.
Try this exercise ↗Check a refresh
The initial total Sales is 140. One new valid order has 2 units at 25 each. After refresh, what should total Sales be?
Try this exercise ↗Choose how to combine monthly exports
January and February contain different order rows with the same column headings. Which operation adds them into one order list?
Try this exercise ↗Reconcile appended sales
January Sales are 100, 50 and 80; February Sales are 40 and 70. After a correct append, enter total Sales.
Try this exercise ↗Detect a duplicated master key
Three order rows have SKUs P101, P102 and P103. Products has two P101 matches and one match for each other SKU. After a left merge and expansion of all matches, how many rows result?
Try this exercise ↗Keep unmatched orders visible
You need every order retained, even if its SKU is absent from Products. Which merge join is appropriate when Orders is the left table?
Try this exercise ↗Keep January periods separate
Data contains January 2025 and January 2026. You need a month-by-month trend across years. Which grouping preserves the intended periods?
Try this exercise ↗Calculate the regional share
B2 is East Sales and B3 is West Sales. Calculate East Sales divided by total Sales in D2. Return a decimal proportion, not text.
Try this exercise ↗Calculate a weighted margin
Order 1 has Sales 100 and Profit 20. Order 2 has Sales 900 and Profit 90. Enter overall margin as a decimal proportion.
Try this exercise ↗Verify slicer connections
A Region slicer changes one PivotTable but a second report stays unchanged. What should you inspect first?
Try this exercise ↗Choose the one-side key
Orders has many lines per SKU. Products should describe each SKU once. Which field must be unique for the intended one-to-many relationship?
Try this exercise ↗Interpret a filtered Sales measure
Office contains P101. Orders for P101 have Sales 100 and 200. P102 is Travel with Sales 900. After filtering Category to Office through a valid relationship, what is Total Sales?
Try this exercise ↗Interpret a total margin measure
The complete model has total Profit 150 and total Sales 1200. Enter DIVIDE(total Profit,total Sales) as a decimal proportion.
Try this exercise ↗Choose a context-aware calculation
Margin must recalculate for whichever categories a reader selects in a slicer. Which design expresses that requirement most directly?
Try this exercise ↗Calculate total attainment
Column B is Actual and column C is Target. Calculate total Actual divided by total Target in E2. Return a numeric proportion.
Try this exercise ↗Show a chronological trend
Choose a Line chart and title it Monthly net sales. The data is ordered January through April.
Try this exercise ↗Investigate inconsistent scope
The chart responds to an East slicer selection but a KPI tile still includes every region. What should you check?
Try this exercise ↗Choose an honest refresh note
A dashboard displays =TODAY() but the sales source has not refreshed for a week. Does that prove the sales data is current?
Try this exercise ↗Value the first SKU
Calculate stock value for A101 as Stock in B2 multiplied by Unit cost in E2. Enter F2.
Try this exercise ↗Calculate cover with a zero rule
For A102 on row 3, return No demand if Weekly demand C3 is zero; otherwise calculate Stock B3 divided by C3. Enter G3.
Try this exercise ↗Calculate the lead-time gap
For A102 on row 3, calculate Weekly demand × Lead weeks − Stock. Positive means a shortfall. Enter H3.
Try this exercise ↗Assign an explicit review status
For row 3 return No demand if C3=0. Otherwise return Review when B3
Return the positive-demand risk list
Use FILTER to return complete A2:E5 records where demand is positive AND Stock is below Weekly demand × Lead weeks. Use No risks when none qualify. Enter K2.
Try this exercise ↗Choose a justified next action
A104 has stock value 1600 and recorded weekly demand zero. What is the best next step from this snapshot alone?
Try this exercise ↗