3w1h Format In Excel New Site
The game-changer for the 3W1H format in Excel is the arrival of Dynamic Array Functions (FILTER, SORT, UNIQUE).
Instead of manually sorting your Who/What/Why/How, let Excel do the work.
Insert a new sheet and build a simple dashboard using:
Example formula for “Open Why issues”:
=COUNTIFS(Table1[Why],"<>", Table1[Status],"Open")
The core of 3W1H consists of four pillars. Open a new Excel sheet and in the first row (Row 1), type the following headers: 3w1h format in excel new
| Column | Header Name | Description | | :--- | :--- | :--- | | A | Who | The person or team responsible. | | B | What | The task, action item, or objective. | | C | When | The deadline, date, or time frame. | | D | How | The method, process, status, or resources used. |
(Pro Tip: You can add a 5th column for "Status" or "Remarks" if you need to track completion, but strictly 3W1H stops at "How".)
If you want to build a professional "New 3W1H Format" from scratch, use this table structure:
| Column | Name | Data Type | Formula Example (New Excel) |
| :--- | :--- | :--- | :--- |
| A | Who | Dropdown List | =INDIRECT("Table1[Owner]") |
| B | What | Short Text | Manual |
| C | Why (Priority Score) | Number (1-5) | =VLOOKUP(D2, PriorityTable, 2,0) |
| D | How (Method) | Text with Validation | List: Agile, Waterfall, Ad-hoc |
| E | How (Due Date) | Date | =WORKDAY(TODAY(), 14) |
| F | How (Progress %) | Percentage | =MIN(1, (TODAY()-B2)/(E2-B2)) | The game-changer for the 3W1H format in Excel
The "New" secret formula: In column F, use:
=TEXT(IF(F2<=0, "Not started", IF(F2>=1, "Complete", TEXT(F2,"0%"))))
Make the "When" column visually alert you if a deadline is approaching or passed.
Result: Any task with a date in the past will automatically turn red, signaling an overdue item.
Create an Excel Table (Ctrl + T) with these headers: The core of 3W1H consists of four pillars
| ID | Date | Category | What | Why | Where | How | Status | |----|------|----------|------|-----|-------|-----|--------|
New Excel tip: Use =TODAY() in the Date column as a default.
For truly new Excel users, combine 3W1H with Power Query.
Imagine you have three different reports:
New 3W1H Method: