AutoFill and Flash Fill in Excel - Dashboard Guide

Dashboard Mode - AutoFill and Flash Fill

Goal

Help a small business quickly fill in missing data and create patterns in their sales spreadsheet without typing everything manually.

Sample Data

Order ID	Customer Name	Product Code	Product Name	Quantity	Unit Price	Total Price	Order Date
1001	John Smith	P001	Notebook	2	5	10	2024-06-01
1002	Mary Lee	P002	Pen	5	2	10	2024-06-02
1003	Tom Brown			3	3	9	2024-06-03
1004	Anna White	P004	Marker	4	4	16	2024-06-04
1005	David Green			1	10	10	2024-06-05

Dashboard Components

• KPI Card: Total Sales - Formula: =SUM(G2:G6) - Shows total revenue from all orders.
• AutoFill Example: Order ID Series - Drag fill handle from 1001 down to 1005 to fill order IDs automatically.
• Flash Fill Example 1: Fill Product Code - In cell C4, type P003 to fill missing product code for Order ID 1003, then use Flash Fill to fill C6 with P005.
• Flash Fill Example 2: Fill Product Name - In cell D4, type Stapler to fill missing product name for Order ID 1003, then use Flash Fill to fill D6 with Folder.
• Formula for Total Price: =E2*F2 copied down to calculate total price per order.

Dashboard Layout

+----------------------+-------------------------+
|      Total Sales      |     Order ID Series     |
|      (KPI Card)       |     (AutoFill)          |
+----------------------+-------------------------+
|    Product Code       |     Product Name        |
|    (Flash Fill)       |     (Flash Fill)        |
+----------------------+-------------------------+
|       Total Price Formula Calculation          |
+------------------------------------------------+

Interactivity

The user types the first few values manually for missing Product Codes and Names. Then they use Flash Fill to automatically fill the rest based on the pattern. AutoFill is used to quickly fill the Order ID series. The Total Price formula updates automatically when Quantity or Unit Price changes.

Self Check

Add a filter to show only orders with Quantity greater than 3. Which components update?

• Total Sales KPI updates to sum only filtered rows.
• Order ID series remains the same but only visible filtered rows show.
• Product Code and Product Name Flash Fill examples remain but only filtered rows visible.
• Total Price formula recalculates only for visible rows.

Key Result

Dashboard shows how to use AutoFill and Flash Fill to complete missing data and calculate total sales.