IFERROR for error handling in Excel - Dashboard Guide

Dashboard Mode - IFERROR for error handling

Goal

Find the average sales per product. If a product has zero sales, show "No Sales" instead of an error.

Sample Data

Product	Sales Q1	Sales Q2	Sales Q3	Sales Q4
Apples	100	150	120	130
Bananas
Cherries	200	180	210	190
Dates	50	60	55	65
Elderberries

Dashboard Components

• KPI Cards: Average sales per product with error handling
• Table: Shows product names and average sales or "No Sales" if all quarters are zero or blank

Formulas Used

=IFERROR(AVERAGE(B2:E2), "No Sales")

Note: This formula is entered in cell F2 and copied down to F6.

Dashboard Layout

+-----------------------------+
|       Average Sales Table    |
| +-------------------------+ |
| | Product | Avg Sales      | |
| |---------|----------------| |
| | Apples  | 125            | |
| | Bananas | No Sales       | |
| | Cherries| 195            | |
| | Dates   | 57.5           | |
| | Elderb. | No Sales       | |
| +-------------------------+ |
+-----------------------------+

Interactivity

Add a filter to select products by name. When a product is selected, the table updates to show only that product's average sales or "No Sales" message.

Self Check

If you add a filter to show only "Bananas" and "Elderberries", what will the average sales column display?

Answer: Both will show "No Sales" because their sales are zero or blank in all quarters.

Key Result

Shows average sales per product with error handling to display 'No Sales' when sales are zero or blank.