Why clean data entry prevents errors in Excel - Dashboard Impact

Dashboard Mode - Why clean data entry prevents errors

Dashboard Goal

This dashboard shows how clean data entry helps prevent errors in sales records and improves accuracy in calculations.

Sample Data

Order ID	Product	Quantity	Price per Unit	Total Sales
1001	Pen	10	1.50	=C2*D2
1002	Notebook	5	3.00	=C3*D3
1003	Pen	abc	1.50	=C4*D4
1004	Marker	7	2.00	=C5*D5
1005	Notebook	3	3.00	=C6*D6
1006	Pen	8	1.50	=C7*D7
1007	Marker	5	2.00	=C8*D8

Note: Row 4 has an invalid quantity entry "abc" to show error from unclean data.

Dashboard Components

• KPI Card: Total Sales
  Formula: =SUM(E2:E8)
  Shows the sum of all total sales, errors in data will affect this.
• KPI Card: Number of Errors
  Formula: =SUMPRODUCT(--ISERROR(E2:E8))
  Counts how many total sales cells have errors due to bad data entry.
• Table: Clean vs Error Rows
  Formula in new column F (Error Check): =IF(ISNUMBER(E2),"Clean","Error")
  Shows which rows have clean data and which have errors.
• Chart: Sales by Product (Clean Data Only)
  Formula for sum per product ignoring errors:
  =SUMIFS(E2:E8,F2:F8,"Clean",B2:B8,"Pen") (similar for other products)
  Visualizes sales only from clean data rows.

Dashboard Layout

+----------------------+----------------------+
|      Total Sales      |    Number of Errors  |
|       (KPI)           |        (KPI)         |
+----------------------+----------------------+
|                                              |
|          Sales by Product Chart               |
|                                              |
+----------------------------------------------+
|                                              |
|          Data Table with Error Check          |
|                                              |
+----------------------------------------------+

Interactivity

Adding a filter for "Product" updates the Sales by Product chart and the Data Table to show only selected products. Errors count and Total Sales update accordingly, showing how errors affect results.

Self Check

If you add a filter to show only "Pen" products, which components update and how?

• The Sales by Product chart updates to show sales only for "Pen" products with clean data.
• The Data Table filters to show only "Pen" rows, highlighting any errors.
• The Total Sales KPI updates to sum only visible rows.
• The Number of Errors KPI updates to count errors only in visible rows.

Key Result

Dashboard demonstrating how clean data entry prevents errors and improves sales calculations.