Build a formula-driven KPI dashboard panel
AdvancedFormula-driven KPI panels let a sales table feed live reporting without any manual recaps. In practice, that means leaders can review revenue trends, order volume, and product performance from one compact section while the source transactions keep changing.
Build the dashboard formulas in B19:B24 using the raw order data in A1:E16. The KPI labels in A19:A24 define the outputs your panel needs to return, and each result should stay linked to the source table rather than being typed manually.
Keep the panel fully dynamic. If values in C2:C16 or E2:E16 change, the metrics in B19:B24 should recalculate automatically, including the product-level winner and its revenue.
For the text result in B24, return the winning product name exactly as it appears in C2:C16. The only valid source labels are Wireless Mouse, USB-C Dock, Monitor Arm, Webcam, Keyboard, and Laptop Stand.
Need some help?
Hint 1
The first four KPIs each come from one column and one function. Only the last two are harder. For those, you first have to add up the rows that belong to each product.
Hint 2
The biggest single order is not the same as the top product. One big sale can still lose to a product that sells well again and again. So add up the revenue per product first. Then compare those totals.
Hint 3
Start with B23. Give SUMIF the whole product column as its criteria: =SUMIF(C2:C16,C2:C16,E2:E16). That gives you one product total per row. MAX then picks the biggest one.
Hint 4
B24 needs the name, not the number. Use MATCH to find where the winning total sits in that same list of totals. Then use INDEX on C2:C16 to return the product name.
Build a formula-driven KPI dashboard panel
AdvancedFormula-driven KPI panels let a sales table feed live reporting without any manual recaps. In practice, that means leaders can review revenue trends, order volume, and product performance from one compact section while the source transactions keep changing.
Build the dashboard formulas in B19:B24 using the raw order data in A1:E16. The KPI labels in A19:A24 define the outputs your panel needs to return, and each result should stay linked to the source table rather than being typed manually.
Keep the panel fully dynamic. If values in C2:C16 or E2:E16 change, the metrics in B19:B24 should recalculate automatically, including the product-level winner and its revenue.
For the text result in B24, return the winning product name exactly as it appears in C2:C16. The only valid source labels are Wireless Mouse, USB-C Dock, Monitor Arm, Webcam, Keyboard, and Laptop Stand.
Need some help?
Hint 1
The first four KPIs each come from one column and one function. Only the last two are harder. For those, you first have to add up the rows that belong to each product.
Hint 2
The biggest single order is not the same as the top product. One big sale can still lose to a product that sells well again and again. So add up the revenue per product first. Then compare those totals.
Hint 3
Start with B23. Give SUMIF the whole product column as its criteria: =SUMIF(C2:C16,C2:C16,E2:E16). That gives you one product total per row. MAX then picks the biggest one.
Hint 4
B24 needs the name, not the number. Use MATCH to find where the winning total sits in that same list of totals. Then use INDEX on C2:C16 to return the product name.